【mysql表分区系】mysql创建分区表提示A PRIMARY KEY must include all columns in the table‘s partitioning function

首先声明下我这边使用的mysql版本是5.7.29版本，当然下面的问题我这边也是基于这个版本。这里因为没有考证其他版本是否也会有这些问题，可自行官方文档来查阅资料

一个唯一键必须包含表分区函数所有的列，根据这个错误提示我们大概就知道怎么处理，但是why?我们还是沉下心来看下官网文档

关于主键和唯一键的限制

MySQL :: MySQL 5.7 Reference Manual :: 22.6.1 Partitioning Keys, Primary Keys, and Unique Keys

his 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 column in 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.) 

这段话大致概括的意思就是说我们的分区表，主键和唯一键必须包含分区表达式的所有列。反过来讲，分区表达式中的列至少有一个是联合主键和联合唯一键的某一列。

用官网的例子解释下

这个是错误的创建语句

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;

执行后，错误提示

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

根据上面官网大致概况的意思分析下上述分区表创建错误原因

t1表中UNIQUE KEY (col1, col2) 联合唯一键没有包含分区表达式HASH(col3) col3这一列，所以报错

t2标中 UNIQUE KEY (col1), UNIQUE KEY (col3) 两个唯一键没有包含分区表达式HASH(col1 + col3) 所有的列

这个是纠正后的创建语句

--col3是联合唯一键的一列字段
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;

--UNIQUE KEY (col1, col3) 这个唯一键必须包含分区表达式所有的列字段
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;

Since every primary key is by definition a unique key, this restriction also includes the table’s primary key,

对于主键也是同样的规则约束

这里就不用分析错误原因了同上面的联合唯一键

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

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

完整示例演示解决过程

那么下来我们就演示一个完整的例子来如何处理这种问题

创建并初始化订单表

CREATE TABLE test_order (
    id INT NOT NULL ,
    order_no varchar(20) not null ,
    create_time DATE NOT NULL,
    PRIMARY KEY(id),
    UNIQUE KEY(order_no)
)ENGINE=InnoDB  DEFAULT CHARSET=utf8;

insert into  test_order  VALUES(1,'001','2023-01-01');
insert into  test_order  VALUES(2,'002','2023-02-01');
insert into  test_order  VALUES(3,'003','2023-03-01');

根据时间字段做分区

alter table test_order  partition by range (TO_DAYS(CREATE_TIME)) (

	partition p_202301 values less than (TO_DAYS('2023-02-01')),
	partition p_202302 values less than (TO_DAYS('2023-03-01')),
	partition p_202303 values less than (TO_DAYS('2023-04-01'))
);

 如何解决呢

--重建主键
alter table test_order DROP PRIMARY KEY, ADD PRIMARY key(ID,CREATE_TIME);

--重建唯一键（删除原来的唯一索引，重新生成唯一键）
ALTER TABLE test_order DROP INDEX order_no, ADD UNIQUE KEY (`order_no`, `create_time`);

 看到这里，我们创建分区的sql已经执行成功了。

找到对应数据目录，我们也看到分区后数据idb文件的存储文件，表分区创建好后，对应的索引也会分区

这里强调一点，生产环境如果原来的表已经存在业务数据，并且业务量很大的情况下，我们还是要停机来处理创建表分区，这个需要注意谨慎操作