mysql error in adding partition, the reason why "1503" error is reported: the columns in the formula in each partition table must be included in the primary key "unique key", otherwise an error will be reported; solution: first use " PRIMARY KEY" keyword creates a composite primary key, adds the partitioning field to the primary key, and then performs partitioning operations.
(Recommended tutorial: mysql video tutorial)
If the partition field is not included in the primary key field, such as table The primary key of A is ID, the partition field is createtime, and it is 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('2010-04-15')), PARTITION p1 VALUES LESS THAN (TO_DAYS('2010-05-01')), PARTITION p2 VALUES LESS THAN (TO_DAYS('2010-05-15')), PARTITION p3 VALUES LESS THAN (TO_DAYS('2010-05-31')), PARTITION p4 VALUES LESS THAN (TO_DAYS('2010-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 restrictions, formulas in each partition table The columns in must be included in the primary key "unique key"
This is explained in the official documentation of MYSQL
##18.5.1. Partitioning Keys, Primary Keys , and Unique KeysThe partition field must be contained within the primary key field, as to why MYSQL will consider this. CSDN's Bamboo explains it this way: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 includes the table's primary key, since it is by definition a unique key. This particular case is discussed later in this section.) For example, each of the following table creation statements is invalid:
Method 1:
Comply with the requirements of MYSQL, add the partition field to the primary key to form a composite primary keyCREATE 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('2010-04-15')), PARTITION p1 VALUES LESS THAN (TO_DAYS('2010-05-01')), PARTITION p2 VALUES LESS THAN (TO_DAYS('2010-05-15')), PARTITION p3 VALUES LESS THAN (TO_DAYS('2010-05-31')), PARTITION p4 VALUES LESS THAN (TO_DAYS('2010-06-15')), PARTITION p19 VALUES LESS ThAN MAXVALUE);The test passed and the partition was successful .
Method 2:
Since MYSQL must include the partition field in the primary key to create a partition, is it possible to not specify the primary key field when creating the table? Woolen cloth? ? 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('2010-04-15')), PARTITION p1 VALUES LESS THAN (TO_DAYS('2010-05-01')), PARTITION p2 VALUES LESS THAN (TO_DAYS('2010-05-15')), PARTITION p3 VALUES LESS THAN (TO_DAYS('2010-05-31')), PARTITION p4 VALUES LESS THAN (TO_DAYS('2010-06-15')), PARTITION p19 VALUES LESS ThAN MAXVALUE);The test passed and the partition was successful. OK copyrightContinue to add the primary key
alter table t1 add PRIMARY KEY(ID)Error 1503, the same error as before.
alter table t1 add PRIMARY KEY(ID,createtime)The primary key was created successfully, but it is still a composite primary key. It seems that there is no other way and I must obey the command. The primary key is created successfully, add the ID to the auto-increment field setting
alter table t1 change id id int not null auto_increment; alter table t1 auto_increment=1;Finally, the MYSQL partition field must be included in the primary key field.
The above is the detailed content of What should I do if there is an error in adding a partition in mysql?. For more information, please follow other related articles on the PHP Chinese website!