MySQL 5.5 range分区增添删除处置惩罚实例详解【MySQL教程】,MySQL,range,删除

本文主要给人人引见了关于MySQL 5.5 range分区增添删除处置惩罚的相干材料，文中经由过程示例代码引见的异常细致，对人人具有肯定的参考进修代价，须要的朋友们下面来一同看看吧。

引见

RANGE分区基于一个给定的一连区间局限，初期版本RANGE主如果基于整数的分区。在5.7版本中DATE、DATETIME列也能够运用RANGE分区，同时在5.5以上的版本供应了基于非整形的RANGE COLUMN分区。RANGE分区必需的一连的且不能堆叠。运用

“VALUES LESS THAN ()” 来定义分区区间,非整形的局限值须要运用单引号，而且能够运用MAXVALUE作为分区的最高值。

本文将给人人引见MySQL 5.5 range分区增添删除处置惩罚的相干内容，分享给人人供人人参考进修，下面来看看细致的引见：

一、删除分区

##检察要处置惩罚的分区的数据量，并导出作为备份

mysql> select count(*) from baby_account_change_log where updated_time >'2016-12-01 00:00:00' and updated_time <'2017-01-01 00:00:00';
+----------+
| count(*) |
+----------+
| 66252 | 
+----------+
1 row in set (0.23 sec)

##导出备份

mysql> select * into outfile '/tmp/baby_account_change_log_p1.sql' from baby_account_change_log where updated_time >'2016-12-01 00:00:00' and updated_time <'2017-01-01 00:00:00' limit 100000000000;
Query OK, 66252 rows affected (2.71 sec)


##确认要处置惩罚分区

mysql> explain partitions select count(*) from baby_account_change_log where updated_time >'2016-12-01 00:00:00' and updated_time <'2017-01-01 00:00:00';

+----+-------------+-------------------------------+------------+-------+---------------+---------+---------+------+-------+--------------------------+
| id | select_type | table       | partitions | type | possible_keys | key  | key_len | ref | rows | Extra     |
+----+-------------+-------------------------------+------------+-------+---------------+---------+---------+------+-------+--------------------------+
| 1 | SIMPLE  | baby_account_change_log | p1   | index | NULL   | PRIMARY | 8  | NULL | 66252 | Using where; Using index | 
+----+-------------+-------------------------------+------------+-------+---------------+---------+---------+------+-------+--------------------------+

##删除分区

mysql> alter table baby_account_change_log drop partition p0;
Query OK, 0 rows affected (0.01 sec)

二、增添分区

#毛病提醒删除存储最大值分区
mysql> alter table baby_account_change_log add partition(PARTITION p13 VALUES LESS THAN (unix_timestamp('2017-12-31 23:59:59')));
ERROR 1481 (HY000): MAXVALUE can only be used in last partition definition

#删除存储最大值分区
mysql> alter table baby_account_change_log drop partition p12;

##增添新的分区

mysql> alter table baby_account_change_log add partition(PARTITION p12 VALUES LESS THAN (unix_timestamp('2017-12-31 23:59:59')));

相干引荐:

mysql分区之range分区的细致引见

mysql分区治理-range分区_MySQL

MySQLrange分区（二）_MySQL

以上就是MySQL 5.5 range分区增添删除处置惩罚实例详解的细致内容，更多请关注ki4网别的相干文章！