分区键和主键、唯一键的关系

控制分区键与主键、唯一键关系的规则是：分区表达式中使用的所有列必须是该数据表可能具有的每个唯一键的一部分。换句话说，分区键必须包含在表的主键、唯一键中。

错误示例

唯一键是 col1 和 col2 的组合，分区键是 col3

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;

-- 报错如下：
# ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's
# partitioning function (prefixed columns are not considered).

两个唯一键分别是 col1 和 col3，分区键是 col1 + col3

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;

-- 报错如下：
# ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's
# partitioning function (prefixed columns are not considered).

两个唯一键分别是 (col1, col2) 和 col3，分区键是 col1 + col3

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;

-- 报错如下：
# ERROR 1491 (HY000): A PRIMARY KEY must include all columns in the table's
# partitioning function.

主键是 col1 和 col2，分区键是 col3

create table t4 (
    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;

-- 报错如下：
# ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's
# partitioning function (prefixed columns are not considered).

主键是 col1 和 col3，唯一键为 col2，分区键为 year(col2)

create table t5 (
    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;

-- 报错如下：
# ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's
# partitioning function (prefixed columns are not considered).

正确示例

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;

create table t3 (
    col1 int not null,
    col2 date not null,
    col3 int not null,
    col4 int not null,
    unique key (col1, col2, col3),
    unique key (col3)
)
partition by hash(col3)
partitions 4;

以下两种情况，主键都不包括分区表达式中引用的所有列，但语句都是有效的

create table t4 (
    col1 int not null,
    col2 date not null,
    col3 int not null,
    col4 int not null,
    primary key(col1, col2)
)
partition by hash(col1 + year(col2))
partitions 4;

create table t5 (
    col1 int not null,
    col2 date not null,
    col3 int not null,
    col4 int not null,
    primary key (col1, col2, col4),
    unique key (col2, col1)
)
partition by hash(col1 + year(col2))
partitions 4;