MYSQL partition field, which must be included in the primary key field _ MySQL

MYSQL partition field, which must be included in the primary key field bitsCN.com

MYSQL partition field, which must be included in the primary key field

MYSQL partition field, which must be included in the primary key field

When partitioning A table, if the partition field is not included in the primary key field, for example, table A's primary key is ID and the partition field is createtime, partitioned by time range, the code is as follows:

Create table T1 (

Id int (8) not null AUTO_INCREMENT,

Createtime datetime not null,

Primary key (id)

) ENGINE = InnoDB AUTO_INCREMENT = 1 default charset = utf8

Partition by range (TO_DAYS (createtime ))

(

PARTITION p0 values less than (TO_DAYS ('2017-04-15 ')),

PARTITION p1 values less than (TO_DAYS ('2017-05-01 ')),

PARTITION p2 values less than (TO_DAYS ('2017-05-15 ')),

PARTITION p3 values less than (TO_DAYS ('2017-05-31 ')),

PARTITION p4 values less than (TO_DAYS ('2017-06-15 ')),

PARTITION p19 values less ThAN MAXVALUE );

Error message: #1503

A primary key must include all columns in the table's PARTITIONING FUNCTION

MySQL primary key restriction. columns in the formula of each partition table must be included in the primary key/unique key

This is explained in the official MYSQL documentation.

18.5.1. Partitioning Keys, Primary Keys, and Unique Keys

This section discusses the relationship of partitioning keys with primary keys and unique keys. the rule governing this relationship can be expressed as follows: All columns used in the partitioning expression for a partitioned table must be part of every unique key that the table may have.

In other words, every unique key on the table must use every columnin the table's partitioning expression. (This also primary des the table's primary key, since it is by definition a unique key. this particle case is discussed later in this section .) for example, each of the following table creation statements is invalid:

The partition field must be included in the primary key field. for the reason why MYSQL considers this, the CSDN pattern is explained as follows:

To ensure the efficiency of the primary key. Otherwise, the same primary key partition is located in partition A and partition B, which is obviously troublesome.

The solution is discussed below. after all, in a table, it is still not common for date primary keys.

Method 1:

Add the partition field to the primary key to form a composite primary key.

Create table T1 (

Id int (8) not null AUTO_INCREMENT,

Createtime datetime not null,

Primary key (id, createtime)

) ENGINE = InnoDB AUTO_INCREMENT = 1 default charset = utf8

Partition by range (TO_DAYS (createtime ))

(

PARTITION p0 values less than (TO_DAYS ('2017-04-15 ')),

PARTITION p1 values less than (TO_DAYS ('2017-05-01 ')),

PARTITION p2 values less than (TO_DAYS ('2017-05-15 ')),

PARTITION p3 values less than (TO_DAYS ('2017-05-31 ')),

PARTITION p4 values less than (TO_DAYS ('2017-06-15 ')),

PARTITION p19 values less ThAN MAXVALUE );

The test is passed and the partition is successful.

Method 2:

Since MYSQL only needs to include the partition field in the primary key to create a partition, can it not specify the primary key field when creating a table ??

The test is as follows:

Create table T1 (

Id int (8) not null,

Createtime datetime NOT NULL

) ENGINE = InnoDB AUTO_INCREMENT = 1 default charset = utf8

Partition by range (TO_DAYS (createtime ))

(

PARTITION p0 values less than (TO_DAYS ('2017-04-15 ')),

PARTITION p1 values less than (TO_DAYS ('2017-05-01 ')),

PARTITION p2 values less than (TO_DAYS ('2017-05-15 ')),

PARTITION p3 values less than (TO_DAYS ('2017-05-31 ')),

PARTITION p4 values less than (TO_DAYS ('2017-06-15 ')),

PARTITION p19 values less ThAN MAXVALUE );

The test is passed and the partition is successful. OK

Continue adding primary key

Alter table t1 add primary key (ID)

Error 1503, the same as the previous error.

Alter table t1 add primary key (ID, createtime)

The primary key is successfully created, but it is still a compound primary key. it seems that there is no way to do it. you must listen to the command.

The primary key is successfully created, and the ID is added with the auto-increment field.

Alter table t1 change id int not null auto_increment;

Alter table t1 auto_increment = 1;

Conclusion: the MYSQL partition field must be included in the primary key field.

BitsCN.com