mysql - 使用动态查询运行存储过程时出现错误

Question

我正在调用下面的存储过程，其中表名称根据当前日期生成:

    DELIMITER $$
USE `test`$$
DROP PROCEDURE IF EXISTS `sp_pickalertstatus`$$
CREATE PROCEDURE `sp_pickalertstatus`()
BEGIN
    DECLARE msgID  BIGINT (10);
    DECLARE tbl VARCHAR(100);
    SET msgID=(SELECT alertID FROM tbl_intofone_alert_transaction WHERE 
    deliverystatus LIKE '22%' LIMIT 1); 
    IF(msgID IS NOT NULL)THEN
        SET tbl=(SELECT CONCAT('history.tbl_',DATE_FORMAT(NOW(),"%y%m%d")));
        INSERT INTO tbl (MEMBERID,SOURCE,MOBILE,SMPPID,PRIORITY,SMSID
        ,SMSCONTENTTYPE,MESSAGE,REQUESTDATETIME,DR_GROUPID,dr_service_type,
        dr_celleb_msg_id,dr_submit_date,dr_done_date,dr_stat,dr_err,dr_smppid)
        SELECT memberid,SenderID,MSISDN,SMPPID,'1',alertID,'TXT',Message,
        updatetime,'0','PIN','0',DATE_FORMAT(InTime,'ymdHis'),
        DATE_FORMAT(updatetime,'ymdHis'),'DELIVRD',
        '000','03' FROM tbl_intofone_alert_transaction WHERE alertID=msgID;         
    END IF; 
    END$$
DELIMITER ;

我收到错误“错误代码:1146 表'test.tbl'不存在”。我在这里做错了什么？

建议使用准备好的语句执行此操作后，我更改了上面的代码:

DELIMITER $$

USE `test`$$

DROP PROCEDURE IF EXISTS `sp_pickalertstatus22`$$

CREATE PROCEDURE `sp_pickalertstatus22`()
BEGIN
    DECLARE msgID  BIGINT (10);
    DECLARE tbl VARCHAR(100);
    SET msgID=(SELECT alertID FROM tbl_intofone_alert_transaction WHERE deliverystatus LIKE '22%' LIMIT 1); 
    IF(msgID IS NOT NULL)THEN
        SET tbl=(SELECT CONCAT('history.tbl_',DATE_FORMAT(NOW(),"%y%m%d")));
        SET @sl=CONCAT('INSERT INTO ?',' (MEMBERID,SOURCE,MOBILE,SMPPID,PRIORITY,SMSID,SMSCONTENTTYPE,MESSAGE,REQUESTDATETIME,
        DR_GROUPID,dr_service_type,dr_celleb_msg_id,dr_submit_date,dr_done_date,dr_stat,dr_err,dr_smppid)
            SELECT memberid,SenderID,MSISDN,SMPPID,?,alertID,?,Message,updatetime,?,?,?,DATE_FORMAT(InTime,?),
            DATE_FORMAT(updatetime,?),?,?,? FROM tbl_intofone_alert_transaction WHERE alertID=?');
        PREPARE stmt FROM @sl;
        SET @tbl=tbl;
        SET @pr='06';
        SET @txt='TXT';
        SET @grp='0';
        SET @srtyp='PIN';
        SET @clmsg='0';
        SET @intm='%y%m%d%H%i%s';
        SET @outm='%y%m%d%H%i%s';
        SET @drst='DELIVRD';
        SET @drerr='000';
        SET @drsmp='03';
        SET @msg=msgID;

        EXECUTE stmt USING @tbl,@pr,@txt,@grp,@srtyp,@clmsg,@intm,@outm,@drst,@drerr,@drsmp,@msg;       
    END IF; 
    END$$
DELIMITER ;

但它仍然不起作用？并收到错误

  Error Code : 1064
    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 '? MEMBERID,SOURCE,MOBILE,SMPPID,PRIORITY,SMSID,
SMSCONTENTTYPE,MESSAGE,REQUESTDA' at line 1 

使用查询字符串执行准备语句时:

    EXECUTE stmt USING '06','TXT','0','PIN','0','%y%m%d%H%i%s'
,'%y%m%d%H%i%s','DELIVRD','000','03';

出现错误:

Error Code : 1064
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 ''06','TXT','0','PIN','0','%y%m%d%H%i%s','%y%m%d%H%i%s','DELIVRD',
'000','03';' at line 25

Answer 1

鉴于您的更新:不要对 tbl 名称使用占位符，而是像我一样在查询字符串中使用占位符，以避免 附近出现语法错误？ (。请注意字段名称之前的 ?？它不会被 @tbl 替换。
还要松开 @ 符号。您正在创建 session 变量(在整个 session 期间保留在内存中)，这仅在需要时才有用。在存储过程中，它们是无关紧要的。过程返回后，它使用的变量可以而且应该被 GC 回收。

使用准备好的语句:

SET tbl=(SELECT CONCAT('history.tbl_',DATE_FORMAT(NOW(),"%y%m%d"))); -- set tbl as you normally would
PREPARE stmt FROM
CONCAT('INSERT INTO ', tbl, -- tbl is concatenated here
       ' (MEMBERID,SOURCE,MOBILE,SMPPID,PRIORITY,SMSID
        ,SMSCONTENTTYPE,MESSAGE,REQUESTDATETIME,DR_GROUPID,dr_service_type,
        dr_celleb_msg_id,dr_submit_date,dr_done_date,dr_stat,dr_err,dr_smppid)
        SELECT memberid,SenderID,MSISDN,SMPPID,?,alertID,?,Message,
        updatetime,?,?,?,DATE_FORMAT(InTime,?),
        DATE_FORMAT(updatetime,?),?,
        ?,?
        FROM tbl_intofone_alert_transaction WHERE alertID=msgID;');

EXECUTE stmt USING '1', 'TXT', '0', 'PIN', '0', 'ymdHis', 'ymdHis', 'DELIVRD','000', '03';

类似的东西应该可以解决问题，More info here

解决此问题的另一种方法仍然使用准备好的语句，可以删除占位符:

CONCAT('INSERT INTO ', tbl,
       ' (MEMBERID,SOURCE,MOBILE,SMPPID,PRIORITY,SMSID
           ,SMSCONTENTTYPE,MESSAGE,REQUESTDATETIME,DR_GROUPID,dr_service_type,
          dr_celleb_msg_id,dr_submit_date,dr_done_date,dr_stat,dr_err,dr_smppid)
          SELECT memberid,SenderID,MSISDN,SMPPID,\'1\',alertID,\'TXT\',Message,
          updatetime,\'0\',\'PIN\',\'0\',DATE_FORMAT(InTime,\'ymdHis\'),
         DATE_FORMAT(updatetime,\'ymdHis\'),\'DELIVRD\',
        \'000\',\'03\' FROM tbl_intofone_alert_transaction WHERE alertID=msgID');

只需转义引号，或使用双引号分隔连接字符串。
但不确定 DATE_FORMAT 格式...不应该是 '%Y%d%m' 吗？