将存储过程参数用作DATE_SUB的单位参数 [英] Use a stored procedure parameter for unit parameter of 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 ;

这篇关于将存储过程参数用作DATE_SUB的单位参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！