MySQL 表分区（二）

•  但当表中含有主键或唯一键时，则每个被用作分区函数的字段必须是表中唯一键和主键的全部或一部分，否则就无法创建分区表

# 如下的表由于唯一键和主键没有相同的字段，所以无法创建表分区
mysql> CREATE TABLE tnp (
    -> id INT NOT NULL AUTO_INCREMENT,
    -> ref BIGINT NOT NULL,
    -> name VARCHAR(255),
    -> PRIMARY KEY pk (id),
    -> UNIQUE KEY uk (ref) )
    -> PARTITION BY RANGE (id)
    -> ( PARTITION p0 VALUES LESS THAN (6), PARTITION p1 VALUES LESS THAN(11));
ERROR 1503 (HY000): A UNIQUE INDEX must include all columns in the table's partitioning function

mysql> CREATE TABLE tnp (
    -> id INT NOT NULL AUTO_INCREMENT,
    -> ref BIGINT NOT NULL,
    -> name VARCHAR(255),
    -> PRIMARY KEY pk (id),
    -> UNIQUE KEY uk (ref) )
    -> PARTITION BY RANGE (ref)
    -> ( PARTITION p0 VALUES LESS THAN (6), PARTITION p1 VALUES LESS THAN(11));
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

# 上述例子中删除唯一键，确保主键中的字段包含分区函数中的所有字段，创建成功
mysql> CREATE TABLE tnp (
    -> id INT NOT NULL AUTO_INCREMENT,
    -> ref BIGINT NOT NULL,
    -> name VARCHAR(255),
    -> PRIMARY KEY pk (id))
    -> PARTITION BY RANGE (id)
    -> ( PARTITION p0 VALUES LESS THAN (6), PARTITION p1 VALUES LESS THAN(11));
Query OK, 0 rows affected (0.09 sec)

mysql> drop table tnp;
Query OK, 0 rows affected (0.05 sec)

# 或者将主键扩展为包含ref字段
mysql> CREATE TABLE tnp (
    -> id INT NOT NULL,
    -> ref BIGINT NOT NULL,
    -> name VARCHAR(255),
    -> PRIMARY KEY pk (id,ref),
    -> UNIQUE KEY uk (ref) )
    -> PARTITION BY RANGE (ref)
    -> ( PARTITION p0 VALUES LESS THAN (6), PARTITION p1 VALUES LESS THAN(11));
Query OK, 0 rows affected (0.27 sec)