博客园  :: 首页  :: 新随笔  :: 联系 :: 订阅 订阅  :: 管理

mysql表分区的限制

Posted on 2020-08-29 15:53  龙翔天下  阅读(2389)  评论(0编辑  收藏  举报

头一次使用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