Shulou(Shulou.com)06/01 Report--

This article mainly describes how to solve mysql add partition error problem, the article is very detailed, has a certain reference value, interested friends must read!

mysql add partition error, report "1503" error Reason: columns in the formula of each partition table must be included in the primary key "unique key", otherwise an error will be reported; Solution: First use the "PRIMARY KEY" keyword to create a composite primary key, add the partition field to the primary key, and then partition operation.

If the partition field is not included in the primary key field, for example, the primary key of Table A is ID, the partition field is createtime, and the partition is divided according to the 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=utf8PARTITION 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: #1503

A PRIMARY KEY MUST INCLUDE ALL COLUMNS IN THE TABLE'S PARTITIONING FUNCTION

MySQL primary key restrictions, each partition table formula column, must be included in the primary key "unique key"

This is stated 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 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:

The partition field must be included in the primary key field. As for why MYSQL considers it this way, CSDN's bamboos are explained as follows:

To ensure the efficiency of the primary key. Otherwise, one thing in the same primary key area is in partition A and the other is in partition B, which will obviously be more troublesome. copyright

The solution is discussed below. After all, it is still uncommon for dates to be the main key in a table.

Method 1:

To comply with MYSQL requirements, partition fields are added 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('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);

Test passed, partition successful.

Method 2:

Since MYSQL can only create partitions by including partition fields in the primary key, is it possible to create tables without specifying primary key fields?

The tests are as follows:

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('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);

Test passed, partition successful. OK copyright

Continue to add the upper primary key

alter table t1 add PRIMARY KEY(ID)

Error 1503, same error as before.

alter table t1 add PRIMARY KEY(ID,createtime)

Primary key created successfully, but it is still a composite primary key. It seems that there is no other way. We must listen to the command.

Primary key created successfully, add ID to self-increment field setting

alter table t1 change id id int not null auto_increment;alter table t1 auto_increment=1;

Finally, MYSQL partition fields must be included in the primary key field.

The above is how to solve mysql add partition error all the content, thank you for reading! Hope to share the content to help everyone, more relevant knowledge, welcome to pay attention to the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.