在mysql中,可以利用alter語句來配合“DROP PARTITION”來刪除分區,“DROP PARTITION”的作用就是用於刪除指定的分區,語法為“ALTER TABLE '表名' DROP PARTITION '分區名' 」。
本教學操作環境:windows10系統、mysql8.0.22版本、Dell G3電腦。
刪除分區語法為:ALTER TABLE '表名' DROP PARTITION '分區名'
##清理分區資料為空,保留分區不刪除,只是清理數據,命令如下alter table bm_scenes_data_reminder truncate partition p20210104;
alter table bm_scenes_data_reminder drop partition p20210104;
ALTER TABLE bm_scenes_data_reminder ADD PARTITION (PARTITION p20210104 VALUES LESS THAN (738159) ENGINE = InnoDB);
mysql> ALTER TABLE bm_scenes_data_reminder ADD PARTITION (PARTITION p20210104 VALUES LESS THAN (738159) ENGINE = InnoDB); ERROR 1481 (HY000): MAXVALUE can only be used in last partition definition mysql>
ALTER TABLE bm_scenes_data_reminder drop PARTITION p20210105; ALTER TABLE bm_scenes_data_reminder drop PARTITION p20210106; ALTER TABLE bm_scenes_data_reminder drop PARTITION p20210107; ALTER TABLE bm_scenes_data_reminder drop PARTITION p20210108; ALTER TABLE bm_scenes_data_reminder drop PARTITION p20210109; ALTER TABLE bm_scenes_data_reminder drop PARTITION p20210110; ALTER TABLE bm_scenes_data_reminder drop PARTITION future;
ALTER TABLE bm_scenes_data_reminder ADD PARTITION (PARTITION p20210104 VALUES LESS THAN (738159) ENGINE = InnoDB);
ALTER TABLE bm_scenes_data_reminder ADD PARTITION (PARTITION p20210105 VALUES LESS THAN (738160) ENGINE = InnoDB); ALTER TABLE bm_scenes_data_reminder ADD PARTITION (PARTITION p20210106 VALUES LESS THAN (738161) ENGINE = InnoDB); ALTER TABLE bm_scenes_data_reminder ADD PARTITION (PARTITION p20210107 VALUES LESS THAN (738162) ENGINE = InnoDB); ALTER TABLE bm_scenes_data_reminder ADD PARTITION (PARTITION p20210108 VALUES LESS THAN (738163) ENGINE = InnoDB); ALTER TABLE bm_scenes_data_reminder ADD PARTITION (PARTITION p20210109 VALUES LESS THAN (738164) ENGINE = InnoDB); ALTER TABLE bm_scenes_data_reminder ADD PARTITION (PARTITION p20210110 VALUES LESS THAN (738165) ENGINE = InnoDB); ALTER TABLE bm_scenes_data_reminder ADD PARTITION (PARTITION future VALUES LESS THAN MAXVALUE ENGINE = InnoDB);
CREATE TABLE `sys_log` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `begin_time` datetime DEFAULT NULL COMMENT '开始时间', `end_time` datetime DEFAULT NULL COMMENT '结束时间', `spend_mills` int(11) DEFAULT NULL COMMENT '运行时长,单位ms', `username` varchar(100) DEFAULT NULL COMMENT '用户id', `log_status` int(11) NOT NULL DEFAULT '0' COMMENT '运行状态,[0]成功[1]失败', `code` int(11) NOT NULL DEFAULT '0' COMMENT '错误码', `remote_addr` varchar(50) DEFAULT '' COMMENT '远程地址', `request_uri` varchar(255) DEFAULT NULL COMMENT '请求路径', `user_agent` text COMMENT '用户代理', `req_data` text NOT NULL COMMENT '请求参数', `resp_data` longtext NOT NULL COMMENT '返回结果', PRIMARY KEY (`id`) USING BTREE, KEY `idx_log_begintime` (`begin_time`) USING BTREE COMMENT '系统日志的beginTime字段索引' ) ENGINE=Innodb DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC
ALTER TABLE `sys_log` DROP PRIMARY KEY, ADD PRIMARY KEY(`id`,`begin_time`); alter table sys_log partition by RANGE (to_days(begin_time)) ( PARTITION p20210816 VALUES LESS THAN (738383), PARTITION future VALUES LESS THAN MAXVALUE );
ALTER TABLE sys_log drop PARTITION future; ALTER TABLE sys_log ADD PARTITION (PARTITION p20210817 VALUES LESS THAN (738384) ENGINE = InnoDB); ALTER TABLE sys_log ADD PARTITION (PARTITION p20210818 VALUES LESS THAN (738385) ENGINE = InnoDB); ALTER TABLE sys_log ADD PARTITION (PARTITION p20210819 VALUES LESS THAN (738386) ENGINE = InnoDB); ALTER TABLE sys_log ADD PARTITION (PARTITION future VALUES LESS THAN MAXVALUE ENGINE = InnoDB);
30 4 * * * /bin/python /home/testuser/SyslogPartitionClear.py >/dev/null 2>&1
以上是mysql怎麼刪除分割區的詳細內容。更多資訊請關注PHP中文網其他相關文章!