mysql存储过程备份表

最新推荐文章于 2022-07-01 14:24:37 发布

伊见钟卿

最新推荐文章于 2022-07-01 14:24:37 发布

阅读量296

点赞数

分类专栏： mysql

本文链接：https://blog.csdn.net/TylorMin/article/details/105373635

版权

mysql 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

CREATE DEFINER=`root`@`%` PROCEDURE `ChintBarcode_Backup`()
BEGIN
   SET @backupMonth = DATE_FORMAT(date_sub(curdate(),interval 4 MONTH),'%Y%m');
SET @tableName = CONCAT("chint_barcode_" ,DATE_FORMAT(curdate(),'%Y'));

   -- 动态创建表
   SET @createSql = CONCAT('create table IF NOT EXISTS ',@tableName,"
       (
           `cod` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
           `barcodeType` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
           `boxBarcode` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
           `clerk` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
           `containBarcode` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
           `invalidDate` datetime(6) NULL DEFAULT NULL,
           `invalider` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
           `isInvalid` int(11) NULL DEFAULT 0,
           `jetCode` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
           `madeDate` datetime(6) NULL DEFAULT NULL,
           `pONo` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
           `printCount` int(11) NULL DEFAULT NULL,
           `printDate` datetime(6) NULL DEFAULT NULL,
           `printID` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
           `ProductCode` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
           `ProductDesc` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
           `quantity` int(11) NULL DEFAULT NULL,
           `sortSeq` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
           `unit` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
           `dimension` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
           PRIMARY KEY (`cod`) USING BTREE,
           INDEX `cBarcode`(`containBarcode`) USING BTREE,
           INDEX `pONo`(`pONo`) USING BTREE,
           INDEX `boxBarcode`(`boxBarcode`) USING BTREE
       ) ENGINE = InnoDB
   ");
   PREPARE createSql FROM @createSql;
EXECUTE createSql;
   -- 动态创建表

   -- 插入数据
SET @insertSql = CONCAT('INSERT INTO ',@tableName,"
SELECT * from chint_barcode where DATE_FORMAT(madeDate,'%Y%m')=",
@backupMonth);
PREPARE insertSql FROM @insertSql;
EXECUTE insertSql;
-- 插入数据

   -- 删除数据
       SET @deleteSql = CONCAT("DELETE FROM chint_barcode where DATE_FORMAT(madeDate,'%Y%m')=",@backupMonth);
       PREPARE deleteSql FROM @deleteSql;
       EXECUTE deleteSql;
   -- 删除数据