MySQL分區表姿勢

• 2019 年 10 月 4 日
• 筆記

大部分內容整理自姜承堯的innodb存儲引擎2學習筆記。

分區：

分區的功能不是在存儲引擎層實現的。因此不只是InnoDB才支援分區。MyISAM、NDB都支援分區操作。

分區的過程是將一個表或者索引分解為多個更小、更可管理的部分。從邏輯上將，只有一個表或者索引，但是在物理上這個表或索引可能由數十個物理分區組成。

每個分區都是獨立的對象，可以獨自處理，也可以作為一個更大對象的一部分進行處理。

MySQL只支援水平分區，不支援垂直分區。

水平分區：將同一表中不同行的記錄分配到不同的物理文件中。

垂直分區：將同一表中不同列的記錄分配到不同的物理文件中。

MySQL資料庫的分區是局部分區索引。一個分區中既存放了數據又存放了索引。而全局分區索引指的是數據存放在各個分區中，但是所有數據的索引放在一個對象中。MySQL暫時還不支援全局分區索引。

查看當前資料庫是否啟用了分區功能：

> show pluginsG   partition狀態是ACTIVE表示可以支援分區。

查看目前MySQL上有哪些分區表：

SELECT TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME,PARTITION_METHOD,CREATE_TIME from `PARTITIONS` where PARTITION_NAME is not null ;

MySQL資料庫支援以下幾種類型的分區：

RANGE分區

LIST分區

HASH分區

KEY分區

如下就是創建分區的表的方式：

> create table t4 (  col1 int null,  col2 date null,  col3 int null,  col4 int null,  key (col3)  ) engine=InnoDB  partition BY HASH (col3)  partitions 4;        -- 劃分成4個分區  -rw-rw---- 1 mariadb mariadb 98304 2016-08-07 09:59 t4#P#p0.ibd  -rw-rw---- 1 mariadb mariadb 98304 2016-08-07 09:59 t4#P#p1.ibd  -rw-rw---- 1 mariadb mariadb 98304 2016-08-07 09:59 t4#P#p2.ibd  -rw-rw---- 1 mariadb mariadb 98304 2016-08-07 09:59 t4#P#p3.ibd

分區類型：

1 RANGE分區：

> create table t1 (id int)  partition by range(id)(  partition p0 values less than (10),  partition p1 values less than (20));  當數據小於10的時候，插入p0分區。大於等於10小於20時候，插入p1分區。  > INSERT INTO t1 SELECT 12;  > INSERT INTO t1 SELECT 2;

表物理文件變成了下面這種：

-rw-rw---- 1 mariadb mariadb 98304 2016-08-07 10:14 t1#P#p0.ibd  -rw-rw---- 1 mariadb mariadb 98304 2016-08-07 10:17 t1#P#p1.ibd

從表面上，看不出來到底插入到什麼分區中了，可以用下面的命令查看：  > SELECT * from information_schema.PARTITIONS where table_schema=database() and table_name='t1'G  ***************************[ 1. row ]***************************  TABLE_CATALOG                 | def  TABLE_SCHEMA                  | hellodb  TABLE_NAME                    | t1  PARTITION_NAME                | p0        # 這裡能看到是插入到p0分區的  SUBPARTITION_NAME             | None  PARTITION_ORDINAL_POSITION    | 1  SUBPARTITION_ORDINAL_POSITION | None  PARTITION_METHOD              | RANGE       # 這裡看得出是range分區類型  SUBPARTITION_METHOD           | None  PARTITION_EXPRESSION          | id  SUBPARTITION_EXPRESSION       | None  PARTITION_DESCRIPTION         | 10  TABLE_ROWS                    | 1           # 這個反映了該分區(這裡是p0)記錄的行數量。  AVG_ROW_LENGTH                | 16384  DATA_LENGTH                   | 16384  MAX_DATA_LENGTH               | None  INDEX_LENGTH                  | 0  DATA_FREE                     | 0  CREATE_TIME                   | None  UPDATE_TIME                   | None  CHECK_TIME                    | None  CHECKSUM                      | None  PARTITION_COMMENT             |   NODEGROUP                     | default  TABLESPACE_NAME               | None  ***************************[ 2. row ]***************************  TABLE_CATALOG                 | def  TABLE_SCHEMA                  | hellodb  TABLE_NAME                    | t1  PARTITION_NAME                | p1        # 這裡能看到是插入到p1分區的  SUBPARTITION_NAME             | None  PARTITION_ORDINAL_POSITION    | 2  SUBPARTITION_ORDINAL_POSITION | None  PARTITION_METHOD              | RANGE  SUBPARTITION_METHOD           | None  PARTITION_EXPRESSION          | id  SUBPARTITION_EXPRESSION       | None  PARTITION_DESCRIPTION         | 20  TABLE_ROWS                    | 1  AVG_ROW_LENGTH                | 16384  DATA_LENGTH                   | 16384  MAX_DATA_LENGTH               | None  INDEX_LENGTH                  | 0  DATA_FREE                     | 0  CREATE_TIME                   | None  UPDATE_TIME                   | None  CHECK_TIME                    | None  CHECKSUM                      | None  PARTITION_COMMENT             |   NODEGROUP                     | default  TABLESPACE_NAME               | None  > INSERT INTO t1 SELECT 32;  # 這個插入會報錯，因為我們上面定義的分區，並不包含這個區間。

對此，要允許插入大數的話，可以修改下表：

> ALTER TABLE t1 add partition( partition p2 values less than (30)); 或者 ALTER TABLE t1 add partition( partition p2 values less than maxvalue );

這下我們插入大數值的話，也不會報錯了。

RANGE分區主要用於日期列的分區，例如對於銷售類的表，可以根據年來分區存放銷售記錄。如下面的分區表sales:

> CREATE TABLE sales (  money INT UNSIGNED NOT NULL,  `date` DATETIME  ) ENGINE=INNODB  PARTITION BY RANGE (YEAR(DATE)) (  PARTITION p2008 VALUES LESS THAN (2009),  PARTITION p2009 VALUES LESS THAN (2010),  PARTITION p2010 VALUES LESS THAN (2011)  );  > INSERT INTO sales SELECT 2399,'2008-04-20';  > INSERT INTO sales SELECT 6569,'2009-01-25';  > INSERT INTO sales SELECT 2399,'2010-12-20';

這樣的話，不同年份的數據就插入到不同的分區中，便於對sales這張表進行管理。

如果要刪除2008年的數據，不需要執行delete from sales where date>='2008-01-01' and date<='2008-12-31'; 只要刪除2008年數據所在的分區即可：

> alter table sales drop partition p2008;

分區的另一個好處是：

加快某些查詢，例如我們只要查詢2009年整年的銷售額，如下即可：

> explain partitions select * from sales where date >='2009-01-01' and date <='2009-12-31'G  ***************************[ 1. row ]***************************  id            | 1  select_type   | SIMPLE  table         | sales  partitions    | p2009    # 只去p2009這個分區去搜索  type          | ALL  possible_keys | None  key           | None  key_len       | None  ref           | None  rows          | 2  Extra         | Using where

最常用的就是range分區。

但是注意：如果分區鍵是timestamp類型的，則必須用UNIX_TIMESTAMP轉換下。如下例子：

ALTER TABLE `order_his_tmp` drop primary key, add primary key(id,order_time);  

去掉原先的主鍵，加一個帶分區ID的主鍵。

ALTER TABLE `order_his_tmp` PARTITION BY RANGE (UNIX_TIMESTAMP (order_time)) (     PARTITION  p201508  VALUES LESS THAN  (UNIX_TIMESTAMP('2015-09-01')) ,    PARTITION  p201509  VALUES LESS THAN  (UNIX_TIMESTAMP('2015-10-01')) ,    PARTITION  P201510  VALUES LESS THAN  (UNIX_TIMESTAMP('2015-11-01')) ,    PARTITION  P201511  VALUES LESS THAN  (UNIX_TIMESTAMP('2015-12-01')) ,    PARTITION  P201512  VALUES LESS THAN  (UNIX_TIMESTAMP('2016-01-01')) ,    PARTITION  P201601  VALUES LESS THAN  (UNIX_TIMESTAMP('2016-02-01')) ,    PARTITION  P201602  VALUES LESS THAN  (UNIX_TIMESTAMP('2016-03-01')) );

對於分區鍵是DATETIME的，要用TO_DAYS()函數操作,如下例子：

> CREATE TABLE sales(  money int unsigned not null,  date datetime)  partition by range (TO_DAYS(date)) (  partition p201001 values less than (TO_DAYS('2010-02-01')),   partition p201002 values less than (TO_DAYS('2010-03-01')),   partition p201003 values less than (TO_DAYS('2010-04-01'))  );

2 LIST分區【不多見】：

和range分區類似，只是分區列的值是散列的，而非連續的。

> CREATE TABLE t (  a INT,  b INT) ENGINE INNODB  PARTITION BY LIST(b) (  PARTITION p0 VALUES IN (1,3,5,7,9),  PARTITION p1 VALUES IN (2,4,6,8)  );

注意：list分區中使用的是VALUES IN 這種格式。

> insert into t select 3,2;  > insert into t select 2,12;  執行這行插入語句會報錯，因為按照LIST(b)劃分的話，12不在上述的LIST裡面。  > insert into t select 3,4;  執行這行插入語句會報錯，因為按照LIST(b)劃分的話，4不在上述的LIST裡面。  > insert into t select 3,5;  > SELECT table_name,partition_name,table_rows from information_schema.Partitions where table_name='t' and table_schema=DATABASE()G

結果如下：

***************************[ 1. row ]***************************  table_name     | t  partition_name | p0  table_rows     | 1  ***************************[ 2. row ]***************************  table_name     | t  partition_name | p1  table_rows     | 2   

表示p1分區有2行數據

注意：

InnoDB和MyISAM在遇到一次性插入多條數據中出現分區未定義錯誤的時候處理方式是不同的。

InnoDB會把整個SQL語句當做一個事務，只要有錯誤，就完全不執行。而MyISAM則會將錯誤之前的sql都執行成功。

3 HASH分區：

HASH分區的目的是將數據均勻地分布到預先定義的各個分區中，保證各分區的數據數量大致都是一樣的。

在RANGE和LIST分區中，必須明確指定一個給定的列值或列值集合應該保存在哪個分區中。在HASH分區中，MySQL自動完成這些工作，用於所要做的只是基於將要進行哈希分區的列值指定一個列值或表達式，以及指定備份去的表將要被分割成的分區數量。

要使用HASH分區來分割一個表，要在CREATE TABLE語句上添加一個 PARTITION BY HASH(expr) 子句，其中expr是一個返回一個整數的表達式。它可以僅僅是欄位類型為MySQL整型的列名。

此外，用戶可以自定義分區的數量，只要加上參數PARTITIONS xxx即可。如下：

> create table t_hash (  a int,  b datetime  ) engine innodb  PARTITION BY HASH (YEAR(b))  PARTITIONS 4;  > INSERT INTO t_hash VALUES (6,'2013-03-11 12:21:22');

上面這條插入的話，實際上要執行MOD(2013,4)取餘數，得出這行數據存放在哪個分區中。

> SELECT table_name,partition_name,table_rows FROM information_schema.`PARTITIONS` WHERE table_schema=DATABASE() AND table_name='t_hash' ;

結果如下：

+------------+----------------+------------+  | table_name | partition_name | table_rows |  +------------+----------------+------------+  | t_hash     | p0             |          0 |    | t_hash     | p1             |          1 |    | t_hash     | p2             |          0 |  | t_hash     | p3             |          0 |  +------------+----------------+------------+

可以看到p1插入了4條數據，因為2013年除以4，餘數是1。因此2013年都數據都落在p1分區

4 LINEAR HASH分區：(線性hash)

mysql還支援這種複雜的分區演算法。語法和HASH分區類似，只是將關鍵字HASH改成了LINEAR HASH。

如下：

> CREATE TABLE t_l_hash (  a INT,  b DATETIME  ) ENGINE INNODB  PARTITION BY LINEAR HASH(YEAR(b))  PARTITIONS 4;

取大於分區數量4的下一個2的冪值V，V=POWER(2，CEILING(LOG(2，num)))=4

所在分區N=YEAR('2010-04-01')&(V-1)=2.

LINEAR HASH分區的

優點：

增加、刪除、合併和拆分分區將變得更加快捷，有利於處理含有大量數據的表。

缺點：

與使用HASH分區得到的數據分布相比，各個分區間數據的分布可能不大均衡。

> INSERT INTO t_l_hash VALUE (2,'2014-04-23 12:23:33');  > SELECT table_name,partition_name,table_rows  FROM information_schema.`PARTITIONS`  WHERE table_schema=DATABASE() AND table_name='t_l_hash'G

結果如下：

***************************[ 1. row ]***************************  table_name     | t_l_hash  partition_name | p0  table_rows     | 1  ***************************[ 2. row ]***************************  table_name     | t_l_hash  partition_name | p1  table_rows     | 0  ***************************[ 3. row ]***************************  table_name     | t_l_hash  partition_name | p2  table_rows     | 1  ***************************[ 4. row ]***************************  table_name     | t_l_hash  partition_name | p3  table_rows     | 0

5 KEY分區：

和HASH分區類似，不同之處在於HASH分區使用用戶定義的函數進行分區，KEY分區使用MySQL資料庫提供的函數進行分區。

對於NDB Cluster引擎，MySQL資料庫使用MD5函數來分區；對於其他引擎，使用MySQL內部的哈希函數來分區。

> CREATE TABLE t_key (  a INT,  b DATETIME  ) ENGINE INNODB  PARTITION BY KEY(b)  PARTITIONS 4 ;

6 COLUMNS分區【很常用】：

前面的幾種分區都是有條件限制的。條件是：必須是整型，如果不是整型，那麼也必須是可以通過函數轉換為整型的，如YEAR()、TO_DAYS()、MONTH()等函數。

MySQL5.5版本開始支援的COLUMNS分區，可視為RANGE分區和LIST分區的一種進化。

COLUMNS分區看直接使用非整型的數據進行分區，分區根據類型直接比較而得，不需要轉換為整型。

此外，COLUMNS分區可以對多個列的值進行分區。

COLUMNS分區支援以下的數據類型：

1 所有的整型類型

支援：INT、SMALLINT、TINYINT、BIGINT。不支援：FLOAT和DECIMAL

2 日期類型 【常用】

支援 DATE、DATETIME

3 字元串類型

支援 CAHR、VARCHAR、BINARY、VARBINARY。不支援BLOB和TEXT

> CREATE TABLE t_columns_range (  a INT,  b DATETIME  )ENGINE INNODB  PARTITION BY  RANGE COLUMNS (B) (  PARTITION p0 VALUES LESS THAN ('2009-01-01'),  PARTITION p1 VALUES LESS THAN ('2010-01-01')  );

注意和range分區的SQL語句差別！！(分區範圍里不用函數處理列了)

對於現有的表改成分區表:

ALTER TABLE `tb_detail` drop primary key ,add primary key (id, bill_date);  ALTER TABLE `tb_detail` PARTITION BY RANGE COLUMNS (bill_date)   (     PARTITION  p201509  VALUES LESS THAN  ('2015-10-01') ,     PARTITION  P201510  VALUES LESS THAN  ('2015-11-01') ,  PARTITION  P201511  VALUES LESS THAN  ('2015-12-01') ,  PARTITION  P201512  VALUES LESS THAN  ('2016-01-01') ,  PARTITION  P201601  VALUES LESS THAN  ('2016-02-01') ,  PARTITION  P201602  VALUES LESS THAN  ('2016-03-01') ,  PARTITION  P201603  VALUES LESS THAN  ('2016-04-01') ,  PARTITION  P201604  VALUES LESS THAN  ('2016-05-01') );

對於RANGE COLUMNS分區，可以使用多個列進行分區，如：

> CREATE TABLE rcx (  a INT,  b INT,  c CHAR(3),  d INT  )ENGINE INNODB  PARTITION BY  RANGE COLUMNS (a,b,c) (  PARTITION p0 VALUES LESS THAN (5,10,'ggg'),  PARTITION p1 VALUES LESS THAN (10,20,'mmm'),  PARTITION p2 VALUES LESS THAN (15,30,'sss'),  PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE,MAXVALUE)  );

MySQL5.5開始支援COLUMNS分區。對於之前的RANGE和LIST分區。用戶可以用RANGE COLUMNS和LIST COLUMNS分區進行很好的代替。

手工添加分區：

 CREATE TABLE `t10` (    `a` int(11) NOT NULL AUTO_INCREMENT,    `b` int(11) DEFAULT NULL,    PRIMARY KEY (`a`)  ) ENGINE=InnoDB AUTO_INCREMENT=121 DEFAULT CHARSET=utf8  PARTITION BY RANGE  COLUMNS(a)  (PARTITION p0 VALUES LESS THAN (10) ENGINE = InnoDB,   PARTITION p1 VALUES LESS THAN (20) ENGINE = InnoDB,   PARTITION p2 VALUES LESS THAN (30) ENGINE = InnoDB,   PARTITION p3 VALUES LESS THAN (40) ENGINE = InnoDB,   PARTITION p4 VALUES LESS THAN (50) ENGINE = InnoDB,   PARTITION p5 VALUES LESS THAN (60) ENGINE = InnoDB,   PARTITION p6 VALUES LESS THAN (70) ENGINE = InnoDB,   PARTITION p7 VALUES LESS THAN (80) ENGINE = InnoDB,   PARTITION p8 VALUES LESS THAN (90) ENGINE = InnoDB,   PARTITION p9 VALUES LESS THAN (100) ENGINE = InnoDB );  

注意這裡最後一個分區範圍沒有像上面其他案例那樣寫死

後期可以使用 ALTER TABLE t10 ADD PARTITION (PARTITION p10 VALUES LESS THAN (110));   這樣就可以加一個分區了。

子分區：

子分區(subpartitioning)是在分區的基礎上在進行分區，有時也稱這種分區為複合分區(composite partitioning)。

MySQL資料庫允許在RANGE和LIST分區上再進行HASH或KEY的子分區。如：

> CREATE TABLE ts (  a INT,  b DATE  ) ENGINE=INNODB  PARTITION BY RANGE(YEAR(b))  SUBPARTITION BY HASH(TO_DAYS(b))  SUBPARTITIONS 2 (  PARTITION p0 VALUES LESS THAN (1990),  PARTITION p1 VALUES LESS THAN (2000),  PARTITION p2 VALUES LESS THAN MAXVALUE);

在物理文件上表示形式如下：

-rw-rw---- 1 mariadb mariadb   711 2016-08-07 19:28 ts.frm  -rw-rw---- 1 mariadb mariadb   108 2016-08-07 19:28 ts.par  -rw-rw---- 1 mariadb mariadb 98304 2016-08-07 19:28 ts#P#p0#SP#p0sp0.ibd  -rw-rw---- 1 mariadb mariadb 98304 2016-08-07 19:28 ts#P#p0#SP#p0sp1.ibd  -rw-rw---- 1 mariadb mariadb 98304 2016-08-07 19:28 ts#P#p1#SP#p1sp0.ibd  -rw-rw---- 1 mariadb mariadb 98304 2016-08-07 19:28 ts#P#p1#SP#p1sp1.ibd  -rw-rw---- 1 mariadb mariadb 98304 2016-08-07 19:28 ts#P#p2#SP#p2sp0.ibd  -rw-rw---- 1 mariadb mariadb 98304 2016-08-07 19:28 ts#P#p2#SP#p2sp1.ibd

先進行range分區(p0p1p2)，再進行hash分區(sp0sp1)。(合計共3*2=6個分區)，上面物理文件上面也能看出來是6個分區。

子分區的建立需要注意下面問題：

1、每個子分區的數量必須相同。

2、要在一個分區表的任何分區上使用SUBPARTITION來明確定義任何子分區，就必須定義所有的子分區。

3、每個SUBPARTITION子句必須包括子分區的一個名字。

4、子分區的名字必須是唯一的。

子分區可以用於特別大的表，在多個磁碟間分別分配數據和索引。

如下為6個磁碟的分區實例(InnoDB引擎)：

> CREATE TABLE ts (  a INT,  b DATE)ENGINE INNODB  PARTITION BY RANGE(YEAR(b))  SUBPARTITION BY HASH(TO_DAYS(b)) (  PARTITION p0 VALUES LESS THAN (2000) (  SUBPARTITION s0  DATA DIRECTORY = '/disk0/data'  INDEX DIRECTORY = '/disk0/idx',  SUBPARTITION s1  DATA DIRECTORY = '/disk1/data'  INDEX DIRECTORY = '/disk1/idx'  ),  PARTITION p1 VALUES LESS THAN (2010) (  SUBPARTITION s2  DATA DIRECTORY = '/disk2/data'  INDEX DIRECTORY = '/disk2/idx',  SUBPARTITION s3  DATA DIRECTORY = '/disk3/data'  INDEX DIRECTORY = '/disk3/idx'  ),  PARTITION p2 VALUES LESS THAN MAXVALUE (  SUBPARTITION s4  DATA DIRECTORY = '/disk4/data'  INDEX DIRECTORY = '/disk4/idx',  SUBPARTITION s5  DATA DIRECTORY = '/disk5/data'  INDEX DIRECTORY = '/disk5/idx'  )  );

分區中的NULL值：

MySQL允許對NULL值做分區。但是處理的方法與其他資料庫完全不同。

MySQL資料庫的分區總是視NULL值小於任何的一個非NULL值，這和MySQL資料庫中處理NULL值的ORDER BY操作是一樣的。因此對於不同的分區類型，MySQL資料庫對於NULL值的處理也是各不相同。

1、RANGE分區中使用NULL值：

> CREATE table t_range(        a int,        b int)        partition by range(b) (        partition p0 VALUES less than (10),        partition p1 VALUES less than (20),        partition p2 values less than maxvalue);      > insert into t_range select 1,1;      > insert into t_range select 2,null;      > SELECT table_name,partition_name,table_rows  FROM information_schema.`PARTITIONS`  WHERE table_schema=DATABASE() AND table_name='t_range';

結果如下：

+------------+----------------+------------+  | table_name | partition_name | table_rows |  +------------+----------------+------------+  | t_range    | p0             |          2 |  | t_range    | p1             |          0 |  | t_range    | p2             |          0 |  +------------+----------------+------------+

可以看到插入的數據都放到了p0分區。

也就是說在RANGE分區下，NULL值會放入最左邊的分區中。

另外，如果刪除了p0分區，刪除的將是小於10的記錄和NULL值的記錄，這點非常重要。

可以看到下圖的表已經為空值了

> alter table t_range drop partition p0;  > SELECT * from t_range;    +-----+-----+  | a   | b   |  |-----+-----|  +-----+-----+

2、LIST分區下使用NULL值，必須顯式的指出哪個分區中放入NULL值，否則會報錯。

如下寫法才能允許插入NULL值。

> CREATE table t_list (  a int,  b int)  partition by list(b) (  partition p0 values in (1,3,5,7,9,NULL),    # 注意必須顯式的指出NULL插入到那個分區才行  partition p1 VALUES in (2,4,6,8,10));  > INSERT INTO t_list SELECT 1,4;  > INSERT INTO t_list SELECT 3,null;   > SELECT table_name,partition_name,table_rows  IFROM information_schema.`PARTITIONS`  WHERE table_schema=DATABASE() AND table_name='t_list'G

結果如下：

***************************[ 1. row ]***************************  table_name     | t_list  partition_name | p0  table_rows     | 1  ***************************[ 2. row ]***************************  table_name     | t_list  partition_name | p1  table_rows     | 1

3、HASH和KEY分區對於NULL的處理方式和RANGE分區、LIST分區不一樣。

任何分區函數都會將含有NULL值的記錄返回為0。【返回0的說明是存放在第一個分區中】

> create table t_hash (  a int,  b int ) engine=innodb  partition by hash(b)  partitions 4;  > INSERT INTO t_hash SELECT 3,null;  > INSERT INTO t_hash SELECT 3,6778;   > SELECT table_name,partition_name,table_rows  FROM information_schema.`PARTITIONS`  WHERE table_schema=DATABASE() AND table_name='t_hash'G

結果如下：

***************************[ 1. row ]***************************  table_name     | t_hash  partition_name | p0  table_rows     | 1  ***************************[ 2. row ]***************************  table_name     | t_hash  partition_name | p1  table_rows     | 0  ***************************[ 3. row ]***************************  table_name     | t_hash  partition_name | p2  table_rows     | 1  ***************************[ 4. row ]***************************  table_name     | t_hash  partition_name | p3  table_rows     | 0

在表和分區之間交換數據：

mysql5.6開始支援 ALTER TABLE … EXCHANGE PARTITION 語法。該語句允許分區或子分區的數據與另一個非分區的表中的數據進行交換。

如果非分區表中的數據為空，那麼相當於分區中的數據移動到非分區表中。

若分區表中的數據為空，則相當於將外部表中的數據導入到分區中。

要使用ALTER TABLE … EXCHANGE語句，必須滿足下面的條件：

1 要交換的表需要和分區表有相同的表結構，但是不能有分區。

2 在非分區表中的數據必須在交換的分區定義內。

3 被交換的表中不能含有外鍵，或者其他的表含有對該表的外鍵引用。

4 用戶除了需要alter、insert、create許可權外，還需要drop的許可權。

此外，有2個小的細節要注意：

1、使用該語句時，不會觸發交換表和被交換表上的觸發器。

2、AUTO_INCREMENT列將被重置。

例子：

創建一個含有RANGE分區的表e，並填充數據：

> CREATE TABLE e (  id INT NOT NULL,  fname VARCHAR(30),  lname VARCHAR(30)  )  PARTITION BY RANGE (id) (  PARTITION p0 VALUES LESS THAN (50),  PARTITION p1 VALUES LESS THAN (100),  PARTITION p2 VALUES LESS THAN (150),  PARTITION p3 VALUES LESS THAN (MAXVALUE)  );  > INSERT INTO e VALUES  (33,'zhang','san'),  (156,'jim','ling'),  (1114,'wang','wu'),  (1454,'li','si');

然後創建交換表e2。結構和表e一樣，但是表e2中不含有分區。

> CREATE TABLE e2 (  id INT NOT NULL,  fname VARCHAR(30),  lname VARCHAR(30)  );   

或者create table e2 like e; alter table e2 remove partitioning; 即可創建相同表結構的不帶分區參數的表e2

觀察分區表的中的數據：

> SELECT partition_NAME,TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME='e';  +------------------+--------------+  | partition_NAME   |   TABLE_ROWS |  |------------------+--------------|  | p0               |            1 |  | p1               |            0 |  | p2               |            0 |  | p3               |            3 |  +------------------+--------------+  > SELECT partition_NAME,TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME='e2';  

可以看到e2表暫時是空的

使用alter table命令交換數據：

> ALTER TABLE e exchange PARTITION p0 WITH TABLE e2;  

將表e的分區p0中的數據移動到表e2中。

再次查看錶e中分區的數據，可以看到p0中已經沒有數據了。

 > SELECT partition_NAME,TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME='e';  +------------------+--------------+  | partition_NAME   |   TABLE_ROWS |  |------------------+--------------|  | p0               |            0 |  | p1               |            0 |  | p2               |            0 |  | p3               |            3 |  +------------------+--------------+

查看e2表，可以看到多了一條數據，就是從e交換而來的。

> SELECT * from e2;    +------+---------+---------+  |   id | fname   | lname   |  |------+---------+---------|  |   33 | zhang   | san     |  +------+---------+---------+