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

来源：互联网发布：软件测试工程师工资编辑：程序博客网时间：2024/04/30 13:30

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一个主键必须包含所有的列 ,包含分区列

0 0