What should I do if there is an error in adding a partition in mysql?-Mysql Tutorial-php.cn

What should I do if there is an error in adding a partition in mysql?

青灯夜游

Release： 2020-10-20 09:52:50

Original

2083 people have browsed it

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.

What should I do if there is an error in adding a partition in mysql?

(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(&#39;2010-04-15&#39;)),
PARTITION p1 VALUES LESS THAN (TO_DAYS(&#39;2010-05-01&#39;)),
PARTITION p2 VALUES LESS THAN (TO_DAYS(&#39;2010-05-15&#39;)),
PARTITION p3 VALUES LESS THAN (TO_DAYS(&#39;2010-05-31&#39;)),
PARTITION p4 VALUES LESS THAN (TO_DAYS(&#39;2010-06-15&#39;)),
PARTITION p19 VALUES LESS ThAN  MAXVALUE);

Copy after login

Error message: #1503

A PRIMARY KEY MUST INCLUDE ALL COLUMNS IN THE TABLE&#39;S PARTITIONING FUNCTION

Copy after login

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 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 contained within the primary key field, as to why MYSQL will consider this. CSDN's Bamboo explains it this way:

In order to ensure the efficiency of the primary key. Otherwise, it will obviously be more troublesome if one of the items in the same primary key area is in partition A and the other is in partition B. copyright

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

Method 1:

Comply with the requirements of MYSQL, 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(&#39;2010-04-15&#39;)),
PARTITION p1 VALUES LESS THAN (TO_DAYS(&#39;2010-05-01&#39;)),
PARTITION p2 VALUES LESS THAN (TO_DAYS(&#39;2010-05-15&#39;)),
PARTITION p3 VALUES LESS THAN (TO_DAYS(&#39;2010-05-31&#39;)),
PARTITION p4 VALUES LESS THAN (TO_DAYS(&#39;2010-06-15&#39;)),
PARTITION p19 VALUES LESS ThAN  MAXVALUE);

Copy after login

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(&#39;2010-04-15&#39;)),
PARTITION p1 VALUES LESS THAN (TO_DAYS(&#39;2010-05-01&#39;)),
PARTITION p2 VALUES LESS THAN (TO_DAYS(&#39;2010-05-15&#39;)),
PARTITION p3 VALUES LESS THAN (TO_DAYS(&#39;2010-05-31&#39;)),
PARTITION p4 VALUES LESS THAN (TO_DAYS(&#39;2010-06-15&#39;)),
PARTITION p19 VALUES LESS ThAN  MAXVALUE);

Copy after login

The test passed and the partition was successful. OK copyright

Continue to add the primary key

alter table t1 add PRIMARY KEY(ID)

Copy after login

Error 1503, the same error as before.

alter table t1 add PRIMARY KEY(ID,createtime)

Copy after login

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;

Copy after login

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!