MySQL8-中文参考-四十五-
MySQL8 中文参考（四十五）
26.2.1 范围分区
通过范围进行分区的表是这样分区的，即每个分区包含分区表达式值位于给定范围内的行。范围应该是连续的但不重叠，并且使用VALUES LESS THAN运算符定义。在接下来的几个示例中，假设你正在创建一个类似以下内容的表，用于保存一家由 1 到 20 号店编号的连锁视频店的人事记录：
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 NOT NULL,
store_id INT NOT NULL
);
注意
这里使用的employees表没有主键或唯一键。虽然示例在当前讨论的目的上可以正常工作，但你应该记住，在实践中，表极有可能具有主键、唯一键或两者，而用于分区列的可选选择取决于用于这些键的列，如果有的话。有关这些问题的讨论，请参阅 Section 26.6.1, “Partitioning Keys, Primary Keys, and Unique Keys”。
这个表可以根据你的需求以多种方式进行范围分区。一种方法是使用store_id列。例如，你可以决定通过添加如下所示的PARTITION BY RANGE子句将表分区为 4 部分：
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 NOT NULL,
store_id INT NOT NULL
)
PARTITION BY RANGE (store_id) (
PARTITION p0 VALUES LESS THAN (6),
PARTITION p1 VALUES LESS THAN (11),
PARTITION p2 VALUES LESS THAN (16),
PARTITION p3 VALUES LESS THAN (21)
);
在这个分区方案中，所有在 1 到 5 号店工作的员工对应的行都存储在p0分区中，而在 6 到 10 号店工作的员工对应的行存储在p1分区中，依此类推。每个分区按顺序定义，从最低到最高。这是PARTITION BY RANGE语法的要求；在这方面，你可以将其类比为 C 或 Java 中一系列if ... elseif ...语句。
很容易确定包含数据(72, 'Mitchell', 'Wilson', '1998-06-25', DEFAULT, 7, 13)的新行被插入到p2分区中，但当你的连锁店增加到第 21 家店时会发生什么？在这个方案下，没有覆盖store_id大于 20 的行的规则，因此会导致错误，因为服务器不知道将其放在哪里。你可以通过在CREATE TABLE语句中使用“catchall”VALUES LESS THAN子句来避免这种情况，提供所有大于明确命名的最高值的值：
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 NOT NULL,
store_id INT NOT NULL
)
PARTITION BY RANGE (store_id) (
PARTITION p0 VALUES LESS THAN (6),
PARTITION p1 VALUES LESS THAN (11),
PARTITION p2 VALUES LESS THAN (16),
*PARTITION p3 VALUES LESS THAN MAXVALUE* );
（与本章中的其他示例一样，我们假设默认存储引擎是InnoDB。）
避免找不到匹配值时出现错误的另一种方法是在INSERT语句中使用IGNORE关键字。有关示例，请参见 Section 26.2.2, “LIST Partitioning”。
MAXVALUE表示一个始终大于最大可能整数值的整数值（在数学语言中，它充当最小上界）。现在，任何store_id列值大于或等于 16（定义的最高值）的行都存储在分区p3中。在将来的某个时候——当店铺数量增加到 25、30 或更多时，您可以使用ALTER TABLE语句为 21-25、26-30 等店铺添加新分区（有关如何执行此操作的详细信息，请参见第 26.3 节，“分区管理”）。
以类似的方式，可以根据员工工作代码对表进行分区，即根据job_code列值的范围进行分区。例如——假设两位数的工作代码用于普通（店内）工人，三位数的代码用于办公室和支持人员，四位数的代码用于管理职位——可以使用以下语句创建分区表：
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 NOT NULL,
store_id INT NOT NULL
)
PARTITION BY RANGE (job_code) (
PARTITION p0 VALUES LESS THAN (100),
PARTITION p1 VALUES LESS THAN (1000),
PARTITION p2 VALUES LESS THAN (10000)
);
在这种情况下，所有与店内工人相关的行将存储在分区p0中，与办公室和支持人员相关的行将存储在p1中，与管理人员相关的行将存储在分区p2中。
也可以在VALUES LESS THAN子句中使用表达式。但是，MySQL 必须能够将表达式的返回值作为LESS THAN（<）比较的一部分进行评估。
您可以根据两个DATE列中的一个基于表达式来进行分区，而不是根据店铺编号拆分表数据。例如，假设您希望根据每位员工离开公司的年份进行分区；也就是说，基于YEAR(separated)的值。下面显示了实现这种分区方案的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
)
PARTITION BY RANGE ( YEAR(separated) ) (
PARTITION p0 VALUES LESS THAN (1991),
PARTITION p1 VALUES LESS THAN (1996),
PARTITION p2 VALUES LESS THAN (2001),
PARTITION p3 VALUES LESS THAN MAXVALUE
);
在此方案中，所有在 1991 年之前离开的员工的行存储在分区p0中；在 1991 年至 1995 年离开的员工中，存储在p1中；在 1996 年至 2000 年离开的员工中，存储在p2中；而在 2000 年之后离开的任何工人中，存储在p3中。
也可以根据TIMESTAMP列的值，使用UNIX_TIMESTAMP()函数，基于RANGE对表进行分区，如下例所示：
CREATE TABLE quarterly_report_status (
report_id INT NOT NULL,
report_status VARCHAR(20) NOT NULL,
report_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)
PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated) ) (
PARTITION p0 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-01-01 00:00:00') ),
PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-04-01 00:00:00') ),
PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-07-01 00:00:00') ),
PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-10-01 00:00:00') ),
PARTITION p4 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-01-01 00:00:00') ),
PARTITION p5 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-04-01 00:00:00') ),
PARTITION p6 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-07-01 00:00:00') ),
PARTITION p7 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-10-01 00:00:00') ),
PARTITION p8 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-01-01 00:00:00') ),
PARTITION p9 VALUES LESS THAN (MAXVALUE)
);
不允许使用涉及TIMESTAMP值的任何其他表达式（请参见 Bug #42849）。
范围分区在以下情况之一或多个情况为真时特别有用：
-
您希望或需要删除“旧”数据。如果您正在使用先前显示的用于
employees表的分区方案，您可以简单地使用ALTER TABLE employees DROP PARTITION p0;来删除所有在 1991 年之前停止为公司工作的员工的行。（有关更多信息，请参见 第 15.1.9 节“ALTER TABLE 语句” 和 第 26.3 节“分区管理”。）对于行数众多的表，这比运行类似于DELETE FROM employees WHERE YEAR(separated) <= 1990;的DELETE查询要高效得多。 -
您希望使用包含日期或时间值的列，或包含从其他系列产生的值。
-
您经常运行依赖于用于对表进行分区的列的查询。例如，当执行类似于
EXPLAIN SELECT COUNT(*) FROM employees WHERE separated BETWEEN '2000-01-01' AND '2000-12-31' GROUP BY store_id;的查询时，MySQL 可以快速确定只需要扫描分区p2，因为其余分区不可能包含任何满足WHERE子句的记录。有关如何实现这一点的更多信息，请参见 第 26.4 节“分区修剪”。
这种分区的变体是 RANGE COLUMNS 分区。通过 RANGE COLUMNS 分区，可以使用多个列来定义适用于将行放置在分区中以及确定在执行分区修剪时包含或排除特定分区的分区范围。有关更多信息，请参见 第 26.2.3.1 节“RANGE COLUMNS 分区”。
基于时间间隔的分区方案。 如果您希望在 MySQL 8.0 中实现基于时间范围或间隔的分区方案，您有两个选项：
-
通过
RANGE对表进行分区，并对分区表达式使用在DATE、TIME或DATETIME列上操作并返回整数值的函数，如下所示：CREATE TABLE members ( firstname VARCHAR(25) NOT NULL, lastname VARCHAR(25) NOT NULL, username VARCHAR(16) NOT NULL, email VARCHAR(35), joined DATE NOT NULL ) PARTITION BY RANGE( YEAR(joined) ) ( PARTITION p0 VALUES LESS THAN (1960), PARTITION p1 VALUES LESS THAN (1970), PARTITION p2 VALUES LESS THAN (1980), PARTITION p3 VALUES LESS THAN (1990), PARTITION p4 VALUES LESS THAN MAXVALUE );在 MySQL 8.0 中，还可以根据
TIMESTAMP列的值使用RANGE对表进行分区，使用UNIX_TIMESTAMP()函数，如下例所示：CREATE TABLE quarterly_report_status ( report_id INT NOT NULL, report_status VARCHAR(20) NOT NULL, report_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated) ) ( PARTITION p0 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-01-01 00:00:00') ), PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-04-01 00:00:00') ), PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-07-01 00:00:00') ), PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-10-01 00:00:00') ), PARTITION p4 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-01-01 00:00:00') ), PARTITION p5 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-04-01 00:00:00') ), PARTITION p6 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-07-01 00:00:00') ), PARTITION p7 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-10-01 00:00:00') ), PARTITION p8 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-01-01 00:00:00') ), PARTITION p9 VALUES LESS THAN (MAXVALUE) );在 MySQL 8.0 中，不允许使用涉及
TIMESTAMP值的任何其他表达式。（参见 Bug #42849。）注意
在 MySQL 8.0 中，也可以使用
UNIX_TIMESTAMP(timestamp_column)作为按LIST分区的表的分区表达式。然而，通常不太实用。 -
通过
RANGE COLUMNS按DATE或DATETIME列作为分区列对表进行分区。例如，members表可以直接使用joined列定义，如下所示：CREATE TABLE members ( firstname VARCHAR(25) NOT NULL, lastname VARCHAR(25) NOT NULL, username VARCHAR(16) NOT NULL, email VARCHAR(35), joined DATE NOT NULL ) PARTITION BY RANGE COLUMNS(joined) ( PARTITION p0 VALUES LESS THAN ('1960-01-01'), PARTITION p1 VALUES LESS THAN ('1970-01-01'), PARTITION p2 VALUES LESS THAN ('1980-01-01'), PARTITION p3 VALUES LESS THAN ('1990-01-01'), PARTITION p4 VALUES LESS THAN MAXVALUE );
注意
使用日期或时间类型的分区列，而不是DATE或DATETIME，在RANGE COLUMNS中不受支持。
26.2.2 LIST 分区
MySQL 中的列表分区在许多方面类似于范围分区。与按RANGE分区一样，每个分区必须明确定义。两种分区类型之间的主要区别在于，在列表分区中，每个分区是根据列值在一组值列表中的成员资格而定义和选择的，而不是在一组连续值范围中的一个。这是通过使用PARTITION BY LIST(*expr*)来完成的，其中expr是一个列值或基于列值并返回整数值的表达式，然后通过VALUES IN (*value_list*)来定义每个分区，其中value_list是一个逗号分隔的整数列表。
注意
在 MySQL 8.0 中，当按LIST分区时，只能匹配一组整数（可能包括NULL—请参见第 26.2.7 节，“MySQL 分区如何处理 NULL”）。
但是，在使用LIST COLUMN分区时，可以在值列表中使用其他列类型，该分区稍后在本节中描述。
与按范围定义的分区不同，列表分区不需要按任何特定顺序声明。有关更详细的语法信息，请参见第 15.1.20 节，“CREATE TABLE Statement”。
对于接下来的示例，我们假设要分区的表的基本定义由此处显示的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
);
（这是用作第 26.2.1 节，“RANGE 分区”示例基础的相同表。与其他分区示例一样，我们假设default_storage_engine为InnoDB。）
假设有 20 家视频商店分布在 4 个特许经营店中，如下表所示。
| 地区 | 商店 ID 号码 |
|---|---|
| 北部 | 3, 5, 6, 9, 17 |
| 东部 | 1, 2, 10, 11, 19, 20 |
| 西部 | 4, 12, 13, 14, 18 |
| 中部 | 7, 8, 15, 16 |
要将此表分区，使属于同一地区的商店行存储在同一分区中，您可以使用此处显示的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
)
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)
);
这使得很容易向表中添加或删除与特定区域相关的员工记录。例如，假设西区的所有商店都被卖给另一家公司。在 MySQL 8.0 中，可以使用查询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语句来恢复表的原始分区方案。）
与RANGE分区一样，可以将LIST分区与哈希或键分区组合以生成复合分区（子分区）。请参见第 26.2.6 节，“子分区”。
与RANGE分区不同，没有像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.11 sec)
mysql> INSERT INTO h2 VALUES (3, 5);
ERROR 1525 (HY000): Table has no partition for value 3
当使用单个INSERT语句将多行插入单个InnoDB表时，InnoDB将该语句视为单个事务，因此任何不匹配的值的存在都会导致该语句完全失败，因此不会插入任何行。
你可以通过使用IGNORE关键字来忽略这种类型的错误，尽管对于每一行包含不匹配的分区列值的情况会发出警告，如下所示。
mysql> TRUNCATE h2;
Query OK, 1 row affected (0.00 sec)
mysql> TABLE 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.01 sec)
Records: 5 Duplicates: 2 Warnings: 2
mysql> SHOW WARNINGS;
+---------+------+------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------+
| Warning | 1526 | Table has no partition for value 6 |
| Warning | 1526 | Table has no partition for value 3 |
+---------+------+------------------------------------+
2 rows in set (0.00 sec)
您可以在以下TABLE语句的输出中看到，包含不匹配的分区列值的行被静默拒绝，而不包含不匹配值的行被插入到表中：
mysql> TABLE h2;
+------+------+
| c1 | c2 |
+------+------+
| 7 | 5 |
| 1 | 9 |
| 2 | 5 |
+------+------+
3 rows in set (0.00 sec)
MySQL 还支持LIST COLUMNS分区，这是LIST分区的一种变体，允许您使用除整数以外的其他类型的列作为分区列，并使用多个列作为分区键。有关更多信息，请参见第 26.2.3.2 节，“LIST COLUMNS 分区”。
26.2.3 列分区
原文：
dev.mysql.com/doc/refman/8.0/en/partitioning-columns.html
26.2.3.1 范围列分区
26.2.3.2 列表列分区
接下来的两节讨论COLUMNS分区，这是RANGE和LIST分区的变体。COLUMNS分区允许在分区键中使用多个列。所有这些列都被考虑用于将行放入分区以及确定哪些分区要检查以匹配行进行分区修剪。
此外，RANGE COLUMNS分区和LIST COLUMNS分区都支持使用非整数列来定义值范围或列表成员。允许的数据类型如下列表所示：
-
所有整数类型：
TINYINT,SMALLINT,MEDIUMINT,INT(INTEGER), 和BIGINT。（这与按RANGE和LIST进行分区相同。）其他数值数据类型（如
DECIMAL或FLOAT和DATETIME。不支持使用其他与日期或时间相关的数据类型作为分区列。
-
以下字符串类型：
CHAR,VARCHAR,BINARY, 和VARBINARY。TEXT和BLOB列不支持作为分区列。
下面两节关于RANGE COLUMNS和LIST COLUMNS分区的讨论假定您已经熟悉基于范围和列表的分区，这在 MySQL 5.1 及更高版本中得到支持；有关更多信息，请参见第 26.2.1 节，“范围分区”和第 26.2.2 节，“列表分区”。
原文：
dev.mysql.com/doc/refman/8.0/en/partitioning-columns-range.html
26.2.3.1 RANGE COLUMNS 分区
列范围分区类似于范围分区，但允许您根据多个列值的范围定义分区。此外，您可以使用非整数类型的列定义范围。
RANGE COLUMNS分区与RANGE分区在以下方面有显著不同：
-
RANGE COLUMNS不接受表达式，只接受列名。 -
RANGE COLUMNS接受一个或多个列的列表。RANGE COLUMNS分区基于元组（列值列表）之间的比较，而不是标量值之间的比较。将行放置在RANGE COLUMNS分区中也是基于元组之间的比较；这将在本节后面进一步讨论。 -
RANGE COLUMNS分区列不限于整数列；字符串、DATE和DATETIME列也可以用作分区列。（详细信息请参阅第 26.2.3 节，“COLUMNS Partitioning”。）
创建由RANGE COLUMNS分区的基本语法如下所示：
CREATE TABLE *table_name*
PARTITION BY RANGE COLUMNS(*column_list*) (
PARTITION *partition_name* VALUES LESS THAN (*value_list*)[,
PARTITION *partition_name* VALUES LESS THAN (*value_list*)][,
...]
)
*column_list*:
*column_name*[, *column_name*][, ...]
*value_list*:
*value*[, *value*][, ...]
注意
在创建分区表时可以使用的并非所有CREATE TABLE选项都在此处展示。有关完整信息，请参阅第 15.1.20 节，“CREATE TABLE Statement”。
在刚刚展示的语法中，column_list是一个或多个列的列表（有时称为分区列列表），value_list是一个值列表（即，它是一个分区定义值列表）。必须为每个分区定义提供一个value_list，并且每个value_list必须具有与column_list中列数相同的值。一般来说，如果在COLUMNS子句中使用了N列，则每个VALUES LESS THAN子句也必须提供一个包含N个值的列表。
分区列列表中的元素和定义每个分区的值列表中的元素必须以相同的顺序出现。此外，值列表中的每个元素必须与列列表中的相应元素具有相同的数据类型。然而，分区列列表和值列表中列名的顺序不必与CREATE TABLE语句的主体部分中表列定义的顺序相同。与通过RANGE分区的表一样，您可以使用MAXVALUE来表示一个值，使得插入到给定列中的任何合法值始终小于此值。以下是一个CREATE TABLE语句的示例，可帮助说明所有这些要点：
mysql> CREATE TABLE rcx (
-> a INT,
-> b INT,
-> c CHAR(3),
-> d INT
-> )
-> PARTITION BY RANGE COLUMNS(a,d,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)
-> );
Query OK, 0 rows affected (0.15 sec)
表rcx包含列a、b、c、d。提供给COLUMNS子句的分区列列表使用了这些列中的 3 列，顺序为a、d、c。用于定义分区的每个值列表包含 3 个值，顺序相同；也就是说，每个值列表元组的形式为（INT、INT、CHAR(3)），这对应于列a、d和c使用的数据类型（按顺序）。
将行放入分区是通过比较要插入的行中与COLUMNS子句中匹配的元组与用于定义表分区的VALUES LESS THAN子句中使用的元组来确定的。因为我们比较的是元组（即值的列表或集合），而不是标量值，所以在与简单的RANGE分区不同的情况下，与RANGE COLUMNS分区一起使用的VALUES LESS THAN的语义有所不同。在RANGE分区中，生成与VALUES LESS THAN中的限制值相等的表达式值的行永远不会放入相应的分区；然而，在使用RANGE COLUMNS分区时，有时可能会将分区列列表的第一个元素的值与VALUES LESS THAN值列表中第一个元素的值相等的行放入相应的分区。
考虑通过以下语句创建的RANGE分区表：
CREATE TABLE r1 (
a INT,
b INT
)
PARTITION BY RANGE (a) (
PARTITION p0 VALUES LESS THAN (5),
PARTITION p1 VALUES LESS THAN (MAXVALUE)
);
如果我们向该表中插入 3 行，使得每行的a列值均为5，则所有 3 行都存储在分区p1中，因为在每种情况下，a列值均不小于 5，我们可以通过针对信息模式PARTITIONS表执行适当的查询来查看：
mysql> INSERT INTO r1 VALUES (5,10), (5,11), (5,12);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT PARTITION_NAME, TABLE_ROWS
-> FROM INFORMATION_SCHEMA.PARTITIONS
-> WHERE TABLE_NAME = 'r1';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0 | 0 |
| p1 | 3 |
+----------------+------------+
2 rows in set (0.00 sec)
现在考虑一个类似的表rc1，它使用了RANGE COLUMNS分区，COLUMNS子句中引用了列a和b，如下所示创建：
CREATE TABLE rc1 (
a INT,
b INT
)
PARTITION BY RANGE COLUMNS(a, b) (
PARTITION p0 VALUES LESS THAN (5, 12),
PARTITION p3 VALUES LESS THAN (MAXVALUE, MAXVALUE)
);
如果我们将刚刚插入r1的相同行插入rc1，则行的分布会有所不同：
mysql> INSERT INTO rc1 VALUES (5,10), (5,11), (5,12);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT PARTITION_NAME, TABLE_ROWS
-> FROM INFORMATION_SCHEMA.PARTITIONS
-> WHERE TABLE_NAME = 'rc1';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0 | 2 |
| p3 | 1 |
+----------------+------------+
2 rows in set (0.00 sec)
这是因为我们比较的是行而不是标量值。我们可以将插入的行值与用于在表rc1中定义分区p0的VALUES THAN LESS THAN子句中的限制行值进行比较，如下所示：
mysql> SELECT (5,10) < (5,12), (5,11) < (5,12), (5,12) < (5,12);
+-----------------+-----------------+-----------------+
| (5,10) < (5,12) | (5,11) < (5,12) | (5,12) < (5,12) |
+-----------------+-----------------+-----------------+
| 1 | 1 | 0 |
+-----------------+-----------------+-----------------+
1 row in set (0.00 sec)
2 元组(5,10)和(5,11)被认为小于(5,12)，因此它们被存储在分区p0中。由于 5 不小于 5，12 不小于 12，(5,12)被认为不小于(5,12)，并存储在分区p1中。
在前面的示例中，SELECT语句也可以使用显式行构造函数编写，如下所示：
SELECT ROW(5,10) < ROW(5,12), ROW(5,11) < ROW(5,12), ROW(5,12) < ROW(5,12);
有关在 MySQL 中使用行构造函数的更多信息，请参阅第 15.2.15.5 节，“行子查询”。
对于只使用单个分区列进行RANGE COLUMNS分区的表，行存储在分区中的方式与通过RANGE分区的等效表相同。以下CREATE TABLE语句创建了一个使用 1 个分区列进行RANGE COLUMNS分区的表：
CREATE TABLE rx (
a INT,
b INT
)
PARTITION BY RANGE COLUMNS (a) (
PARTITION p0 VALUES LESS THAN (5),
PARTITION p1 VALUES LESS THAN (MAXVALUE)
);
如果我们将行(5,10)、(5,11)和(5,12)插入到这个表中，我们可以看到它们的放置方式与我们之前创建和填充的表r相同：
mysql> INSERT INTO rx VALUES (5,10), (5,11), (5,12);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT PARTITION_NAME,TABLE_ROWS
-> FROM INFORMATION_SCHEMA.PARTITIONS
-> WHERE TABLE_NAME = 'rx';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0 | 0 |
| p1 | 3 |
+----------------+------------+
2 rows in set (0.00 sec)
也可以创建按RANGE COLUMNS分区的表，其中一个或多个列的限制值在连续的分区定义中重复。只要用于定义分区的列值元组严格递增，就可以这样做。例如，以下每个CREATE TABLE语句都是有效的：
CREATE TABLE rc2 (
a INT,
b INT
)
PARTITION BY RANGE COLUMNS(a,b) (
PARTITION p0 VALUES LESS THAN (0,10),
PARTITION p1 VALUES LESS THAN (10,20),
PARTITION p2 VALUES LESS THAN (10,30),
PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE)
);
CREATE TABLE rc3 (
a INT,
b INT
)
PARTITION BY RANGE COLUMNS(a,b) (
PARTITION p0 VALUES LESS THAN (0,10),
PARTITION p1 VALUES LESS THAN (10,20),
PARTITION p2 VALUES LESS THAN (10,30),
PARTITION p3 VALUES LESS THAN (10,35),
PARTITION p4 VALUES LESS THAN (20,40),
PARTITION p5 VALUES LESS THAN (MAXVALUE,MAXVALUE)
);
即使乍看之下可能不会成功，以下语句也会成功，因为列b的限制值对于分区p0为 25，对于分区p1为 20，列c的限制值对于分区p1为 100，对于分区p2为 50：
CREATE TABLE rc4 (
a INT,
b INT,
c INT
)
PARTITION BY RANGE COLUMNS(a,b,c) (
PARTITION p0 VALUES LESS THAN (0,25,50),
PARTITION p1 VALUES LESS THAN (10,20,100),
PARTITION p2 VALUES LESS THAN (10,30,50),
PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE,MAXVALUE)
);
在设计按RANGE COLUMNS分区的表时，您可以通过使用mysql客户端对所需元组进行比较来测试连续的分区定义，如下所示：
mysql> SELECT (0,25,50) < (10,20,100), (10,20,100) < (10,30,50);
+-------------------------+--------------------------+
| (0,25,50) < (10,20,100) | (10,20,100) < (10,30,50) |
+-------------------------+--------------------------+
| 1 | 1 |
+-------------------------+--------------------------+
1 row in set (0.00 sec)
如果CREATE TABLE语句包含不严格递增顺序的分区定义，它将失败并显示错误，如下例所示：
mysql> CREATE TABLE rcf (
-> a INT,
-> b INT,
-> c INT
-> )
-> PARTITION BY RANGE COLUMNS(a,b,c) (
-> PARTITION p0 VALUES LESS THAN (0,25,50),
-> PARTITION p1 VALUES LESS THAN (20,20,100),
-> PARTITION p2 VALUES LESS THAN (10,30,50),
-> PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE,MAXVALUE)
-> );
ERROR 1493 (HY000): VALUES LESS THAN value must be strictly increasing for each partition
当你遇到这样的错误时，可以通过对它们的列列表进行“小于”比较来推断哪些分区定义是无效的。在这种情况下，问题出在分区p2的定义上，因为用于定义它的元组不小于用于定义分区p3的元组，如下所示：
mysql> SELECT (0,25,50) < (20,20,100), (20,20,100) < (10,30,50);
+-------------------------+--------------------------+
| (0,25,50) < (20,20,100) | (20,20,100) < (10,30,50) |
+-------------------------+--------------------------+
| 1 | 0 |
+-------------------------+--------------------------+
1 row in set (0.00 sec)
当使用RANGE COLUMNS时，同一列中的MAXVALUE可能出现在多个VALUES LESS THAN子句中。但是，连续分区定义中各列的限制值应该是递增的，不应该定义超过一个分区，其中MAXVALUE用作所有列值的上限，并且此分区定义应该出现在PARTITION ... VALUES LESS THAN子句列表的最后。此外，您不能将MAXVALUE用作连续分区定义中第一列的限制值。
如前所述，使用RANGE COLUMNS分区还可以使用非整数列作为分区列。（有关这些列的完整列表，请参阅第 26.2.3 节，“列分区”。）考虑一个名为employees的表（未分区），使用以下语句创建：
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 NOT NULL,
store_id INT NOT NULL
);
使用RANGE COLUMNS分区，您可以创建这个表的一个版本，根据员工的姓氏将每一行存储在四个分区中的一个，就像这样：
CREATE TABLE employees_by_lname (
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 NOT NULL,
store_id INT NOT NULL
)
PARTITION BY RANGE COLUMNS (lname) (
PARTITION p0 VALUES LESS THAN ('g'),
PARTITION p1 VALUES LESS THAN ('m'),
PARTITION p2 VALUES LESS THAN ('t'),
PARTITION p3 VALUES LESS THAN (MAXVALUE)
);
或者，您可以使用以下ALTER TABLE语句使之前创建的employees表按照这种方案进行分区。
ALTER TABLE employees PARTITION BY RANGE COLUMNS (lname) (
PARTITION p0 VALUES LESS THAN ('g'),
PARTITION p1 VALUES LESS THAN ('m'),
PARTITION p2 VALUES LESS THAN ('t'),
PARTITION p3 VALUES LESS THAN (MAXVALUE)
);
注意
因为不同的字符集和校对规则具有不同的排序顺序，所以在使用字符串列作为分区列进行分区时，正在使用的字符集和校对规则可能会影响表按RANGE COLUMNS分区的哪个分区存储给定行。此外，在创建这样一个表之后更改给定数据库、表或列的字符集或校对规则可能会导致行分布方式的变化。例如，在使用区分大小写的校对规则时，'and'在'Andersen'之前排序，但在使用不区分大小写的校对规则时，情况则相反。
有关 MySQL 如何处理字符集和校对规则的信息，请参阅第十二章，字符集、校对规则、Unicode。
类似地，您可以使用此处显示的ALTER TABLE语句使employees表按照雇佣员工的年代进行分区，使每一行存储在几个分区中的一个。
ALTER TABLE employees PARTITION BY RANGE COLUMNS (hired) (
PARTITION p0 VALUES LESS THAN ('1970-01-01'),
PARTITION p1 VALUES LESS THAN ('1980-01-01'),
PARTITION p2 VALUES LESS THAN ('1990-01-01'),
PARTITION p3 VALUES LESS THAN ('2000-01-01'),
PARTITION p4 VALUES LESS THAN ('2010-01-01'),
PARTITION p5 VALUES LESS THAN (MAXVALUE)
);
有关PARTITION BY RANGE COLUMNS语法的更多信息，请参阅第 15.1.20 节，“CREATE TABLE 语句”。
原文：
dev.mysql.com/doc/refman/8.0/en/partitioning-columns-list.html
26.2.3.2 列表列分区
MySQL 8.0 支持列表列分区。这是列表分区的一种变体，允许将多个列用作分区键，并且可以使用数据类型为整数类型以外的列作为分区列；您可以使用字符串类型、DATE和DATETIME列。（有关COLUMNS分区列允许的数据类型的更多信息，请参见第 26.2.3 节，“列分区”。）
假设您有一个业务，客户分布在 12 个城市，为了销售和营销目的，您将这些城市组织成了每个包含 3 个城市的 4 个地区，如下表所示：
| 地区 | 城市 |
|---|---|
| 1 | 奥斯卡沙姆，赫格斯比，蒙斯特罗斯 |
| 2 | 温默比，胡尔特斯弗雷德，韦斯特维克 |
| 3 | 尼舍，艾克绍，维特兰达 |
| 4 | 乌普维丁厄，阿尔韦斯塔，韦克西厄 |
使用列表列分区，您可以创建一个客户数据表，根据客户所居住城市的名称将行分配给这些地区中的任何一个分区，如下所示：
CREATE TABLE customers_1 (
first_name VARCHAR(25),
last_name VARCHAR(25),
street_1 VARCHAR(30),
street_2 VARCHAR(30),
city VARCHAR(15),
renewal DATE
)
PARTITION BY LIST COLUMNS(city) (
PARTITION pRegion_1 VALUES IN('Oskarshamn', 'Högsby', 'Mönsterås'),
PARTITION pRegion_2 VALUES IN('Vimmerby', 'Hultsfred', 'Västervik'),
PARTITION pRegion_3 VALUES IN('Nässjö', 'Eksjö', 'Vetlanda'),
PARTITION pRegion_4 VALUES IN('Uppvidinge', 'Alvesta', 'Växjo')
);
与范围列分区一样，您不需要在COLUMNS()子句中使用表达式将列值转换为整数。（实际上，除了列名之外，不允许在COLUMNS()中使用其他表达式。）
也可以使用DATE和DATETIME列，如下例所示，使用与之前customers_1表相同的名称和列，但根据renewal列使用列表列分区，根据 2010 年 2 月的某周，将客户账户计划续订的情况存储在 4 个分区中的一个中：
CREATE TABLE customers_2 (
first_name VARCHAR(25),
last_name VARCHAR(25),
street_1 VARCHAR(30),
street_2 VARCHAR(30),
city VARCHAR(15),
renewal DATE
)
PARTITION BY LIST COLUMNS(renewal) (
PARTITION pWeek_1 VALUES IN('2010-02-01', '2010-02-02', '2010-02-03',
'2010-02-04', '2010-02-05', '2010-02-06', '2010-02-07'),
PARTITION pWeek_2 VALUES IN('2010-02-08', '2010-02-09', '2010-02-10',
'2010-02-11', '2010-02-12', '2010-02-13', '2010-02-14'),
PARTITION pWeek_3 VALUES IN('2010-02-15', '2010-02-16', '2010-02-17',
'2010-02-18', '2010-02-19', '2010-02-20', '2010-02-21'),
PARTITION pWeek_4 VALUES IN('2010-02-22', '2010-02-23', '2010-02-24',
'2010-02-25', '2010-02-26', '2010-02-27', '2010-02-28')
);
这种方法有效，但如果涉及的日期数量非常大，则定义和维护会变得繁琐；在这种情况下，通常更实用的是使用范围或范围列分区。在这种情况下，由于我们希望用作分区键的列是一个DATE列，我们使用范围列分区，如下所示：
CREATE TABLE customers_3 (
first_name VARCHAR(25),
last_name VARCHAR(25),
street_1 VARCHAR(30),
street_2 VARCHAR(30),
city VARCHAR(15),
renewal DATE
)
PARTITION BY RANGE COLUMNS(renewal) (
PARTITION pWeek_1 VALUES LESS THAN('2010-02-09'),
PARTITION pWeek_2 VALUES LESS THAN('2010-02-15'),
PARTITION pWeek_3 VALUES LESS THAN('2010-02-22'),
PARTITION pWeek_4 VALUES LESS THAN('2010-03-01')
);
更多信息请参见第 26.2.3.1 节，“范围列分区”。
此外（与范围列分区一样），您可以在COLUMNS()子句中使用多个列。
有关PARTITION BY LIST COLUMNS()语法的更多信息，请参见第 15.1.20 节，“CREATE TABLE 语句”。
26.2.4 哈希分区
26.2.4.1 线性哈希分区
主要使用HASH分区来确保数据在预定数量的分区之间均匀分布。使用范围或列表分区，您必须明确指定给定列值或一组列值应存储在哪个分区；使用哈希分区，这个决定已经为您处理，您只需指定一个基于列值的列值或表达式进行哈希处理，以及要将分区表分成的分区数。
要使用HASH分区对表进行分区，必须在CREATE TABLE语句中追加一个PARTITION BY HASH (*expr*)子句，其中expr是返回整数的表达式。这可以简单地是 MySQL 整数类型之一的列名。此外，您很可能希望在此之后跟上PARTITIONS *num*，其中num是表示要将表分成的分区数的正整数。
注意
为简单起见，接下来的示例中的表不使用任何键。您应该知道，如果表具有任何唯一键，那么用于此表的分区表达式中的每一列都必须是每个唯一键的一部分，包括主键。有关更多信息，请参见第 26.6.1 节“分区键、主键和唯一键”。
以下语句创建了一个表，该表在store_id列上使用哈希分区，并分为 4 个分区：
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 HASH(store_id)
PARTITIONS 4;
如果不包括PARTITIONS子句，则分区数默认为1；在PARTITIONS关键字后没有跟随数字会导致语法错误。
您还可以使用返回整数的 SQL 表达式作为expr。例如，您可能希望根据雇员入职的年份进行分区。可以按照这里所示进行操作：
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 HASH( YEAR(hired) )
PARTITIONS 4;
expr必须返回一个非常量、非随机的整数值（换句话说，它应该是变化的但确定的），并且不得包含任何禁止的构造，如第 26.6 节“分区的限制和限制”中所述。您还应该记住，每次插入、更新（或可能删除）一行时，都会评估此表达式；这意味着非常复杂的表达式可能会导致性能问题，特别是在执行影响大量行的操作（如批量插入）时。
最有效的哈希函数是一个仅作用于单个表列的函数，其值随着列值的增加或减少而一致变化，因为这允许在分区范围上进行“修剪”。也就是说，表达式与其基于的列值的变化越密切，MySQL 就能更有效地使用该表达式进行哈希分区。
例如，当date_col是DATE类型的列时，表达式TO_DAYS(date_col)被认为与date_col的值直接变化，因为对于date_col值的每一次变化，表达式的值都以一种一致的方式变化。与date_col相关的表达式YEAR(date_col)的变化与date_col不像TO_DAYS(date_col)那么直接，因为并非每一次date_col的变化都会产生与YEAR(date_col)等效的变化。即便如此，YEAR(date_col)是一个很好的哈希函数候选，因为它与date_col的一部分直接变化，并且没有任何可能的date_col变化会导致YEAR(date_col)的不成比例变化。
相比之下，假设你有一个名为int_col的列，其类型是INT。现在考虑表达式POW(5-int_col,3) + 6。这将是一个糟糕的哈希函数选择，因为int_col值的变化不能保证产生表达式值的成比例变化。通过给定量改变int_col的值可能会导致表达式值的差异很大。例如，将int_col从5更改为6会导致表达式值减少-1，但将int_col的值从6更改为7会导致表达式值减少-7。
换句话说，列值与表达式值之间的图形越接近由方程y=*c*x跟踪的直线，其中c是某个非零常数，表达式越适合用于哈希。这与表达式越非线性，数据在分区之间分布越不均匀有关。
理论上，对涉及多个列值的表达式也可以进行修剪，但确定哪些表达式适合可能会非常困难和耗时。因此，不建议特别使用涉及多个列的哈希表达式。
当使用PARTITION BY HASH时，存储引擎根据表达式的结果的模数确定使用哪个num分区。换句话说，对于给定的表达式expr，记录存储在分区号N中，其中*N* = MOD(*expr*, *num*)。假设表t1定义如下，因此有 4 个分区：
CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE)
PARTITION BY HASH( YEAR(col3) )
PARTITIONS 4;
如果你向t1插入一个col3值为'2005-09-15'的记录，则存储它的分区如下确定：
MOD(YEAR('2005-09-01'),4)
= MOD(2005,4)
= 1
MySQL 8.0 还支持一种称为线性哈希的HASH分区的变体，它采用更复杂的算法来确定插入到分区表中的新行的位置。请参阅 Section 26.2.4.1, “线性哈希分区”，了解此算法的描述。
用户提供的表达式在每次插入或更新记录时进行评估。根据情况，当记录被删除时也可能进行评估。
原文：
dev.mysql.com/doc/refman/8.0/en/partitioning-linear-hash.html
26.2.4.1 线性哈希分区
MySQL 还支持线性哈希，与常规哈希不同之处在于线性哈希利用线性二次幂算法，而常规哈希使用哈希函数值的模。
在语法上，线性哈希分区和常规哈希之间唯一的区别是在PARTITION BY子句中添加LINEAR关键字，如下所示：
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 LINEAR HASH( YEAR(hired) )
PARTITIONS 4;
给定表达式expr，当使用线性哈希时存储记录的分区是从num个分区中的第N个分区，其中N根据以下算法派生：
-
找到大于
num的下一个 2 的幂。我们称这个值为V；可以计算如下：*V* = POWER(2, CEILING(LOG(2, *num*)))(假设
num为 13。那么LOG(2,13)为 3.7004397181411。CEILING(3.7004397181411)为 4，V=POWER(2,4)，即 16。) -
设置
N=F(column_list) & (V- 1)。 -
当
N>=num时：-
设置
V=V/ 2。 -
设置
N=N& (V- 1)。
-
假设使用线性哈希分区并具有 6 个分区的表t1是使用以下语句创建的：
CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE)
PARTITION BY LINEAR HASH( YEAR(col3) )
PARTITIONS 6;
现在假设你想要将两条记录插入到具有col3列值'2003-04-14'和'1998-10-19'的t1中。第一条记录的分区号计算如下：
*V* = POWER(2, CEILING( LOG(2,6) )) = 8
*N* = YEAR('2003-04-14') & (8 - 1)
= 2003 & 7
= 3
(*3 >= 6 is FALSE: record stored in partition #3*)
第二条记录存储在的分区号计算如下：
*V* = 8
*N* = YEAR('1998-10-19') & (8 - 1)
= 1998 & 7
= 6
(*6 >= 6 is TRUE: additional step required*)
*N* = 6 & ((8 / 2) - 1)
= 6 & 3
= 2
(*2 >= 6 is FALSE: record stored in partition #2*)
通过线性哈希分区的优势在于添加、删除、合并和分割分区变得更快，这在处理包含极大量（TB 级）数据的表时可能会有益。缺点是与使用常规哈希分区获得的分布相比，数据在分区之间的均匀分布性较低。
26.2.5 键分区
按键分区类似于按哈希分区，不同之处在于哈希分区使用用户定义的表达式，而键分区的哈希函数由 MySQL 服务器提供。NDB Cluster 使用MD5()来实现这一目的；对于使用其他存储引擎的表，服务器使用自己的内部哈希函数。
CREATE TABLE ... PARTITION BY KEY的语法规则与创建哈希分区的表的规则类似。主要区别如下：
-
使用
KEY而不是HASH。 -
KEY只接受零个或多个列名的列表。用作分区键的列必须包含表的主键的一部分或全部，如果表有主键的话。如果未指定列名作为分区键，则使用表的主键，如果有的话。例如，以下CREATE TABLE语句在 MySQL 8.0 中是有效的：CREATE TABLE k1 ( id INT NOT NULL PRIMARY KEY, name VARCHAR(20) ) PARTITION BY KEY() PARTITIONS 2;如果没有主键但有唯一键，则唯一键将用作分区键：
CREATE TABLE k1 ( id INT NOT NULL, name VARCHAR(20), UNIQUE KEY (id) ) PARTITION BY KEY() PARTITIONS 2;但是，如果唯一键列未定义为
NOT NULL，则前面的语句将失败。在这两种情况下，分区键是
id列，即使在SHOW CREATE TABLE的输出中或者在信息模式PARTITIONS表的PARTITION_EXPRESSION列中没有显示。与其他分区类型不同，
KEY分区的列不限于整数或NULL值。例如，以下CREATE TABLE语句是有效的：CREATE TABLE tm1 ( s1 CHAR(32) PRIMARY KEY ) PARTITION BY KEY(s1) PARTITIONS 10;如果指定了不同的分区类型，则前面的语句将无效。（在这种情况下，仅使用
PARTITION BY KEY()也是有效的，并且与PARTITION BY KEY(s1)具有相同的效果，因为s1是表的主键。）关于这个问题的更多信息，请参见第 26.6 节，“分区的限制和限制”。
不支持具有索引前缀的列用作分区键。这意味着
CHAR、VARCHAR、BINARY和VARBINARY列可以用作分区键，只要它们不使用前缀；因为在索引定义中必须指定前缀，所以无法在分区键中使用BLOB和TEXT列。在 MySQL 8.0.21 之前，创建、修改或升级分区表时允许使用前缀的列，即使它们未包含在表的分区键中；在 MySQL 8.0.21 及更高版本中，这种宽容行为已被弃用，并且当使用一个或多个这样的列时，服务器会显示适当的警告或错误。有关更多信息和示例，请参见不支持键分区的列索引前缀。注意
使用
NDB存储引擎的表隐式地通过KEY进行分区，使用表的主键作为分区键（与其他 MySQL 存储引擎一样）。如果 NDB Cluster 表没有显式主键，则由NDB存储引擎为每个 NDB Cluster 表生成的“隐藏”主键将用作分区键。如果为
NDB表定义了显式分区方案，则表必须具有显式主键，并且分区表达式中使用的任何列必须是该键的一部分。但是，如果表使用“空”分区表达式——即PARTITION BY KEY()而没有列引用，则不需要显式主键。您可以使用ndb_desc实用程序（使用
-p选项）观察到这种分区。重要
对于使用键分区的表，您不能执行
ALTER TABLE DROP PRIMARY KEY，因为这样做会生成错误 ERROR 1466 (HY000): Field in list of fields for partition function not found in table。对于使用KEY进行分区的 NDB Cluster 表，这不是问题；在这种情况下，表将使用“隐藏”的主键重新组织为表的新分区键。参见第二十五章，MySQL NDB Cluster 8.0。
也可以通过线性键对表进行分区。这里是一个简单的例子：
CREATE TABLE tk (
col1 INT NOT NULL,
col2 CHAR(5),
col3 DATE
)
PARTITION BY LINEAR KEY (col1)
PARTITIONS 3;
LINEAR 关键字在 KEY 分区上具有与 HASH 分区相同的效果，分区号是使用二的幂算法而不是模算术推导出来的。参见第 26.2.4.1 节，“线性哈希分区”，了解该算法及其影响的描述。
26.2.6 子分区化
原文：
dev.mysql.com/doc/refman/8.0/en/partitioning-subpartitions.html
子分区化，也称为复合分区化，是对分区表中每个分区的进一步划分。考虑以下CREATE TABLE语句：
CREATE TABLE ts (id INT, purchased DATE)
PARTITION BY RANGE( YEAR(purchased) )
SUBPARTITION BY HASH( TO_DAYS(purchased) )
SUBPARTITIONS 2 (
PARTITION p0 VALUES LESS THAN (1990),
PARTITION p1 VALUES LESS THAN (2000),
PARTITION p2 VALUES LESS THAN MAXVALUE
);
表ts有 3 个RANGE分区。这些分区——p0、p1和p2——进一步划分为 2 个子分区。实际上，整个表被划分为3 * 2 = 6个分区。但是，由于PARTITION BY RANGE子句的作用，前两个仅存储purchased列中值小于 1990 的记录。
可以对按RANGE或LIST分区的表进行子分区化。子分区可以使用HASH或KEY分区。这也称为复合分区化。
注意
SUBPARTITION BY HASH和SUBPARTITION BY KEY通常遵循与PARTITION BY HASH和PARTITION BY KEY相同的语法规则。一个例外是，SUBPARTITION BY KEY（不像PARTITION BY KEY）目前不支持默认列，因此必须指定用于此目的的列，即使表具有显式主键。这是一个我们正在努力解决的已知问题；有关更多信息和示例，请参见子分区的问题。
还可以使用SUBPARTITION子句明确定义子分区，以指定各个子分区的选项。例如，以更冗长的方式创建与前面示例中所示的相同表ts的方法如下：
CREATE TABLE ts (id INT, purchased DATE)
PARTITION BY RANGE( YEAR(purchased) )
SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
PARTITION p0 VALUES LESS THAN (1990) (
SUBPARTITION s0,
SUBPARTITION s1
),
PARTITION p1 VALUES LESS THAN (2000) (
SUBPARTITION s2,
SUBPARTITION s3
),
PARTITION p2 VALUES LESS THAN MAXVALUE (
SUBPARTITION s4,
SUBPARTITION s5
)
);
这里列出了一些需要注意的语法项：
-
每个分区必须具有相同数量的子分区。
-
如果您在分区表的任何分区上明确定义了任何子分区，必须定义它们全部。换句话说，以下语句将失败：
CREATE TABLE ts (id INT, purchased DATE) PARTITION BY RANGE( YEAR(purchased) ) SUBPARTITION BY HASH( TO_DAYS(purchased) ) ( PARTITION p0 VALUES LESS THAN (1990) ( SUBPARTITION s0, SUBPARTITION s1 ), PARTITION p1 VALUES LESS THAN (2000), PARTITION p2 VALUES LESS THAN MAXVALUE ( SUBPARTITION s2, SUBPARTITION s3 ) );即使使用
SUBPARTITIONS 2，此语句仍将失败。 -
每个
SUBPARTITION子句必须至少包括一个子分区的名称。否则，您可以为子分区设置任何所需选项，或允许其假定该选项的默认设置。 -
子分区名称必须在整个表中是唯一的。例如，以下
CREATE TABLE语句是有效的：CREATE TABLE ts (id INT, purchased DATE) PARTITION BY RANGE( YEAR(purchased) ) SUBPARTITION BY HASH( TO_DAYS(purchased) ) ( PARTITION p0 VALUES LESS THAN (1990) ( SUBPARTITION s0, SUBPARTITION s1 ), PARTITION p1 VALUES LESS THAN (2000) ( SUBPARTITION s2, SUBPARTITION s3 ), PARTITION p2 VALUES LESS THAN MAXVALUE ( SUBPARTITION s4, SUBPARTITION s5 ) );
26.2.7 MySQL 分区如何处理 NULL
原文：
dev.mysql.com/doc/refman/8.0/en/partitioning-handling-nulls.html
MySQL 中的分区对于将NULL作为分区表达式的值（无论是列值还是用户提供的表达式的值）并不做任何限制。即使允许将NULL用作必须产生整数的表达式的值，但重要的是要记住NULL不是一个数字。MySQL 的分区实现将NULL视为小于任何非NULL值，就像ORDER BY一样。
这意味着NULL的处理在不同类型的分区之间有所不同，并且如果您没有为此做好准备，可能会产生您意想不到的行为。在这种情况下，我们在本节中讨论了每种 MySQL 分区类型在确定应将行存储在哪个分区时如何处理NULL值，并为每种情况提供了示例。
使用 RANGE 分区处理 NULL。 如果您向由RANGE分区的表插入一行，使得用于确定分区的列值为NULL，则该行将插入到最低的分区中。考虑以下在名为p的数据库中创建的两个表：
mysql> CREATE TABLE t1 (
-> c1 INT,
-> c2 VARCHAR(20)
-> )
-> PARTITION BY RANGE(c1) (
-> PARTITION p0 VALUES LESS THAN (0),
-> PARTITION p1 VALUES LESS THAN (10),
-> PARTITION p2 VALUES LESS THAN MAXVALUE
-> );
Query OK, 0 rows affected (0.09 sec)
mysql> CREATE TABLE t2 (
-> c1 INT,
-> c2 VARCHAR(20)
-> )
-> PARTITION BY RANGE(c1) (
-> PARTITION p0 VALUES LESS THAN (-5),
-> PARTITION p1 VALUES LESS THAN (0),
-> PARTITION p2 VALUES LESS THAN (10),
-> PARTITION p3 VALUES LESS THAN MAXVALUE
-> );
Query OK, 0 rows affected (0.09 sec)
通过以下查询INFORMATION_SCHEMA数据库中的PARTITIONS表，您可以看到这两个CREATE TABLE语句创建的分区：
mysql> SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH
> FROM INFORMATION_SCHEMA.PARTITIONS
> WHERE TABLE_SCHEMA = 'p' AND TABLE_NAME LIKE 't_';
+------------+----------------+------------+----------------+-------------+
| TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |
+------------+----------------+------------+----------------+-------------+
| t1 | p0 | 0 | 0 | 0 |
| t1 | p1 | 0 | 0 | 0 |
| t1 | p2 | 0 | 0 | 0 |
| t2 | p0 | 0 | 0 | 0 |
| t2 | p1 | 0 | 0 | 0 |
| t2 | p2 | 0 | 0 | 0 |
| t2 | p3 | 0 | 0 | 0 |
+------------+----------------+------------+----------------+-------------+
7 rows in set (0.00 sec)
（有关此表的更多信息，请参见第 28.3.21 节，“The INFORMATION_SCHEMA PARTITIONS Table”。）现在让我们用包含在用作分区键的列中的NULL的单行填充这些表，并验证使用一对SELECT语句插入了这些行：
mysql> INSERT INTO t1 VALUES (NULL, 'mothra');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO t2 VALUES (NULL, 'mothra');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM t1;
+------+--------+
| id | name |
+------+--------+
| NULL | mothra |
+------+--------+
1 row in set (0.00 sec)
mysql> SELECT * FROM t2;
+------+--------+
| id | name |
+------+--------+
| NULL | mothra |
+------+--------+
1 row in set (0.00 sec)
您可以通过重新运行针对INFORMATION_SCHEMA.PARTITIONS的上一个查询并检查输出来查看用于存储插入行的分区：
mysql> SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH
> FROM INFORMATION_SCHEMA.PARTITIONS
> WHERE TABLE_SCHEMA = 'p' AND TABLE_NAME LIKE 't_';
+------------+----------------+------------+----------------+-------------+
| TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |
+------------+----------------+------------+----------------+-------------+
*| t1 | p0 | 1 | 20 | 20 |*
| t1 | p1 | 0 | 0 | 0 |
| t1 | p2 | 0 | 0 | 0 |
*| t2 | p0 | 1 | 20 | 20 |*
| t2 | p1 | 0 | 0 | 0 |
| t2 | p2 | 0 | 0 | 0 |
| t2 | p3 | 0 | 0 | 0 |
+------------+----------------+------------+----------------+-------------+
7 rows in set (0.01 sec)
您还可以通过删除这些分区，然后重新运行SELECT语句来演示这些行存储在每个表的最低编号分区中：
mysql> ALTER TABLE t1 DROP PARTITION p0;
Query OK, 0 rows affected (0.16 sec)
mysql> ALTER TABLE t2 DROP PARTITION p0;
Query OK, 0 rows affected (0.16 sec)
mysql> SELECT * FROM t1;
Empty set (0.00 sec)
mysql> SELECT * FROM t2;
Empty set (0.00 sec)
（有关ALTER TABLE ... DROP PARTITION的更多信息，请参见第 15.1.9 节，“ALTER TABLE Statement”。）
对于使用 SQL 函数的分区表达式，NULL也以这种方式处理。假设我们使用类似于以下的CREATE TABLE语句定义一个表：
CREATE TABLE tndate (
id INT,
dt DATE
)
PARTITION BY RANGE( YEAR(dt) ) (
PARTITION p0 VALUES LESS THAN (1990),
PARTITION p1 VALUES LESS THAN (2000),
PARTITION p2 VALUES LESS THAN MAXVALUE
);
与其他 MySQL 函数一样，YEAR(NULL)返回NULL。具有NULL值的dt列行被视为分区表达式评估为低于任何其他值的值，因此被插入到分区p0中。
使用 LIST 分区处理 NULL 值。 通过LIST分区的表仅在其中一个分区使用包含NULL的值列表定义时才允许NULL值。相反，通过LIST分区的表如果在值列表中没有明确使用NULL，则拒绝导致分区表达式产生NULL值的行，如下例所示：
mysql> CREATE TABLE ts1 (
-> c1 INT,
-> c2 VARCHAR(20)
-> )
-> PARTITION BY LIST(c1) (
-> PARTITION p0 VALUES IN (0, 3, 6),
-> PARTITION p1 VALUES IN (1, 4, 7),
-> PARTITION p2 VALUES IN (2, 5, 8)
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO ts1 VALUES (9, 'mothra');
ERROR 1504 (HY000): Table has no partition for value 9
mysql> INSERT INTO ts1 VALUES (NULL, 'mothra');
ERROR 1504 (HY000): Table has no partition for value NULL
只有c1值在0和8之间（包括 0 和 8）的行才能插入到ts1中。NULL不在此范围内，就像数字9一样。我们可以创建包含NULL值列表的ts2和ts3表，如下所示：
mysql> CREATE TABLE ts2 (
-> c1 INT,
-> c2 VARCHAR(20)
-> )
-> PARTITION BY LIST(c1) (
-> PARTITION p0 VALUES IN (0, 3, 6),
-> PARTITION p1 VALUES IN (1, 4, 7),
-> PARTITION p2 VALUES IN (2, 5, 8),
-> PARTITION p3 VALUES IN (NULL)
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE TABLE ts3 (
-> c1 INT,
-> c2 VARCHAR(20)
-> )
-> PARTITION BY LIST(c1) (
-> PARTITION p0 VALUES IN (0, 3, 6),
-> PARTITION p1 VALUES IN (1, 4, 7, NULL),
-> PARTITION p2 VALUES IN (2, 5, 8)
-> );
Query OK, 0 rows affected (0.01 sec)
在为分区定义值列表时，您可以（也应该）将NULL视为任何其他值一样对待。例如，VALUES IN (NULL)和VALUES IN (1, 4, 7, NULL)都是有效的，就像VALUES IN (1, NULL, 4, 7)，VALUES IN (NULL, 1, 4, 7)等一样。您可以将具有c1列为NULL的行插入到ts2和ts3中：
mysql> INSERT INTO ts2 VALUES (NULL, 'mothra');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO ts3 VALUES (NULL, 'mothra');
Query OK, 1 row affected (0.00 sec)
通过针对INFORMATION_SCHEMA.PARTITIONS发出适当的查询，您可以确定刚刚插入的行使用了哪些分区进行存储（我们假设，与前面的示例一样，分区表是在p数据库中创建的）：
mysql> SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH
> FROM INFORMATION_SCHEMA.PARTITIONS
> WHERE TABLE_SCHEMA = 'p' AND TABLE_NAME LIKE 'ts_';
+------------+----------------+------------+----------------+-------------+
| TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |
+------------+----------------+------------+----------------+-------------+
| ts2 | p0 | 0 | 0 | 0 |
| ts2 | p1 | 0 | 0 | 0 |
| ts2 | p2 | 0 | 0 | 0 |
*| ts2 | p3 | 1 | 20 | 20 |*
| ts3 | p0 | 0 | 0 | 0 |
*| ts3 | p1 | 1 | 20 | 20 |*
| ts3 | p2 | 0 | 0 | 0 |
+------------+----------------+------------+----------------+-------------+
7 rows in set (0.01 sec)
正如本节前面所示，您还可以通过删除这些分区并执行SELECT来验证用于存储行的分区。
使用 HASH 和 KEY 分区处理 NULL 值。 对于使用HASH或KEY分区的表，NULL的处理略有不同。在这些情况下，任何产生NULL值的分区表达式都被视为其返回值为零。我们可以通过创建一个使用适当值的记录的HASH分区表并查看其对文件系统的影响来验证这种行为。假设您使用以下语句创建了一个名为th的表（也在p数据库中）：
mysql> CREATE TABLE th (
-> c1 INT,
-> c2 VARCHAR(20)
-> )
-> PARTITION BY HASH(c1)
-> PARTITIONS 2;
Query OK, 0 rows affected (0.00 sec)
可以使用以下查询查看属于该表的分区：
mysql> SELECT TABLE_NAME,PARTITION_NAME,TABLE_ROWS,AVG_ROW_LENGTH,DATA_LENGTH
> FROM INFORMATION_SCHEMA.PARTITIONS
> WHERE TABLE_SCHEMA = 'p' AND TABLE_NAME ='th';
+------------+----------------+------------+----------------+-------------+
| TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |
+------------+----------------+------------+----------------+-------------+
| th | p0 | 0 | 0 | 0 |
| th | p1 | 0 | 0 | 0 |
+------------+----------------+------------+----------------+-------------+
2 rows in set (0.00 sec)
每个分区的TABLE_ROWS为 0。现在向th插入两行，这两行的c1列值分别为NULL和 0，并验证这些行是否已插入，如下所示：
mysql> INSERT INTO th VALUES (NULL, 'mothra'), (0, 'gigan');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM th;
+------+---------+
| c1 | c2 |
+------+---------+
| NULL | mothra |
+------+---------+
| 0 | gigan |
+------+---------+
2 rows in set (0.01 sec)
对于任意整数N，NULL MOD *N*的值始终为NULL。对于按HASH或KEY分区的表，此结果被视为确定正确分区的0。再次检查信息模式PARTITIONS表，我们可以看到两行都被插入到分区p0中：
mysql> SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH
> FROM INFORMATION_SCHEMA.PARTITIONS
> WHERE TABLE_SCHEMA = 'p' AND TABLE_NAME ='th';
+------------+----------------+------------+----------------+-------------+
| TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |
+------------+----------------+------------+----------------+-------------+
*| th | p0 | 2 | 20 | 20 |*
| th | p1 | 0 | 0 | 0 |
+------------+----------------+------------+----------------+-------------+
2 rows in set (0.00 sec)
通过在表的定义中使用PARTITION BY KEY替换PARTITION BY HASH来重复上一个示例，您可以验证对于这种类型的分区，NULL也被视为 0。
26.3 分区管理
原文：
dev.mysql.com/doc/refman/8.0/en/partitioning-management.html
26.3.1 RANGE 和 LIST 分区管理
26.3.2 HASH 和 KEY 分区管理
26.3.3 与表交换分区和子分区
26.3.4 分区维护
26.3.5 获取分区信息
有多种使用 SQL 语句修改分区表的方法；可以通过使用分区扩展来添加、删除、重新定义、合并或拆分现有分区来修改分区表，这些操作都是通过 ALTER TABLE 语句完成的。还有一些方法可以获取关于分区表和分区的信息。我们将在接下来的章节中讨论这些主题。
-
有关在按
RANGE或LIST分区的表中进行分区管理的信息，请参见 第 26.3.1 节，“RANGE 和 LIST 分区的管理”。 -
有关管理
HASH和KEY分区的讨论，请参见 第 26.3.2 节，“HASH 和 KEY 分区的管理”。 -
请参见 第 26.3.5 节，“获取分区信息”，了解 MySQL 8.0 提供的用于获取关于分区表和分区信息的机制。
-
有关对分区执行维护操作的讨论，请参见 第 26.3.4 节，“分区维护”。
注意
所有分区表的分区必须具有相同数量的子分区；一旦表被创建，就无法更改子分区。
要更改表的分区方案，只需使用带有 partition_options 选项的 ALTER TABLE 语句，其语法与用于创建分区表的 CREATE TABLE 相同；这个选项总是以关键字 PARTITION BY 开头。假设以下 CREATE TABLE 语句用于创建一个按范围分区的表：
CREATE TABLE trb3 (id INT, name VARCHAR(50), purchased DATE)
PARTITION BY RANGE( YEAR(purchased) ) (
PARTITION p0 VALUES LESS THAN (1990),
PARTITION p1 VALUES LESS THAN (1995),
PARTITION p2 VALUES LESS THAN (2000),
PARTITION p3 VALUES LESS THAN (2005)
);
要将此表重新分区，使其按键分为两个分区，使用 id 列值作为键的基础，可以使用以下语句：
ALTER TABLE trb3 PARTITION BY KEY(id) PARTITIONS 2;
这对表的结构具有与删除表并使用 CREATE TABLE trb3 PARTITION BY KEY(id) PARTITIONS 2; 重新创建相同的效果。
ALTER TABLE ... ENGINE = ...仅更改表使用的存储引擎，并保留表的分区方案不变。该语句仅在目标存储引擎提供分区支持时成功。您可以使用ALTER TABLE ... REMOVE PARTITIONING来移除表的分区；参见 Section 15.1.9, “ALTER TABLE Statement”。
重要提示
在给定的ALTER TABLE语句中只能使用单个PARTITION BY、ADD PARTITION、DROP PARTITION、REORGANIZE PARTITION或COALESCE PARTITION子句。如果您（例如）希望删除一个分区并重新组织表的其余分区，您必须在两个单独的ALTER TABLE语句中执行此操作（一个使用DROP PARTITION，然后第二个使用REORGANIZE PARTITION）。
你可以使用ALTER TABLE ... TRUNCATE PARTITION从一个或多个选定的分区中删除所有行。
26.3.1 范围和列表分区的管理
原文：
dev.mysql.com/doc/refman/8.0/en/partitioning-management-range-list.html
添加和删除范围和列表分区的操作方式类似，因此我们在本节讨论了这两种分区管理方式。 有关处理按哈希或键分区的表的信息，请参阅 第 26.3.2 节，“HASH 和 KEY 分区的管理”。
通过使用带有 DROP PARTITION 选项的 ALTER TABLE 语句，可以删除按 RANGE 或 LIST 分区的表中的分区。 假设您已创建了一个按范围分区的表，然后使用以下 CREATE TABLE 和 INSERT 语句插入了 10 条记录：
mysql> CREATE TABLE tr (id INT, name VARCHAR(50), purchased DATE)
-> PARTITION BY RANGE( YEAR(purchased) ) (
-> PARTITION p0 VALUES LESS THAN (1990),
-> PARTITION p1 VALUES LESS THAN (1995),
-> PARTITION p2 VALUES LESS THAN (2000),
-> PARTITION p3 VALUES LESS THAN (2005),
-> PARTITION p4 VALUES LESS THAN (2010),
-> PARTITION p5 VALUES LESS THAN (2015)
-> );
Query OK, 0 rows affected (0.28 sec)
mysql> INSERT INTO tr VALUES
-> (1, 'desk organiser', '2003-10-15'),
-> (2, 'alarm clock', '1997-11-05'),
-> (3, 'chair', '2009-03-10'),
-> (4, 'bookcase', '1989-01-10'),
-> (5, 'exercise bike', '2014-05-09'),
-> (6, 'sofa', '1987-06-05'),
-> (7, 'espresso maker', '2011-11-22'),
-> (8, 'aquarium', '1992-08-04'),
-> (9, 'study desk', '2006-09-16'),
-> (10, 'lava lamp', '1998-12-25');
Query OK, 10 rows affected (0.05 sec)
Records: 10 Duplicates: 0 Warnings: 0
您可以查看应该插入到分区 p2 中的项目，如下所示：
mysql> SELECT * FROM tr
-> WHERE purchased BETWEEN '1995-01-01' AND '1999-12-31';
+------+-------------+------------+
| id | name | purchased |
+------+-------------+------------+
| 2 | alarm clock | 1997-11-05 |
| 10 | lava lamp | 1998-12-25 |
+------+-------------+------------+
2 rows in set (0.00 sec)
您还可以使用分区选择获取此信息，如下所示：
mysql> SELECT * FROM tr PARTITION (p2);
+------+-------------+------------+
| id | name | purchased |
+------+-------------+------------+
| 2 | alarm clock | 1997-11-05 |
| 10 | lava lamp | 1998-12-25 |
+------+-------------+------------+
2 rows in set (0.00 sec)
更多信息请参见 第 26.5 节，“分区选择”。
要删除名为 p2 的分区，请执行以下命令：
mysql> ALTER TABLE tr DROP PARTITION p2;
Query OK, 0 rows affected (0.03 sec)
注意
NDBCLUSTER 存储引擎不支持 ALTER TABLE ... DROP PARTITION。 但是，它支持本章中描述的与分区相关的其他 ALTER TABLE 扩展。
非常重要的一点是，当您删除一个分区时，也会删除存储在该分区中的所有数据。 通过重新运行先前的 SELECT 查询，您可以看到这一点：
mysql> SELECT * FROM tr WHERE purchased
-> BETWEEN '1995-01-01' AND '1999-12-31';
Empty set (0.00 sec)
注意
DROP PARTITION 受本地分区就地 API 支持，可与 ALGORITHM={COPY|INPLACE} 一起使用。 使用 ALGORITHM=INPLACE 的 DROP PARTITION 删除存储在分区中的数据并删除该分区。 但是，使用 ALGORITHM=COPY 或 old_alter_table=ON 的 DROP PARTITION 会重建分区表，并尝试将无法移动到另一个具有兼容 PARTITION ... VALUES 定义的分区的数据移动到另一个分区。 无法移动到另一个分区的数据将被删除。
因此，在执行 ALTER TABLE ... DROP PARTITION 之前，您必须具有表的 DROP 权限。
如果您希望删除所有分区中的所有数据，同时保留表定义及其分区方案，请使用TRUNCATE TABLE语句。（请参阅第 15.1.37 节，“TRUNCATE TABLE Statement”。）
如果您打算更改表的分区而不丢失数据，请使用ALTER TABLE ... REORGANIZE PARTITION。有关REORGANIZE PARTITION的信息，请参见下文或第 15.1.9 节，“ALTER TABLE Statement”。
如果现在执行SHOW CREATE TABLE语句，您可以看到表的分区结构已经发生了变化：
mysql> SHOW CREATE TABLE tr\G
*************************** 1\. row ***************************
Table: tr
Create Table: CREATE TABLE `tr` (
`id` int(11) DEFAULT NULL,
`name` varchar(50) DEFAULT NULL,
`purchased` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY RANGE ( YEAR(purchased))
(PARTITION p0 VALUES LESS THAN (1990) ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN (1995) ENGINE = InnoDB,
PARTITION p3 VALUES LESS THAN (2005) ENGINE = InnoDB,
PARTITION p4 VALUES LESS THAN (2010) ENGINE = InnoDB,
PARTITION p5 VALUES LESS THAN (2015) ENGINE = InnoDB) */ 1 row in set (0.00 sec)
当你在更改后的表中插入具有purchased列值在'1995-01-01'和'2004-12-31'之间（包括这两个日期）的新行时，这些行将存储在分区p3中。您可以按照以下步骤验证这一点：
mysql> INSERT INTO tr VALUES (11, 'pencil holder', '1995-07-12');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM tr WHERE purchased
-> BETWEEN '1995-01-01' AND '2004-12-31';
+------+----------------+------------+
| id | name | purchased |
+------+----------------+------------+
| 1 | desk organiser | 2003-10-15 |
| 11 | pencil holder | 1995-07-12 |
+------+----------------+------------+
2 rows in set (0.00 sec)
mysql> ALTER TABLE tr DROP PARTITION p3;
Query OK, 0 rows affected (0.03 sec)
mysql> SELECT * FROM tr WHERE purchased
-> BETWEEN '1995-01-01' AND '2004-12-31';
Empty set (0.00 sec)
由于服务器不会像等效的DELETE查询那样报告由于ALTER TABLE ... DROP PARTITION而从表中删除的行数。
删除LIST分区与删除RANGE分区使用完全相同的ALTER TABLE ... DROP PARTITION语法。然而，这对之后对表的使用有一个重要的区别：您不能再向表中插入具有被删除分区定义的值列表中的任何值的行。（请参阅第 26.2.2 节，“LIST 分区”，以获取示例。）
要向先前分区的表添加新的范围或列表分区，请使用ALTER TABLE ... ADD PARTITION语句。对于按RANGE分区的表，这可以用于在现有分区列表的末尾添加新的范围。假设您有一个包含组织成员数据的分区表，其定义如下：
CREATE TABLE members (
id INT,
fname VARCHAR(25),
lname VARCHAR(25),
dob DATE
)
PARTITION BY RANGE( YEAR(dob) ) (
PARTITION p0 VALUES LESS THAN (1980),
PARTITION p1 VALUES LESS THAN (1990),
PARTITION p2 VALUES LESS THAN (2000)
);
进一步假设成员的最小年龄为 16 岁。随着日历接近 2015 年底，您意识到必须很快准备好接纳 2000 年（及以后）出生的成员。您可以修改members表以适应在 2000 年至 2010 年出生的新成员，如下所示：
ALTER TABLE members ADD PARTITION (PARTITION p3 VALUES LESS THAN (2010));
对于按范围分区的表，您可以使用ADD PARTITION仅向分区列表的高端添加新分区。尝试以这种方式在现有分区之间或之前添加新分区会导致错误，如下所示：
mysql> ALTER TABLE members
> ADD PARTITION (
> PARTITION n VALUES LESS THAN (1970));
ERROR 1463 (HY000): VALUES LESS THAN value must be strictly »
increasing for each partition
您可以通过将第一个分区重新组织为两个新分区，将它们之间的范围分割，来解决这个问题，就像这样：
ALTER TABLE members
REORGANIZE PARTITION p0 INTO (
PARTITION n0 VALUES LESS THAN (1970),
PARTITION n1 VALUES LESS THAN (1980)
);
使用SHOW CREATE TABLE，您可以看到ALTER TABLE语句已经产生了预期的效果：
mysql> SHOW CREATE TABLE members\G
*************************** 1\. row ***************************
Table: members
Create Table: CREATE TABLE `members` (
`id` int(11) DEFAULT NULL,
`fname` varchar(25) DEFAULT NULL,
`lname` varchar(25) DEFAULT NULL,
`dob` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY RANGE ( YEAR(dob))
(PARTITION n0 VALUES LESS THAN (1970) ENGINE = InnoDB,
PARTITION n1 VALUES LESS THAN (1980) ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN (1990) ENGINE = InnoDB,
PARTITION p2 VALUES LESS THAN (2000) ENGINE = InnoDB,
PARTITION p3 VALUES LESS THAN (2010) ENGINE = InnoDB) */ 1 row in set (0.00 sec)
另请参阅 Section 15.1.9.1, “ALTER TABLE Partition Operations”。
您还可以使用ALTER TABLE ... ADD PARTITION来向由LIST分区的表中添加新分区。假设一个表tt是使用以下CREATE TABLE语句定义的：
CREATE TABLE tt (
id INT,
data INT
)
PARTITION BY LIST(data) (
PARTITION p0 VALUES IN (5, 10, 15),
PARTITION p1 VALUES IN (6, 12, 18)
);
您可以添加一个新分区，用于存储具有data列值7、14和21的行，如下所示：
ALTER TABLE tt ADD PARTITION (PARTITION p2 VALUES IN (7, 14, 21));
请记住，不能添加一个新的LIST分区，其中包含已经包含在现有分区值列表中的任何值。如果尝试这样做，将导致错误：
mysql> ALTER TABLE tt ADD PARTITION
> (PARTITION np VALUES IN (4, 8, 12));
ERROR 1465 (HY000): Multiple definition of same constant »
in list partitioning
因为具有data列值12的任何行已经分配给了分区p1，所以无法在表tt上创建一个包含12在其值列表中的新分区。为了实现这一点，您可以删除p1，然后添加np，然后一个新的p1，并修改定义。然而，正如前面讨论的，这将导致所有存储在p1中的数据丢失，而且通常情况下这并不是您真正想要做的。另一个解决方案可能是制作一个具有新分区的表的副本，并使用CREATE TABLE ... SELECT ...将数据复制到其中，然后删除旧表并重命名新表，但是在处理大量数据时可能非常耗时。在需要高可用性的情况下，这也可能不可行。
您可以在单个ALTER TABLE ... ADD PARTITION语句中添加多个分区，如下所示：
CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(50) NOT NULL,
lname VARCHAR(50) NOT NULL,
hired DATE NOT NULL
)
PARTITION BY RANGE( YEAR(hired) ) (
PARTITION p1 VALUES LESS THAN (1991),
PARTITION p2 VALUES LESS THAN (1996),
PARTITION p3 VALUES LESS THAN (2001),
PARTITION p4 VALUES LESS THAN (2005)
);
ALTER TABLE employees ADD PARTITION (
PARTITION p5 VALUES LESS THAN (2010),
PARTITION p6 VALUES LESS THAN MAXVALUE
);
幸运的是，MySQL 的分区实现提供了重新定义分区而不丢失数据的方法。让我们首先看一下涉及RANGE分区的几个简单示例。回想一下现在定义如下的members表：
mysql> SHOW CREATE TABLE members\G
*************************** 1\. row ***************************
Table: members
Create Table: CREATE TABLE `members` (
`id` int(11) DEFAULT NULL,
`fname` varchar(25) DEFAULT NULL,
`lname` varchar(25) DEFAULT NULL,
`dob` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY RANGE ( YEAR(dob))
(PARTITION n0 VALUES LESS THAN (1970) ENGINE = InnoDB,
PARTITION n1 VALUES LESS THAN (1980) ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN (1990) ENGINE = InnoDB,
PARTITION p2 VALUES LESS THAN (2000) ENGINE = InnoDB,
PARTITION p3 VALUES LESS THAN (2010) ENGINE = InnoDB) */ 1 row in set (0.00 sec)
假设您想将所有出生在 1960 年之前的会员行移动到一个单独的分区中。正如我们已经看到的，这不能通过ALTER TABLE ... ADD PARTITION来实现。然而，您可以使用另一个与分区相关的扩展来ALTER TABLE来完成这个任务：
ALTER TABLE members REORGANIZE PARTITION n0 INTO (
PARTITION s0 VALUES LESS THAN (1960),
PARTITION s1 VALUES LESS THAN (1970)
);
实际上，这个命令将分区p0分割成两个新分区s0和s1。它还根据两个PARTITION ... VALUES ...子句中体现的规则，将存储在p0中的数据移动到新分区中，因此s0只包含那些YEAR(dob)小于 1960 的记录，而s1包含那些YEAR(dob)大于或等于 1960 但小于 1970 的行。
REORGANIZE PARTITION子句也可用于合并相邻分区。您可以撤销对members表的上一个语句的影响，如下所示：
ALTER TABLE members REORGANIZE PARTITION s0,s1 INTO (
PARTITION p0 VALUES LESS THAN (1970)
);
使用REORGANIZE PARTITION拆分或合并分区时不会丢失任何数据。在执行上述语句时，MySQL 将所有存储在分区s0和s1中的记录移动到分区p0中。
REORGANIZE PARTITION的一般语法如下所示：
ALTER TABLE *tbl_name*
REORGANIZE PARTITION *partition_list*
INTO (*partition_definitions*);
这里，tbl_name是分区表的名称，partition_list是要更改的一个或多个现有分区的名称的逗号分隔列表。partition_definitions是一个逗号分隔的新分区定义列表，遵循与CREATE TABLE中使用的partition_definitions列表相同的规则。在使用REORGANIZE PARTITION时，您不限于将多个分区合并为一个，或将一个分区分割为多个。例如，您可以将members表的四个分区重新组织为两个，如下所示：
ALTER TABLE members REORGANIZE PARTITION p0,p1,p2,p3 INTO (
PARTITION m0 VALUES LESS THAN (1980),
PARTITION m1 VALUES LESS THAN (2000)
);
您还可以在按LIST进行分区的表上使用REORGANIZE PARTITION。让我们回到向列表分区的tt表添加新分区的问题，并因为新分区的值已经存在于现有分区的值列表中而失败。我们可以通过添加一个仅包含非冲突值的分区，然后重新组织新分区和现有分区，使存储在现有分区中的值现在移动到新分区来处理这个问题：
ALTER TABLE tt ADD PARTITION (PARTITION np VALUES IN (4, 8));
ALTER TABLE tt REORGANIZE PARTITION p1,np INTO (
PARTITION p1 VALUES IN (6, 18),
PARTITION np VALUES in (4, 8, 12)
);
在使用ALTER TABLE ... REORGANIZE PARTITION重新分区按RANGE或LIST进行分区的表时，请记住以下要点：
-
用于确定新分区方案的
PARTITION选项受与CREATE TABLE语句相同的规则约束。新的
RANGE分区方案不能有任何重叠的范围；新的LIST分区方案不能有任何重叠的值集。 -
partition_definitions列表中的分区组合应该总体上与partition_list中命名的组合分区涵盖相同的范围或值集。例如，在本节示例中使用的
members表中，分区p1和p2一起涵盖 1980 年至 1999 年的年份。对这两个分区的任何重新组织应该总体上涵盖相同的年份范围。 -
对于按
RANGE进行分区的表，您只能重新组织相邻分区；您不能跳过范围分区。例如，您不能使用以
ALTER TABLE members REORGANIZE PARTITION p0,p2 INTO ...开头的语句重新组织示例members表，因为p0涵盖 1970 年之前的年份，而p2涵盖 1990 年至 1999 年的年份，因此这些不是相邻的分区。（在这种情况下，您不能跳过分区p1。） -
你不能使用
REORGANIZE PARTITION来改变表使用的分区类型（例如，你不能将RANGE分区更改为HASH分区或反之）。你也不能使用这个语句来更改分区表达式或列。要完成这两项任务而不必删除和重新创建表，你可以使用ALTER TABLE ... PARTITION BY ...，如下所示：ALTER TABLE members PARTITION BY HASH( YEAR(dob) ) PARTITIONS 8;
26.3.2 哈希和键分区的管理
原文：
dev.mysql.com/doc/refman/8.0/en/partitioning-management-hash-key.html
使用哈希或键分区的表在修改分区设置方面非常相似，与按范围或列表分区的表在许多方面不同。因此，本节仅讨论了对使用哈希或键分区的表进行修改。有关对按范围或列表分区的表添加和删除分区的讨论，请参见第 26.3.1 节，“范围和列表分区的管理”。
与可以从按RANGE或LIST分区的表中删除分区的方式不同，您无法像从中删除分区一样从按HASH或KEY分区的表中删除分区。但是，您可以使用ALTER TABLE ... COALESCE PARTITION合并HASH或KEY分区。假设一个包含有关客户数据的clients表被分成了 12 个分区，如下所示创建：
CREATE TABLE clients (
id INT,
fname VARCHAR(30),
lname VARCHAR(30),
signed DATE
)
PARTITION BY HASH( MONTH(signed) )
PARTITIONS 12;
要将分区数从 12 减少到 8，请执行以下ALTER TABLE语句：
mysql> ALTER TABLE clients COALESCE PARTITION 4;
Query OK, 0 rows affected (0.02 sec)
COALESCE在使用HASH、KEY、LINEAR HASH或LINEAR KEY分区的表上同样有效。以下是一个类似于前一个示例的示例，唯一不同之处在于表是通过LINEAR KEY分区的：
mysql> CREATE TABLE clients_lk (
-> id INT,
-> fname VARCHAR(30),
-> lname VARCHAR(30),
-> signed DATE
-> )
-> PARTITION BY LINEAR KEY(signed)
-> PARTITIONS 12;
Query OK, 0 rows affected (0.03 sec)
mysql> ALTER TABLE clients_lk COALESCE PARTITION 4;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
COALESCE PARTITION后面的数字是要合并到剩余部分中的分区数，换句话说，要从表中删除的分区数。
尝试删除比表中存在的分区更多的分区会导致如下错误：
mysql> ALTER TABLE clients COALESCE PARTITION 18;
ERROR 1478 (HY000): Cannot remove all partitions, use DROP TABLE instead
要将clients表的分区数从 12 增加到 18，请使用如下ALTER TABLE ... ADD PARTITION：
ALTER TABLE clients ADD PARTITION PARTITIONS 6;
26.3.3 与表交换分区和子分区
原文：
dev.mysql.com/doc/refman/8.0/en/partitioning-management-exchange.html
在 MySQL 8.0 中，可以使用ALTER TABLE *pt* EXCHANGE PARTITION *p* WITH TABLE *nt*来交换表分区或子分区与未分区表nt，其中pt是分区表，p是要与未分区表nt交换的pt的分区或子分区，前提是以下陈述为真：
-
表
nt本身没有分区。 -
表
nt不是临时表。 -
表
pt和nt的结构在其他方面是相同的。 -
表
nt不包含外键引用，也没有其他表有任何外键引用指向nt。 -
nt中没有位于p的分区定义边界之外的行。如果使用WITHOUT VALIDATION，则不适用此条件。 -
两个表必须使用相同的字符集和校对规则。
-
对于
InnoDB表，两个表必须使用相同的行格式。要确定InnoDB表的行格式，请查询INFORMATION_SCHEMA.INNODB_TABLES。 -
任何分区级别的
MAX_ROWS设置对于p必须与为nt设置的表级别MAX_ROWS值相同。对于p的任何分区级别的MIN_ROWS设置也必须与为nt设置的表级别MIN_ROWS值相同。无论
pt是否具有显式的表级别MAX_ROWS或MIN_ROWS选项生效，这在任何情况下都是正确的。 -
AVG_ROW_LENGTH在表pt和表nt之间不能有差异。 -
表
pt不能有任何使用DATA DIRECTORY选项的分区。这个限制在 MySQL 8.0.14 及更高版本中对InnoDB表解除。 -
INDEX DIRECTORY在表和要与之交换的分区之间不能有差异。 -
任何表或分区
TABLESPACE选项都不能在任何表中使用。
除了通常需要的ALTER、INSERT和CREATE权限外，您必须具有DROP权限才能执行ALTER TABLE ... EXCHANGE PARTITION。
您还应该了解ALTER TABLE ... EXCHANGE PARTITION的以下影响：
-
执行
ALTER TABLE ... EXCHANGE PARTITION不会触发分区表或要交换表上的任何触发器。 -
交换表中的任何
AUTO_INCREMENT列都会被重置。 -
使用
ALTER TABLE ... EXCHANGE PARTITION时，IGNORE关键字不起作用。
ALTER TABLE ... EXCHANGE PARTITION的语法如下，其中pt是分区表，p是要交换的分区（或子分区），nt是要与p交换的非分区表：
ALTER TABLE *pt*
EXCHANGE PARTITION *p*
WITH TABLE *nt*;
可选地，你可以附加WITH VALIDATION或WITHOUT VALIDATION。当指定WITHOUT VALIDATION时，ALTER TABLE ... EXCHANGE PARTITION 操作在交换分区到非分区表时不执行逐行验证，允许数据库管理员承担确保行在分区定义边界内的责任。WITH VALIDATION是默认选项。
在单个ALTER TABLE EXCHANGE PARTITION语句中，只能将一个分区或子分区与一个非分区表交换。要交换多个分区或子分区，请使用多个ALTER TABLE EXCHANGE PARTITION语句。EXCHANGE PARTITION不能与其他ALTER TABLE选项结合使用。分区表使用的分区和（如果适用）子分区可以是 MySQL 8.0 支持的任何类型。
与非分区表交换分区
假设已经使用以下 SQL 语句创建和填充了分区表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
(1669, "Jim", "Smith"),
(337, "Mary", "Jones"),
(16, "Frank", "White"),
(2005, "Linda", "Black");
现在我们创建一个名为e2的非分区副本e。可以使用mysql客户端来完成，如下所示：
mysql> CREATE TABLE e2 LIKE e;
Query OK, 0 rows affected (0.04 sec)
mysql> ALTER TABLE e2 REMOVE PARTITIONING;
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
通过查询信息模式PARTITIONS表，你可以看到表e中包含行的分区，就像这样：
mysql> 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 |
+----------------+------------+
2 rows in set (0.00 sec)
注意
对于分区InnoDB表，信息模式PARTITIONS表中TABLE_ROWS列中给出的行数仅是 SQL 优化中使用的估计值，并不总是准确的。
要交换表e中的分区p0与表e2，可以使用ALTER TABLE，如下所示：
mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2;
Query OK, 0 rows affected (0.04 sec)
更准确地说，刚刚执行的语句导致在分区中找到的任何行与表中找到的行交换。你可以通过再次查询信息模式PARTITIONS表来观察这是如何发生的。之前在分区p0中找到的表行不再存在：
mysql> 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 |
+----------------+------------+
4 rows in set (0.00 sec)
如果查询表e2，你会发现“缺失”的行现在可以在那里找到：
mysql> SELECT * FROM e2;
+----+-------+-------+
| id | fname | lname |
+----+-------+-------+
| 16 | Frank | White |
+----+-------+-------+
1 row in set (0.00 sec)
与分区交换的表不一定要为空。为了演示这一点，我们首先向表e插入一行新数据，确保这行数据存储在分区p0中，方法是选择一个小于 50 的id列值，并在之后通过查询PARTITIONS表进行验证：
mysql> INSERT INTO e VALUES (41, "Michael", "Green");
Query OK, 1 row affected (0.05 sec)
mysql> 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 |
+----------------+------------+
4 rows in set (0.00 sec)
现在我们再次使用与之前相同的ALTER TABLE语句交换分区p0与表e2：
mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2;
Query OK, 0 rows affected (0.28 sec)
以下查询的输出显示，在发出ALTER TABLE语句之前存储在分区p0中的表行和存储在表e2中的表行现在已经交换位置：
mysql> SELECT * FROM e;
+------+-------+-------+
| id | fname | lname |
+------+-------+-------+
| 16 | Frank | White |
| 1669 | Jim | Smith |
| 337 | Mary | Jones |
| 2005 | Linda | Black |
+------+-------+-------+
4 rows in set (0.00 sec)
mysql> 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 |
+----------------+------------+
4 rows in set (0.00 sec)
mysql> SELECT * FROM e2;
+----+---------+-------+
| id | fname | lname |
+----+---------+-------+
| 41 | Michael | Green |
+----+---------+-------+
1 row in set (0.00 sec)
不匹配的行
请记住，在发出ALTER TABLE ... EXCHANGE PARTITION语句之前，在非分区表中找到的任何行必须满足存储在目标分区中的条件；否则，该语句将失败。为了看到这是如何发生的，首先向e2插入一行数据，该行数据超出了表e的分区p0的定义范围。例如，插入一个id列值过大的行；然后，再次尝试与分区交换表：
mysql> INSERT INTO e2 VALUES (51, "Ellen", "McDonald");
Query OK, 1 row affected (0.08 sec)
mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2;
ERROR 1707 (HY000): Found row that does not match the partition
只有WITHOUT VALIDATION选项才能使此操作成功：
mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2 WITHOUT VALIDATION;
Query OK, 0 rows affected (0.02 sec)
当将一个分区与包含不符合分区定义的行的表交换时，由数据库管理员负责修复不匹配的行，可以使用REPAIR TABLE或ALTER TABLE ... REPAIR PARTITION来执行。
不需要逐行验证即可交换分区
当将一个分区与包含许多行的表交换时，为了避免耗时的验证，可以在ALTER TABLE ... EXCHANGE PARTITION语句中添加WITHOUT VALIDATION来跳过逐行验证步骤。
以下示例比较了与和不带验证时交换分区与非分区表的执行时间差异。分区表（表e）包含两个各有 100 万行的分区。表e的 p0 中的行被移除，并且 p0 与一个有 100 万行的非分区表交换。WITH VALIDATION操作耗时 0.74 秒。相比之下，WITHOUT VALIDATION操作只需 0.01 秒。
# Create a partitioned table with 1 million rows in each partition
CREATE TABLE e (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30)
)
PARTITION BY RANGE (id) (
PARTITION p0 VALUES LESS THAN (1000001),
PARTITION p1 VALUES LESS THAN (2000001),
);
mysql> SELECT COUNT(*) FROM e;
| COUNT(*) |
+----------+
| 2000000 |
+----------+
1 row in set (0.27 sec)
# View the rows in each partition
SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'e';
+----------------+-------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+-------------+
| p0 | 1000000 |
| p1 | 1000000 |
+----------------+-------------+
2 rows in set (0.00 sec)
# Create a nonpartitioned table of the same structure and populate it with 1 million rows
CREATE TABLE e2 (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30)
);
mysql> SELECT COUNT(*) FROM e2;
+----------+
| COUNT(*) |
+----------+
| 1000000 |
+----------+
1 row in set (0.24 sec)
# Create another nonpartitioned table of the same structure and populate it with 1 million rows
CREATE TABLE e3 (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30)
);
mysql> SELECT COUNT(*) FROM e3;
+----------+
| COUNT(*) |
+----------+
| 1000000 |
+----------+
1 row in set (0.25 sec)
# Drop the rows from p0 of table e
mysql> DELETE FROM e WHERE id < 1000001;
Query OK, 1000000 rows affected (5.55 sec)
# Confirm that there are no rows in partition p0
mysql> SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'e';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0 | 0 |
| p1 | 1000000 |
+----------------+------------+
2 rows in set (0.00 sec)
# Exchange partition p0 of table e with the table e2 'WITH VALIDATION'
mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2 WITH VALIDATION;
Query OK, 0 rows affected (0.74 sec)
# Confirm that the partition was exchanged with table e2
mysql> SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'e';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0 | 1000000 |
| p1 | 1000000 |
+----------------+------------+
2 rows in set (0.00 sec)
# Once again, drop the rows from p0 of table e
mysql> DELETE FROM e WHERE id < 1000001;
Query OK, 1000000 rows affected (5.55 sec)
# Confirm that there are no rows in partition p0
mysql> SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'e';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0 | 0 |
| p1 | 1000000 |
+----------------+------------+
2 rows in set (0.00 sec)
# Exchange partition p0 of table e with the table e3 'WITHOUT VALIDATION'
mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e3 WITHOUT VALIDATION;
Query OK, 0 rows affected (0.01 sec)
# Confirm that the partition was exchanged with table e3
mysql> SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'e';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0 | 1000000 |
| p1 | 1000000 |
+----------------+------------+
2 rows in set (0.00 sec)
如果将一个分区与包含不符合分区定义的行的表进行交换，数据库管理员有责任修复不匹配的行，可以使用REPAIR TABLE或ALTER TABLE ... REPAIR PARTITION来执行此操作。
用非分区表交换子分区
你也可以使用ALTER TABLE ... EXCHANGE PARTITION语句，将分区表的一个子分区（参见第 26.2.6 节，“子分区”）与非分区表进行交换。在下面的示例中，我们首先创建一个按RANGE分区并按KEY子分区的表es，像我们创建表e一样填充这个表，然后创建一个空的、非分区的副本es2，如下所示：
mysql> CREATE TABLE es (
-> id INT NOT NULL,
-> fname VARCHAR(30),
-> lname VARCHAR(30)
-> )
-> PARTITION BY RANGE (id)
-> SUBPARTITION BY KEY (lname)
-> SUBPARTITIONS 2 (
-> PARTITION p0 VALUES LESS THAN (50),
-> PARTITION p1 VALUES LESS THAN (100),
-> PARTITION p2 VALUES LESS THAN (150),
-> PARTITION p3 VALUES LESS THAN (MAXVALUE)
-> );
Query OK, 0 rows affected (2.76 sec)
mysql> INSERT INTO es VALUES
-> (1669, "Jim", "Smith"),
-> (337, "Mary", "Jones"),
-> (16, "Frank", "White"),
-> (2005, "Linda", "Black");
Query OK, 4 rows affected (0.04 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> CREATE TABLE es2 LIKE es;
Query OK, 0 rows affected (1.27 sec)
mysql> ALTER TABLE es2 REMOVE PARTITIONING;
Query OK, 0 rows affected (0.70 sec)
Records: 0 Duplicates: 0 Warnings: 0
虽然我们在创建表es时没有明确命名任何子分区，但我们可以通过在从INFORMATION_SCHEMA中的PARTITIONS表中选择时包含SUBPARTITION_NAME列来获取这些子分区的生成名称，如下所示：
mysql> SELECT PARTITION_NAME, SUBPARTITION_NAME, TABLE_ROWS
-> FROM INFORMATION_SCHEMA.PARTITIONS
-> WHERE TABLE_NAME = 'es';
+----------------+-------------------+------------+
| PARTITION_NAME | SUBPARTITION_NAME | TABLE_ROWS |
+----------------+-------------------+------------+
| p0 | p0sp0 | 1 |
| p0 | p0sp1 | 0 |
| p1 | p1sp0 | 0 |
| p1 | p1sp1 | 0 |
| p2 | p2sp0 | 0 |
| p2 | p2sp1 | 0 |
| p3 | p3sp0 | 3 |
| p3 | p3sp1 | 0 |
+----------------+-------------------+------------+
8 rows in set (0.00 sec)
以下ALTER TABLE语句将表es中的子分区p3sp0与非分区表es2进行交换：
mysql> ALTER TABLE es EXCHANGE PARTITION p3sp0 WITH TABLE es2;
Query OK, 0 rows affected (0.29 sec)
你可以通过发出以下查询来验证行是否已经交换：
mysql> SELECT PARTITION_NAME, SUBPARTITION_NAME, TABLE_ROWS
-> FROM INFORMATION_SCHEMA.PARTITIONS
-> WHERE TABLE_NAME = 'es';
+----------------+-------------------+------------+
| PARTITION_NAME | SUBPARTITION_NAME | TABLE_ROWS |
+----------------+-------------------+------------+
| p0 | p0sp0 | 1 |
| p0 | p0sp1 | 0 |
| p1 | p1sp0 | 0 |
| p1 | p1sp1 | 0 |
| p2 | p2sp0 | 0 |
| p2 | p2sp1 | 0 |
| p3 | p3sp0 | 0 |
| p3 | p3sp1 | 0 |
+----------------+-------------------+------------+
8 rows in set (0.00 sec)
mysql> SELECT * FROM es2;
+------+-------+-------+
| id | fname | lname |
+------+-------+-------+
| 1669 | Jim | Smith |
| 337 | Mary | Jones |
| 2005 | Linda | Black |
+------+-------+-------+
3 rows in set (0.00 sec)
如果表被子分区，你只能交换表的一个子分区，而不是整个分区，如下所示：
mysql> ALTER TABLE es EXCHANGE PARTITION p3 WITH TABLE es2;
ERROR 1704 (HY000): Subpartitioned table, use subpartition instead of partition
表结构严格比较；分区表和非分区表的列和索引的数量、顺序、名称和类型必须完全匹配。此外，两个表必须使用相同的存储引擎：
mysql> CREATE TABLE es3 LIKE e;
Query OK, 0 rows affected (1.31 sec)
mysql> ALTER TABLE es3 REMOVE PARTITIONING;
Query OK, 0 rows affected (0.53 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW CREATE TABLE es3\G
*************************** 1\. row ***************************
Table: es3
Create Table: CREATE TABLE `es3` (
`id` int(11) NOT NULL,
`fname` varchar(30) DEFAULT NULL,
`lname` varchar(30) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec)
mysql> ALTER TABLE es3 ENGINE = MyISAM;
Query OK, 0 rows affected (0.15 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE es EXCHANGE PARTITION p3sp0 WITH TABLE es3;
ERROR 1497 (HY000): The mix of handlers in the partitions is not allowed in this version of MySQL
26.3.4 分区的维护
译文：
dev.mysql.com/doc/refman/8.0/en/partitioning-maintenance.html
可以使用针对此类目的而设计的 SQL 语句在分区表上执行许多表和分区维护任务。
可以使用支持分区表的 CHECK TABLE、OPTIMIZE TABLE、ANALYZE TABLE 和 REPAIR TABLE 语句来完成分区表的表维护。
您可以使用一些扩展功能来直接在一个或多个分区上执行此类操作，如下列表所述：
-
重建分区。 重建分区；这与删除分区中存储的所有记录，然后重新插入它们具有相同效果。这对于碎片整理很有用。
示例：
ALTER TABLE t1 REBUILD PARTITION p0, p1; -
优化分区。 如果您从分区中删除了大量行，或者对具有可变长度行的分区表进行了许多更改（即具有
VARCHAR、BLOB或TEXT列），您可以使用ALTER TABLE ... OPTIMIZE PARTITION来回收未使用的空间并对分区数据文件进行碎片整理。示例：
ALTER TABLE t1 OPTIMIZE PARTITION p0, p1;在给定分区上使用
OPTIMIZE PARTITION相当于在该分区上运行CHECK PARTITION、ANALYZE PARTITION和REPAIR PARTITION。一些 MySQL 存储引擎，包括
InnoDB，不支持每个分区的优化；在这些情况下，ALTER TABLE ... OPTIMIZE PARTITION会分析并重建整个表，并发出适当的警告（Bug #11751825, Bug #42822）。为避免此问题，请改用ALTER TABLE ... REBUILD PARTITION和ALTER TABLE ... ANALYZE PARTITION。 -
分析分区。 这读取并存储分区的键分布。
示例：
ALTER TABLE t1 ANALYZE PARTITION p3; -
修复分区。 这修复损坏的分区。
示例：
ALTER TABLE t1 REPAIR PARTITION p0,p1;通常情况下，当分区包含重复键错误时，
REPAIR PARTITION操作会失败。您可以使用ALTER IGNORE TABLE选项，此时由于存在重复键而无法移动的所有行将从分区中删除（Bug #16900947）。 -
检查分区。 您可以以与对非分区表使用
CHECK TABLE相同的方式检查分区中的错误。示例：
ALTER TABLE trb3 CHECK PARTITION p1;这个语句告诉您表
t1中分区p1中的数据或索引是否损坏。如果是这种情况，请使用ALTER TABLE ... REPAIR PARTITION来修复该分区。通常，当分区包含重复键错误时，
CHECK PARTITION会失败。您可以在此选项中使用ALTER IGNORE TABLE，在这种情况下，该语句将返回在发现重复键违规的分区中每一行的内容。仅报告表的分区表达式中的列的值。（Bug #16900947）
列出的每个语句还支持关键字ALL代替分区名称列表。使用ALL会导致该语句作用于表中的所有分区。
您还可以使用ALTER TABLE ... TRUNCATE PARTITION截断分区。该语句可用于删除一个或多个分区中的所有行，方式与TRUNCATE TABLE删除表中的所有行类似。
ALTER TABLE ... TRUNCATE PARTITION ALL截断表中的所有分区。
26.3.5 获取有关分区的信息
本节讨论获取有关现有分区的信息，可以通过多种方式进行。获取此类信息的方法包括以下内容：
-
使用
SHOW CREATE TABLE语句查看创建分区表时使用的分区子句。 -
使用
SHOW TABLE STATUS语句来确定表是否被分区。 -
查询信息模式
PARTITIONS表。 -
使用语句
EXPLAIN SELECT来查看给定SELECT使用的分区。
从 MySQL 8.0.16 开始，当对分区表进行插入、删除或更新操作时，二进制日志记录有关发生行事件的分区和（如果有）子分区的信息。即使涉及的表相同，也为发生在不同分区或子分区中的修改创建新的行事件。因此，如果一个事务涉及三个分区或子分区，将生成三个行事件。对于更新事件，分区信息被记录在“之前”图像和“之后”图像中。如果在使用mysqlbinlog查看二进制日志时指定了-v或--verbose选项，则会显示分区信息。仅当使用基于行的日志记录时（binlog_format=ROW）才记录分区信息。
如本章其他地方所讨论的，SHOW CREATE TABLE在其输出中包含用于创建分区表的PARTITION BY子句。例如：
mysql> SHOW CREATE TABLE trb3\G
*************************** 1\. row ***************************
Table: trb3
Create Table: CREATE TABLE `trb3` (
`id` int(11) DEFAULT NULL,
`name` varchar(50) DEFAULT NULL,
`purchased` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
/*!50100 PARTITION BY RANGE (YEAR(purchased))
(PARTITION p0 VALUES LESS THAN (1990) ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN (1995) ENGINE = InnoDB,
PARTITION p2 VALUES LESS THAN (2000) ENGINE = InnoDB,
PARTITION p3 VALUES LESS THAN (2005) ENGINE = InnoDB) */ 0 row in set (0.00 sec)
对于分区表，SHOW TABLE STATUS的输出与非分区表相同，只是Create_options列包含字符串partitioned。Engine列包含表的所有分区使用的存储引擎的名称。（有关此语句的更多信息，请参见第 15.7.7.38 节，“SHOW TABLE STATUS Statement”。）
您还可以从INFORMATION_SCHEMA中获取有关分区的信息，其中包含一个PARTITIONS表。请参阅第 28.3.21 节，“INFORMATION_SCHEMA PARTITIONS 表”。
可以通过使用解释来确定分区表中哪些分区涉及给定的SELECT查询。解释输出中的partitions列列出了查询将匹配的记录的分区。
假设创建并填充了一个名为trb1的表如下：
CREATE TABLE trb1 (id INT, name VARCHAR(50), purchased DATE)
PARTITION BY RANGE(id)
(
PARTITION p0 VALUES LESS THAN (3),
PARTITION p1 VALUES LESS THAN (7),
PARTITION p2 VALUES LESS THAN (9),
PARTITION p3 VALUES LESS THAN (11)
);
INSERT INTO trb1 VALUES
(1, 'desk organiser', '2003-10-15'),
(2, 'CD player', '1993-11-05'),
(3, 'TV set', '1996-03-10'),
(4, 'bookcase', '1982-01-10'),
(5, 'exercise bike', '2004-05-09'),
(6, 'sofa', '1987-06-05'),
(7, 'popcorn maker', '2001-11-22'),
(8, 'aquarium', '1992-08-04'),
(9, 'study desk', '1984-09-16'),
(10, 'lava lamp', '1998-12-25');
你可以看到在查询中使用了哪些分区，比如SELECT * FROM trb1;，如下所示：
mysql> EXPLAIN SELECT * FROM trb1\G
*************************** 1\. row ***************************
id: 1
select_type: SIMPLE
table: trb1
partitions: p0,p1,p2,p3
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 10
Extra: Using filesort
在这种情况下，所有四个分区都被搜索。然而，当在查询中添加使用分区键的限制条件时，您可以看到只有包含匹配值的分区被扫描，如下所示：
mysql> EXPLAIN SELECT * FROM trb1 WHERE id < 5\G
*************************** 1\. row ***************************
id: 1
select_type: SIMPLE
table: trb1
partitions: p0,p1
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 10
Extra: Using where
解释还提供了有关使用的键和可能的键的信息：
mysql> ALTER TABLE trb1 ADD PRIMARY KEY (id);
Query OK, 10 rows affected (0.03 sec)
Records: 10 Duplicates: 0 Warnings: 0
mysql> EXPLAIN SELECT * FROM trb1 WHERE id < 5\G
*************************** 1\. row ***************************
id: 1
select_type: SIMPLE
table: trb1
partitions: p0,p1
type: range
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: NULL
rows: 7
Extra: Using where
如果使用解释来检查针对非分区表的查询，不会产生错误，但partitions列的值始终为NULL。
解释输出的rows列显示表中的总行数。
另请参阅第 15.8.2 节，“解释语句”。
26.4 分区修剪
原文：
dev.mysql.com/doc/refman/8.0/en/partitioning-pruning.html
分区修剪优化基于一个相对简单的概念，可以描述为“不要扫描那些不可能有匹配值的分区”。假设通过以下语句创建了一个分区表t1：
CREATE TABLE t1 (
fname VARCHAR(50) NOT NULL,
lname VARCHAR(50) NOT NULL,
region_code TINYINT UNSIGNED NOT NULL,
dob DATE NOT NULL
)
PARTITION BY RANGE( region_code ) (
PARTITION p0 VALUES LESS THAN (64),
PARTITION p1 VALUES LESS THAN (128),
PARTITION p2 VALUES LESS THAN (192),
PARTITION p3 VALUES LESS THAN MAXVALUE
);
假设您希望从类似于以下语句的SELECT语句中获取结果：
SELECT fname, lname, region_code, dob
FROM t1
WHERE region_code > 125 AND region_code < 130;
很容易看出，应该返回的行中没有任何位于分区p0或p3中的行；也就是说，我们只需要在分区p1和p2中搜索匹配的行。通过限制搜索范围，可以比扫描表中的所有分区更少的时间和精力来找到匹配的行。这种“剪掉”不需要的分区的操作称为修剪。当优化器在执行此查询时可以利用分区修剪时，查询的执行速度可能比针对包含相同列定义和数据的非分区表的相同查询快一个数量级。
优化器可以在WHERE条件可以简化为以下两种情况之一时执行修剪：
-
*partition_column* = *constant* -
*partition_column* IN (*constant1*, *constant2*, ..., *constantN*)
在第一种情况下，优化器简单地对给定值评估分区表达式，确定包含该值的分区，并仅扫描此分区。在许多情况下，等号可以替换为另一个算术比较，包括<、>、<=、>=和<>。一些在WHERE子句中使用BETWEEN的查询也可以利用分区修剪。请参见本节后面的示例。
在第二种情况下，优化器对列表中的每个值评估分区表达式，创建一个匹配分区的列表，然后仅扫描此分区列表中的分区。
SELECT、DELETE和UPDATE语句支持分区修剪。INSERT语句也仅访问每个插入行的一个分区；即使对于使用HASH或KEY进行分区的表，这也是正确的，尽管目前在EXPLAIN的输出中没有显示。
修剪也可以应用于短范围，优化器可以将其转换为等效值列表。例如，在前面的例子中，WHERE 子句可以转换为 WHERE region_code IN (126, 127, 128, 129)。然后优化器可以确定列表中的前两个值位于分区 p1 中，剩下的两个值位于分区 p2 中，其他分区不包含相关值，因此不需要搜索匹配行。
优化器还可以对使用 RANGE COLUMNS 或 LIST COLUMNS 分区的表上涉及多列比较的 WHERE 条件执行修剪。
只要分区表达式由相等性或可减少为一组相等性的范围组成，或者分区表达式表示递增或递减关系，就可以应用这种优化。当分区表达式使用 YEAR() 或 TO_DAYS() 函数时，也可以应用修剪到基于 DATE 或 DATETIME 列分区的表。当分区表达式使用 TO_SECONDS() 函数时，也可以应用修剪到这样的表。
假设表 t2，根据一个 DATE 列进行分区，是使用以下语句创建的：
CREATE TABLE t2 (
fname VARCHAR(50) NOT NULL,
lname VARCHAR(50) NOT NULL,
region_code TINYINT UNSIGNED NOT NULL,
dob DATE NOT NULL
)
PARTITION BY RANGE( YEAR(dob) ) (
PARTITION d0 VALUES LESS THAN (1970),
PARTITION d1 VALUES LESS THAN (1975),
PARTITION d2 VALUES LESS THAN (1980),
PARTITION d3 VALUES LESS THAN (1985),
PARTITION d4 VALUES LESS THAN (1990),
PARTITION d5 VALUES LESS THAN (2000),
PARTITION d6 VALUES LESS THAN (2005),
PARTITION d7 VALUES LESS THAN MAXVALUE
);
以下使用 t2 的语句可以利用分区修剪：
SELECT * FROM t2 WHERE dob = '1982-06-23';
UPDATE t2 SET region_code = 8 WHERE dob BETWEEN '1991-02-15' AND '1997-04-25';
DELETE FROM t2 WHERE dob >= '1984-06-21' AND dob <= '1999-06-21'
对于最后一条语句，优化器还可以执行以下操作：
-
找到包含范围低端的分区。
YEAR('1984-06-21')返回值为1984，该值位于分区d3中。 -
找到包含范围高端的分区。
YEAR('1999-06-21')计算结果为1999，该值位于分区d5中。 -
仅扫描这两个分区以及可能位于它们之间的任何分区。
在这种情况下，这意味着只有分区
d3、d4和d5被扫描。其余分区可以安全地被忽略（并且被忽略）。
重要提示
对于针对分区表的语句中 WHERE 条件引用的无效 DATE 和 DATETIME 值，将被视为 NULL。这意味着像 SELECT * FROM *partitioned_table* WHERE *date_column* < '2008-12-00' 这样的查询不会返回任何值（参见 Bug #40972）。
到目前为止，我们只看过使用 RANGE 分区的示例，但修剪也可以应用于其他分区类型。
考虑一个由LIST分区的表，其中分区表达式是递增或递减的，例如这里显示的表t3。（在本例中，为简洁起见，我们假设region_code列的值限定在 1 到 10 之间。）
CREATE TABLE t3 (
fname VARCHAR(50) NOT NULL,
lname VARCHAR(50) NOT NULL,
region_code TINYINT UNSIGNED NOT NULL,
dob DATE NOT NULL
)
PARTITION BY LIST(region_code) (
PARTITION r0 VALUES IN (1, 3),
PARTITION r1 VALUES IN (2, 5, 8),
PARTITION r2 VALUES IN (4, 9),
PARTITION r3 VALUES IN (6, 7, 10)
);
对于像SELECT * FROM t3 WHERE region_code BETWEEN 1 AND 3这样的语句，优化器确定值 1、2 和 3 在哪些分区中找到（r0和r1），并跳过其余的分区（r2和r3）。
对于由HASH或[LINEAR] KEY分区的表，如果WHERE子句使用简单的=关系针对分区表达式中使用的列，则也可以进行分区修剪。考虑创建如下表：
CREATE TABLE t4 (
fname VARCHAR(50) NOT NULL,
lname VARCHAR(50) NOT NULL,
region_code TINYINT UNSIGNED NOT NULL,
dob DATE NOT NULL
)
PARTITION BY KEY(region_code)
PARTITIONS 8;
将列值与常量进行比较的语句可以被修剪：
UPDATE t4 WHERE region_code = 7;
对于短范围，修剪也可以用于，因为优化器可以将这种条件转换为IN关系。例如，使用之前定义的相同表t4，可以修剪以下查询：
SELECT * FROM t4 WHERE region_code > 2 AND region_code < 6;
SELECT * FROM t4 WHERE region_code BETWEEN 3 AND 5;
在这两种情况下，优化器将WHERE子句转换为WHERE region_code IN (3, 4, 5)。
重要
只有当范围大小小于分区数时才使用这种优化。考虑这个语句：
DELETE FROM t4 WHERE region_code BETWEEN 4 AND 12;
WHERE子句中的范围涵盖了 9 个值（4, 5, 6, 7, 8, 9, 10, 11, 12），但t4只有 8 个分区。这意味着DELETE无法被修剪。
当表由HASH或[LINEAR] KEY分区时，修剪只能用于整数列。例如，这个语句不能使用修剪，因为dob是一个DATE列：
SELECT * FROM t4 WHERE dob >= '2001-04-14' AND dob <= '2005-10-15';
然而，如果表在INT列中存储年份值，则查询WHERE year_col >= 2001 AND year_col <= 2005可以被修剪。
使用提供自动分区的存储引擎的表，例如 MySQL Cluster 使用的NDB存储引擎，如果它们被显式分区，则可以被修剪。
26.5 分区选择
原文：
dev.mysql.com/doc/refman/8.0/en/partitioning-selection.html
支持为匹配给定WHERE条件的行显式选择分区和子分区。分区选择类似于分区修剪，只检查特定分区是否匹配，但在两个关键方面有所不同：
-
要检查的分区由语句的发出者指定，与自动分区修剪不同。
-
分区修剪仅适用于查询，而显式选择分区支持查询和一些 DML 语句。
支持显式分区选择的 SQL 语句列在此处：
-
SELECT -
DELETE -
INSERT -
REPLACE -
UPDATE -
LOAD DATA。 -
LOAD XML。
本节的其余部分讨论了显式分区选择，通常适用于刚刚列出的语句，并提供了一些示例。
显式分区选择是使用PARTITION选项实现的。对于所有支持的语句，此选项使用以下语法：
PARTITION (*partition_names*)
*partition_names*:
*partition_name*, ...
此选项始终跟随分区或子分区所属的表的名称。partition_names是要使用的分区或子分区的逗号分隔列表。此列表中的每个名称必须是指定表的现有分区或子分区的名称；如果找不到任何分区或子分区，则该语句将失败并显示错误（分区'partition_name'不存在）。partition_names中列出的分区和子分区可以以任何顺序列出，并且可以重叠。
使用PARTITION选项时，仅检查列出的分区和子分区以匹配行。此选项可用于SELECT语句，以确定哪些行属于给定分区。考虑一个名为employees的分区表，使用以下语句创建和填充：
SET @@SQL_MODE = '';
CREATE TABLE employees (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
fname VARCHAR(25) NOT NULL,
lname VARCHAR(25) NOT NULL,
store_id INT NOT NULL,
department_id INT NOT NULL
)
PARTITION BY RANGE(id) (
PARTITION p0 VALUES LESS THAN (5),
PARTITION p1 VALUES LESS THAN (10),
PARTITION p2 VALUES LESS THAN (15),
PARTITION p3 VALUES LESS THAN MAXVALUE
);
INSERT INTO employees VALUES
('', 'Bob', 'Taylor', 3, 2), ('', 'Frank', 'Williams', 1, 2),
('', 'Ellen', 'Johnson', 3, 4), ('', 'Jim', 'Smith', 2, 4),
('', 'Mary', 'Jones', 1, 1), ('', 'Linda', 'Black', 2, 3),
('', 'Ed', 'Jones', 2, 1), ('', 'June', 'Wilson', 3, 1),
('', 'Andy', 'Smith', 1, 3), ('', 'Lou', 'Waters', 2, 4),
('', 'Jill', 'Stone', 1, 4), ('', 'Roger', 'White', 3, 2),
('', 'Howard', 'Andrews', 1, 2), ('', 'Fred', 'Goldberg', 3, 3),
('', 'Barbara', 'Brown', 2, 3), ('', 'Alice', 'Rogers', 2, 2),
('', 'Mark', 'Morgan', 3, 3), ('', 'Karen', 'Cole', 3, 2);
您可以这样查看存储在分区p1中的行：
mysql> SELECT * FROM employees PARTITION (p1);
+----+-------+--------+----------+---------------+
| id | fname | lname | store_id | department_id |
+----+-------+--------+----------+---------------+
| 5 | Mary | Jones | 1 | 1 |
| 6 | Linda | Black | 2 | 3 |
| 7 | Ed | Jones | 2 | 1 |
| 8 | June | Wilson | 3 | 1 |
| 9 | Andy | Smith | 1 | 3 |
+----+-------+--------+----------+---------------+
5 rows in set (0.00 sec)
结果与通过查询SELECT * FROM employees WHERE id BETWEEN 5 AND 9获得的结果相同。
要从多个分区获取行，请将它们的名称作为逗号分隔的列表提供。例如，SELECT * FROM employees PARTITION (p1, p2)返回分区p1和p2中的所有行，同时排除其余分区的行。
对分区表的任何有效查询都可以通过PARTITION选项重写，以限制结果为一个或多个所需的分区。您可以使用WHERE条件、ORDER BY和LIMIT选项等。您还可以使用带有HAVING和GROUP BY选项的聚合函数。在先前定义的employees表上运行以下每个查询都会产生有效结果：
mysql> SELECT * FROM employees PARTITION (p0, p2)
-> WHERE lname LIKE 'S%';
+----+-------+-------+----------+---------------+
| id | fname | lname | store_id | department_id |
+----+-------+-------+----------+---------------+
| 4 | Jim | Smith | 2 | 4 |
| 11 | Jill | Stone | 1 | 4 |
+----+-------+-------+----------+---------------+
2 rows in set (0.00 sec)
mysql> SELECT id, CONCAT(fname, ' ', lname) AS name
-> FROM employees PARTITION (p0) ORDER BY lname;
+----+----------------+
| id | name |
+----+----------------+
| 3 | Ellen Johnson |
| 4 | Jim Smith |
| 1 | Bob Taylor |
| 2 | Frank Williams |
+----+----------------+
4 rows in set (0.06 sec)
mysql> SELECT store_id, COUNT(department_id) AS c
-> FROM employees PARTITION (p1,p2,p3)
-> GROUP BY store_id HAVING c > 4;
+---+----------+
| c | store_id |
+---+----------+
| 5 | 2 |
| 5 | 3 |
+---+----------+
2 rows in set (0.00 sec)
使用分区选择的语句可以与使用任何支持的分区类型的表一起使用。当使用[LINEAR] HASH或[LINEAR] KEY分区创建表时，如果未指定分区的名称，MySQL 会自动将分区命名为p0、p1、p2、...、p*N-1*，其中N是分区的数量。对于未明确命名的子分区，MySQL 会自动为每个分区p*X*中的子分区分配名称p*X*sp0、p*X*sp1、p*X*sp2、...、p*X*sp*M-1*，其中M是子分区的数量。在针对这个表执行SELECT（或其他允许显式分区选择的 SQL 语句）时，您可以在PARTITION选项中使用这些生成的名称，如下所示：
mysql> CREATE TABLE employees_sub (
-> id INT NOT NULL AUTO_INCREMENT,
-> fname VARCHAR(25) NOT NULL,
-> lname VARCHAR(25) NOT NULL,
-> store_id INT NOT NULL,
-> department_id INT NOT NULL,
-> PRIMARY KEY pk (id, lname)
-> )
-> PARTITION BY RANGE(id)
-> SUBPARTITION BY KEY (lname)
-> SUBPARTITIONS 2 (
-> PARTITION p0 VALUES LESS THAN (5),
-> PARTITION p1 VALUES LESS THAN (10),
-> PARTITION p2 VALUES LESS THAN (15),
-> PARTITION p3 VALUES LESS THAN MAXVALUE
-> );
Query OK, 0 rows affected (1.14 sec)
mysql> INSERT INTO employees_sub # reuse data in employees table
-> SELECT * FROM employees;
Query OK, 18 rows affected (0.09 sec)
Records: 18 Duplicates: 0 Warnings: 0
mysql> SELECT id, CONCAT(fname, ' ', lname) AS name
-> FROM employees_sub PARTITION (p2sp1);
+----+---------------+
| id | name |
+----+---------------+
| 10 | Lou Waters |
| 14 | Fred Goldberg |
+----+---------------+
2 rows in set (0.00 sec)
您还可以在SELECT部分的INSERT ... SELECT语句中使用PARTITION选项，如下所示：
mysql> CREATE TABLE employees_copy LIKE employees;
Query OK, 0 rows affected (0.28 sec)
mysql> INSERT INTO employees_copy
-> SELECT * FROM employees PARTITION (p2);
Query OK, 5 rows affected (0.04 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM employees_copy;
+----+--------+----------+----------+---------------+
| id | fname | lname | store_id | department_id |
+----+--------+----------+----------+---------------+
| 10 | Lou | Waters | 2 | 4 |
| 11 | Jill | Stone | 1 | 4 |
| 12 | Roger | White | 3 | 2 |
| 13 | Howard | Andrews | 1 | 2 |
| 14 | Fred | Goldberg | 3 | 3 |
+----+--------+----------+----------+---------------+
5 rows in set (0.00 sec)
分区选择也可以与连接一起使用。假设我们使用以下语句创建和填充了两个表：
CREATE TABLE stores (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
city VARCHAR(30) NOT NULL
)
PARTITION BY HASH(id)
PARTITIONS 2;
INSERT INTO stores VALUES
('', 'Nambucca'), ('', 'Uranga'),
('', 'Bellingen'), ('', 'Grafton');
CREATE TABLE departments (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(30) NOT NULL
)
PARTITION BY KEY(id)
PARTITIONS 2;
INSERT INTO departments VALUES
('', 'Sales'), ('', 'Customer Service'),
('', 'Delivery'), ('', 'Accounting');
您可以明确地从连接中的任何或所有表中选择分区（或子分区，或两者）。（用于从给定表中选择分区的PARTITION选项紧随表名之后，位于所有其他选项之前，包括任何表别名。）例如，以下查询获取所有在销售或交付部门（departments表的分区p1）中的商店（stores表的分区p0）中工作的员工的姓名、员工 ID、部门和城市：
mysql> SELECT
-> e.id AS 'Employee ID', CONCAT(e.fname, ' ', e.lname) AS Name,
-> s.city AS City, d.name AS department
-> FROM employees AS e
-> JOIN stores PARTITION (p1) AS s ON e.store_id=s.id
-> JOIN departments PARTITION (p0) AS d ON e.department_id=d.id
-> ORDER BY e.lname;
+-------------+---------------+-----------+------------+
| Employee ID | Name | City | department |
+-------------+---------------+-----------+------------+
| 14 | Fred Goldberg | Bellingen | Delivery |
| 5 | Mary Jones | Nambucca | Sales |
| 17 | Mark Morgan | Bellingen | Delivery |
| 9 | Andy Smith | Nambucca | Delivery |
| 8 | June Wilson | Bellingen | Sales |
+-------------+---------------+-----------+------------+
5 rows in set (0.00 sec)
有关 MySQL 中连接的一般信息，请参见第 15.2.13.2 节，“JOIN Clause”。
当PARTITION选项与DELETE语句一起使用时，只有在选项中列出的那些分区（和子分区，如果有）会被检查以删除行。任何其他分区都会被忽略，如下所示：
mysql> SELECT * FROM employees WHERE fname LIKE 'j%';
+----+-------+--------+----------+---------------+
| id | fname | lname | store_id | department_id |
+----+-------+--------+----------+---------------+
| 4 | Jim | Smith | 2 | 4 |
| 8 | June | Wilson | 3 | 1 |
| 11 | Jill | Stone | 1 | 4 |
+----+-------+--------+----------+---------------+
3 rows in set (0.00 sec)
mysql> DELETE FROM employees PARTITION (p0, p1)
-> WHERE fname LIKE 'j%';
Query OK, 2 rows affected (0.09 sec)
mysql> SELECT * FROM employees WHERE fname LIKE 'j%';
+----+-------+-------+----------+---------------+
| id | fname | lname | store_id | department_id |
+----+-------+-------+----------+---------------+
| 11 | Jill | Stone | 1 | 4 |
+----+-------+-------+----------+---------------+
1 row in set (0.00 sec)
只有与WHERE条件匹配的p0和p1分区中的两行被删除。从第二次运行SELECT时的结果中可以看到，表中仍然存在一行与WHERE条件匹配，但位于不同的分区（p2）。
使用显式分区选择的UPDATE语句的行为相同；只有在确定要更新的行时，才考虑PARTITION选项引用的分区中的行，可以通过执行以下语句来查看：
mysql> UPDATE employees PARTITION (p0)
-> SET store_id = 2 WHERE fname = 'Jill';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0
mysql> SELECT * FROM employees WHERE fname = 'Jill';
+----+-------+-------+----------+---------------+
| id | fname | lname | store_id | department_id |
+----+-------+-------+----------+---------------+
| 11 | Jill | Stone | 1 | 4 |
+----+-------+-------+----------+---------------+
1 row in set (0.00 sec)
mysql> UPDATE employees PARTITION (p2)
-> SET store_id = 2 WHERE fname = 'Jill';
Query OK, 1 row affected (0.09 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM employees WHERE fname = 'Jill';
+----+-------+-------+----------+---------------+
| id | fname | lname | store_id | department_id |
+----+-------+-------+----------+---------------+
| 11 | Jill | Stone | 2 | 4 |
+----+-------+-------+----------+---------------+
1 row in set (0.00 sec)
同样，当与DELETE一起使用PARTITION时，只有在分区列表中命名的分区中的行才会被检查删除。
对于插入行的语句，行为有所不同，找不到合适分区会导致语句失败。这对于INSERT和REPLACE语句都是适用的，如下所示：
mysql> INSERT INTO employees PARTITION (p2) VALUES (20, 'Jan', 'Jones', 1, 3);
ERROR 1729 (HY000): Found a row not matching the given partition set
mysql> INSERT INTO employees PARTITION (p3) VALUES (20, 'Jan', 'Jones', 1, 3);
Query OK, 1 row affected (0.07 sec)
mysql> REPLACE INTO employees PARTITION (p0) VALUES (20, 'Jan', 'Jones', 3, 2);
ERROR 1729 (HY000): Found a row not matching the given partition set
mysql> REPLACE INTO employees PARTITION (p3) VALUES (20, 'Jan', 'Jones', 3, 2);
Query OK, 2 rows affected (0.09 sec)
对于使用InnoDB存储引擎的分区表写入多行的语句：如果VALUES后面的列表中的任何行无法写入到partition_names列表中指定的分区之一，则整个语句将失败，不会写入任何行。这在下面的示例中展示了INSERT语句，重用了之前创建的employees表：
mysql> ALTER TABLE employees
-> REORGANIZE PARTITION p3 INTO (
-> PARTITION p3 VALUES LESS THAN (20),
-> PARTITION p4 VALUES LESS THAN (25),
-> PARTITION p5 VALUES LESS THAN MAXVALUE
-> );
Query OK, 6 rows affected (2.09 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> SHOW CREATE TABLE employees\G
*************************** 1\. row ***************************
Table: employees
Create Table: CREATE TABLE `employees` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`fname` varchar(25) NOT NULL,
`lname` varchar(25) NOT NULL,
`store_id` int(11) NOT NULL,
`department_id` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8mb4
/*!50100 PARTITION BY RANGE (id)
(PARTITION p0 VALUES LESS THAN (5) ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN (10) ENGINE = InnoDB,
PARTITION p2 VALUES LESS THAN (15) ENGINE = InnoDB,
PARTITION p3 VALUES LESS THAN (20) ENGINE = InnoDB,
PARTITION p4 VALUES LESS THAN (25) ENGINE = InnoDB,
PARTITION p5 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ 1 row in set (0.00 sec)
mysql> INSERT INTO employees PARTITION (p3, p4) VALUES
-> (24, 'Tim', 'Greene', 3, 1), (26, 'Linda', 'Mills', 2, 1);
ERROR 1729 (HY000): Found a row not matching the given partition set
mysql> INSERT INTO employees PARTITION (p3, p4, p5) VALUES
-> (24, 'Tim', 'Greene', 3, 1), (26, 'Linda', 'Mills', 2, 1);
Query OK, 2 rows affected (0.06 sec)
Records: 2 Duplicates: 0 Warnings: 0
对于写入多行的INSERT语句和REPLACE语句，上述内容都是适用的。
对于使用提供自动分区的存储引擎（如NDB）的表，分区选择被禁用。
26.6 关于分区的限制和限制
原文：
dev.mysql.com/doc/refman/8.0/en/partitioning-limitations.html
26.6.1 分区键、主键和唯一键
26.6.2 与存储引擎相关的分区限制
26.6.3 与函数相关的分区限制
本节讨论了 MySQL 分区支持的当前限制和限制。
禁止的结构。 不允许在分区表达式中使用以下结构：
-
存储过程、存储函数、可加载函数或插件。
-
声明变量或用户变量。
有关允许在分区表达式中使用的 SQL 函数列表，请参见 第 26.6.3 节，“与函数相关的分区限制”。
算术和逻辑运算符。 在分区表达式中允许使用算术运算符 +, -, 和 *。然而，结果必须是整数值或 NULL（除了 [LINEAR] KEY 分区，如本章其他地方讨论的那样；有关更多信息，请参见 第 26.2 节，“分区类型”）。
DIV 运算符也受支持；不允许使用 / 运算符。
位运算符 |, &, ^, <<, >>, 和 ~ 不允许在分区表达式中使用。
服务器 SQL 模式。 使用自定义分区的表不会保留创建时有效的 SQL 模式。如本手册其他地方所述（请参见 第 7.1.11 节，“服务器 SQL 模式”），许多 MySQL 函数和运算符的结果可能根据服务器 SQL 模式而变化。因此，在创建分区表后任何时候更改 SQL 模式可能导致这些表行为发生重大变化，并可能轻易导致数据损坏或丢失。因此，强烈建议您在创建分区表后永远不要更改服务器 SQL 模式。
对于服务器 SQL 模式中的一种更改使分区表无法使用的情况，请考虑以下 CREATE TABLE 语句，只有在 NO_UNSIGNED_SUBTRACTION 模式生效时才能成功执行：
mysql> SELECT @@sql_mode;
+------------+
| @@sql_mode |
+------------+
| |
+------------+
1 row in set (0.00 sec)
mysql> CREATE TABLE tu (c1 BIGINT UNSIGNED)
-> PARTITION BY RANGE(c1 - 10) (
-> PARTITION p0 VALUES LESS THAN (-5),
-> PARTITION p1 VALUES LESS THAN (0),
-> PARTITION p2 VALUES LESS THAN (5),
-> PARTITION p3 VALUES LESS THAN (10),
-> PARTITION p4 VALUES LESS THAN (MAXVALUE)
-> );
ERROR 1563 (HY000): Partition constant is out of partition function domain
mysql> SET sql_mode='NO_UNSIGNED_SUBTRACTION';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @@sql_mode;
+-------------------------+
| @@sql_mode |
+-------------------------+
| NO_UNSIGNED_SUBTRACTION |
+-------------------------+
1 row in set (0.00 sec)
mysql> CREATE TABLE tu (c1 BIGINT UNSIGNED)
-> PARTITION BY RANGE(c1 - 10) (
-> PARTITION p0 VALUES LESS THAN (-5),
-> PARTITION p1 VALUES LESS THAN (0),
-> PARTITION p2 VALUES LESS THAN (5),
-> PARTITION p3 VALUES LESS THAN (10),
-> PARTITION p4 VALUES LESS THAN (MAXVALUE)
-> );
Query OK, 0 rows affected (0.05 sec)
如果在创建 tu 后移除 NO_UNSIGNED_SUBTRACTION 服务器 SQL 模式，则可能无法再访问此表：
mysql> SET sql_mode='';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM tu;
ERROR 1563 (HY000): Partition constant is out of partition function domain
mysql> INSERT INTO tu VALUES (20);
ERROR 1563 (HY000): Partition constant is out of partition function domain
另请参阅 第 7.1.11 节，“服务器 SQL 模式”。
服务器 SQL 模式也会影响分区表的复制。源和副本上不同的 SQL 模式可能导致分区表达式的评估不同；这可能导致数据在源和副本的给定表的副本之间分布不同，并且甚至可能导致在源上成功的分区表插入在副本上失败。为获得最佳结果，您应始终在源和副本上使用相同的服务器 SQL 模式。
性能考虑。 分区操作对性能的一些影响如下：
-
文件系统操作。 分区和重新分区操作（例如
ALTER TABLE使用PARTITION BY ...,REORGANIZE PARTITION, 或REMOVE PARTITIONING）的实现取决于文件系统操作。这意味着这些操作的速度受到诸如文件系统类型和特性、磁盘速度、交换空间、操作系统的文件处理效率以及与文件处理相关的 MySQL 服务器选项和变量的影响。特别是，您应确保large_files_support已启用，并且open_files_limit已正确设置。涉及InnoDB表的分区和重新分区操作可能通过启用innodb_file_per_table变得更加高效。另请参阅 最大分区数。
-
表锁。 通常，对表执行分区操作的进程会对表进行写锁定。对这些表的读取相对不受影响；挂起的
INSERT和UPDATE操作将在分区操作完成后立即执行。有关InnoDB特定的例外情况，请参阅 分区操作。 -
索引；分区修剪。 与非分区表一样，正确使用索引可以显着加快对分区表的查询速度。此外，设计分区表和对这些表的查询以利用分区修剪可以显著提高性能。有关更多信息，请参阅第 26.4 节，“分区修剪”。
支持对分区表进行索引条件下推。请参阅第 10.2.1.6 节，“索引条件下推优化”。
-
使用 LOAD DATA 的性能。 在 MySQL 8.0 中，
LOAD DATA使用缓冲区来提高性能。您应该知道，为了实现这一点，每个分区的缓冲区使用 130 KB 内存。
最大分区数。 对于不使用NDB存储引擎的给定表，最大可能的分区数为 8192。此数字包括子分区。
对于使用NDB存储引擎的表的最大可能的用户定义分区数根据使用的 NDB Cluster 软件版本、数据节点数量和其他因素确定。有关更多信息，请参阅 NDB 和用户定义分区。
如果在创建具有大量分区的表（但少于最大值）时遇到错误消息，例如从存储引擎获取错误...：打开文件时资源不足，您可以通过增加open_files_limit系统变量的值来解决此问题。但是，这取决于操作系统，并且在所有平台上可能不可行或不可取；有关更多信息，请参阅第 B.3.2.16 节，“文件未找到和类似错误”。在某些情况下，由于其他原因，使用大量（数百个）分区也可能不可取，因此使用更多分区并不会自动导致更好的结果。
另请参阅文件系统操作。
不支持对分区 InnoDB 表使用外键。 使用InnoDB存储引擎的分区表不支持外键。更具体地说，这意味着以下两个语句是正确的：
-
不得包含外键引用的
InnoDB表的定义使用用户定义的分区；包含外键引用的InnoDB表的定义不得分区。 -
任何
InnoDB表定义都不能包含对用户分区表的外键引用；任何具有用户定义分区的InnoDB表都不能包含被外键引用的列。
刚刚列出的限制范围包括所有使用InnoDB存储引擎的表。不允许创建CREATE TABLE和ALTER TABLE语句导致违反这些限制的表。
ALTER TABLE ... ORDER BY。 对分区表运行的ALTER TABLE ... ORDER BY *column*语句仅导致每个分区内的行排序。
ADD COLUMN ... ALGORITHM=INSTANT。 一旦在分区表上执行ALTER TABLE ... ADD COLUMN ... ALGORITHM=INSTANT，就不再可能与该表交换分区。
通过修改主键对 REPLACE 语句的影响。 在某些情况下可能是可取的（参见第 26.6.1 节，“分区键、主键和唯一键”），修改表的主键。请注意，如果您的应用程序使用REPLACE语句并且您这样做，这些语句的结果可能会发生 drastical 改变。有关更多信息和示例，请参见第 15.2.12 节，“REPLACE 语句”。
全文索引。 分区表不支持FULLTEXT索引或搜索。
空间列。 具有空间数据类型（如POINT或GEOMETRY）的列不能在分区表中使用。
临时表。 临时表不能被分区。
日志表。 无法对日志表进行分区；对这样的表运行ALTER TABLE ... PARTITION BY ...语句会失败并显示错误。
分区键的数据类型。 分区键必须是整数列或解析为整数的表达式。不能使用包含ENUM列的表达式。列或表达式的值也可以是NULL；请参见第 26.2.7 节，“MySQL 分区如何处理 NULL”。
这个限制有两个例外：
-
当通过[
LINEAR]KEY进行分区时，可以使用除TEXT或BLOB之外的任何有效的 MySQL 数据类型作为分区键，因为内部键哈希函数会从这些类型中生成正确的数据类型。例如，以下两个CREATE TABLE语句是有效的：CREATE TABLE tkc (c1 CHAR) PARTITION BY KEY(c1) PARTITIONS 4; CREATE TABLE tke ( c1 ENUM('red', 'orange', 'yellow', 'green', 'blue', 'indigo', 'violet') ) PARTITION BY LINEAR KEY(c1) PARTITIONS 6; -
当使用
RANGE COLUMNS或LIST COLUMNS进行分区时，可以使用字符串、DATE和DATETIME列。例如，以下每个CREATE TABLE语句都是有效的：CREATE TABLE rc (c1 INT, c2 DATE) PARTITION BY RANGE COLUMNS(c2) ( PARTITION p0 VALUES LESS THAN('1990-01-01'), PARTITION p1 VALUES LESS THAN('1995-01-01'), PARTITION p2 VALUES LESS THAN('2000-01-01'), PARTITION p3 VALUES LESS THAN('2005-01-01'), PARTITION p4 VALUES LESS THAN(MAXVALUE) ); CREATE TABLE lc (c1 INT, c2 CHAR(1)) PARTITION BY LIST COLUMNS(c2) ( PARTITION p0 VALUES IN('a', 'd', 'g', 'j', 'm', 'p', 's', 'v', 'y'), PARTITION p1 VALUES IN('b', 'e', 'h', 'k', 'n', 'q', 't', 'w', 'z'), PARTITION p2 VALUES IN('c', 'f', 'i', 'l', 'o', 'r', 'u', 'x', NULL) );
前述两个例外情况均不适用于BLOB或TEXT列类型。
子查询。 分区键不能是子查询，即使该子查询解析为整数值或NULL。
不支持列索引前缀用于键分区。 在创建按键分区的表时，分区键中使用列前缀的任何列都不会用于表的分区函数。考虑以下CREATE TABLE语句，其中有三个VARCHAR列，主键使用了所有三列，并为其中两列指定了前缀：
CREATE TABLE t1 (
a VARCHAR(10000),
b VARCHAR(25),
c VARCHAR(10),
PRIMARY KEY (a(10), b, c(2))
) PARTITION BY KEY() PARTITIONS 2;
此语句被接受，但实际创建的表实际上是如同您发出了以下语句一样创建的，仅使用了不包含前缀的主键列（列b）作为分区键：
CREATE TABLE t1 (
a VARCHAR(10000),
b VARCHAR(25),
c VARCHAR(10),
PRIMARY KEY (a(10), b, c(2))
) PARTITION BY KEY(b) PARTITIONS 2;
在 MySQL 8.0.21 之前，没有发出警告或提供任何其他指示表明发生了这种情况，除非所有指定为分区键的列都使用了前缀，此时语句会失败，但会显示一个误导性的错误消息，如下所示：
mysql> CREATE TABLE t2 (
-> a VARCHAR(10000),
-> b VARCHAR(25),
-> c VARCHAR(10),
-> PRIMARY KEY (a(10), b(5), c(2))
-> ) PARTITION BY KEY() PARTITIONS 2;
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the
table's partitioning function
当执行ALTER TABLE或升级此类表时也会发生这种情况。
从 MySQL 8.0.21 开始，不再支持这种宽松行为（并且可能在将来的 MySQL 版本中删除）。从 MySQL 8.0.21 开始，使用一个或多个具有前缀的列作为分区键会对每个这样的列产生警告，如下所示：
mysql> CREATE TABLE t1 (
-> a VARCHAR(10000),
-> b VARCHAR(25),
-> c VARCHAR(10),
-> PRIMARY KEY (a(10), b, c(2))
-> ) PARTITION BY KEY() PARTITIONS 2;
Query OK, 0 rows affected, 2 warnings (1.25 sec)
mysql> SHOW WARNINGS\G
*************************** 1\. row ***************************
Level: Warning
Code: 1681
Message: Column 'test.t1.a' having prefix key part 'a(10)' is ignored by the
partitioning function. Use of prefixed columns in the PARTITION BY KEY() clause
is deprecated and will be removed in a future release.
*************************** 2\. row ***************************
Level: Warning
Code: 1681
Message: Column 'test.t1.c' having prefix key part 'c(2)' is ignored by the
partitioning function. Use of prefixed columns in the PARTITION BY KEY() clause
is deprecated and will be removed in a future release. 2 rows in set (0.00 sec)
这包括分区函数中使用的列被隐式定义为通过使用空的PARTITION BY KEY()子句来定义表的主键中的列的情况。
在 MySQL 8.0.21 及更高版本中，如果分区键指定的所有列都使用了前缀，则使用的CREATE TABLE语句将因错误消息而失败，该消息正确标识了问题：
mysql> CREATE TABLE t1 (
-> a VARCHAR(10000),
-> b VARCHAR(25),
-> c VARCHAR(10),
-> PRIMARY KEY (a(10), b(5), c(2))
-> ) PARTITION BY KEY() PARTITIONS 2;
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's
partitioning function (prefixed columns are not considered).
有关按键分区表的一般信息，请参见第 26.2.5 节，“键分区”。
子分区存在问题。 子分区必须使用HASH或KEY分区。只有RANGE和LIST分区可以进行子分区；HASH和KEY分区不能进行子分区。
SUBPARTITION BY KEY要求明确指定子分区列或列，不同于PARTITION BY KEY的情况，后者可以省略（在这种情况下，默认使用表的主键列）。考虑通过此语句创建的表：
CREATE TABLE ts (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(30)
);
您可以通过类似于以下语句创建一个具有相同列、按KEY分区的表：
CREATE TABLE ts (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(30)
)
PARTITION BY KEY()
PARTITIONS 4;
前面的语句被视为如下所示编写，其中表的主键列用作分区列：
CREATE TABLE ts (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(30)
)
PARTITION BY KEY(id)
PARTITIONS 4;
然而，尝试使用默认列作为子分区列创建子分区表的以下语句将失败，必须为语句指定列才能成功，如下所示：
mysql> CREATE TABLE ts (
-> id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> name VARCHAR(30)
-> )
-> PARTITION BY RANGE(id)
-> SUBPARTITION BY KEY()
-> SUBPARTITIONS 4
-> (
-> PARTITION p0 VALUES LESS THAN (100),
-> PARTITION p1 VALUES LESS THAN (MAXVALUE)
-> );
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near ')
mysql> CREATE TABLE ts (
-> id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> name VARCHAR(30)
-> )
-> PARTITION BY RANGE(id)
-> SUBPARTITION BY KEY(id)
-> SUBPARTITIONS 4
-> (
-> PARTITION p0 VALUES LESS THAN (100),
-> PARTITION p1 VALUES LESS THAN (MAXVALUE)
-> );
Query OK, 0 rows affected (0.07 sec)
这是一个已知问题（参见 Bug #51470）。
数据目录和索引目录选项。 忽略表级DATA DIRECTORY和INDEX DIRECTORY选项（参见 Bug #32091）。您可以为InnoDB表的各个分区或子分区使用这些选项。截至 MySQL 8.0.21，DATA DIRECTORY子句中指定的目录必须为InnoDB所知。有关更多信息，请参见使用 DATA DIRECTORY 子句。
修复和重建分区表。 CHECK TABLE、OPTIMIZE TABLE、ANALYZE TABLE和REPAIR TABLE语句支持分区表。
此外，您可以使用ALTER TABLE ... REBUILD PARTITION来重建分区表的一个或多个分区；ALTER TABLE ... REORGANIZE PARTITION也会导致分区重建。有关这两个语句的更多信息，请参见第 15.1.9 节，“ALTER TABLE Statement”。
ANALYZE、CHECK、OPTIMIZE、REPAIR和TRUNCATE操作支持子分区。请参见第 15.1.9.1 节，“ALTER TABLE Partition Operations”。
用于分区和子分区的文件名分隔符。 表分区和子分区文件名包括生成的分隔符，如#P#和#SP#。此类分隔符的大小写可能有所不同，不应依赖于此。
26.6.1 分区键、主键和唯一键
原文：
dev.mysql.com/doc/refman/8.0/en/partitioning-limitations-partitioning-keys-unique-keys.html
本节讨论了分区键与主键和唯一键的关系。规定这种关系的规则可以表述如下：用于分区表的分区表达式中的所有列必须是表可能具有的每个唯一键的一部分。
换句话说，表上的每个唯一键都必须使用表的分区表达式中的每一列。（这也包括表的主键，因为根据定义，它是一个唯一键。这种特殊情况稍后在本节中讨论。）例如，以下每个表创建语句都是无效的：
CREATE TABLE t1 (
col1 INT NOT NULL,
col2 DATE NOT NULL,
col3 INT NOT NULL,
col4 INT NOT NULL,
UNIQUE KEY (col1, col2)
)
PARTITION BY HASH(col3)
PARTITIONS 4;
CREATE TABLE t2 (
col1 INT NOT NULL,
col2 DATE NOT NULL,
col3 INT NOT NULL,
col4 INT NOT NULL,
UNIQUE KEY (col1),
UNIQUE KEY (col3)
)
PARTITION BY HASH(col1 + col3)
PARTITIONS 4;
在每种情况下，建议的表至少应该有一个不包括分区表达式中使用的所有列的唯一键。
每个以下语句都是有效的，并代表了相应的无效表创建语句可以正常工作的一种方式：
CREATE TABLE t1 (
col1 INT NOT NULL,
col2 DATE NOT NULL,
col3 INT NOT NULL,
col4 INT NOT NULL,
UNIQUE KEY (col1, col2, col3)
)
PARTITION BY HASH(col3)
PARTITIONS 4;
CREATE TABLE t2 (
col1 INT NOT NULL,
col2 DATE NOT NULL,
col3 INT NOT NULL,
col4 INT NOT NULL,
UNIQUE KEY (col1, col3)
)
PARTITION BY HASH(col1 + col3)
PARTITIONS 4;
这个例子展示了这种情况下产生的错误：
mysql> CREATE TABLE t3 (
-> col1 INT NOT NULL,
-> col2 DATE NOT NULL,
-> col3 INT NOT NULL,
-> col4 INT NOT NULL,
-> UNIQUE KEY (col1, col2),
-> UNIQUE KEY (col3)
-> )
-> PARTITION BY HASH(col1 + col3)
-> PARTITIONS 4;
ERROR 1491 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function
CREATE TABLE 语句失败，因为col1和col3都包含在建议的分区键中，但这两列都不是表上的两个唯一键的一部分。这显示了无效表定义的一种可能修复方法：
mysql> CREATE TABLE t3 (
-> col1 INT NOT NULL,
-> col2 DATE NOT NULL,
-> col3 INT NOT NULL,
-> col4 INT NOT NULL,
-> UNIQUE KEY (col1, col2, col3),
-> UNIQUE KEY (col3)
-> )
-> PARTITION BY HASH(col3)
-> PARTITIONS 4;
Query OK, 0 rows affected (0.05 sec)
在这种情况下，建议的分区键col3是两个唯一键的一部分，表创建语句成功。
以下表根本无法分区，因为无法在分区键中包含任何同时属于唯一键的列：
CREATE TABLE t4 (
col1 INT NOT NULL,
col2 INT NOT NULL,
col3 INT NOT NULL,
col4 INT NOT NULL,
UNIQUE KEY (col1, col3),
UNIQUE KEY (col2, col4)
);
由于每个主键根据定义都是唯一键，如果表有主键，这个限制也包括表的主键。例如，下面的两个语句是无效的：
CREATE TABLE t5 (
col1 INT NOT NULL,
col2 DATE NOT NULL,
col3 INT NOT NULL,
col4 INT NOT NULL,
PRIMARY KEY(col1, col2)
)
PARTITION BY HASH(col3)
PARTITIONS 4;
CREATE TABLE t6 (
col1 INT NOT NULL,
col2 DATE NOT NULL,
col3 INT NOT NULL,
col4 INT NOT NULL,
PRIMARY KEY(col1, col3),
UNIQUE KEY(col2)
)
PARTITION BY HASH( YEAR(col2) )
PARTITIONS 4;
在这两种情况下，主键都不包括分区表达式中引用的所有列。然而，下面的两个语句都是有效的：
CREATE TABLE t7 (
col1 INT NOT NULL,
col2 DATE NOT NULL,
col3 INT NOT NULL,
col4 INT NOT NULL,
PRIMARY KEY(col1, col2)
)
PARTITION BY HASH(col1 + YEAR(col2))
PARTITIONS 4;
CREATE TABLE t8 (
col1 INT NOT NULL,
col2 DATE NOT NULL,
col3 INT NOT NULL,
col4 INT NOT NULL,
PRIMARY KEY(col1, col2, col4),
UNIQUE KEY(col2, col1)
)
PARTITION BY HASH(col1 + YEAR(col2))
PARTITIONS 4;
如果一个表没有唯一键——这包括没有主键——那么这个限制就不适用，你可以使用分区表达式中的任何列或多列，只要列的类型与分区类型兼容。
出于同样的原因，除非该键包含表的分区表达式中使用的所有列，否则您不能随后向分区表添加唯一键。考虑在此处创建的分区表：
mysql> CREATE TABLE t_no_pk (c1 INT, c2 INT)
-> PARTITION BY RANGE(c1) (
-> PARTITION p0 VALUES LESS THAN (10),
-> PARTITION p1 VALUES LESS THAN (20),
-> PARTITION p2 VALUES LESS THAN (30),
-> PARTITION p3 VALUES LESS THAN (40)
-> );
Query OK, 0 rows affected (0.12 sec)
可以使用以下任一ALTER TABLE语句向t_no_pk添加主键：
# possible PK
mysql> ALTER TABLE t_no_pk ADD PRIMARY KEY(c1);
Query OK, 0 rows affected (0.13 sec)
Records: 0 Duplicates: 0 Warnings: 0
# drop this PK
mysql> ALTER TABLE t_no_pk DROP PRIMARY KEY;
Query OK, 0 rows affected (0.10 sec)
Records: 0 Duplicates: 0 Warnings: 0
# use another possible PK
mysql> ALTER TABLE t_no_pk ADD PRIMARY KEY(c1, c2);
Query OK, 0 rows affected (0.12 sec)
Records: 0 Duplicates: 0 Warnings: 0
# drop this PK
mysql> ALTER TABLE t_no_pk DROP PRIMARY KEY;
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
然而，下一个语句失败，因为c1是分区键的一部分，但不是建议的主键的一部分：
# fails with error 1503
mysql> ALTER TABLE t_no_pk ADD PRIMARY KEY(c2);
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function
由于t_no_pk只有c1在其分区表达式中，尝试仅在c2上添加唯一键将失败。但是，您可以添加一个使用c1和c2的唯一键。
这些规则也适用于您希望使用ALTER TABLE ... PARTITION BY对现有的非分区表进行分区的情况。考虑一个如下所示创建的表np_pk：
mysql> CREATE TABLE np_pk (
-> id INT NOT NULL AUTO_INCREMENT,
-> name VARCHAR(50),
-> added DATE,
-> PRIMARY KEY (id)
-> );
Query OK, 0 rows affected (0.08 sec)
以下ALTER TABLE语句将因为added列不是表中任何唯一键的一部分而失败：
mysql> ALTER TABLE np_pk
-> PARTITION BY HASH( TO_DAYS(added) )
-> PARTITIONS 4;
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function
然而，使用id列作为分区列的语句是有效的，如下所示：
mysql> ALTER TABLE np_pk
-> PARTITION BY HASH(id)
-> PARTITIONS 4;
Query OK, 0 rows affected (0.11 sec)
Records: 0 Duplicates: 0 Warnings: 0
对于np_pk，唯一可以用作分区表达式的列是id；如果您希望使用分区表达式中的任何其他列对该表进行分区，您必须首先修改表，可以通过将所需的列添加到主键中或完全删除主键。
26.6.2 关于存储引擎的分区限制
译文链接：
dev.mysql.com/doc/refman/8.0/en/partitioning-limitations-storage-engines.html
在 MySQL 8.0 中，分区支持实际上并非由 MySQL Server 提供，而是由表存储引擎自身或本地分区处理程序提供。在 MySQL 8.0 中，只有InnoDB和NDB存储引擎提供本地分区处理程序。这意味着分区表不能使用除这些之外的任何其他存储引擎来创建。（您必须使用带有NDB存储引擎的 MySQL NDB Cluster 来创建NDB表。）
InnoDB 存储引擎。 InnoDB外键和 MySQL 分区不兼容。分区的InnoDB表不能具有外键引用，也不能具有被外键引用的列。具有或被外键引用的InnoDB表不能进行分区。
对于使用InnoDB的分区表，ALTER TABLE ... OPTIMIZE PARTITION无法正确工作。对于这些表，请改用ALTER TABLE ... REBUILD PARTITION和ALTER TABLE ... ANALYZE PARTITION。有关更多信息，请参见第 15.1.9.1 节，“ALTER TABLE Partition Operations”。
用户定义的分区和 NDB 存储引擎（NDB Cluster）。 KEY（包括LINEAR KEY）分区是唯一支持的NDB存储引擎的分区类型。在 NDB Cluster 中，通常情况下不可能使用除[LINEAR] KEY之外的任何分区类型来创建 NDB Cluster 表，尝试这样做会导致错误。
异常（不适用于生产）：可以通过在 NDB Cluster SQL 节点上设置new系统变量为ON来覆盖此限制。如果您选择这样做，您应该意识到不支持使用除[LINEAR] KEY之外的其他分区类型的表进行生产。在这种情况下，您可以创建和使用除KEY或LINEAR KEY之外的其他分区类型的表，但这完全是自担风险的。您还应该意识到，此功能现已被弃用，并可能在将来的 NDB Cluster 版本中不经通知地被移除。
可以为NDB 表定义的最大分区数取决于集群中的数据节点和节点组数量、正在使用的 NDB Cluster 软件版本以及其他因素。有关更多信息，请参见 NDB 和用户定义的分区。
在NDB表中每个分区中可以存储的固定大小数据的最大量为 128 TB。以前，这个值是 16 GB。
会导致用户分区的NDB 表不满足以下两个要求之一或两者的CREATE TABLE和ALTER TABLE语句不被允许，并且会因错误而失败：
-
表必须具有显式主键。
-
表的分区表达式中列出的所有列必须是主键的一部分。
异常。 如果使用空列列表（即使用PARTITION BY KEY()或PARTITION BY LINEAR KEY()）创建用户分区的NDB 表，则不需要显式主键。
分区选择。 NDB 表不支持分区选择。有关更多信息，请参见第 26.5 节“分区选择”。
升级分区表。 在执行升级时，使用KEY进行分区的表必须进行转储和重新加载。使用除InnoDB之外的存储引擎进行分区的表无法从 MySQL 5.7 或更早版本升级到 MySQL 8.0 或更高版本；您必须在升级之前使用ALTER TABLE ... REMOVE PARTITIONING删除这些表的分区，或者使用ALTER TABLE ... ENGINE=INNODB将其转换为InnoDB。
有关将MyISAM表转换为InnoDB的信息，请参见第 17.6.1.5 节“从 MyISAM 转换表到 InnoDB”。
26.6.3 与函数相关的分区限制
原文：
dev.mysql.com/doc/refman/8.0/en/partitioning-limitations-functions.html
这一部分讨论了 MySQL 分区中与分区表达式中使用的函数相关的限制。
只有以下列出的 MySQL 函数允许在分区表达式中使用：
-
ABS() -
CEILING()（参见 CEILING() 和 FLOOR() and FLOOR()")) -
DATEDIFF() -
DAY() -
DAYOFMONTH() -
DAYOFWEEK() -
DAYOFYEAR() -
EXTRACT()（参见 带有 WEEK 参数的 EXTRACT() 函数 function with WEEK specifier")） -
FLOOR()（参见 CEILING() 和 FLOOR() and FLOOR()")） -
HOUR() -
MICROSECOND() -
MINUTE() -
MOD() -
MONTH() -
QUARTER() -
SECOND() -
TIME_TO_SEC() -
TO_DAYS() -
TO_SECONDS() -
UNIX_TIMESTAMP()（带有TIMESTAMP列） -
WEEKDAY() -
YEAR() -
YEARWEEK()
在 MySQL 8.0 中，支持对 TO_DAYS()、TO_SECONDS()、YEAR() 和 UNIX_TIMESTAMP() 函数进行分区修剪。更多信息请参见 第 26.4 节，“分区修剪”。
CEILING()和 FLOOR()。 如果这些函数的参数是精确数值类型，如INT类型或DECIMAL之一，则每个函数仅返回整数。这意味着，例如，以下CREATE TABLE语句将因错误而失败，如下所示：
mysql> CREATE TABLE t (c FLOAT) PARTITION BY LIST( FLOOR(c) )(
-> PARTITION p0 VALUES IN (1,3,5),
-> PARTITION p1 VALUES IN (2,4,6)
-> );
ERROR 1490 (HY000): The PARTITION function returns the wrong type
带有 WEEK 指定符的 EXTRACT()函数。 当作为EXTRACT(WEEK FROM *col*)使用时，EXTRACT()函数返回的值取决于default_week_format系统变量的值。因此，当指定单位为WEEK时，EXTRACT()不允许作为分区函数。（Bug #54483）
查看第 14.6.2 节，“数学函数”，了解这些函数的返回类型的更多信息，以及第 13.1 节，“数值数据类型”。
第二十七章 存储对象
目录
27.1 定义存储程序
27.2 使用存储过程
27.2.1 存储过程语法
27.2.2 存储过程和 MySQL 权限
27.2.3 存储过程元数据
27.2.4 存储过程、函数、触发器和 LAST_INSERT_ID()
27.3 使用触发器
27.3.1 触发器语法和示例
27.3.2 触发器元数据
27.4 使用事件调度程序
27.4.1 事件调度程序概述
27.4.2 事件调度程序配置
27.4.3 事件语法
27.4.4 事件元数据
27.4.5 事件调度程序状态
27.4.6 事件调度程序和 MySQL 权限
27.5 使用视图
27.5.1 视图语法
27.5.2 视图处理算法
27.5.3 可更新和可插入视图
27.5.4 视图 WITH CHECK OPTION 子句
27.5.5 视图元数据
27.6 存储对象访问控制
27.7 存储程序二进制日志记录
27.8 存储程序限制
27.9 视图限制
本章讨论了以 SQL 代码形式定义并存储在服务器上以供以后执行的存储数据库对象。
存储对象包括以下对象类型：
-
存储过程：使用
CREATE PROCEDURE创建的对象，并使用CALL语句调用。存储过程没有返回值，但可以修改其参数以供调用者稍后检查。它还可以生成结果集以返回给客户端程序。 -
存储函数：使用
CREATE FUNCTION创建的对象，类似于内置函数。您可以在表达式中调用它，并在表达式评估期间返回一个值。 -
触发器：使用
CREATE TRIGGER创建的与表关联的对象。当表发生特定事件时（如插入或更新），触发器被激活。 -
事件：使用
CREATE EVENT创建的对象，并根据计划由服务器调用。 -
视图：使用
CREATE VIEW创建的对象，当引用时产生一个结果集。视图充当虚拟表。
本文档中使用的术语反映了存储对象层次结构：
-
存储程序包括存储过程和函数。
-
存储程序包括存储过程、触发器和事件。
-
存储对象包括存储程序和视图。
本章描述了如何使用存储对象。以下各节提供了有关与这些对象相关的语句的 SQL 语法的附加信息，以及有关对象处理的信息：
-
对于每种对象类型，都有
CREATE、ALTER和DROP语句来控制对象的存在和定义方式。参见第 15.1 节，“数据定义语句”。 -
CALL语句用于调用存储过程。参见第 15.2.1 节，“CALL 语句”。 -
存储程序定义包括一个主体，可以使用复合语句、循环、条件语句和声明变量。参见第 15.6 节，“复合语句语法”。
-
存储程序引用的对象的元数据更改会被检测到，并在下次执行程序时导致受影响语句的自动重新解析。有关更多信息，请参见第 10.10.3 节，“准备语句和存储程序的缓存”。
27.1 定义存储程序
原文：
dev.mysql.com/doc/refman/8.0/en/stored-programs-defining.html
每个存储程序包含一个由 SQL 语句组成的主体。该语句可以是由分号(;)字符分隔的多个语句组成的复合语句。例如，以下存储过程具有由BEGIN ... END块组成的主体，其中包含一个SET语句和一个包含另一个SET语句的REPEAT循环：
CREATE PROCEDURE dorepeat(p1 INT)
BEGIN
SET @x = 0;
REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
END;
如果您使用mysql客户端程序定义包含分号字符的存储程序，则会出现问题。默认情况下，mysql本身将分号识别为语句分隔符，因此您必须临时重新定义分隔符，以使mysql将整个存储程序定义传递给服务器。
要重新定义mysql的分隔符，请使用delimiter命令。以下示例展示了如何为刚刚显示的dorepeat()过程执行此操作。将分隔符更改为//以便将整个定义作为单个语句传递给服务器，然后在调用过程之前将其恢复为;。这样可以使过程体中使用的;分隔符传递到服务器，而不是被mysql本身解释。
mysql> delimiter //
mysql> CREATE PROCEDURE dorepeat(p1 INT)
-> BEGIN
-> SET @x = 0;
-> REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
-> END
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> CALL dorepeat(1000);
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @x;
+------+
| @x |
+------+
| 1001 |
+------+
1 row in set (0.00 sec)
您可以将分隔符重新定义为除//之外的其他字符串，并且分隔符可以由单个字符或多个字符组成。应避免使用反斜杠（\）字符，因为这是 MySQL 的转义字符。
以下是一个接受参数、使用 SQL 函数执行操作并返回结果的函数示例。在这种情况下，不需要使用delimiter，因为函数定义不包含内部的;语句分隔符：
mysql> CREATE FUNCTION hello (s CHAR(20))
mysql> RETURNS CHAR(50) DETERMINISTIC
-> RETURN CONCAT('Hello, ',s,'!');
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT hello('world');
+----------------+
| hello('world') |
+----------------+
| Hello, world! |
+----------------+
1 row in set (0.00 sec)
27.2 使用存储过程
27.2.1 存储过程语法
27.2.2 存储过程和 MySQL 权限
27.2.3 存储过程元数据
27.2.4 存储过程、函数、触发器和 LAST_INSERT_ID()
MySQL 支持存储过程（过程和函数）。存储过程是一组可以存储在服务器中的 SQL 语句。一旦完成这个过程，客户端就不需要不断重新发出各个语句，而是可以引用存储过程。
在某些情况下，存储过程可能特别有用：
-
当多个客户端应用程序使用不同语言编写或在不同平台上运行，但需要执行相同的数据库操作时。
-
当安全性至关重要时。例如，银行使用存储过程和函数进行所有常见操作。这提供了一个一致且安全的环境，而例程可以确保每个操作都被正确记录。在这样的设置中，应用程序和用户无法直接访问数据库表，而只能执行特定的存储过程。
存储过程可以提供更好的性能，因为服务器和客户端之间需要传输的信息更少。这样做的代价是这会增加数据库服务器的负载，因为更多的工作是在服务器端完成的，而在客户端（应用程序）端完成的工作较少。如果许多客户机（如 Web 服务器）由一个或几个数据库服务器提供服务，请考虑这一点。
存储过程还使您能够在数据库服务器中拥有函数库。这是现代应用程序语言共享的功能，这些语言内部允许这样的设计（例如，通过使用类）。即使在数据库使用范围之外，利用这些客户端应用程序语言功能对程序员也是有益的。
MySQL 遵循 SQL:2003 的存储过程语法，这也是 IBM 的 DB2 所使用的。这里描述的所有语法都受支持，任何限制和扩展都在适当的地方有文档记录。
其他资源
-
在处理存储过程和函数时，您可能会发现存储过程用户论坛很有用。
-
有关 MySQL 中存储过程的一些常见问题的答案，请参阅附录 A.4，“MySQL 8.0 FAQ：存储过程和函数”。
-
对于存储过程的使用有一些限制。请参阅第 27.8 节，“存储程序的限制”。
-
存储过程的二进制日志记录过程如第 27.7 节，“存储程序二进制日志记录”中所述。
27.2.1 存储过程语法
原文：
dev.mysql.com/doc/refman/8.0/en/stored-routines-syntax.html
存储过程可以是过程或函数。存储过程是使用CREATE PROCEDURE和CREATE FUNCTION语句创建的（参见 Section 15.1.17, “CREATE PROCEDURE and CREATE FUNCTION Statements”）。通过CALL语句调用过程（参见 Section 15.2.1, “CALL Statement”），只能使用输出变量传回值。函数可以像其他函数一样从语句内部调用（即通过调用函数的名称），并且可以返回一个标量值。存储过程的主体可以使用复合语句（参见 Section 15.6, “Compound Statement Syntax”）。
存储过程可以使用DROP PROCEDURE和DROP FUNCTION语句删除（参见 Section 15.1.29, “DROP PROCEDURE and DROP FUNCTION Statements”），并且可以使用ALTER PROCEDURE和ALTER FUNCTION语句进行修改（参见 Section 15.1.7, “ALTER PROCEDURE Statement”）。
存储过程或函数与特定数据库相关联。这有几个含义：
-
当调用例程时，会执行隐式的
USE *db_name*（在例程终止时撤消）。存储过程内部不允许使用USE语句。 -
您可以使用数据库名称限定例程名称。这可用于引用不在当前数据库中的例程。例如，要调用与
test数据库关联的存储过程p或函数f，可以使用CALL test.p()或test.f()。 -
当一个数据库被删除时，与之相关的所有存储过程也会被删除。
存储函数不能是递归的。
存储过程中允许递归，但默认情况下被禁用。要启用递归，请将max_sp_recursion_depth服务器系统变量设置为大于零的值。存储过程递归会增加线程堆栈空间的需求。如果增加max_sp_recursion_depth的值，可能需要通过增加服务器启动时thread_stack的值来增加线程堆栈大小。有关更多信息，请参见第 7.1.8 节，“服务器系统变量”。
MySQL 支持一个非常有用的扩展，允许在存储过程中使用常规的SELECT语句（即，不使用游标或本地变量）。这种查询的结果集直接发送到客户端。多个SELECT语句会生成多个结果集，因此客户端必须使用支持多个结果集的 MySQL 客户端库。这意味着客户端必须使用至少与 MySQL 4.1 版本一样新的客户端库。客户端在连接时还应指定CLIENT_MULTI_RESULTS选项。对于 C 程序，可以使用mysql_real_connect() C API 函数来实现。请参见 mysql_real_connect()，以及多语句执行支持。
在 MySQL 8.0.22 及更高版本中，存储过程中的语句引用的用户变量在存储过程首次调用时确定其类型，并在此后的每次调用存储过程时保留此类型。
27.2.2 存储例程和 MySQL 权限
原文：
dev.mysql.com/doc/refman/8.0/en/stored-routines-privileges.html
MySQL 授权系统如下考虑存储例程：
-
需要
CREATE ROUTINE权限来创建存储例程。 -
需要
ALTER ROUTINE权限来修改或删除存储例程。如果需要，此权限将自动授予例程的创建者，并在删除例程时从创建者那里删除。 -
执行存储例程需要
EXECUTE权限。但是，如果需要，此权限将自动授予例程的创建者（在删除例程时从创建者那里删除）。此外，例程的默认SQL SECURITY特性为DEFINER，这使得具有与例程关联的数据库访问权限的用户可以执行该例程。 -
如果
automatic_sp_privileges系统变量为 0，则不会自动授予和删除例程创建者的EXECUTE和ALTER ROUTINE权限。 -
例程的创建者是用于执行其
CREATE语句的帐户。这可能与例程定义中命名为DEFINER的帐户不同。 -
例程
DEFINER命名的帐户可以查看所有例程属性，包括其定义。因此，该帐户完全可以访问由以下产生的例程输出：-
信息模式
ROUTINES表的内容。 -
SHOW CREATE FUNCTION和SHOW CREATE PROCEDURE语句。 -
SHOW FUNCTION CODE和SHOW PROCEDURE CODE语句。 -
SHOW FUNCTION STATUS和SHOW PROCEDURE STATUS语句。
-
-
对于非例程
DEFINER命名的帐户，访问例程属性取决于授予帐户的权限：-
使用
SHOW_ROUTINE权限或全局SELECT权限，帐户可以查看所有例程属性，包括其定义。 -
通过在包含例程的范围内授予
CREATE ROUTINE、ALTER ROUTINE或EXECUTE权限，帐户可以查看所有例程属性，除了其定义。
-
27.2.3 存储过程元数据
原文：
dev.mysql.com/doc/refman/8.0/en/stored-routines-metadata.html
获取存储过程的元数据：
-
查询
INFORMATION_SCHEMA数据库的ROUTINES表。参见 Section 28.3.30, “The INFORMATION_SCHEMA ROUTINES Table”。 -
使用
SHOW CREATE PROCEDURE和SHOW CREATE FUNCTION语句查看过程定义。参见 Section 15.7.7.9, “SHOW CREATE PROCEDURE Statement”。 -
使用
SHOW PROCEDURE STATUS和SHOW FUNCTION STATUS语句查看过程特征。参见 Section 15.7.7.28, “SHOW PROCEDURE STATUS Statement”。 -
使用
SHOW PROCEDURE CODE和SHOW FUNCTION CODE语句查看过程的内部实现表示。参见 Section 15.7.7.27, “SHOW PROCEDURE CODE Statement”。
27.2.4 存储过程、函数、触发器和 LAST_INSERT_ID()
原文：
dev.mysql.com/doc/refman/8.0/en/stored-routines-last-insert-id.html
在存储过程（procedure 或 function）或触发器的主体内，LAST_INSERT_ID()的值会像在这些对象的主体外执行语句时一样发生变化（参见第 14.15 节，“信息函数”）。存储过程或触发器对LAST_INSERT_ID()值的影响取决于存储过程的类型：
-
如果存储过程执行更改
LAST_INSERT_ID()值的语句，则后续语句会看到更改后的值。 -
对于改变值的存储函数和触发器，在函数或触发器结束时，值会恢复，因此后续语句不会看到更改后的值。
27.3 使用触发器
27.3.1 触发器语法和示例
27.3.2 触发器元数据
触发器是与表关联的命名数据库对象，当表发生特定事件时激活。触发器的一些用途包括对要插入表中的值执行检查，或对参与更新的值执行计算。
当语句在关联表中插入、更新或删除行时，触发器被定义为激活。这些行操作是触发器事件。例如，行可以通过 INSERT 或 LOAD DATA 语句插入，每插入一行触发器就会激活一次。触发器可以设置为在触发事件之前或之后激活。例如，您可以在将每行插入到表中之前或之后激活触发器。
重要提示
MySQL 触发器仅在通过 SQL 语句对表进行更改时激活。这包括对底层可更新视图的基本表进行的更改。触发器不会在通过不向 MySQL 服务器传输 SQL 语句的 API 对表进行的更改时激活。这意味着触发器不会被使用 NDB API 进行的更新所激活。
触发器不会因 INFORMATION_SCHEMA 或 performance_schema 表的更改而激活。这些表实际上是视图，视图上不允许触发器。
以下各节描述了创建和删除触发器的语法，展示了如何使用它们的一些示例，并指示如何获取触发器元数据。
其他资源
-
在处理触发器时，您可能会发现 MySQL 用户论坛 有所帮助。
-
有关 MySQL 中触发器常见问题的答案，请参阅 第 A.5 节 “MySQL 8.0 FAQ: 触发器”。
-
对触发器的使用有一些限制；请参阅 第 27.8 节 “存储程序限制”。
-
触发器的二进制日志记录如 第 27.7 节 “存储程序二进制日志记录” 中所述。
27.3.1 触发器语法和示例
要创建触发器或删除触发器，请使用CREATE TRIGGER或DROP TRIGGER语句，描述在第 15.1.22 节，“CREATE TRIGGER Statement”和第 15.1.34 节，“DROP TRIGGER Statement”中。
这里有一个简单的示例，将触发器与表关联起来，以激活INSERT操作。 触发器充当累加器，对表的某一列插入的值进行求和。
mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
Query OK, 0 rows affected (0.03 sec)
mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account
FOR EACH ROW SET @sum = @sum + NEW.amount;
Query OK, 0 rows affected (0.01 sec)
CREATE TRIGGER语句创建一个名为ins_sum的触发器，与account表关联。 它还包括指定触发器动作时间、触发事件以及触发器激活时要执行的操作的子句：
-
关键字
BEFORE表示触发器动作时间。 在这种情况下，触发器在每行插入到表之前激活。 这里允许的其他关键字是AFTER。 -
关键字
INSERT表示触发事件；也就是说，激活触发器的操作类型。 在示例中，INSERT操作会导致触发器激活。 您还可以为DELETE和UPDATE操作创建触发器。 -
FOR EACH ROW后面的语句定义了触发器体；也就是说，每次触发器激活时执行的语句，这发生在触发事件影响的每一行。 在示例中，触发器体是一个简单的SET，将插入到amount列中的值累积到用户变量中。 该语句将列称为NEW.amount，意思是“要插入新行的amount列的值”。
要使用触发器，请将累加器变量设置为零，执行一个INSERT语句，然后查看变量之后的值：
mysql> SET @sum = 0;
mysql> INSERT INTO account VALUES(137,14.98),(141,1937.50),(97,-100.00);
mysql> SELECT @sum AS 'Total amount inserted';
+-----------------------+
| Total amount inserted |
+-----------------------+
| 1852.48 |
+-----------------------+
在这种情况下，INSERT语句执行后@sum的值为14.98 + 1937.50 - 100，即1852.48。
要销毁触发器，请使用DROP TRIGGER语句。 如果触发器不在默认模式中，则必须指定模式名称：
mysql> DROP TRIGGER test.ins_sum;
如果删除表，则表的任何触发器也将被删除。
触发器名称存在于模式命名空间中，这意味着所有触发器在模式内必须具有唯一名称。 不同模式中的触发器可以具有相同的名称。
可以为给定表定义多个具有相同触发事件和操作时间的触发器。例如，您可以为表定义两个BEFORE UPDATE触发器。默认情况下，具有相同触发事件和操作时间的触发器按照它们创建的顺序激活。要影响触发器顺序，请在FOR EACH ROW之后指定一个子句，指示FOLLOWS或PRECEDES以及一个具有相同触发事件和操作时间的现有触发器的名称。使用FOLLOWS，新触发器在现有触发器之后激活。使用PRECEDES，新触发器在现有触发器之前激活。
例如，以下触发器定义为account表定义了另一个BEFORE INSERT触发器：
mysql> CREATE TRIGGER ins_transaction BEFORE INSERT ON account
FOR EACH ROW PRECEDES ins_sum
SET
@deposits = @deposits + IF(NEW.amount>0,NEW.amount,0),
@withdrawals = @withdrawals + IF(NEW.amount<0,-NEW.amount,0);
Query OK, 0 rows affected (0.01 sec)
此触发器ins_transaction类似于ins_sum，但分别累积存款和取款。它具有一个PRECEDES子句，使其在ins_sum之前激活；如果没有该子句，它将在ins_sum之后激活，因为它是在ins_sum之后创建的。
在触发器主体内，OLD和NEW关键字使您能够访问触发器影响的行中的列。OLD和NEW是 MySQL 触发器的扩展；它们不区分大小写。
在INSERT触发器中，只能使用NEW.*col_name*；没有旧行。在DELETE触发器中，只能使用OLD.*col_name*；没有新行。在UPDATE触发器中，您可以使用OLD.*col_name*来引用更新前行的列，使用NEW.*col_name*来引用更新后行的列。
以OLD命名的列是只读的。您可以引用它（如果您具有SELECT权限），但不能修改它。如果您对其具有SELECT权限，则可以引用以NEW命名的列。在BEFORE触发器中，如果您对其具有UPDATE权限，则还可以使用SET NEW.*col_name* = *value*来更改其值。这意味着您可以使用触发器修改要插入新行或用于更新行的值。（在AFTER触发器中，这样的SET语句没有效果，因为行更改已经发生。）
在BEFORE触发器中，AUTO_INCREMENT列的NEW值为 0，而不是在实际插入新行时自动生成的序列号。
通过使用BEGIN ... END结构，可以定义执行多个语句的触发器。在BEGIN块内，还可以使用其他在存储过程中允许的语法，如条件和循环。然而，就像对于存储过程一样，如果使用mysql程序定义执行多个语句的触发器，则需要重新定义mysql语句定界符，以便在触发器定义内使用;语句定界符。以下示例说明了这些要点。它定义了一个UPDATE触发器，检查要用于更新每行的新值，并修改值使其在 0 到 100 的范围内。这必须是BEFORE触发器，因为必须在使用该值更新行之前检查该值：
mysql> delimiter //
mysql> CREATE TRIGGER upd_check BEFORE UPDATE ON account
FOR EACH ROW
BEGIN
IF NEW.amount < 0 THEN
SET NEW.amount = 0;
ELSEIF NEW.amount > 100 THEN
SET NEW.amount = 100;
END IF;
END;//
mysql> delimiter ;
将存储过程单独定义，然后使用简单的CALL语句从触发器中调用它可能更容易。如果要从多个触发器内执行相同的代码，这也是有利的。
触发器执行时，触发器执行的语句中出现的内容有限制：
-
触发器不能使用
CALL语句调用返回数据给客户端或使用动态 SQL 的存储过程。（存储过程可以通过OUT或INOUT参数将数据返回给触发器。） -
触发器不能使用明确或隐式开始或结束事务的语句，例如
START TRANSACTION，COMMIT或ROLLBACK。(ROLLBACK to SAVEPOINT是允许的，因为它不会结束事务。)。
另请参阅 Section 27.8, “存储程序的限制”。
MySQL 在触发器执行期间处理错误如下：
-
如果
BEFORE触发器失败，则不会执行对应行的操作。 -
BEFORE触发器在尝试插入或修改行时被激活，无论尝试是否成功。 -
仅当任何
BEFORE触发器和行操作成功执行时，才会执行AFTER触发器。 -
BEFORE或AFTER触发器中的错误导致触发器调用的整个语句失败。 -
对于事务性表，语句失败应导致语句执行的所有更改回滚。触发器失败会导致语句失败，因此触发器失败也会导致回滚。对于非事务性表，无法进行此类回滚，因此尽管语句失败，但在错误点之前执行的任何更改仍然有效。
触发器可以通过名称直接引用表，例如此示例中显示的名为testref的触发器：
CREATE TABLE test1(a1 INT);
CREATE TABLE test2(a2 INT);
CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
CREATE TABLE test4(
a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
b4 INT DEFAULT 0
);
delimiter |
CREATE TRIGGER testref BEFORE INSERT ON test1
FOR EACH ROW
BEGIN
INSERT INTO test2 SET a2 = NEW.a1;
DELETE FROM test3 WHERE a3 = NEW.a1;
UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
END;
|
delimiter ;
INSERT INTO test3 (a3) VALUES
(NULL), (NULL), (NULL), (NULL), (NULL),
(NULL), (NULL), (NULL), (NULL), (NULL);
INSERT INTO test4 (a4) VALUES
(0), (0), (0), (0), (0), (0), (0), (0), (0), (0);
假设您将以下数值插入到表test1中，如下所示：
mysql> INSERT INTO test1 VALUES
(1), (3), (1), (7), (1), (8), (4), (4);
Query OK, 8 rows affected (0.01 sec)
Records: 8 Duplicates: 0 Warnings: 0
结果，四个表包含以下数据：
mysql> SELECT * FROM test1;
+------+
| a1 |
+------+
| 1 |
| 3 |
| 1 |
| 7 |
| 1 |
| 8 |
| 4 |
| 4 |
+------+
8 rows in set (0.00 sec)
mysql> SELECT * FROM test2;
+------+
| a2 |
+------+
| 1 |
| 3 |
| 1 |
| 7 |
| 1 |
| 8 |
| 4 |
| 4 |
+------+
8 rows in set (0.00 sec)
mysql> SELECT * FROM test3;
+----+
| a3 |
+----+
| 2 |
| 5 |
| 6 |
| 9 |
| 10 |
+----+
5 rows in set (0.00 sec)
mysql> SELECT * FROM test4;
+----+------+
| a4 | b4 |
+----+------+
| 1 | 3 |
| 2 | 0 |
| 3 | 1 |
| 4 | 2 |
| 5 | 0 |
| 6 | 0 |
| 7 | 1 |
| 8 | 1 |
| 9 | 0 |
| 10 | 0 |
+----+------+
10 rows in set (0.00 sec)
27.3.2 触发器元数据
要获取有关触发器的元数据：
-
查询
INFORMATION_SCHEMA数据库的TRIGGERS表。参见 Section 28.3.45, “The INFORMATION_SCHEMA TRIGGERS Table”。 -
使用
SHOW CREATE TRIGGER语句。参见 Section 15.7.7.11, “SHOW CREATE TRIGGER Statement”。 -
使用
SHOW TRIGGERS语句。参见 Section 15.7.7.40, “SHOW TRIGGERS Statement”。
27.4 使用事件调度器
27.4.1 事件调度器概述
27.4.2 事件调度器配置
27.4.3 事件语法
27.4.4 事件元数据
27.4.5 事件调度器状态
27.4.6 事件调度器和 MySQL 权限
MySQL 事件调度器管理事件的调度和执行，即按照时间表运行的任务。以下讨论涵盖了事件调度器，并分为以下几个部分：
-
第 27.4.1 节，“事件调度器概述”，介绍了 MySQL 事件的简介和概念概述。
-
第 27.4.3 节，“事件语法”，讨论了用于创建、修改和删除 MySQL 事件的 SQL 语句。
-
第 27.4.4 节，“事件元数据”，展示了如何获取有关事件的信息以及 MySQL 服务器如何存储这些信息。
-
第 27.4.6 节，“事件调度器和 MySQL 权限”，讨论了处理事件所需的权限以及在执行时事件对权限的影响。
存储过程需要mysql系统数据库中的events数据字典表。此表在 MySQL 8.0 安装过程中创建。如果您从早期版本升级到 MySQL 8.0，请确保执行升级过程，以确保您的系统数据库是最新的。请参阅第三章，升级 MySQL。
其他资源
-
对事件的使用有一些限制；请参阅第 27.8 节，“存储程序的限制”。
-
事件的二进制日志记录如第 27.7 节，“存储程序的二进制日志记录”所述进行。
-
您可能还会发现MySQL 用户论坛很有帮助。
27.4.1 事件调度程序概述
MySQL 事件是按照时间表运行的任务。因此，我们有时将它们称为定时事件。当您创建一个事件时，实际上是创建了一个命名的数据库对象，其中包含一个或多个要在一个或多个常规间隔执行的 SQL 语句，开始和结束于特定日期和时间。从概念上讲，这类似于 Unix 的crontab（也称为“cron job”）或 Windows 任务计划程序的概念。
这种类型的定时任务有时也被称为“时间触发器”，暗示这些是由时间流逝触发的对象。虽然这基本上是正确的，但我们更倾向于使用术语事件，以避免与第 27.3 节“使用触发器”中讨论的触发器混淆。事件更具体地不应与“临时触发器”混淆。触发器是一个数据库对象，其语句在发生在给定表上的特定类型事件时执行，而（定时）事件是一个对象，其语句在经过指定的时间间隔后执行。
虽然 SQL 标准中没有关于事件调度的规定，但其他数据库系统中有先例，您可能会注意到这些实现与 MySQL 服务器中的实现之间的一些相似之处。
MySQL 事件具有以下主要特性和属性：
-
在 MySQL 中，事件通过其名称和分配给它的模式唯一标识。
-
事件根据时间表执行特定操作。此操作包括一个 SQL 语句，如果需要，可以是一个
BEGIN ... END块中的复合语句（请参阅第 15.6 节“复合语句语法”）。事件的时间可以是一次性的或循环的。一次性事件仅执行一次。循环事件在常规间隔重复其操作，并且循环事件的时间表可以分配一个特定的开始日期和时间，结束日期和时间，两者都有，或两者都没有。（默认情况下，循环事件的时间表在创建时立即开始，并持续无限期，直到被禁用或删除。）如果重复事件在其调度间隔内没有终止，则可能会导致多个事件实例同时执行。如果这是不希望的，您应该实施一种机制来防止同时实例。例如，您可以使用
GET_LOCK()函数，或行或表锁定。 -
用户可以使用专为此目的设计的 SQL 语句创建、修改和删除计划事件。语法无效的事件创建和修改语句将失败，并显示适当的错误消息。用户可能在事件的操作中包含需要用户实际上没有的权限的语句。事件创建或修改语句成功，但事件的操作失败。有关详细信息，请参见 第 27.4.6 节，“事件调度程序和 MySQL 权限”。
-
许多事件的属性可以使用 SQL 语句进行设置或修改。这些属性包括事件的名称、定时、持久性（即在其计划过期后是否保留）、状态（启用或禁用）、要执行的操作以及分配给它的模式。参见 第 15.1.3 节，“ALTER EVENT 语句”。
事件的默认定义者是创建事件的用户，除非事件已被修改，在这种情况下，定义者是发出影响该事件的最后一个
ALTER EVENT语句的用户。任何具有对定义事件的数据库上的EVENT权限的用户都可以修改事件。有关详细信息，请参见 第 27.4.6 节，“事件调度程序和 MySQL 权限”。 -
事件的操作语句可以包含大多数存储过程中允许的 SQL 语句。有关限制，请参见 第 27.8 节，“存储程序的限制”。
27.4.2 事件调度器配置
原文：
dev.mysql.com/doc/refman/8.0/en/events-configuration.html
事件由一个特殊的事件调度器线程执行；当我们提到事件调度器时，实际上是指这个线程。当运行时，具有PROCESS权限的用户可以在SHOW PROCESSLIST的输出中看到事件调度器线程及其当前状态，如下面的讨论所示。
全局event_scheduler系统变量确定事件调度器在服务器上是否启用和运行。它具有以下值之一，这些值会影响事件调度的描述：
-
ON: 事件调度器已启动；事件调度器线程运行并执行所有预定事件。ON是默认的event_scheduler值。当事件调度器为
ON时，事件调度器线程将作为守护进程在SHOW PROCESSLIST的输出中列出，并且其状态如下所示：mysql> SHOW PROCESSLIST\G *************************** 1\. row *************************** Id: 1 User: root Host: localhost db: NULL Command: Query Time: 0 State: NULL Info: show processlist *************************** 2\. row *************************** Id: 2 User: event_scheduler Host: localhost db: NULL Command: Daemon Time: 3 State: Waiting for next activation Info: NULL 2 rows in set (0.00 sec)通过将
event_scheduler的值设置为OFF来停止事件调度。 -
OFF: 事件调度器已停止。事件调度器线程不运行，不会显示在SHOW PROCESSLIST的输出中，也不会执行任何预定事件。当事件调度器停止时（
event_scheduler为OFF），可以通过将event_scheduler的值设置为ON来启动它。（见下一项。） -
DISABLED: 此值使事件调度器无法运行。当事件调度器为DISABLED时，事件调度器线程不运行（因此不会出现在SHOW PROCESSLIST的输出中）。此外，事件调度器状���无法在运行时更改。
如果事件调度器状态未设置为DISABLED，则可以在event_scheduler的值之间切换为ON和OFF（使用SET）。在设置此变量时，也可以使用0表示OFF，使用1表示ON。因此，在mysql客户端中可以使用以下任何一条语句来启动事件调度器：
SET GLOBAL event_scheduler = ON;
SET @@GLOBAL.event_scheduler = ON;
SET GLOBAL event_scheduler = 1;
SET @@GLOBAL.event_scheduler = 1;
类似地，可以使用以下任何一条语句关闭事件调度器：
SET GLOBAL event_scheduler = OFF;
SET @@GLOBAL.event_scheduler = OFF;
SET GLOBAL event_scheduler = 0;
SET @@GLOBAL.event_scheduler = 0;
注意
如果事件调度器已启用，则启用super_read_only系统变量会阻止其在events数据字典表中更新事件“上次执行”时间戳。这会导致事件调度器在下次尝试执行计划事件时停止，并在写入服务器错误日志后发出消息。（在这种情况下，event_scheduler系统变量不会从ON更改为OFF。一个含义是，此变量拒绝了 DBA 意图，即事件调度器启用或禁用，其实际状态为启动或停止可能是不同的。）如果在启用后随后禁用super_read_only，服务器会根据需要自动重新启动事件调度器，截至 MySQL 8.0.26。在 MySQL 8.0.26 之前，需要手动重新启动事件调度器以再次启用它。
尽管ON和OFF有数值等价物，但由SELECT或SHOW VARIABLES显示的event_scheduler的值始终为OFF、ON或DISABLED。DISABLED没有数值等价物。因此，在设置此变量时，通常优先选择ON和OFF而不是1和0。
请注意，尝试设置event_scheduler而不将其指定为全局变量会导致错误：
mysql< SET @@event_scheduler = OFF;
ERROR 1229 (HY000): Variable 'event_scheduler' is a GLOBAL
variable and should be set with SET GLOBAL
重要
只能在服务器启动时将事件调度器设置为DISABLED。如果event_scheduler为ON或OFF，则无法在运行时将其设置为DISABLED。此外，如果事件调度器在启动时设置为DISABLED，则无法在运行时更改event_scheduler的值。
要禁用事件调度器，请使用以下两种方法之一：
-
作为启动服务器时的命令行选项：
--event-scheduler=DISABLED -
在服务器配置文件（
my.cnf，或 Windows 系统上的my.ini）中，包含可以被服务器读取的行（例如，在[mysqld]部分）：event_scheduler=DISABLED
要启用事件调度器，请在重新启动服务器时不使用--event-scheduler=DISABLED命令行选项，或在服务器配置文件中删除或注释包含event-scheduler=DISABLED的行，或者在适当的情况下。另外，当启动服务器时，您可以使用ON（或1）或OFF（或0）来代替DISABLED值。
注意
当event_scheduler设置为DISABLED时，可以发出事件操作语句。在这种情况下不会生成警告或错误（前提是语句本身有效）。但是，在将此变量设置为ON（或1）之前，计划事件无法执行。一旦完成此操作，事件调度程序线程将执行所有满足调度条件的事件。
使用--skip-grant-tables选项启动 MySQL 服务器会将event_scheduler设置为DISABLED，覆盖在命令行或my.cnf或my.ini文件中设置的任何其他值（Bug #26807）。
有关用于创建、修改和删除事件的 SQL 语句，请参见第 27.4.3 节，“事件语法”。
MySQL 在INFORMATION_SCHEMA数据库中提供了一个EVENTS表。可以查询此表以获取关于服务器上已定义的计划事件的信息。更多信息请参见第 27.4.4 节，“事件元数据”和第 28.3.14 节，“INFORMATION_SCHEMA EVENTS 表”。
有关事件调度和 MySQL 权限系统的信息，请参见第 27.4.6 节，“事件调度程序和 MySQL 权限”。
27.4.3 事件语法
MySQL 提供了几个用于处理计划事件的 SQL 语句：
-
使用
CREATE EVENT语句定义新事件。参见 Section 15.1.13, “CREATE EVENT Statement”。 -
通过
ALTER EVENT语句可以更改现有事件的定义。参见 Section 15.1.3, “ALTER EVENT Statement”。 -
当不再需要或不再需要计划事件时，可以由其定义者使用
DROP EVENT语句从服务器中删除。参见 Section 15.1.25, “DROP EVENT Statement”。事件是否在其计划结束后继续存在还取决于其ON COMPLETION子句，如果有的话。参见 Section 15.1.13, “CREATE EVENT Statement”。任何具有数据库上定义事件的
EVENT权限的用户都可以删除事件。参见 Section 27.4.6, “The Event Scheduler and MySQL Privileges”。
27.4.4 事件元数据
要获取有关事件的元数据：
-
查询
INFORMATION_SCHEMA数据库的EVENTS表。参见 Section 28.3.14, “The INFORMATION_SCHEMA EVENTS Table”。 -
使用
SHOW CREATE EVENT语句。参见 Section 15.7.7.7, “SHOW CREATE EVENT Statement”。 -
使用
SHOW EVENTS语句。参见 Section 15.7.7.18, “SHOW EVENTS Statement”。
事件调度器时间表示
MySQL 中的每个会话都有一个会话时区（STZ）。这是会话time_zone值，当会话开始时从服务器的全局time_zone值初始化，但在会话期间可能会更改。
当CREATE EVENT或ALTER EVENT语句执行时，使用当前会话时区来解释事件定义中指定的时间。这成为事件时区（ETZ）；也就是用于事件调度并在事件执行时生效的时区。
为了在数据字典中表示事件信息，execute_at、starts和ends时间被转换为 UTC 并与事件时区一起存储。这使得事件执行可以按照定义进行，而不受服务器时区或夏令时效果的影响。last_executed时间也以 UTC 存储。
事件时间可以通过从信息模式EVENTS表或SHOW EVENTS中选择来获取，但它们以 ETZ 或 STZ 值报告。以下表总结了事件时间的表示。
| 值 | EVENTS 表 |
SHOW EVENTS |
|---|---|---|
| 执行时间 | ETZ | ETZ |
| 开始时间 | ETZ | ETZ |
| 结束时间 | ETZ | ETZ |
| 上次执行时间 | ETZ | n/a |
| 创建时间 | STZ | n/a |
| 上次修改时间 | STZ | n/a |
27.4.5 事件调度程序状态
事件调度程序会将执行过程中出现错误或警告的事件信息写入 MySQL 服务器的错误日志。参见第 27.4.6 节，“事件调度程序和 MySQL 权限”中的示例。
为了获取有关事件调度程序状态的信息，以进行调试和故障排除，运行mysqladmin debug（参见第 6.5.2 节，“mysqladmin — 一个 MySQL 服务器管理程序”）；运行此命令后，服务器的错误日志将包含与事件调度程序相关的输出，类似于这里显示的内容：
Events status:
LLA = Last Locked At LUA = Last Unlocked At
WOC = Waiting On Condition DL = Data Locked
Event scheduler status:
State : INITIALIZED
Thread id : 0
LLA : n/a:0
LUA : n/a:0
WOC : NO
Workers : 0
Executed : 0
Data locked: NO
Event queue status:
Element count : 0
Data locked : NO
Attempting lock : NO
LLA : init_queue:95
LUA : init_queue:103
WOC : NO
Next activation : never
在事件调度程序执行的语句中，诊断消息（不仅限于错误，还包括警告）会被写入错误日志，并且在 Windows 上也会写入应用程序事件日志。对于频繁执行的事件，可能会导致许多日志消息。例如，对于SELECT ... INTO *var_list*语句，如果查询没有返回任何行，会出现带有错误代码 1329 的警告（没有数据），并且变量值保持不变。如果查询返回多行，会出现错误 1172（结果包含多于一行）。对于任一条件，您可以通过声明条件处理程序来避免警告被记录；参见第 15.6.7.2 节，“DECLARE ... HANDLER 语句”。对于可能检索多行的语句，另一种策略是使用LIMIT 1将结果集限制为单行。
27.4.6 事件调度程序和 MySQL 权限
要启用或禁用计划事件的执行，需要设置全局event_scheduler系统变量的值。这需要足够的权限来设置全局系统变量。参见第 7.1.9.1 节，“系统变量权限”。
EVENT权限管理事件的创建、修改和删除。可以使用GRANT授予此权限。例如，此GRANT语句将在用户jon@ghidora上为名为myschema的模式授予EVENT权限：
GRANT EVENT ON myschema.* TO jon@ghidora;
（我们假设该用户帐户已经存在，并且我们希望其保持不变。）
要授予同一用户在所有模式上的EVENT权限，请使用以下语句：
GRANT EVENT ON *.* TO jon@ghidora;
EVENT权限具有全局或模式级别范围。因此，尝试在单个表上授予它会导致错误，如下所示：
mysql> GRANT EVENT ON myschema.mytable TO jon@ghidora;
ERROR 1144 (42000): Illegal GRANT/REVOKE command; please
consult the manual to see which privileges can be used
重要的是要理解事件是以其定义者的权限执行的，并且它不能执行其定义者没有所需权限的任何操作。例如，假设jon@ghidora对myschema具有EVENT权限。还假设该用户对myschema具有SELECT权限，但对该模式没有其他权限。jon@ghidora可以创建一个新事件，如下所示：
CREATE EVENT e_store_ts
ON SCHEDULE
EVERY 10 SECOND
DO
INSERT INTO myschema.mytable VALUES (UNIX_TIMESTAMP());
用户等待一分钟左右，然后执行SELECT * FROM mytable;查询，期望在表中看到几行新数据。然而，表是空的。由于用户没有表的INSERT权限，因此事件没有效果。
如果检查 MySQL 错误日志（*hostname*.err），您会看到事件正在执行，但它尝试执行的操作失败：
2013-09-24T12:41:31.261992Z 25 [ERROR] Event Scheduler:
[jon@ghidora][cookbook.e_store_ts] INSERT command denied to user
'jon'@'ghidora' for table 'mytable'
2013-09-24T12:41:31.262022Z 25 [Note] Event Scheduler:
[jon@ghidora].[myschema.e_store_ts] event execution failed.
2013-09-24T12:41:41.271796Z 26 [ERROR] Event Scheduler:
[jon@ghidora][cookbook.e_store_ts] INSERT command denied to user
'jon'@'ghidora' for table 'mytable'
2013-09-24T12:41:41.272761Z 26 [Note] Event Scheduler:
[jon@ghidora].[myschema.e_store_ts] event execution failed.
由于这个用户很可能无法访问错误日志，可以通过直接执行事件的操作语句来验证该事件是否有效：
mysql> INSERT INTO myschema.mytable VALUES (UNIX_TIMESTAMP());
ERROR 1142 (42000): INSERT command denied to user
'jon'@'ghidora' for table 'mytable'
检查信息模式EVENTS表显示e_store_ts存在且已启用，但其LAST_EXECUTED列为NULL：
mysql> SELECT * FROM INFORMATION_SCHEMA.EVENTS
> WHERE EVENT_NAME='e_store_ts'
> AND EVENT_SCHEMA='myschema'\G
*************************** 1\. row ***************************
EVENT_CATALOG: NULL
EVENT_SCHEMA: myschema
EVENT_NAME: e_store_ts
DEFINER: jon@ghidora
EVENT_BODY: SQL
EVENT_DEFINITION: INSERT INTO myschema.mytable VALUES (UNIX_TIMESTAMP())
EVENT_TYPE: RECURRING
EXECUTE_AT: NULL
INTERVAL_VALUE: 5
INTERVAL_FIELD: SECOND
SQL_MODE: NULL
STARTS: 0000-00-00 00:00:00
ENDS: 0000-00-00 00:00:00
STATUS: ENABLED
ON_COMPLETION: NOT PRESERVE
CREATED: 2006-02-09 22:36:06
LAST_ALTERED: 2006-02-09 22:36:06
LAST_EXECUTED: NULL
EVENT_COMMENT: 1 row in set (0.00 sec)
要撤销EVENT权限，请使用REVOKE语句。在此示例中，从jon@ghidora用户账户中移除了模式myschema上的EVENT权限：
REVOKE EVENT ON myschema.* FROM jon@ghidora;
重要提示
从用户那里撤销EVENT权限不会删除或禁用该用户可能创建的任何事件。
事件不会因为创建它的用户被重命名或删除而迁移或删除。
假设用户jon@ghidora已被授予myschema模式上的EVENT和INSERT权限。然后该用户创建了以下事件：
CREATE EVENT e_insert
ON SCHEDULE
EVERY 7 SECOND
DO
INSERT INTO myschema.mytable;
创建了这个事件后，root撤销了jon@ghidora的EVENT权限。然而，e_insert继续执行，每七秒插入一行到mytable中。如果root发出了以下任一语句，情况也是如此：
-
DROP USER jon@ghidora; -
RENAME USER jon@ghidora TO someotherguy@ghidora;
您可以通过在发出DROP USER或RENAME USER语句之前和之后检查信息模式EVENTS表来验证这一点。
事件定义存储在数据字典中。要删除由另一个用户账户创建的事件，您必须是 MySQL root用户或具有必要权限的其他用户。
用户的EVENT权限存储在mysql.user和mysql.db表的Event_priv列中。在这两种情况下，该列保存值'Y'或'N'中的一个。'N'是默认值。对于给定用户，只有当该用户具有全局EVENT权限（即，如果使用GRANT EVENT ON *.*授予了权限）时，mysql.user.Event_priv才设置为'Y'。对于模式级别的EVENT权限，GRANT在mysql.db中创建一行，并将该行的Db列设置为模式名称，User列设置为用户名称，Event_priv列设置为'Y'。永远不应该直接操作这些表，因为GRANT EVENT和REVOKE EVENT语句会在其上执行所需的操作。
五个状态变量提供了与事件相关操作的计数（但不包括事件执行的语句；请参阅第 27.8 节，“存储程序的限制”）。这些是：
-
Com_create_event: 自上次服务器重启以来执行的CREATE EVENT语句的次数。 -
Com_alter_event: 自上次服务器重启以来执行的ALTER EVENT语句的次数。 -
Com_drop_event: 自上次服务器重启以来执行的DROP EVENT语句的次数。 -
Com_show_create_event: 自上次服务器重启以来执行的SHOW CREATE EVENT语句的次数。 -
Com_show_events: 自上次服务器重启以来执行的SHOW EVENTS语句的次数。
通过运行语句SHOW STATUS LIKE '%event%';，您可以一次查看所有这些的当前值。
27.5 使用视图
27.5.1 视图语法
27.5.2 视图处理算法
27.5.3 可更新和可插入视图
27.5.4 带有 CHECK OPTION 子句的视图
27.5.5 视图元数据
MySQL 支持视图，包括可更新的视图。视图是存储的查询，当调用时产生一个结果集。视图充当虚拟表。
以下讨论描述了创建和删除视图的语法，并展示了如何使用它们的一些示例。
其他资源
-
在处理视图时，您可能会发现MySQL 用户论坛很有帮助。
-
关于 MySQL 中视图的一些常见问题的答案，请参见第 A.6 节，“MySQL 8.0 FAQ：视图”。
-
使用视图时有一些限制；参见第 27.9 节，“视图限制”。
27.5.1 视图语法
CREATE VIEW语句创建一个新视图（参见 Section 15.1.23, “CREATE VIEW Statement”）。要修改视图的定义或删除视图，请使用ALTER VIEW（参见 Section 15.1.11, “ALTER VIEW Statement”）或DROP VIEW（参见 Section 15.1.35, “DROP VIEW Statement”）。
视图可以从多种类型的SELECT语句创建。它可以引用基本表或其他视图。它可以使用连接、UNION和子查询。SELECT甚至不需要引用任何表。以下示例定义了一个视图，从另一个表中选择了两列，以及从这些列计算出的表达式：
mysql> CREATE TABLE t (qty INT, price INT);
mysql> INSERT INTO t VALUES(3, 50), (5, 60);
mysql> CREATE VIEW v AS SELECT qty, price, qty*price AS value FROM t;
mysql> SELECT * FROM v;
+------+-------+-------+
| qty | price | value |
+------+-------+-------+
| 3 | 50 | 150 |
| 5 | 60 | 300 |
+------+-------+-------+
mysql> SELECT * FROM v WHERE qty = 5;
+------+-------+-------+
| qty | price | value |
+------+-------+-------+
| 5 | 60 | 300 |
+------+-------+-------+
27.5.2 视图处理算法
CREATE VIEW 或 ALTER VIEW 的可选 ALGORITHM 子句是 MySQL 对标准 SQL 的扩展。它影响 MySQL 处理视图的方式。ALGORITHM 有三个值：MERGE、TEMPTABLE 或 UNDEFINED。
-
对于
MERGE，引用视图的语句文本和视图定义被合并，以便视图定义的部分替换语句的相应部分。 -
对于
TEMPTABLE，视图的结果被检索到一个临时表中，然后用于执行语句。 -
对于
UNDEFINED，MySQL 会选择使用哪种算法。如果可能的话，它会优先选择MERGE而不是TEMPTABLE，因为MERGE通常更有效率，而且如果使用临时表，则视图无法更新。 -
如果没有
ALGORITHM子句，则默认算法由optimizer_switch系统变量的derived_merge标志的值确定。有关更多讨论，请参见 Section 10.2.2.4, “Optimizing Derived Tables, View References, and Common Table Expressions with Merging or Materialization”。
明确指定 TEMPTABLE 的一个原因是，在创建临时表后并在用于完成语句处理之前，可以释放对基础表的锁。这可能导致比 MERGE 算法更快地释放锁，以便使用视图的其他客户端不会被阻塞太久。
视图算法可能为 UNDEFINED 有三个原因：
-
CREATE VIEW语句中没有ALGORITHM子句。 -
CREATE VIEW语句具有显式的ALGORITHM = UNDEFINED子句。 -
为只能使用临时表处理的视图指定了
ALGORITHM = MERGE。在这种情况下，MySQL 会生成警告并将算法设置为UNDEFINED。
如前所述，MERGE 是通过将视图定义的相应部分合并到引用该视图的语句中来处理的。以下示例简要说明了 MERGE 算法的工作原理。这些示例假设存在一个具有以下定义的视图 v_merge：
CREATE ALGORITHM = MERGE VIEW v_merge (vc1, vc2) AS
SELECT c1, c2 FROM t WHERE c3 > 100;
示例 1：假设我们发出以下语句：
SELECT * FROM v_merge;
MySQL 处理该语句如下：
-
v_merge变为t -
*变为vc1, vc2，对应于c1, c2 -
视图
WHERE子句被添加
要执行的结果语句变为：
SELECT c1, c2 FROM t WHERE c3 > 100;
示例 2：假设我们发出以下语句：
SELECT * FROM v_merge WHERE vc1 < 100;
这个语句的处理方式与前一个类似，只是vc1 < 100变成了c1 < 100，并且视图的WHERE子句被添加到语句的WHERE子句中，使用AND连接词（并且添加括号以确保子句的部分以正确的优先级执行）。最终要执行的语句变为：
SELECT c1, c2 FROM t WHERE (c3 > 100) AND (c1 < 100);
实际上，要执行的语句具有以下形式的WHERE子句：
WHERE (select WHERE) AND (view WHERE)
如果无法使用MERGE算法，则必须使用临时表。阻止合并的结构与阻止派生表和公共表达式合并的结构相同。例如，在子查询中使用SELECT DISTINCT或LIMIT。有关详细信息，请参见 Section 10.2.2.4，“使用合并或实体化优化派生表、视图引用和公共表达式”。
27.5.3 可更新和可插入视图
一些视图是可更新的，并且对它们的引用可以用于指定在数据更改语句中要更新的表。也就是说，您可以在UPDATE、DELETE或INSERT等语句中使用它们来更新基础表的内容。派生表和公共表达式也可以在多表UPDATE和DELETE语句中指定，但只能用于读取数据以指定要更新或删除的行。通常，视图引用必须是可更新的，这意味着它们可以合并而不是实体化。复合视图有更复杂的规则。
要使视图可更新，视图中的行与基础表中的行之间必须是一对一的关系。还有一些其他构造使视图不可更新。更具体地说，如果视图包含以下任何内容，则视图不可更新：
-
聚合函数或窗口函数（
SUM()、MIN()、MAX()、COUNT()等） -
DISTINCT -
GROUP BY -
HAVING -
UNION或UNION ALL -
在选择列表中的子查询
在选择列表中，非依赖子查询对于
INSERT失败，但对于UPDATE和DELETE可行。对于选择列表中的依赖子查询，不允许数据更改语句。 -
某些连接（请参阅本节后面的其他连接讨论）
-
在
FROM子句中引用不可更新的视图 -
在
FROM子句中引用WHERE子句中的子查询 -
仅引用文字值（在这种情况下，没有要更新的基础表）
-
ALGORITHM = TEMPTABLE（始终使用临时表会使视图不可更新） -
对基表的任何列的多次引用（对于
INSERT失败，对于UPDATE和DELETE可行）
视图中的生成列被视为可更新，因为可以对其进行赋值。但是，如果显式更新此类列，则唯一允许的值是DEFAULT。有关生成列的信息，请参见第 15.1.20.8 节，“CREATE TABLE and Generated Columns”。
有时，多表视图可能是可更新的，假设它可以使用MERGE算法处理。为使其工作，视图必须使用内连接（而不是外连接或UNION）。此外，视图定义中只能更新一个表，因此SET子句必须仅命名视图中一个表的列。即使理论上可更新，也不允许使用UNION ALL的视图。
就插入性（使用INSERT语句可更新）而言，如果可更新视图还满足视图列的以下附加要求，则可插入：
-
视图列名称不能重复。
-
视图必须包含基表中没有默认值的所有列。
-
视图列必须是简单的列引用。不能是表达式，比如这些：
3.14159 col1 + 3 UPPER(col2) col3 / col4 (*subquery*)
MySQL 在CREATE VIEW时设置一个称为视图可更新性标志的标志。如果视图对UPDATE和DELETE（以及类似操作）是合法的，则将该标志设置为YES（true）。否则，将该标志设置为NO（false）。信息模式VIEWS表中的IS_UPDATABLE列显示此标志的状态。这意味着服务器始终知道视图是否可更新。
如果视图不可更新，则UPDATE、DELETE和INSERT等语句是非法的并将被拒绝。（即使视图是可更新的，也可能无法插入，如本节其他地方所述。）
视图的可更新性可能会受到updatable_views_with_limit系统变量值的影响。请参阅第 7.1.8 节，“服务器系统变量”。
对于以下讨论，假设存在这些表和视图：
CREATE TABLE t1 (x INTEGER);
CREATE TABLE t2 (c INTEGER);
CREATE VIEW vmat AS SELECT SUM(x) AS s FROM t1;
CREATE VIEW vup AS SELECT * FROM t2;
CREATE VIEW vjoin AS SELECT * FROM vmat JOIN vup ON vmat.s=vup.c;
允许INSERT、UPDATE和DELETE语句如下：
-
INSERT：INSERT语句的插入表可以是一个合并的视图引用。如果视图是一个连接视图，则视图的所有组件必须是可更新的（非物化的）。对于多表可更新视图，如果插入到单个表，则INSERT可以工作。该语句无效，因为连接视图的一个组件是不可更新的：
INSERT INTO vjoin (c) VALUES (1);这个语句是有效的；视图不包含实体组件：
INSERT INTO vup (c) VALUES (1); -
UPDATE：在UPDATE语句中要更新的表或表可以是合并的视图引用。如果一个视图是连接视图，视图的至少一个组件必须是可更新的（这与INSERT不同）。在多表
UPDATE语句中，语句的更新表引用必须是基本表或可更新的视图引用。未更新的表引用可以是物化视图或派生表。这个语句是有效的；列
c来自连接视图的可更新部分：UPDATE vjoin SET c=c+1;这个语句是无效的；列
x来自不可更新的部分：UPDATE vjoin SET x=x+1;这个语句是有效的；多表
UPDATE的更新表引用是一个可更新的视图（vup）：UPDATE vup JOIN (SELECT SUM(x) AS s FROM t1) AS dt ON ... SET c=c+1;这个语句是无效的；它试图更新一个实体派生表：
UPDATE vup JOIN (SELECT SUM(x) AS s FROM t1) AS dt ON ... SET s=s+1; -
DELETE：在DELETE语句中要从中删除的表或表必须是合并视图。不允许连接视图（这与INSERT和UPDATE不同）。这个语句是无效的，因为视图是一个连接视图：
DELETE vjoin WHERE ...;这个语句是有效的，因为视图是一个合并的（可更新的）视图：
DELETE vup WHERE ...;这个语句是有效的，因为它从一个合并的（可更新的）视图中删除：
DELETE vup FROM vup JOIN (SELECT SUM(x) AS s FROM t1) AS dt ON ...;
附加讨论和示例如下。
本节早期讨论指出，如果视图不是所有列都是简单列引用（例如，如果包含表达式或复合表达式的列），则视图是不可插入的。尽管这样的视图不可插入，但如果只更新非表达式列，则可以更新。考虑这个视图：
CREATE VIEW v AS SELECT col1, 1 AS col2 FROM t;
这个视图不可插入，因为col2是一个表达式。但如果更新不尝试更新col2，则可以更新。这个更新是允许的：
UPDATE v SET col1 = 0;
此更新不允许，因为它试图更新一个表达式列：
UPDATE v SET col2 = 0;
如果表包含一个AUTO_INCREMENT列，在对不包括AUTO_INCREMENT列的表上插入可插入视图时，不会改变LAST_INSERT_ID()的值，因为插入默认值到视图中不是可见的列的副作用不应该可见。
27.5.4 视图 WITH CHECK OPTION 子句
可以为可更新视图提供WITH CHECK OPTION子句，以防止插入到select_statement中WHERE子句不为真的行。它还防止更新使WHERE子句为真但更新会导致其不为真的行（换句话说，它防止可见行被更新为不可见行）。
在可更新视图的WITH CHECK OPTION子句中，LOCAL和CASCADED关键字确定了在视图以另一个视图的形式定义时进行检查测试的范围。当没有给出关键字时，默认为CASCADED。
WITH CHECK OPTION测试符合标准：
-
使用
LOCAL，视图WHERE子句会被检查，然后检查递归到底层视图并应用相同的规则。 -
使用
CASCADED，视图WHERE子句会被检查，然后检查递归到底层视图，为它们添加WITH CASCADED CHECK OPTION（用于检查目的；它们的定义保持不变），并应用相同的规则。 -
没有检查选项时，视图
WHERE子句不会被检查，然后检查会递归到底层视图，并应用相同的规则。
考虑以下表和一组视图的定义：
CREATE TABLE t1 (a INT);
CREATE VIEW v1 AS SELECT * FROM t1 WHERE a < 2
WITH CHECK OPTION;
CREATE VIEW v2 AS SELECT * FROM v1 WHERE a > 0
WITH LOCAL CHECK OPTION;
CREATE VIEW v3 AS SELECT * FROM v1 WHERE a > 0
WITH CASCADED CHECK OPTION;
这里的v2和v3视图是以另一个视图v1的形式定义的。
对于v2的插入会根据其LOCAL检查选项进行检查，然后检查递归到v1并再次应用规则。v1的规则导致检查失败。v3的检查也失败：
mysql> INSERT INTO v2 VALUES (2);
ERROR 1369 (HY000): CHECK OPTION failed 'test.v2'
mysql> INSERT INTO v3 VALUES (2);
ERROR 1369 (HY000): CHECK OPTION failed 'test.v3'
