A PRIMARY KEY must include all columns in the table’s partitioning function

清疚 2023-07-05 05:21 48阅读 0赞

这个是今天在学习 mysql 的建分区数据表时，遇到的一个问题，会提示：

> A PRIMARY KEY must include all columns in the table’s partitioning function

如果分区字段没有包含在主键字段内，如表A的主键为ID,分区字段为createtime ，按时间范围分区，执行会出上述的错误，代码如下：

CREATE TABLE T1 (
         id int(8) NOT NULL AUTO_INCREMENT,
         createtime datetime NOT NULL,
          PRIMARY KEY (id)
    ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
    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')),
    PARTITION p3 VALUES LESS THAN (TO_DAYS('2010-05-31')),
    PARTITION p4 VALUES LESS THAN (TO_DAYS('2010-06-15')),
    PARTITION p19 VALUES LESS ThAN  MAXVALUE);

MySQL主键的限制，每一个分区表中的公式中的列，必须在主键/unique key 中包括。

在MYSQL的官方文档里是这么说明的：

> 18.5.1. Partitioning Keys, Primary Keys, and Unique Keys  
> This section discusses the relationship of partitioning keys with primary keys and unique keys. The rule governing this relationship can be expressed as follows: All columns used in the partitioning expression for a partitioned table must be part of every unique key that the table may have.
> 
> In other words,every unique key on the table must use every columnin the table’s partitioning expression. (This also includes the table’s primary key, since it is by definition a unique key. This particular case is discussed later in this section.) For example, each of the following table creation statements is invalid:

分区字段必须包含在主键字段内，至于为什么MYSQL会这样考虑，CSDN的斑竹是这么解释的：  
为了确保主键的效率。否则同一主键区的东西一个在Ａ分区，一个在Ｂ分区，显然会比较麻烦。  
下面讨论解决办法，毕竟在一张表里，日期做主键的还是不常见。

方法1：顺应MYSQL的要求，就把分区字段加入到主键中，组成复合主键

CREATE TABLE T1 (
         id int(8) NOT NULL AUTO_INCREMENT,
         createtime datetime NOT NULL,
          PRIMARY KEY (id,createtime)
    ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
    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')),
    PARTITION p3 VALUES LESS THAN (TO_DAYS('2010-05-31')),
    PARTITION p4 VALUES LESS THAN (TO_DAYS('2010-06-15')),
    PARTITION p19 VALUES LESS ThAN  MAXVALUE);

方法2：既然MYSQL要把分区字段包含在主键内才能创建分区，那么在创建表的时候，先不指定主键字段。代码如下：

CREATE TABLE T1 (
         id int(8) NOT NULL ,
         createtime datetime NOT NULL
          ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
    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')),
    PARTITION p3 VALUES LESS THAN (TO_DAYS('2010-05-31')),
    PARTITION p4 VALUES LESS THAN (TO_DAYS('2010-06-15')),
    PARTITION p19 VALUES LESS ThAN  MAXVALUE);

继续添加上主键：

alter table t1 add PRIMARY KEY(ID)

执行的结果和前面一样的错误。还是按复合主键方案，没其他的办法了，必须听指挥了。

alter table t1 add PRIMARY KEY(ID,createtime)

主键创建成功，把ID加上自增字段设置

alter table t1 change id id int not null auto_increment;
    alter table t1 auto_increment=1;

最后结论，MYSQL的分区数据表如有主键字段，那么分区字段必须包含在主键字段内。

另外再加个在分区数据表中是不能创建全局索引的，只能是创建分区的局部索引，通过以下的代码就可实现：

create index idx_name on table_name(colname…) local;