mysql分区键约束,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

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