我写了一个mysql程序，语法有错误，找不出正确的语法

7cjasjjr 于 2021-06-25 发布在 Mysql

关注(0)|答案(3)|浏览(297)

mysql过程将
1如果表1不存在，则创建类似于表1的表1\u存档。
2.根据日期条件将表1中的值插入表1\存档
三。从表1中删除这些记录。
代码链接：https://paiza.io/projects/eq7i5ygo-lt7_gu8wpqndg?language=mysql

CREATE PROCEDURE ARCHIVE_EVENTS ( IN f_table VARCHAR(255),
                                IN t_table VARCHAR(255),
                                IN t_ts TIMESTAMP)
BEGIN
    DECLARE c_sql VARCHAR(255);
     DECLARE i_sql VARCHAR(255);
     DECLARE d_sql VARCHAR(255);    

SET @c_sql = CONCAT(' CREATE TABLE IF NOT EXISTS ', @t_table , ' LIKE ', @f_table );
PREPARE stmt FROM  @c_sql;
EXECUTE stmt ;

SET @i_sql = CONCAT(' INSERT INTO ', @t_table, ' SELECT * FROM ', @f_table, ' WHERE `event_date` <=  ', @t_ts);
PREPARE stmt FROM  @i_sql;
EXECUTE stmt ;

COMMIT;

SET @d_sql = CONCAT(' DELETE FROM ', @f_table, ' WHERE `event_date` <= ', @t_ts);
PREPARE stmt FROM  @d_sql;
EXECUTE stmt ;
COMMIT;
END;

CALL ARCHIVE_EVENTS ('TABLE1', 'TABLE1_ARCHIVE', now());

进行更改后，过程如下所示，但仍不起作用：
我得到的错误是
第2行出现错误1064（42000）：sql语法有错误；请查看与mysql服务器版本相对应的手册，以获取第3行“”附近要使用的正确语法

CREATE PROCEDURE ARCHIVE_EVENTS (IN f_table VARCHAR(255),IN t_table VARCHAR(255),IN t_ts TIMESTAMP)
BEGIN
    DECLARE c_sql VARCHAR(255);
    DECLARE i_sql VARCHAR(255);
    DECLARE d_sql VARCHAR(255);    

    SET c_sql = CONCAT(' CREATE TABLE IF NOT EXISTS ', t_table , ' LIKE ', f_table);
    PREPARE stmt FROM  c_sql;
    EXECUTE stmt ;

    SET i_sql = CONCAT(' INSERT INTO ', t_table, ' SELECT * FROM ', f_table, ' WHERE `event_date` <= ', t_ts);
    PREPARE stmt FROM  i_sql;
    EXECUTE stmt ;

    COMMIT;

    SET d_sql = CONCAT(' DELETE FROM ', f_table, ' WHERE `event_date` <= ', t_ts);
    PREPARE stmt FROM  d_sql;
    EXECUTE stmt ;
    COMMIT;
END;

CALL ARCHIVE_EVENTS ('TEST', 'TEST_ARCHIVE', now());

mysql stored-procedures Procedures

来源：https://stackoverflow.com/questions/50255283/i-have-written-a-mysql-procedure-and-there-is-mistake-in-syntax-cant-figure-ou

3条答案

按热度按时间

vfh0ocws1#

CREATE PROCEDURE ARCHIVE_EVENTS (IN f_table CHAR(100),IN t_table 
CHAR(100),IN t_ts TIMESTAMP)

BEGIN

SET @c_sql = CONCAT(' CREATE TABLE IF NOT EXISTS ', t_table , ' LIKE ', f_table);

SET @i_sql = CONCAT(' INSERT INTO ', t_table, ' SELECT * FROM ', f_table, ' WHERE `event_date` <= ',char(39), t_ts,char(39));

SET @d_sql = CONCAT(' DELETE FROM ', f_table, ' WHERE `event_date` <= ',char(39), t_ts,char(39));

PREPARE stmt FROM  @c_sql;
PREPARE stmt1 FROM  @i_sql;
PREPARE stmt2 FROM  @d_sql;

EXECUTE stmt ;
EXECUTE stmt1 ;
EXECUTE stmt2 ;
COMMIT;

END;

这是工作代码，感谢以上的答案。这是对我有效的查询的最终解决方案。

赞(0）回复(0）举报 2021-06-25

ht4b089n2#

DROP PROCEDURE IF EXISTS ARCHIVE_EVENTS;
CREATE PROCEDURE ARCHIVE_EVENTS (IN f_table CHAR(100),IN t_table 
CHAR(100),IN t_ts TIMESTAMP)
READS SQL DATA
COMMENT 'Test'
BEGIN

SET @c_sql = CONCAT(' CREATE TABLE IF NOT EXISTS ', t_table , ' LIKE ', f_table);
PREPARE stmt FROM  @c_sql;
EXECUTE stmt ;

SET @i_sql = CONCAT(' INSERT INTO ', t_table, ' SELECT * FROM ', f_table, ' WHERE `dateTime` <= ', DATE(t_ts));
PREPARE stmt1 FROM  @i_sql;
EXECUTE stmt1 ;

COMMIT;

SET @d_sql = CONCAT(' DELETE FROM ', f_table, ' WHERE `dateTime` <= ', DATE(t_ts));
PREPARE stmt2 FROM  @d_sql;
EXECUTE stmt2 ;
COMMIT;
END;

CALL ARCHIVE_EVENTS ('TEST', 'WS_REL_TEST', now());

赞(0）回复(0）举报 2021-06-25

ycggw6v23#

调试这样的过程并不困难，只需放入几个select语句。注意@variables必须在准备好的语句中使用，并且您没有引用日期，可能还没有设置分隔符。

drop procedure if exists p;
    delimiter $$

    CREATE PROCEDURE p (IN f_table VARCHAR(255),IN t_table VARCHAR(255),IN t_ts TIMESTAMP)
    BEGIN

    SET @c_sql = (select CONCAT(' CREATE TABLE IF NOT EXISTS ', t_table , ' LIKE ', f_table));
    select @c_sql;

    /*PREPARE stmt FROM  @c_sql;
        EXECUTE stmt ;
         deallocate prepare stmt;
    */   
    SET @i_sql = (select CONCAT(' INSERT INTO ', t_table, ' SELECT * FROM ', f_table, ' WHERE `event_date` <= ',char(39), t_ts,char(39)));
    select @i_sql;
    /*
    PREPARE stmt FROM  @i_sql;
    EXECUTE stmt ;
    deallocate prepare stmt;

    COMMIT;
    */
    SET @d_sql = (select CONCAT(' DELETE FROM ', f_table, ' WHERE `event_date` <= ', char(39),t_ts, char(39)));
    select @d_sql;
    /*
    PREPARE stmt FROM  @d_sql;
    EXECUTE stmt ;
    deallocate prepare stmt;
    COMMIT;
    */
END $$
delimiter ;
drop table users_copy;
CALL p('users', 'users_copy', now());

Yield these statements
+---------------------------------------------------+
| @c_sql                                            |
+---------------------------------------------------+
|  CREATE TABLE IF NOT EXISTS users_copy LIKE users |
+---------------------------------------------------+
1 row in set (0.00 sec)

+-----------------------------------------------------------------------------------------+
| @i_sql                                                                                  |
+-----------------------------------------------------------------------------------------+
|  INSERT INTO users_copy SELECT * FROM users WHERE `event_date` <= '2018-05-09 19:36:54' |
+-----------------------------------------------------------------------------------------+
1 row in set (0.02 sec)

+----------------------------------------------------------------+
| @d_sql                                                         |
+----------------------------------------------------------------+
|  DELETE FROM users WHERE `event_date` <= '2018-05-09 19:36:54' |
+----------------------------------------------------------------+
1 row in set (0.04 sec)

如果您愿意，您可以测试它们中的每一个，或者取消对准备好的语句的注解，然后让rip。

赞(0）回复(0）举报 2021-06-25

我来回答

我写了一个mysql程序，语法有错误，找不出正确的语法

3条答案

相关问题

热门标签

最新问答