msyql分区命令,删除命令
-
建表建分区
CREATE TABLE `db`.`table_new` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增id', `time` datetime NOT NULL COMMENT '时间', `clusterid` varchar(255) COLLATE utf8mb4_bin NOT NULL COMMENT '集群id', PRIMARY KEY (`id`,`time`), KEY `cluster_time_index` (`clusterid`,`time`), KEY `time_index` (`time`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='table_new表' /*!50500 PARTITION BY RANGE COLUMNS(`time`) (PARTITION p0001 VALUES LESS THAN ("2020-08-15 00:00:00") ENGINE = InnoDB, PARTITION p0002 VALUES LESS THAN ("2020-08-30 00:00:00") ENGINE = InnoDB, PARTITION p0003 VALUES LESS THAN ("2020-09-15 00:00:00") ENGINE = InnoDB, PARTITION p9999 VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB) */;
-
删除分区
alter table titles drop partition p0;
-
有表 新增分区
ALTER TABLE db.table_name PARTITION BY RANGE COLUMNS(id) ( PARTITION p0001 VALUES LESS THAN (10), PARTITION p9999 VALUES LESS THAN (MAXVALUE) );
-
执行 create table table_name_new as select * from table_name 报错Statement violates GTID consistency 【参考】
create table xxx as select 的方式拆分成两部分。
create table xxxx like data_mgr;
insert into xxxx select * from data_mgr; -
drop delete truncate区别
drop 删除表 删除数据 无法回滚 会释放空间
truncate 不删除表 删除数据 无法回滚 会释放空间
delete 不删除表 删除指定数据