我想知道是否可以将参数传递给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 ;更多推荐
将存储过程参数用作DATE
发布评论