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

MYSQL partition field must be included in the primary key field MYSQL partition field. It must be included in the primary key field to partition the table. If the partition field is not included in the primary key field, for example, if the primary key of table a is ID and the partition field is createtime and partitioned by time range, the Code is as follows: www.2cto.com create table T1 (id int (8) not null AUTO_INCREMENT, createtime datetime not null, primary key (id) ENGINE = InnoDB AUTO_INCREMENT = 1 default charset = utf8PARTITION by range (TO_DAYS (createtime )) (PARTITION p0 values less than (TO_DAYS ('1970-04-15 '), PARTITION p1 values less (TO_DAYS ('2017-05-01 '), PARTITION p2 values less than (TO_DAYS ('2017-05-15 ')), PARTITION p3 values less than (TO_DAYS ('1970-05-31 '), PARTITION p4 values less than (TO_DAYS ('1970-06-15'), PARTITION p19 values less ThAN MAXVALUE ); error message: #1503 a primary key must include all columns in the table's PARTITIONING FUNCTIONMySQL primary key restriction, column in the formula IN each partition TABLE, the primary key/unique key must contain www.2cto.com, which is described in the official MYSQL documentation as 18.5.1. par Titioning Keys, Primary Keys, and Unique KeysThis 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 col Umnin 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. As For why MYSQL considers this, the CSDN pattern explains this: 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: To comply with MYSQL requirements, 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 = utf8PARTITION 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 passes and the PARTITION is successful. Method 2: Since MYSQL needs to include the partition field in the primary key to create a partition, it is okay not to specify the primary key field when creating the table ?? Test www.2cto.com: create table T1 (id int (8) not null, createtime datetime not null) ENGINE = InnoDB AUTO_INCREMENT = 1 default charset = utf8PARTITION 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 ('1970-05-15 '), PARTITION p3 values less than (TO_DAYS ('1970-05-31'), PART ITION p4 values less than (TO_DAYS ('1970-06-15 '), PARTITION p19 values less ThAN MAXVALUE); pass the test and the PARTITION is successful. OK to continue adding the 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 the primary key is still composite. It seems that there is no way to do this. You must listen to the command. The primary key is successfully created, and the ID and auto-increment field are set to alter table t1 change id int not null auto_increment; alter table t1 auto_increment = 1; finally, the MYSQL partition field, it must be included in the primary key field.