mysql 创建分区表注意事项,每一个唯一约束必须包含在Mysql分区表的分区键（也包括主键约束）。

CREATE TABLE tnp (
    id INT NOT NULL AUTO_INCREMENT,
    ref BIGINT NOT NULL,
    name INT,
    PRIMARY KEY pk (id),
    UNIQUE KEY uk (name)
); 

mysql> desc tnp
    -> ;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| ref   | bigint(20)   | NO   |     | NULL    |                |
| name  | varchar(255) | YES  | UNI | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.03 sec)

ID作为分区键；
alter table tnp partition by range(id)
( 
partition p1012 values less than (100)
);
mysql> alter table tnp partition by range(id)
    -> ( 
    -> partition p1012 values less than (100)
    -> );
ERROR 1503 (HY000): A UNIQUE INDEX must include all columns in the table's partitioning function

一个唯一索引必须表明所有的列在表的分区函数里

mysql> desc tnp;
+-------+------------+------+-----+---------+----------------+
| Field | Type       | Null | Key | Default | Extra          |
+-------+------------+------+-----+---------+----------------+
| id    | int(11)    | NO   | PRI | NULL    | auto_increment |
| ref   | bigint(20) | NO   |     | NULL    |                |
| name  | int(11)    | YES  | UNI | NULL    |                |
+-------+------------+------+-----+---------+----------------+
3 rows in set (0.02 sec)

mysql> alter table tnp  PARTITION BY LIST (name) (partition p2 values in (3));
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

一个主键必须包含所有的列 ,包含分区列