mysql新增分割區出錯怎麼辦？

mysql新增分區出錯，報「1503」錯誤的原因：每一個分區表中的公式中的列，必須在主鍵「unique key」中包括，否則就報錯；解決方法：先使用「 PRIMARY KEY」關鍵字建立一個複合主鍵，將分區欄位加入主鍵中，再進行分區操作。

（推薦教學：mysql影片教學）

如果分割區欄位沒有包含在主鍵欄位內，如表A的主鍵為ID,分區欄位為createtime ，依時間範圍分區，程式碼如下：

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

錯誤提示：#1503 

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

MySQL主鍵的限制，每一個分區表中的公式中的列，必須在主鍵「unique key」中包含 

在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 tableed in part pitionion for partitionion partion All columns tableed in partion 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 al. 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: 

#. MYSQL會這樣考慮，CSDN的斑竹是這麼解釋的：

為了確保主鍵的效率。否則同一主鍵區的東西一個在Ａ分區，一個在Ｂ分區，顯然會比較麻煩。 copyright

下面討論解決辦法，畢竟在一張表裡，日期做主鍵的還是不常見。

方法1：

順應MYSQL的要求，就把分割欄位加入主鍵中，組成複合主鍵

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

 測試通過，分割區成功。

方法2：

既然MYSQL要把分區字段包含在主鍵內才能建立分區，那麼在建立表的時候，先不指定主鍵字段，是否可以呢？ ？ 

測試如下： 

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

測試通過，分區成功。 OK copyright

繼續加入主鍵

alter table t1 add PRIMARY KEY(ID)

錯誤1503，和前面一樣的錯誤。

alter table t1 add PRIMARY KEY(ID,createtime)

創建主鍵成功，但還是複合主鍵，看來是沒辦法了，必須聽指揮了。

主鍵創建成功，把ID加上自增字段設定

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

最後結論，MYSQL的分區字段，必須包含在主鍵字段內。 ###

以上是mysql新增分割區出錯怎麼辦？的詳細內容。更多資訊請關注PHP中文網其他相關文章！