MySQL分区表姿势

保持热爱奔赴山海

发布于 2019-09-17 11:05:30

5.5K0

发布于 2019-09-17 11:05:30

文章被收录于专栏：饮水机管理员饮水机管理员

大部分内容整理自姜承尧的innodb存储引擎2学习笔记。

分区：

分区的功能不是在存储引擎层实现的。因此不只是InnoDB才支持分区。MyISAM、NDB都支持分区操作。

分区的过程是将一个表或者索引分解为多个更小、更可管理的部分。从逻辑上将，只有一个表或者索引，但是在物理上这个表或索引可能由数十个物理分区组成。

每个分区都是独立的对象，可以独自处理，也可以作为一个更大对象的一部分进行处理。

MySQL只支持水平分区，不支持垂直分区。

水平分区：将同一表中不同行的记录分配到不同的物理文件中。

垂直分区：将同一表中不同列的记录分配到不同的物理文件中。

MySQL数据库的分区是局部分区索引。一个分区中既存放了数据又存放了索引。而全局分区索引指的是数据存放在各个分区中，但是所有数据的索引放在一个对象中。MySQL暂时还不支持全局分区索引。

查看当前数据库是否启用了分区功能：

> show plugins\G 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分区(p0\p1\p2)，再进行hash分区(sp0\sp1)。(合计共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     |
+------+---------+---------+

本文参与腾讯云自媒体分享计划，分享自作者个人站点/博客。

原始发表：2017-08-12 ，如有侵权请联系 cloudcommunity@tencent.com 删除

sql