mysql 字符列做日期分区_mysql 根据日期字段做表分区

mysql 的表分区有几种类型， list， hash， range等， 今天我们介绍下日期分区， 假如与如下表：

新建mysql分区表CREATE TABLE core(   core_id INT NOT NULL AUTO_INCREMENT,   stamp DATETIME,   data VARCHAR(80),   PRIMARY KEY(core_id,stamp) ) PARTITION BY LIST(TO_DAYS(stamp))(   PARTITION core_20100715 VALUES IN (TO_DAYS('2010-07-15')),   PARTITION core_20100716 VALUES IN (TO_DAYS('2010-07-16')) );

需要注意分区列必须包含在主键中， 否则mysql会报错。

测试分区表

我们向表中插入一条数据：INSERT INTO core (stamp,data) VALUES ('20100715','a')

上面的sql语句应该会顺利执行， 我们插入的日期范围在分区字段指定的范围内。

那么如果我们插入一条数据日期范围不再分区字段范围内，会如何呢？INSERT INTO core (stamp,data) VALUES ('20100717','x')

mysql会报错：Error Code: 1526. Table has no partition for value 734335

我们可以通过mysql的系统表information_schema.PARTITIONS查看分区的信息：SELECT * FROM information_schema.PARTITIONS WHERE TABLE_NAME='core';

sql执行的内容如下：TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PARTITION_NAME SUBPARTITION_NAME PARTITION_ORDINAL_POSITION SUBPARTITION_ORDINAL_POSITION PARTITION_METHOD SUBPARTITION_METHOD PARTITION_EXPRESSION SUBPARTITION_EXPRESSION PARTITION_DESCRIPTION TABLE_ROWS AVG_ROW_LENGTH DATA_LENGTH MAX_DATA_LENGTH INDEX_LENGTH DATA_FREE CREATE_TIME UPDATE_TIME CHECK_TIME CHECKSUM PARTITION_COMMENT NODEGROUP TABLESPACE_NAME NULL test core core_20100715 NULL 1 NULL LIST NULL TO_DAYS(stamp) NULL 734333 0 0 16384 NULL 0 4194304 NULL NULL NULL NULL default NULL NULL test core core_20100716 NULL 2 NULL LIST NULL TO_DAYS(stamp) NULL 734334 0 0 16384 NULL 0 4194304 NULL NULL NULL NULL default NULL

和我们的预期是一致的

可以通过下面的sql语句来添加新的分区, 如下sql给表新添一个分区ALTER TABLE core ADD PARTITION (PARTITION core_20100717 VALUES IN (TO_DAYS('2010-07-17')));

下面的sql语句可以删除一个分区：ALTER TABLE core DROP PARTITION core_20100717;

如果表创建的时候没有创建分区，我们可以通过alter table来给表添上分区设置，如下sqlALTER TABLE core PARTITION BY LIST(TO_DAYS(stamp))(   PARTITION core_20100715 VALUES IN (TO_DAYS('2010-07-15')),   PARTITION core_20100716 VALUES IN (TO_DAYS('2010-07-16')) );

如果要给现有表删除分区，可以通过下面的sql来操作：ALTER TABLE core REMOVE PARTITIONING;

添加和删除分区并不会影响表中的数据， 但是添加分区时分区必须可以覆盖表中现有的数据

如果服务器上有多块磁盘，可以将不同的分区对应文件放到不同的磁盘上

如下sql语句将不同的分区放到了不同的目录上面了ALTER TABLE core PARTITION BY LIST(TO_DAYS(stamp))(   PARTITION p20100715 VALUES IN (TO_DAYS('2010-07-15'))   DATA DIRECTORY '/data/2010-07-15'   INDEX DIRECTORY '/data/2010-07-15',   PARTITION p20100716 VALUES IN (TO_DAYS('2010-07-16'))   DATA DIRECTORY '/data/2010-07-16'   INDEX DIRECTORY '/data/2010-07-16' );

在执行上面的sql时有可能会遇到下面的错误You may get this error: SQL Error (1210): Incorrect arguments to DATA DIRECTORY

出现这个错误可能是因为这个目录已经被其他mysql分区占用了， 分区路径不能是mysql当前的数据路径。 如果给分区指定路径，最好独立于mysql数据文件路径

mysql官方文档，分区资料很全面，可以作为参考

原文链接：http://outofmemory.cn/mysql/mysql-partition-by-date

打开App，阅读手记