create table TempComments Like dycomments;
上述 SQL语句创建的新表带有原表的所有属性,主键,索引等。
自动分表怎么做呢?
使用上述语句自动创建分表。
那么ID怎么设置呢?
更改表格自增主键的起始值 例如 表格为 xxx_201604 那么将起始值设为201604000000000000000
//具体操作
1.创建模板表格,如上述Sql语句
2.设置Mysql 允许执行事件
SET GLOBAL event_scheduler = 1;
3.创建事件,自动分表
CREATE DEFINER=`root`@`%` EVENT `Event_SpliteTable_Comments`
ON SCHEDULE
EVERY 1 MINUTE STARTS '2019-06-01'
ON COMPLETION PRESERVE
ENABLE
COMMENT '自动创建下个dycomments_YYYYMM表'
DO BEGIN
SET @NextTailStr = DATE_FORMAT(DATE_ADD(NOW(),INTERVAL 1 MONTH), '%Y%m');
-- create table TempComments Like dycomments;
SET @createEventSQL = CONCAT('create table dycomments_' ,CAST(@NextTailStr AS CHAR),' Like TempComments;');
-- ALTER TABLE person201604211757 AUTO_INCREMENT=201604000000000000000;
SET @alterEventSql = CONCAT('ALTER TABLE dycomments_' ,CAST(@NextTailStr AS CHAR),' AUTO_INCREMENT = ',CAST(@NextTailStr AS CHAR),'000000000000000;');
SELECT @createEventSQL;
PREPARE CreateEventStatement FROM @createEventSQL;
EXECUTE CreateEventStatement;
SELECT @alterEventSql;
PREPARE AlterEventStatement FROM @createEventSQL;
EXECUTE AlterEventStatement;
END