4.2 InnoDB存储引擎之表(Named File Formats、约束、视图、分区)

4.5 Named File Formats 机制

• 长字符类型字段的存储。这些新的页数据结构和之前版本的页并不兼容，因此从 InnoDB 1.0.x版本开始，InnoDB存储引通过 Named File Formats机制来解决不同版本下页结构兼容性的问题。
• InnoDB存储引擎将1.0.x 版本之前的文件格式（file format）定义为 Antelope，将这个版本支持的文件格式定义为 Barracuda。新的文件格式总是包含于之前的版本的页格式。图 4-8显示了Barracuda 文件格式和 Antelope 文件格式之间的关系，Antelope文件格式有Compact 和 Redudant 的行格式，Barracuda 文件格式既包括了 Antelope 所有的文件格式，另外新加入了之前已经提到过的 Compressed和 Dynamic行格式。
  
• 参数 innodb_file_format 用来指定文件格式，可以通过下面的方式来查看当前所使用的InnoDB存储引擎的文件格式。
  
• 参数 innodb_filc_format_check 用来检测当前 InnoDB存储引擎文件格式的支持度，该值默认为 ON，如果出现不支持的文件格式，用户可能在错误日志文件中看到类似如下的错误∶
  
  

4.6 约束

4.6.1 数据完整性

• 关系型数据库系统和文件系统的一个不同点是，关系数据库本身能保证存储数据的完整性，不需要应用程序的控制，而文件系统一般需要在程序端进行控制。 当前几平所有的关系型数据库都提供了约束（constraint）机制，该机制提供了一条强大而简易的途径来保证数据库中数据的完整性。一般来说，数据完整性有以下三种形式∶

• 实体完整性保证表中有一个主键。在 InnoDB存储引擎表中，用户可以通过定义Primary Key 或 Unique Key 约束来保证实体的完整性。用户还可以通过编写一个触发器来保证数据完整性。

• 域完整性保证数据每列的值满足特定的条件。在 InnoDB存储引擎表中，域完整性可以通过以下几种途径来保证∶

1. 选择合适的数据类型确保一个数据值满足特定条件。
2. 外键（Foreign Key）约束。
3. 编写触发器。
4. 还可以考虑用DEFAULT约束作为强制域完整性的一个方面。

• 参照完整性保证两张表之间的关系。InnoDB存储引擎支持外键，因此允许用户定义外键以强制参照完整性，也可以通过编写触发器以强制执行。
  对于InnoDB存储引擎本身而言，提供了以下几种约束∶

1. Primary Key
2. Unique Key
3. Foreign Key
4. Default
5. NOT NULL

4.6.2 约束的创建和查找

约束的创建可以采用以下两种方式∶

• 表建立时就进行约束定义
• 利用 ALTER TABLE 命令来进行创建约束

对 Unique Key（唯一索引）的约束，用户还可以通过命令 CREATE UNIQUE INDEX 来建立。对于主键约束而言，其默认约束名为PRIMARY。而对于Unique Key 约束而言，默认约束名和列名一样，当然也可以人为指定 Unique Key约束的名字。Foreign Key 约束似乎会有一个比较神秘的默认名称。下面是一个简单的创建表的语句，表上有一个主键和一个唯一键∶

接着来看 Foreign Key的约束。为了创建 Foreign Key，用户必须创建另一张表，例如在下面的示例中创建表 p。

4.6.3 约束和索引的区别

在前面的小节中已经看到 Primary Key 和 Unique Key 的约束，有人不禁会问∶这不就是通常创建索引的方法吗?那约束和索引有什么区别呢?
的确，当用户创建了一个唯一索引就创建了一个唯一的约束。但是约束和索引的概念还是有所不同的，约束更是一个逻辑的概念，用来保证数据的完整性，而索引是一个数据结构，既有逻辑上的概念，在数据库中还代表着物理存储的方式。

4.6.4 对错误数据的约束

• 在某些默认设置下，MySOL 数据库允许非法的或不正确的数据的插入或更新，又或者可以在数据库内部将其转化为一个合法的值，如向 NOT NULL 的字段插入一个NULL值，MySOL数据库会将其更改为 0 再进行插入，因此数据库本身没有对数据的正确性进行约束。
• 如果用户想通过约束对于数据库非法数据的插入或更新，即 MySQL 数据库提示报错而不是警告，那么用户必须设置参数sql_mode，用来严格审核输入的参数，如∶
  SET sql_mode = 'STRICT_TRANS_TABLES'
  

4.6.5 ENUM和 SET约束

MySQL数据库不支持传统的 CHECK约束，但是通过 ENUM 和 SET类型可以解决部分这样的约束需求。例如表上有一个性别类型，规定域的范围只能是male 或 female，在这种情况下用户可以通过 ENUM类型来进行约束。

这次对非法的输入值进行了约束，但是只限于对离散数值的约束，对于传统CHECK约束支持的连续值的范围约束或更复杂的约束，ENUM 和 SET类型还是无能为力，这时用户需要通过触发器来实现对于值域的约束。

4.6.6 触发器与约束

• 触发器的作用是在执行INSERT、DELETE 和UPDATE命令之前或之后自动调用SQL 命令或存储过程。MySQL 5.0对触发器的实现还不是非常完善，限制比较多，MySQL 5.1 开始触发器已经相对稳定，功能也较之前有了大幅的提高。
  创建触发器的命令是CREATE TRIGGER，只有具备 Super权限的 MySQL 数据库用户才可以执行这条命令∶
  
• 最多可以为一个表建立6个触发器，即分别为INSERT、UPDATE、DELETE的BEFORE 和 AFTER各定义一个。BEFORE和 AFTER代表触发器发生的时间，表示是在每行操作的之前发生还是之后发生。当前 MySOL数据库只支持FOR EACH ROW的触发方式，即按每行记录进行触发，不支持像 DB2的 FOR EACH STATEMENT的触发方式。
• 通过触发器，用户可以实现MySOL数据库本身并不支持的一些特性，如对干传统CHECK 约束的支持，物化视图、高级复制、审计等特性。这里先关注触发器对于约束的支持。
• 假设有张用户消费表，每次用户购买一样物品后其金额都是减的，若这时有"不怀好意"的用户做了类似减去一个负值的操作，这样用户的钱没减少反而会不断增加，如∶

4.6.7 外键约束

外键用来保证参照完整性，MySQL 数据库的 MyISAM存储引擎本身并不支持外键，对于外键的定义只是起到一个注释的作用。而InnoDB存储引擎则完整支持外键约束。外键的定义如下∶

• 一般来说，称被引用的表为父表，引用的表称为子表。外键定义时的ON DELETE 和 ON UPDATE表示在对父表进行DELETE 和UPDATE操作时，对子表所做的操作，可定义的子表操作有∶

1. CASCADE
2. SET NULL
3. NO ACTION
4. RESTRICT

• CASCADE表示当父表发生 DELETE或UPDATE操作时，对相应的子表中的数据也进行DELETE或UPDATE操作。SET NULL表示当父表发生DELETE或 UPDATE 操作时，相应的子表中的数据被更新为 NULL 值，但是子表中相对应的列必须允许为NULL值。NO ACTION表示当父表发生DELETE或 UPDATE操作时，抛出错误，不允许这类操作发生。RESTRICT表示当父表发生 DELETE或 UPDATE操作时，抛出错误，不允许这类操作发生。如果定义外键时没有指定ON DELETE或ON UPDATE，RESTRICT就是默认的外键设置。
• 在其他数据库中，如 Oracle数据库，有一种称为延时检查（deferred check）的外键约束，即检查在 SQL语句运行完成后再进行。而目前 MySOL数据库的外键约束都是即时检查（immediate check），因此从上面的定义可以看出，在 MySQL数据库中NO ACTION和 RESTRICT的功能是相同的。
• InnoDB存储引擎在外键建立时会自动地对该列加一个索引。因此可以很好地避免外键列上无索引而导致的死锁问题的产生。例如在上述的例子中，表child创建时只定义了外键，并没有手动指定 parent_id列为索引，但是通过命令 SHOW CREATE TABLE可以发现 InnoDB存储引擎自动为外键约束的parent_id添加了索引∶

• 对于参照完整性约束，外键能起到一个非常好的作用。但是对于数据的导入操作时，外键往往导致在外键约束的检查上花费大量时间。因为 MySQL 数据库的外键是即时检查的，所以对导入的每一行都会进行外键检查。但是用户可以在导入过程中忽视外键的检查，如∶
  

4.7 视图

在 MySQL数据库中，视图（View）是一个命名的虚表，它由一个SQL查询来定义，可以当做表使用。与持久表（permanent table）不同的是，视图中的数据没有实际的物理存储。

4.7.1 视图的作用

• 视图在数据库中发挥着重要的作用。视图的主要用途之一是被用做一个抽象装置，特别是对干一些应用程序，程序本身不需要关心基表（base table）的结构，只需要按照视图定义来取数据或更新数据，因此，视图同时在一定程度上起到一个安全层的作用。
• MySQL 数据库从5.0版本开始支持视图。

4.8 分区表

4.8.1 分区概述

• 分区功能并不是在存储引擎层完成的，因此不是只有InnoDB存储引擎支持分区，常见的存储引擎 MyISAM、NDB等都支持。但也并不是所有的存储引擎都支持， 如CSV、FEDORATED、MERGE等就不支持。在使用分区功能前，应该对选择的存储引擎对分区的支持有所了解。
• MySQL 数据库在5.1版本时添加了对分区的支持。分区的过程是将一个表或索引分解为多个更小、更可管理的部分。就访问数据库的应用而言，从逻辑上讲，只有一个表或一个索引，但是在物理上这个表或索引可能由数十个物理分区组成。每个分区都是独立的对象，可以独自处理，也可以作为一个更大对象的一部分进行处理。
• MySQL 数据库支持的分区类型为水平分区（不同行的数据放到不同物理文件），并不支持垂直分区（不同列的数据放到不同物理文件）。此外，MySQL 数据库的分区是局部分区索引，一个分区中既存放了数据又存放了索引。而全局分区是指，数据存放在各个分区中，但是所有数据的索引放在一个对象中。目前，MySQL数据库还不支持全局分区。
• 可以通过以下命令来查看当前数据库是否启用了分区功能∶
  show variables like '%partition%';
  也可以通过show plugins; 来查看
  
• 大多数DBA会有这样一个误区∶只要启用了分区，数据库就会运行得更快。这个结论是存在很多问题的。就我的经验看来，分区可能会给某些 SOL 语句性能带来提高，但是分区主要用于数据库高可用性的管理。在 OLTP应用中，对于分区的使用应该非常小心。总之，如果只是一味地使用分区，而不理解分区是如何工作的，也不清楚你的应用如何使用分区，那么分区极有可能会对性能产生负面的影响。
  当前 MySQL 数据库支持以下几种类型的分区。

1. RANGE 分区∶行数据基于属于一个给定连续区间的列值被放入分区。MySQL5.5开始支持RANGE COLUMNS的分区。
2. LIST分区∶和 RANGE分区类型，只是 LIST分区面向的是离散的值。MySQL5.5开始支持LIST COLUMNS的分区。
3. HASH分区∶根据用户自定义的表达式的返回值来进行分区，返回值不能为负数。
4. KEY分区∶根据 MySQL数据库提供的哈希函数来进行分区。
   不论创建何种类型的分区，如果表中存在主键或唯一索引时，分区列必须是唯一索引的一个组成部分。

• 唯一索引可以是允许 NULL值的，并且分区列只要是唯一索引的一个组成部分，不需要整个唯一索引列都是分区列，如∶
  

• 如果建表时没有指定主键，唯一索引，可以指定任何一个列为分区列。

4.8.2 分区类型

1.RANGE分区
我们介绍的第一种分区类型是RANGE分区，也是最常用的一种分区类型。下面的CREATE TABLE语句创建了一个id列的区间分区表。当id小于10时，数据插入 p0分区。当id大于等于10小于20时，数据插入p1分区。

查看表在磁盘上的物理文件，启用分区之后，表不再由一个ibd文件组成了，而是由建立分区时的各个分区 ibd文件组成，如下面的t#P#p0.ibd，t#P#p1.ibd∶

• TABLE ROWS列反映了每个分区中记录的数量。由于之前向表中插入了9、10、15三条记录，因此可以看到，当前分区p0中有1条记录，分区p1中有2条记录。PARTITION METHOD表示分区的类型，这里显示的是RANGE。

• 对于表t，由于我们定义了分区，因此对于插入的值应该严格遵守分区的定义，当插入一个不在分区中定义的值时，MySQL数据库会抛出一个异常。如下所示，我们向表t 中插入30这个值。
  

• 对于上述问题，我们可以对分区添加一个MAXVALUE 值的分区。MAXVALUE可以理解为正无穷，因此所有大于等于20且小于MAXVALUE 的值别放入 p2分区。
  

• RANGE主要用于日期列的分页，比如可以根据年来分区存放销售记录。

• 
  这样创建的好处是，便于对 sales 这张表的管理。如果我们要删除 2008年的数据，不需要执行DELETE FROM sales WHERE date>='2008-01-01’and date<‘2009-01-01’，只需删除2008年数据所在的分区即可∶
  
  这样创建的另一个好处是可以加快某些查询操作，如果我们只需要查询2008年整年的销售额，可以这样∶

mysql > EXPAIN PARTITIONS
		>SELECT * FROM sales  WHERE date>='2008-01-01' AND date<='2008-12-31';

这样的话只会查询p2008这个分区，不会查询所有分区。

• 但是这里又会有点不同

mysql > EXPAIN PARTITIONS
		>SELECT * FROM sales  WHERE date>='2008-01-01' AND date<'2009-01-01';

• 这次条件改为 date<'2009-01-01·而不是 date<=·2008-12-31·时，优化器会选择搜索 p2008和 p2009两个分区，这是我们不希望看到的。因此对于启用分区，应该根据分区的特性来编写最优的SQL 语句。
  对于sales 这张分区表，我曾看到过另一种分区函数，设计者的原意是按照每年每月进行分区，
  
  但是在执行 SQL 语句时开发人员发现，优化器不会根据分区进行选择，即使他们编写的 SQL语句已经符合了分区的要求，如∶
  
  可以看到优化对分区 p201001，p201002，p201003都进行了搜索。产生这个问题的主要原因是对于RANGE分区的查询，优化器只能对YEAR()，TO DAYS()，TO SECONDSO()，UNIX TIMESTAMP() 这类函数进行优化选择，因此对于上述的要求，需要将分区函数改为 TO_DAYS，如∶

mysql> CREATE TABLE sales{
	-> money INT UNSIGNED NOT NULL, 
	-> date DATETO,E
	 ->) ENGINE=INNODB
	-> PARTITION by range (TO_DAYS(date))(
	-> PARTITION p201001
	-> VALUES LESSs THEN(TO_DAYS('2010-02-01')), -> PARTITION p201002
	-> VALUES LESS THEN(TO_DAYS('2010-03-01')), -> PARTITION p201003
	->VALUES LESs THEN(TO DAYS('2010-04-01'))->);

此时就可以进行分区查询了

2. LIST分区
LIST分区和RANGE 分区非常相似，只是分区列的值是离散的，而非连续的。如∶

	mysql> 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(0,2,4,6,8)
		->);
	Query OK,0 rows affected (0.26 sec)
	Records:1 Duplicates: 0 Warnings: 0
	
mysql> INSERT INTO t SELECT 1,2;
	Query OK,1 row affected (0.03 sec)Records: 1 Duplicates: 0 Warnings: 0

mysql> INSERT INTO t SELECT 1,3;
	Query OK,1 row affected (0.03 sec)Records: 1 Duplicates:0 Warnings: 0
	
mysql> INSERT INTO t SELECT 1,4;
	Query OK,1 row affected (0.03 sec)Records:1 Duplicates: 0 Warnings:0
	
mysql> SELECT table_name,partition_name, table_rows
	-> FROM information_schema.PARTITIONS
	-> WHERE table_name='t'AND table_schema=DATABASE()\G;
*************************** 1.FOW*************************
	table_name: t 
	partition_name: p0
	table rows: 2
*************************** 2.FOW*************************
	table name: t 
	partition_name: pl
	table_rows:2
	2 rows in set (0.00 sec)

如果插入的值不在分区，MYSQL同样会报错。

• 另外，在用INSERT插入多个行数据的过程中遇到分区未定义的值时，MyISAM和InnoDB存储引擎的处理完全不同。MyISAM引擎会将之前的行数据都插入，但之后的数据不会被插入。而InnoDB存储引擎将其视为一个事务，因此没有任何数据插入。在使用分区时，要对不同引擎的事务特性进行考虑。

3.HASH 分区

• HASH分区的目的是将数据均匀地分布到预先定义的各个分区中，保证各分区的数据数量大致都是一样的。在 RANGE 和LIST分区中，必须明确指定一个给定的列值或列值集合应该保存在哪个分区中;而在HASH分区中，MySQL 自动完成这些工作，用户所要做的只是基于将要进行哈希分区的列值指定一个列值或表达式，以及指定被分区的表将要被分割成的分区数量。

• 要使用HASH分区来分割一个表，要在 CREATE TABLE语句上添加一个"PARTITION BY HASH（expr）"子句，其中"expr"是一个返回一个整数的表达式。它可以仅仅是字段类型为 MySQL整型的列名。此外，用户很可能需要在后面再添加一个"PARTITIONS num’子句，其中 mum是一个非负的整数，它表示表将要被分割成分区的数量。如果没有包括一个 PARTITIONS子句，那么分区的数量将默认为1。
  下面的例子创建了一个HASH分区的表t，分区按日期列 b 进行∶
  
  

• 可以看到 p2分区有1条记录。当然这个例子中也许并不能把数据均匀地分布到各个分区中去，因为分区是按照YEAR函数进行的，而这个值本身可是离散的。如果对于连续的值进行 HASH分区，如自增长的主键，则可以较好地将数据进行平均分布。

• MySQL 数据库还支持一种称为LINEAR HASH的分区，它使用一个更加复杂的算法来确定新行插入到已经分区的表中的位置。它的语法和 HASH分区的语法相似，只是将关键字HASH改为LINEAR HASH。下面创建一个LINEAR HASH的分区表t_linear_hash，它和之前的表t_hash相似，只是分区类型不同。
  
  
  LINEAR HASH分区的优点在于，增加、删除、合并和拆分分区将变得更加快捷，这有利于处理含有大量数据的表。它的缺点在于，与使用HASH分区得到的数据分布相比，各个分区间数据的分布可能不大均衡。

4.KEY分区
KEY分区和 HASH分区相似，不同之处在于HASH分区使用用户定义的函数进行分区，KEY分区使用MySQL 数据库提供的函数进行分区。对于NDB Cluster 引擎，MySQL数据库使用MD5 函数来分区;对于其他存储引擎，MySOL数据库使用其内部的哈希函数，这些函数基于与PASSWORD()一样的运算法则。如∶

• 在 KEY分区中使用关键字LINEAR和在 HASH分区中使用具有同样的效果，分区的编号是通过2的幂（powers-of-two）算法得到的，而不是通过模数算法。

5.COLUMNS 分区
在前面介绍的RANGE、LIST、HASH 和KEY这四种分区中，分区的条件是∶数据必须是整型（interger），如果不是整型，那应该需要通过函数将其转化为整型，如YEAR()，TO_DAYS()，MONTH()等函数。MySQL5.5版本开始支持COLUMNS分区，可视为RANGE分区和LIST分区的一种进化。COLUMINS分区可以直接使用非整型的数据进行分区，分区根据类型直接比较而得，不需要转化为整型。此外，RANGECOLUMNS分区可以对多个列的值进行分区。

• COLUMNS分区支持以下的数据类型∶

1. 所有的整型类型，如 INT、SMALLINT、TINYINT、BIGINT。FLOAT和 DECIMAL则不予支持。
2. 日期类型，如 DATE 和 DATETIME。其余的日期类型不予支持。
3. 字符串类型，如 CHAR、VARCHAR、BINARY和 VARBINARY。BLOB和 TEXT类型不予支持。
   对于日期类型的分区，我们不再需要 YEAR()和 TO DAYS()函数了，而直接可以使用COLUMNS。

4.8.3 子分区

子分区（subpartitioning）是在分区的基础上再进行分区，有时也称这种分区为复合分区（composite partitioning）。MySQL数据库允许在RANGE和LIST的分区上再进行HASH或KEY的子分区。子分区可以用于特别大的表，在多个磁盘间分别分配数据和索引。

4.8.4 分区中的NULL值

MySQL 数据库允许对NULL值做分区，但是处理的方法与其他数据库可能完全不同。MYSOL数据库的分区总是视NULL值视小于任何的一个非NULL值，这和MySQL数据库中处理NULL值的 ORDER BY操作是一样的。因此对于不同的分区类型，MySQL数据库对于NULL 值的处理也是各不相同。

• 对于RANGE分区，如果向分区列插入了NULL值，则 MySQL 数据库会将该值放入最左边的分区。
• 在LIST分区下要使用NULL值，则必须显式地指出哪个分区中放入NULL值，否则会报错。
• HASH和KEY分区对于NULL的处理方式和RANGE分区、LIST分区不一样。任何分区函数都会将含有NIIII 值的i记录返向为0。

4.8.5 分区和性能

• 我常听到开发人员说"对表做个分区"，然后数据库的查询就会快了。这是真的吗?实际上可能根本感觉不到查询速度的提升，甚至会发现查询速度急剧下降。因此，在合理使用分区之前，必须了解分区的使用环境。
• 数据库的应用分为两类∶一类是 OLTP（在线事务处理），如 Blog、电子商务、网络游戏等;另一类是 OLAP（在线分析处理），如数据仓库、数据集市。在一个实际的应用环境中，可能既有OLTP的应用，也有OLAP的应用。如网络游戏中，玩家操作的游戏数据库应用就是OLTP 的，但是游戏厂商可能需要对游戏产生的日志进行分析，通过分析得到的结果来更好地服务于游戏，预测玩家的行为等，而这却是OLAP的应用。
• 对于 OLAP的应用，分区的确是可以很好地提高查询的性能，因为 OLAP 应用大多数查询需要频繁地扫描一张很大的表。假设有一张 1亿行的表，其中有一个时间戳属性列。用户的查询需要从这张表中获取一年的数据**。如果按时间戳进行分区，则只需要扫描相应的分区即可**。这就是前面介绍的 Partition Pruning 技术。
• 然而对于OLTP 的应用，分区应该非常小心。在这种应用下，通常不可能会获取一张大表中10%的数据，大部分都是通过索引返回几条记录即可。而根据B+树索引的原理可知，对于一张大表，一般的 B+树需要2～3次的磁盘IO。因此 B+树可以很好地完成操作，不需要分区的帮助，并且设计不好的分区会带来严重的性能问题。
• 我发现很多开发团队会认为含有1000W行的表是一张非常巨大的表，所以他们往往会选择采用分区，如对主键做10个HASH的分区，这样每个分区就只有 100W的数据了，因此查询应该变得更快了，如 SELECT*FROM TABLE WHERE PK=@pk。但是有没有考虑过这样一种情况∶100W 和 1000W行的数据本身构成的 B+ 树的层次都是一样的，可能都是2层，那么上述走主键分区的索引并不会带来性能的提高。如果 1000W的 B+树的高度是3，100W的B+树的高度是2，那么上述按主键分区的索引可以避免1次 IO，从而提高查询的效率。这没问题，但是这张表只有主键索引，没有任何其他的列需要查询的。如果还有类似如下的 SOL 语句SELECT* FROM TABLE WHERE KEY=@key，这时对于KEY 的查询需要扫描所有的10个分区，即使每个分区的查询开销为2次IO，则一共需要20次IO。而对于原来单表的设计，对于KEY的查询只需要2～3次IO。
• 接着来看如下的表 Profile，根据主键 ID进行了HASH分区，HASH分区的数量为10，表 Profile 有接近1000W的数据∶
  
  十个分区之间数据大致比较均匀。
  

如果进行主键查询，分区的确是有意义的。

mysql> EXPLAIN PARTITIONS SELECT * FROM Profile WHERE id=1\G;
*************************** 1.rOW ***************************
	id: 1
	select_type:SIMPLE
	table: Profile partitions: p1
	type: const
	possible_keys:PRIMARY
	key:PRIMARY 
	key_len:4
	ref: const 
	rows:1 
	Extra:
	1 row in set(0.00 sec)

可以发现只寻找了p1 分区，但是对于表 Profile 中 nickname 列索引的查询，EXPLAIN PARTITIONS 则会得到如下的结果∶

可以看到，MySQL数据库会搜索所有分区，因此查询速度上会慢很多，比较上述的语句∶

InnoDB在OLTP的情况下分区要特别小心。

4.8.6 在表和分区间交换数据

MySQL5.6开始支持ALTER TABLE…EXCHANGE PARTITION语法。该语句允许分区或子分区中的数据与另一个非分区的表中的数据进行交换。如果非分区表中的数据为空，那么相当于将分区中的数据移动到非分区表中。若分区表中的数据为空，则相当于将外部表中的数据导入到分区中。
要使用ALTER TABLE…·EXCHANGEPARTITIQN 语句，必须满足下面的条件∶

1. 要交换的表需和分区表有着相同的表结构，但是表不能含有分区
2. 在非分区表中的数据必须在交换的分区定义内
3. 被交换的表中不能含有外键，或者其他的表含有对该表的外键引用
4. 用户除了需要 ALTER、INSERT和 CREATE 权限外，还需要 DROP 的权限

此外，有两个小的细节需要注意∶

1. 使用该语句时，不会触发交换表和被交换表上的触发器
2. AUTO_INCREMENT列将被重置
   接着来看一个例子，首先创建含有 RANGE分区的表e，并填充相应的数据∶