-- 删除存储过程

DROP PROCEDURE IF EXISTS `renew_message_queue`;

-- 添加; 的转义

DELIMITER ;;

CREATE PROCEDURE `renew_message_queue`()

BEGIN

    -- 旧表备份的后缀

    DECLARE old_table_suffix VARCHAR(8) DEFAULT DATE_FORMAT(NOW(), '%Y%m%d');

    -- 事务标记

    DECLARE t_error INTEGER DEFAULT 0;

    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET @t_error=1;

    START TRANSACTION;

        -- 创建表

        CREATE TABLE `ecm_message_queue_new` LIKE `ecm_message_queue`;

        -- 备份旧表

        SET @back_table_sql = CONCAT('ALTER TABLE ecm_message_queue RENAME TO ecm_message_queue_bak_', old_table_suffix);

        -- 执行动态生成的sql语句

        PREPARE temp FROM @back_table_sql;

        EXECUTE temp;

        -- 重命名新表

        ALTER TABLE ecm_message_queue_new RENAME TO ecm_message_queue;

        -- 将未处理的数据迁移到新表

        SET @move_data_sql = CONCAT('INSERT INTO `ecm_message_queue` SELECT * FROM ecm_message_queue_bak_', old_table_suffix, ' WHERE send_status = 0');

        -- 执行动态生成的sql语句

        PREPARE temp FROM @move_data_sql;

        EXECUTE temp;

        IF t_error = 1 THEN

                ROLLBACK;

        ELSE

                COMMIT;

        END IF;

    -- 返回标识位的结果集，可不执行

    -- select t_error;

END;

;;

DELIMITER ;

CALL `renew_message_queue`();