msyql分区命令,删除命令

  1. 建表建分区

         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) */;
    
  2. 删除分区

          alter table titles drop partition p0;
    
  3. 有表 新增分区

         ALTER TABLE db.table_name
         PARTITION BY RANGE COLUMNS(id)
         (
         PARTITION p0001 VALUES LESS THAN (10),
         PARTITION p9999 VALUES LESS THAN (MAXVALUE)
         );
    
  4. 执行 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;

  5. drop delete truncate区别

    drop 删除表 删除数据 无法回滚 会释放空间
    truncate 不删除表 删除数据 无法回滚 会释放空间
    delete 不删除表 删除指定数据

posted @ 2020-12-03 17:17  CalronLoveRonnie  阅读(108)  评论(0编辑  收藏  举报
AmazingCounters.com