存储过程连接

Question

CREATE PROCEDURE concatenation()
      BEGIN
              DECLARE i  INT default 1;
              declare t varchar(50);
              repeat 
              set @t =  concat("INSERT ignore INTO `site_values_" , i , "` (report_time) SELECT  CONCAT(", "pcu_rtc_year" , "-" , "pcu_rtc_month" , "-" , "pcu_rtc_day" , "  " , "pcu_rtc_hour" , ":" , " pcu_rtc_minute" , ":" , " pcu_rtc_secound",")" ,
                   " FROM site_values where site_id =" , i);
    PREPARE stmt FROM @t;
    EXECUTE stmt; 
    DEALLOCATE PREPARE stmt;
    set i = i+1;
    until i =1001
    end repeat;
    END;

我有上面的代码。我可以创建一个过程。但当我叫它调用串联，它显示

"SQLSyntaxError (1064， "SQL语法错误;查看与MySQL服务器版本对应的手册，以便在':pcu_rtc_minute: pcu_rtc_second '附近使用正确的语法')FROM site_values where site_id =1' at line 1")"

我的代码有什么问题?

Answer 1

您不需要在字段名周围加上引号。它应该看起来像这样:

concat("INSERT ignore INTO `site_values_" , i , "` (report_time) SELECT  CONCAT(pcu_rtc_year , "-" , pcu_rtc_month , "-" , pcu_rtc_day , "  " , pcu_rtc_hour, ":" , pcu_rtc_minute, ":" , pcu_rtc_secound) FROM site_values where site_id = " , i);

也因为你使用2 CONCAT语句，引号可能是一个问题。你可以试试这个:

concat("INSERT ignore INTO `site_values_" , i , "` (report_time) SELECT  CONCAT(pcu_rtc_year , '-' , pcu_rtc_month , '-' , pcu_rtc_day , '  ' , pcu_rtc_hour, ':' , pcu_rtc_minute, ':' , pcu_rtc_secound) FROM site_values where site_id = " , i);

Answer 2

您的SQL没有构造语法错误吗?

"...SELECT  CONCAT(", "pcu_rtc_year" , "-" , "pcu_rtc_month" , "-" , "pcu_rtc_day" , "  " , "pcu_rtc_hour" , ":" , " pcu_

将导致

SELECT CONCAT(pcu_rtc_year-pcu_rtc_month-pcu_rtc_day pcu_rtc_hour:pcu...)

你好像少了逗号。

"...SELECT  CONCAT(", "pcu_rtc_year," , "'-'," , "pcu_rtc_month," , "'-'," , "pcu_rtc_day," , "'  '," , "pcu_rtc_hour," , "':'," , " pcu_...)

产生:

SELECT CONCAT(pcu_rtc_year,'-',pcu_rtc_month,'-',pcu_rtc_day,' ',pcu_rtc_hour,':',pcu...)

Answer 3

这里缺少引号，并且在两个concats中混淆了:

set @t =  concat("INSERT ignore INTO `site_values_" , i , "` (report_time)",
     "SELECT CONCAT(pcu_rtc_year, '-', pcu_rtc_month, '-', pcu_rtc_day, '  ',
                    pcu_rtc_hour, ':', pcu_rtc_minute, ':', pcu_rtc_secound)",
     "FROM site_values where site_id =" , i);