mysql 事件按月分表

最新推荐文章于 2024-08-20 02:16:09 发布

weixin_30566111

最新推荐文章于 2024-08-20 02:16:09 发布

阅读量162

点赞数

文章标签：数据库

原文链接：http://www.cnblogs.com/liuxiaoji/p/5091769.html

版权

/****** 对象:  Table Order_201512    脚本日期: 2015/12/18 11:44:23 ******/
/****** 字段数据长度 = 2599 字节 ******/
CREATE TABLE `Order_201512`(
    `OrderID`                          varchar(30)          NOT NULL DEFAULT ''                    COMMENT '订单id',
    `CreateTime`                       datetime             NOT NULL DEFAULT '1900-01-01 00:00:00' COMMENT '创建时间',
    PRIMARY KEY ( `OrderID` ),
    INDEX `IDX_InsuranceProducts201512_1` (`CreateTime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='产品订单:产品订单';


/****** 对象:  Event Event_C_Order_YYYYMM    脚本日期: 2015/12/18 11:44:23 ******/
DELIMITER |
SET GLOBAL event_scheduler = 1;
DROP EVENT IF EXISTS `Event_C_Order_YYYYMM`;
CREATE EVENT `Event_C_Order_YYYYMM`
  ON SCHEDULE EVERY 1 MONTH STARTS '2015-12-27 23:01:01'
  ON COMPLETION NOT PRESERVE
  ENABLE
  Comment '自动创建下个Order_YYYYMM表' DO
BEGIN
SET @NextTailStr = DATE_FORMAT(DATE_ADD(NOW(),INTERVAL 1 MONTH), '%Y%m');
SET @createEventSQL = CONCAT('CREATE TABLE IF NOT EXISTS `Order_',
 CAST(@NextTailStr AS CHAR),'`(
    `OrderID`                          varchar(30)          NOT NULL DEFAULT ''''                    COMMENT ''订单id'',
    `CreateTime`                       datetime             NOT NULL DEFAULT ''1900-01-01 00:00:00'' COMMENT ''创建时间'',
    PRIMARY KEY ( `OrderID` ),
    INDEX `IDX_InsuranceProducts',CAST(@NextTailStr AS CHAR),'_1` (`CreateTime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=''订单'';');
SELECT @createEventSQL;
PREPARE CreateEventStatement FROM @createEventSQL;
EXECUTE CreateEventStatement;
END |
DELIMITER ;