MySQL 5.7 列表分区

列表分区
MySQL中的列表分区在很多方面与范围分区类似。与按范围分区一样，每个分区都必须显式定义。这两种分区的主要区别在于，在列表分区中， 每个分区是根据列值在一组值列表中的成员关系定义和选择的，而不是根据一组连续范围的值的成员关系。这是通过使用分区列表（expr）来完 成的，其中expr是一个列值或一个基于列值并返回整数值的表达式，然后通过（value_list）中的值来定义每个分区，其中value_list是一个逗 号分隔的整数值列表。与由range定义分区的情况不同，列表分区不需要以任何特定顺序声明。

对于下面的示例，我们假设要分区的表的基本定义由如下所示的CREATE table语句提供：

CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT,
store_id INT
);

要对表进行分区，使属于同一个区域的数据行存储在同一个分区中，用户可以使用下面的CREATE table语句：

mysql> CREATE TABLE employees (
    -> id INT NOT NULL,
    -> fname VARCHAR(30),
    -> lname VARCHAR(30),
    -> hired DATE NOT NULL DEFAULT '1970-01-01',
    -> separated DATE NOT NULL DEFAULT '9999-12-31',
    -> job_code INT,
    -> store_id INT
    -> )
    -> PARTITION BY LIST(store_id) (
    -> PARTITION pNorth VALUES IN (3,5,6,9,17),
    -> PARTITION pEast VALUES IN (1,2,10,11,19,20),
    -> PARTITION pWest VALUES IN (4,12,13,14,18),
    -> PARTITION pCentral VALUES IN (7,8,15,16)
    -> );
Query OK, 0 rows affected (0.02 sec)

这使得向表中添加或删除特定区域的员工记录变得容易。例如，假设西部地区的所有门店都卖给了另一家公司。在 MySQL 5.7 中，与该地区门 店员工相关的所有行都可以通过执行 ALTER TABLE employees TRUNCATE PARTITION pWest 这个查询来删除，这比执行等效的 DELETE 语句 DELETE FROM employees WHERE store_id IN (4，12，13，14，18)； 要高效得多。（使用 ALTER TABLE employees DROP PARTITION pWest 也 会删除所有这些行，但还会从表的定义中移除分区 pWest；您需要使用 ALTER TABLE…ADD PARTITION 语句来恢复表的原始分区方案。）

与范围分区一样，也可以将列表分区与按哈希或键分区结合起来，以生成复合分区（子分区）。

与范围分区不同，这里没有诸如 MAXVALUE 这样的“万能”选项；分区表达式的所有预期值都应在 PARTITION … VALUES IN (…) 子句中涵 盖。包含未匹配分区列值的 INSERT 语句会因错误而失败，如下例所示：

mysql> CREATE TABLE h2 (
    ->   c1 INT,
    ->   c2 INT
    ->   )
    ->   PARTITION BY LIST(c1) (
    ->   PARTITION p0 VALUES IN (1, 4, 7),
    ->   PARTITION p1 VALUES IN (2, 5, 8)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO h2 VALUES (3, 5);
ERROR 1526 (HY000): Table has no partition for value 3

使用单个 INSERT 语句插入多行时，其行为取决于表是否使用事务存储引擎。对于 InnoDB 表，该语句被视为单个事务，因此任何不匹配的值的 存在都会导致整个语句完全失败，且不会插入任何行。对于使用非事务存储引擎（如 MyISAM）的表，包含不匹配值的行之前的任何行都会被插 入，但之后的行则不会。

使用IGNORE关键字可以忽略这种类型的错误。如果这样做，包含不匹配的分区列值的行不会被插入，但是包含匹配值的行会被插入，并且不会报告错误：

mysql> TRUNCATE h2;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM h2;
Empty set (0.00 sec)

mysql> INSERT IGNORE INTO h2 VALUES (2, 5), (6, 10), (7, 5), (3, 1), (1, 9);
Query OK, 3 rows affected, 2 warnings (0.00 sec)
Records: 5  Duplicates: 2  Warnings: 2

mysql> SELECT * FROM h2;
+------+------+
| c1   | c2   |
+------+------+
|    7 |    5 |
|    1 |    9 |
|    2 |    5 |
+------+------+
3 rows in set (0.00 sec)

MySQL 5.7支持列表列分区。这是列表分区的一个变体，允许您使用整数类型以外的其他类型的列进行分区，也可以使用多个列作为分区键。