mysql表分区的限制

头一次使用mysql表分区，遇到不少的问题，现在总结下遇到的问题和解决方案。

 

1、如果分区值类型不是整型值，会出现如下错误：

[Err] 1697 - VALUES value for partition 'p0' must have type INT

分区值必须为整型值。例如下方的建表就会出这样的错误：

create table rms (d date)
   partition by range (d)
   (partition p0 VALUES LESS THAN ('1995-01-01'),
    partition p1 VALUES LESS THAN ('2010-01-01'));

类似的解决方案，改成下方的样子就可以通过：

create table rms (d TIMESTAMP)
   partition by range (UNIX_TIMESTAMP(d))
   (partition p0 VALUES LESS THAN (UNIX_TIMESTAMP('1995-01-01')),
    partition p1 VALUES LESS THAN (UNIX_TIMESTAMP('2010-01-01')));

 

2、如果上方的错误示例，改成如下：

create table rms (d DATE)
partition by range (UNIX_TIMESTAMP(d))
   (partition p0 VALUES LESS THAN (UNIX_TIMESTAMP('1995-01-01')),
    partition p1 VALUES LESS THAN (UNIX_TIMESTAMP('2010-01-01')));

就会出现如下错误：

[Err] 1486 - Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed

这是因为UNIX_TIMESTAMP函数在转换DATE类型值时，需要考虑时区问题。以上错误信息的意思就是：常量、随机值和时区相关的表达式不能用于分区表达式中。

可以反过来考虑，假如mysql所在的系统今天如果更改了当前时区，那就会有可能出现更改前与更改后，同一日期的数据存储在不同分区的现象了。常量与随机值也同样如此，可能会因不同环境和时间出现不同的值。

如果真要存储DATE或DATETIME类型，那么可以改成以下解决方案：

create table rms (d DATE)
partition by range (to_days(d))
   (partition p0 VALUES LESS THAN (to_days('1995-01-01')),
    partition p1 VALUES LESS THAN (to_days('2010-01-01')));

 

3、如果主键或唯一索引键没包含分区使用的键，比如：

CREATE TABLE T1 (
     id int(8) NOT NULL AUTO_INCREMENT,
     createtime datetime NOT NULL,
      PRIMARY KEY (id)
)
PARTITION BY RANGE(TO_DAYS (createtime))
(
PARTITION p0 VALUES LESS THAN (TO_DAYS('2010-04-15')),
PARTITION p1 VALUES LESS THAN (TO_DAYS('2010-05-01')),
PARTITION p2 VALUES LESS THAN (TO_DAYS('2010-05-15'))
);

就会出错：[Err] 1503 - A PRIMARY KEY must include all columns in the table's partitioning function

意思是主键必须包括所有分区函数中所有字段。

不仅是主键，连唯一索引键都会如此。以下建表SQL都会出错：

CREATE TABLE t1 (
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    UNIQUE KEY (col1, col2)
)
PARTITION BY HASH(col3)
PARTITIONS 4;

CREATE TABLE t2 (
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    UNIQUE KEY (col1),
    UNIQUE KEY (col3)
)
PARTITION BY HASH(col1 + col3)
PARTITIONS 4;

CREATE TABLE t3 (
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    UNIQUE KEY (col1, col2),
    UNIQUE KEY (col3)
     )
    PARTITION BY HASH(col1 + col3)
    PARTITIONS 4;

 

改成以下方式则可通过：

CREATE TABLE t1 (
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    UNIQUE KEY (col1, col2, col3)
)
PARTITION BY HASH(col3)
PARTITIONS 4;

CREATE TABLE t2 (
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    UNIQUE KEY (col1, col3)
)
PARTITION BY HASH(col1 + col3)
PARTITIONS 4;

 

 

 4、另外，如果使用一些强制转换函数，分区不支持：

CREATE TABLE part_date1
          (  c1 int default NULL,
      c2 varchar(30) default NULL,
      c3 date default NULL) engine=myisam
          partition by range (cast(date_format(c3,'%Y%m%d') as int))
     (PARTITION p0 VALUES LESS THAN (19950101),
     PARTITION p1 VALUES LESS THAN (19960101) ,
     PARTITION p2 VALUES LESS THAN (19970101));

 

 [Err] 1564 - This partition function is not allowed。

 

 

参考文章：

https://blog.csdn.net/zhang168/article/details/46911305/

https://dev.mysql.com/doc/refman/5.6/en/partitioning-limitations-partitioning-keys-unique-keys.html