将存储过程参数用作DATE

本文介绍了将存储过程参数用作DATE_SUB的单位参数的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我想知道是否可以将参数传递给mysql存储过程并将此参数用作DATE_SUB函数的 unit 参数.看来 unit 参数是保留字，所以我不知道unit是否有类型.

I would like to know if it's possible to pass a parameter to a mysql stored procedure and use this parameter as the unit parameter of the DATE_SUB function. It seems that the unit parameter is a reserved word so I don't know if there's a type for unit.

我正在尝试做的事:

DELIMITER $$ DROP PROCEDURE IF EXISTS `test` $$ CREATE DEFINER=`root`@`%` PROCEDURE `test`(param1 unit) BEGIN select DATE_SUB(now(), INTERVAL 1 param1); END $$ DELIMITER ;

更新1 我尝试了prepare语句，可以创建存储的proc.

UPDATE 1 I tried with a prepare statement, I'm able to create the stored proc.

DELIMITER $$ DROP PROCEDURE IF EXISTS `test` $$ CREATE DEFINER=`root`@`%` PROCEDURE `test`(param1 varchar(10)) BEGIN PREPARE stmt FROM 'SELECT DATE_SUB(now(), INTERVAL 1 ?)'; EXECUTE stmt USING @param1; END $$ DELIMITER ;

当我执行它时: 通话测试('WEEK'); 您的SQL语法有误；请查看与您的MySQL服务器版本相对应的手册，以在第1行的'?)'附近使用正确的语法

When I executed it : CALL test('WEEK'); You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?)' at line 1

推荐答案

无论有没有预备语句，在存储过程中都无法做到.

What you are trying to do is not possible in a stored procedure, with or without a prepared statement.

您可以执行类似的操作:

You could do something similar like this:

DELIMITER $$ DROP PROCEDURE IF EXISTS test_proc $$ CREATE PROCEDURE test_proc(param1 varchar(20)) BEGIN CASE UPPER(param1) WHEN 'MICROSECOND' THEN select DATE_SUB(now(), INTERVAL 1 MICROSECOND); WHEN 'SECOND' THEN select DATE_SUB(now(), INTERVAL 1 SECOND); WHEN 'MINUTE' THEN select DATE_SUB(now(), INTERVAL 1 MINUTE); WHEN 'HOUR' THEN select DATE_SUB(now(), INTERVAL 1 HOUR); WHEN 'DAY' THEN select DATE_SUB(now(), INTERVAL 1 DAY); WHEN 'WEEK' THEN select DATE_SUB(now(), INTERVAL 1 WEEK); WHEN 'MONTH' THEN select DATE_SUB(now(), INTERVAL 1 MONTH); WHEN 'QUARTER' THEN select DATE_SUB(now(), INTERVAL 1 QUARTER); WHEN 'YEAR' THEN select DATE_SUB(now(), INTERVAL 1 YEAR); ELSE select 'UNEXPECTED UNIT'; END CASE; END $$ DELIMITER ;