但行好事 莫问前程

存储程序的二进制日志
二进制日志包含修改数据库内容的SQL语句信息。这些信息以描述修改的“事件”的形式存储。二进制日志有两个重要的目的。
.对于复制，主复制服务器使用二进制日志记录要发送到从服务器的语句。主服务器将包含在其二进制日志中的事件发送给从服务器，从服务器 执行这些事件以进行与主服务器相同的数据更改。

.某些数据恢复操作需要使用二进制日志。恢复备份文件后，将重新执行二进制日志中记录的备份后的事件。这些事件使数据库从备份点更新到 最新状态。

但是，如果日志记录发生在语句级别，存储程序（存储过程和函数、触发器和事件）会出现某些二进制日志记录问题：
.在某些情况下，一条语句可能会影响主从端的不同行集。

.在从服务器上执行的复制语句由具有完全特权的从SQL线程处理。一个过程可能在主服务器和从服务器上遵循不同的执行路径，因此用户可以编 写包含危险语句的例程，该例程仅在由具有完全特权的线程处理的从服务器上执行

.如果修改数据的存储程序是不确定的，则它是不可重复的。这可能导致主服务器和从服务器上的数据不同，或者导致恢复的数据与原始数据不 同。

本节描述MySQL如何处理存储程序的二进制日志记录。它陈述了实现对存储程序使用的当前条件，以及您可以做些什么来避免日志记录问题。它 还提供了关于这些条件的原因的额外信息。
一般来说，当SQL语句级别发生二进制日志记录时，就会出现这里描述的问题。如果使用基于行的二进制日志，则日志包含由于执行SQL语句而对 单个行所做的更改。当例程或触发器执行时，记录行更改，而不是进行更改的语句。对于存储过程，这意味着不会记录CALL语句。对于存储函数 ，在函数内所做的行更改将被记录，而不是函数调用。对于触发器，触发器所做的行更改将被记录。在从端，只会看到行上的变化，而不会看到 对存储程序的调用。

除非另有说明，否则这里的注释假定您通过使用——log-bin选项启动服务器，从而启用了二进制日志记录。如果未启用二进制日志，则无法进 行复制，也无法使用二进制日志进行数据恢复。

在MySQL中使用存储函数的条件可以总结如下。这些条件不适用于存储过程或Event Scheduler事件，除非启用了二进制日志记录，否则这些条件 不适用。

.要创建或修改存储函数，除了具有create权限外，还必须具有SUPER权限通常需要的ROUTINE或ALTER ROUTINE权限。(根据函数定义中的DEFINER 值，无论是否启用二进制日志记录，都可能需要SUPER。）

.创建存储函数时，必须声明它是确定性的，或者声明它不修改数据。否则，可能导致数据恢复或复制不安全。

默认情况下，要接受一个CREATE FUNCTION语句，至少要满足DETERMINISTIC、NO SQL或READS SQL DATA必须显式指定。否则会出现错误：

ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL,
or READS SQL DATA in its declaration and binary logging is enabled
(you *might* want to use the less safe log_bin_trust_function_creators
variable)

这个函数是确定的（并且不修改数据），所以它是安全的：

CREATE FUNCTION f1(i INT)
RETURNS INT
DETERMINISTIC
READS SQL DATA
BEGIN
RETURN i;
END;

这个函数使用不确定的UUID()，所以这个函数也不确定，也不安全：

CREATE FUNCTION f2()
RETURNS CHAR(36) CHARACTER SET utf8
BEGIN
RETURN UUID();
END;

这个函数修改数据，所以它可能不安全：

CREATE FUNCTION f3(p_id INT)
RETURNS INT
BEGIN
UPDATE t SET modtime = NOW() WHERE id = p_id;
RETURN ROW_COUNT();
END;

对函数性质的评估是基于创建者的“诚实”：MySQL不会检查被声明为DETERMINISTIC性的函数是否没有产生不确定性结果的语句。

.虽然可以在不指定deterministic的情况下创建确定性存储函数，但不能使用基于语句的二进制日志记录执行该函数。要执行这样的函数，必须 使用基于行或混合二进制日志记录。另外，如果在函数定义中显式指定DETERMINISTIC，则可以使用任何类型的日志记录，包括基于语句的日志 记录。

.要放宽上述创建函数的条件（必须具有SUPER权限，并且必须声明函数是确定的或不能修改数据），请将全局 log_bin_trust_function_creators系统变量设置为1。默认情况下，这个变量的值为0，但你可以这样修改它：

mysql> SET GLOBAL log_bin_trust_function_creators = 1;

您也可以在启动服务器时使用–log-bin-trust-function-creators=1选项来设置这个变量。

.有关可能对复制不安全的内置函数（从而导致使用它们的存储函数也不安全）的信息，请参阅第16.4.1节，“复制功能和问题”。

触发器类似于存储函数，因此前面关于函数的注释也适用于触发器，但有以下例外：CREATE TRIGGER没有可选的DETERMINISTIC特征，因此假定 触发器始终是确定性的。然而，这种假设在某些情况下可能是无效的。例如，UUID（）函数是不确定的（并且不复制）。在触发器中使用这类函 数时要小心。

触发器可以更新表，因此，如果您没有所需的权限，则CREATE TRIGGER会出现与存储函数类似的错误消息。在从服务器端，从服务器使用触发器 DEFINER属性来确定哪个用户被认为是触发器的创建者。
本节的其余部分将提供有关日志记录实现及其含义的更多细节。除非您对存储例程使用中当前与日志相关的条件的基本原理的背景知识感兴趣， 否则您不需要阅读它。本讨论仅适用于基于语句的日志记录，而不适用于基于行的日志记录，但第一项除外：无论日志记录模式如何，CREATE和 DROP语句都作为语句记录。

.服务器写入CREATE EVENT, CREATE PROCEDURE, CREATE FUNCTION, ALTER EVENT, ALTER PROCEDURE, ALTER FUNCTION, DROP EVENT, DROP PROCEDURE,DROP FUNCTION语句到二进制日志中。

.如果函数更改了数据并且发生在语句中，那么存储函数调用将被记录为SELECT语句，否则不会被记录。这可以防止由于在非日志语句中使用存 储函数而导致的数据更改的非复制。例如，SELECT语句不会写入二进制日志，但是SELECT语句可能会调用进行更改的存储函数。为了处理这个问 题，当给定函数进行更改时，将SELECT func_name（）语句写入二进制日志。假设在主服务器上执行以下语句：

CREATE FUNCTION f1(a INT) RETURNS INT
BEGIN
IF (a < 3) THEN
INSERT INTO t2 VALUES (a);
END IF;
RETURN 0;
END;

CREATE TABLE t1 (a INT);

INSERT INTO t1 VALUES (1),(2),(3);

SELECT f1(a) FROM t1;

在执行SELECT语句时，函数f1（）被调用三次。其中两个调用插入一行，MySQL为每个调用记录一个SELECT语句。也就是说，MySQL将以下语句写 入二进制日志：

SELECT f1(1);
SELECT f1(2);

当存储函数调用导致错误的存储过程时，服务器还会记录存储函数调用的SELECT语句。在这种情况下，服务器将SELECT语句连同预期的错误代码 一起写入日志。在从属服务器上，如果出现相同的错误，这就是预期的结果，复制将继续进行。否则，复制停止。
.记录存储函数调用，而不是记录函数执行的语句，对复制有安全隐患，这源于两个因素：
.一个函数可能在主服务器和从服务器上遵循不同的执行路径。

.在从属线程上执行的语句由具有完全权限的从属SQL线程处理。

这意味着，尽管用户必须具有CREATE ROUTINE权限才能创建函数，但用户可以编写一个包含危险语句的函数，该语句将只在具有完全权限的线程 处理的从机上执行。例如，如果主服务器和从服务器的服务器ID分别为1和2，则主服务器上的用户可以创建并调用不安全函数unsafe_func()， 如下所示：

mysql> delimiter //
mysql> CREATE FUNCTION unsafe_func () RETURNS INT
-> BEGIN
-> IF @@server_id=2 THEN dangerous_statement; END IF;
-> RETURN 1;
-> END;
-> //
mysql> delimiter ;
mysql> INSERT INTO t VALUES(unsafe_func());

CREATE FUNCTION和INSERT语句被写入二进制日志，因此从属服务器将执行它们。因为从SQL线程拥有全部特权，所以它将执行危险的语句。因此 ，函数调用对主服务器和从服务器有不同的影响，并且不是复制安全的。

为了防止启用二进制日志记录的服务器出现这种危险，存储函数创建者除了需要通常的CREATE ROUTINE权限外，还必须具有SUPER权限。类似地 ，要使用ALTER FUNCTION，除了具有ALTER ROUTINE权限外，还必须具有SUPER权限。如果没有SUPER权限，将会出现错误：

ERROR 1419 (HY000): You do not have the SUPER privilege and
binary logging is enabled (you *might* want to use the less safe
log_bin_trust_function_creators variable)

如果您不想要求函数创建者拥有SUPER权限（例如，如果在您的系统上具有CREATE ROUTINE权限的所有用户都是经验丰富的应用程序开发人员） ，则将全局log_bin_trust_function_creators系统变量设置为1。您也可以在启动服务器时使用–log-bin-trust-function-creators=1选项来 设置此变量。如果没有启用二进制日志记录，log_bin_trust_function_creators将不适用。创建函数时不需要SUPER，除非如前所述，函数定义 中的DEFINER值需要它。

.如果执行更新的函数是不确定的，则它是不可重复的。这可能会产生两种不良影响：
.它将使从服务器与主服务器不同。

.恢复后的数据将与原始数据不同。

为了处理这些问题，MySQL强制执行以下要求：在主服务器上，除非您声明函数是确定的或不修改数据，否则拒绝创建和更改函数。这里适用两 组函数特征：
.DETERMINISTIC和NOT DETERMINISTIC特征表明对于给定的输入，函数是否总是产生相同的结果。如果两个特征都没有给出，默认值是NOT DETERMINISTIC。要声明函数是确定性的，必须显式指定deterministic。

.CONTAINS SQL、NO SQL、READS SQL DATA和modified SQL DATA特性提供了函数是否读取或写入数据的信息。NO SQL或READS SQL DATA表示函 数不更改数据，但您必须显式指定其中一个，因为如果没有给定特征，默认值是CONTAINS SQL。

默认情况下，要接受一个CREATE FUNCTION语句，至少要满足DETERMINISTIC、NO SQL或READS SQL DATA必须显式指定。否则会出现错误：

ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL,
or READS SQL DATA in its declaration and binary logging is enabled
(you *might* want to use the less safe log_bin_trust_function_creators
variable)

如果将log_bin_trust_function_creators设置为1，则不再要求函数是确定性的或不修改数据。

.存储过程调用在语句级别记录，而不是在CALL级别记录。也就是说，服务器不记录CALL语句，而是记录实际执行的过程中的那些语句。因此， 在主服务器上发生的相同更改将在从服务器上被观察到。这可以防止由于过程在不同的机器上具有不同的执行路径而导致的问题。

通常，在存储过程中执行的语句将使用与以独立方式执行的语句相同的规则写入二进制日志。在记录过程语句时需要特别注意，因为过程中的语 句执行与非过程上下文中的语句执行不完全相同：
.要记录的语句可能包含对局部过程变量的引用。这些变量不存在于存储过程上下文之外，因此引用此类变量的语句不能按字面记录。相反，为 了记录日志，每个对局部变量的引用都被这个结构替换：

NAME_CONST(var_name, var_value)

Var_name是本地变量名，var_value是一个常量，指示该变量在记录语句时所具有的值。NAME_CONST（）有一个var_value值和一个
var_name的“name”。因此，如果你直接调用这个函数，你会得到这样的结果：

mysql> SELECT NAME_CONST('myname', 14);
+--------+
| myname |
+--------+
|     14 |
+--------+
1 row in set (0.00 sec)

NAME_CONST（）允许在从机上执行已记录的独立语句，其效果与存储过程中在主机上执行的原始语句相同。

使用NAME_CONST（）可能会导致CREATE TABLE…SELECT语句当源列表达式引用局部变量时，。将这些引用转换为NAME_CONST（）表达式可能导致 主服务器和从服务器上的列名不同，或者列名太长而不能作为合法的列标识符。一种解决方法是为引用局部变量的列提供别名。考虑当myvar的 值为1时的这个语句：

CREATE TABLE t1 SELECT myvar;

它将被重写如下：

CREATE TABLE t1 SELECT NAME_CONST(myvar, 1);

为了确保主表和从表具有相同的列名，编写这样的语句：

CREATE TABLE t1 SELECT myvar AS myvar;

改写后的语句变成：

CREATE TABLE t1 SELECT NAME_CONST(myvar, 1) AS myvar;

.要记录的语句可能包含对用户定义变量的引用。为了解决这个问题，MySQL在二进制日志中写入一个SET语句，以确保从服务器上的变量与主服 务器上的变量值相同。例如，如果有一条语句引用了变量@my_var，那么在二进制日志中，这条语句的前面会有以下语句，其中value是@my_var 在master上的值：

SET @my_var = value;

.过程调用可以发生在已提交或回滚的事务中。考虑了事务上下文，以便正确地复制过程执行的事务方面。也就是说，服务器在实际执行和修改 数据的过程中记录这些语句，并在必要时记录BEGIN、COMMIT和ROLLBACK语句。例如，如果过程只更新事务表，并且在回滚的事务中执行，则不 会记录这些更新。如果该过程发生在已提交的事务中，则BEGIN和COMMIT语句将随更新一起记录。对于在回滚事务中执行的过程，其语句使用与 以独立方式执行语句相同的规则记录：
.对事务表的更新不记录日志。

.对非事务性表的更新要记录日志，因为回滚不会取消它们。

.对于事务表和非事务表的混合更新都被记录在BEGIN和ROLLBACK中，这样从服务器就可以和主服务器一样进行相同的更改和回滚。

.如果从存储函数中调用存储过程，则不会在语句级别将存储过程调用写入二进制日志。在这种情况下，唯一记录的是调用函数的语句（如果它 发生在记录日志的语句中）或DO语句（如果它发生在没有记录日志的语句中）。因此，在使用调用过程的存储函数时应该谨慎，即使过程本身是 安全的。

存储程序和视图的访问控制
存储程序和视图是在使用之前定义的，当被引用时，会在一个安全上下文中执行，这个安全上下文中决定了它们的权限。这些权限由它们的 DEFINER属性控制，如果有，则由它们的SQL SECURITY控制。

所有存储程序（过程、函数、触发器和事件）和视图都可以有一个DEFINER属性来命名MySQL帐户。如果在存储程序或视图定义中省略了DEFINER 属性，则默认帐户是创建对象的用户。

触发器和事件没有SQL SECURITY特性，总是在定义器上下文中执行。服务器根据需要自动调用这些对象，因此不存在调用用户。

定义器和调用程序安全上下文的区别如下：
.在definer安全上下文中执行的存储程序或视图使用由其definer属性命名的帐户的特权执行。这些权限可能与调用用户的权限完全不同。调用 者必须具有适当的权限来引用对象（例如，EXECUTE以调用存储过程，或SELECT以从视图中选择），但是当对象执行时，调用者的权限被忽略， 只有定义者帐户权限重要。如果该帐户的权限很少，则对象可以执行的操作就会相应地受到限制。如果定义者帐户具有高度特权（例如root帐户 ），那么无论谁调用它，对象都可以执行强大的操作。

.在调用者安全上下文中执行的存储例程或视图只能执行调用者具有权限的操作。可以指定DEFINER属性，但对在调用者上下文中执行的对象无效 。
考虑下面的存储过程：

CREATE DEFINER = 'admin'@'localhost' PROCEDURE p1()
SQL SECURITY DEFINER
BEGIN
UPDATE t1 SET counter = counter + 1;
END;

任何拥有p1的EXECUTE权限的用户都可以用CALL语句调用它。然而，当p1执行时，它是在definer安全上下文中执行的，因此以` admin ` @ ` localhost `的特权执行，该帐户在definer属性中命名。该帐户必须具有p1的EXECUTE权限以及表t1的UPDATE权限。否则，该过程将失败。

现在考虑这个存储过程，它与p1相同，只是它的SQL SECURITY是INVOKER：

CREATE DEFINER = 'admin'@'localhost' PROCEDURE p2()
SQL SECURITY INVOKER
BEGIN
UPDATE t1 SET counter = counter + 1;
END;

与p1不同，P2在invoker安全上下文中执行。DEFINER属性是不相关的，p2以调用用户的权限执行。如果调用者缺少p2的EXECUTE特权或表t1的 UPDATE特权，p2就会失败。

MySQL使用以下规则来控制用户可以在对象DEFINER属性中指定哪些帐户：
.只有当您具有SUPER权限时，才能指定除您自己的帐户之外的DEFINER值。

.如果您没有SUPER权限，那么唯一合法的用户值就是您自己的帐户，可以直接功或使用CURRENT_USER指定，您不能将定义器设置为其他帐户。

为了尽量减少存储程序和视图创建和使用的潜在风险，请遵循以下指导方针
.对于存储例程或视图，尽可能在对象定义中使用SQL SECURITY INVOKER，以便仅由具有该对象执行的操作的适当权限的用户使用。

.如果在使用具有SUPER权限的帐户时创建定义上下文存储的程序或视图，请指定一个显式的DEFINER属性，该属性指定仅拥有该对象执行的操作 所需权限的帐户。只有在绝对必要时才指定具有高度特权的DEFINER帐户。

.管理员可以通过不授予用户SUPER权限来阻止用户指定高权限的DEFINER帐户。

.在编写定义器-上下文对象时，应牢记它们可能能够访问调用用户没有特权的数据。在某些情况下，可以通过不授予未授权用户特定权限来阻止 对这些对象的引用：
.没有EXECUTE权限的用户不能引用存储过程或函数。

.视图不能被没有相应权限的用户引用（SELECT用于从中选择，INSERT用于向其中插入，等等）。

但是，触发器不存在这种控制，因为用户不直接引用它们。触发器总是在definer上下文中执行，通过访问与其关联的表来激活，即使是没有特 权的用户访问普通表也可以。如果定义帐户是高度特权，触发器可以执行敏感或危险的操作。如果从创建触发器的用户的帐户中撤销了创建触发 器所需的SUPER和TRIGGER权限，则仍然如此。管理员在授予用户这种特权组合时应该特别小心。

使用视图
MySQL支持视图，包括可更新视图。视图是存储的查询，在调用时生成结果集。视图充当虚拟表。

下面的讨论描述了创建和删除视图的语法，并展示了如何使用它们的一些示例。

视图语法
CREATE VIEW语句创建一个新视图。要更改视图的定义或删除视图，请使用alter view或drop view。

可以用多种SELECT语句创建视图。它可以引用基表或其他视图。它可以使用联结、联合和子查询。SELECT甚至不需要引用任何表。下面的例子定 义了一个从另一个表中选择两列的视图，以及从这两列中计算出的表达式：

mysql> CREATE TABLE t (qty INT, price INT);
ERROR 1050 (42S01): Table 't' already exists
mysql> drop table t;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE t (qty INT, price INT);
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t VALUES(3, 50), (5, 60);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> CREATE VIEW v AS SELECT qty, price, qty*price AS value FROM t;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM v;
+------+-------+-------+
| qty  | price | value |
+------+-------+-------+
|    3 |    50 |   150 |
|    5 |    60 |   300 |
+------+-------+-------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM v WHERE qty = 5;
+------+-------+-------+
| qty  | price | value |
+------+-------+-------+
|    5 |    60 |   300 |
+------+-------+-------+
1 row in set (0.00 sec)

视图处理算法
CREATE VIEW或ALTER VIEW的可选算法子句是标准SQL的MySQL扩展。它影响MySQL如何处理视图。算法有三个值：MERGE、TEMPTABLE和UNDEFINED 。
.对于MERGE，引用视图和视图定义的语句的文本被合并，以便视图定义的部分替换语句的相应部分。

.对于TEMPTABLE，将视图的结果检索到临时表中，然后使用临时表执行语句

.对于UNDEFINED， MySQL选择使用哪个算法。如果可能的话，它更倾向于MERGE而不是TEMPTABLE，因为MERGE通常更有效，而且如果使用临时表 ，则视图无法更新。

.
如果没有ALGORITHM子句存在，UNDEFINED是MySQL 5.7.6之前的默认算法。从5.7.6开始，默认算法由optimizer_switch系统变量的 derived_merge标志的值决定。

显式指定TEMPTABLE的一个原因是，在创建临时表之后，在使用临时表完成对语句的处理之前，可以释放底层表上的锁。这可能导致比MERGE算法 更快地释放锁，从而使使用该视图的其他客户端不会被阻塞太久。

视图算法可以被定义为UNDEFINED，原因有三：
.在CREATE VIEW语句中没有ALGORITHM子句。

.CREATE VIEW语句有一个显式的ALGORITHM = UNDEFINED子句。

.ALGORITHM = MERGE是为一个只能用临时表处理的视图指定的。在这种情况下，MySQL生成一个警告并将算法设置为UNDEFINED。

如前所述，合并是通过将视图定义的相应部分合并到引用视图的语句中来处理的。下面的例子简要说明了合并算法的工作原理。这些示例假设有 一个视图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，并且使用and连接词将view WHERE子句添加到语句WHERE子句中（添加括号以确 保子句的各部分以正确的优先级执行）。执行的结果语句变成：

SELECT c1, c2 FROM t WHERE (c3 > 100) AND (c1 < 100);

实际上，要执行的语句有一个如下形式的WHERE子句：

WHERE (select WHERE) AND (view WHERE)

如果不能使用MERGE算法，则必须使用临时表。防止合并的构造与防止派生表中合并的构造相同。例如子查询中的SELECT DISTINCT或LIMIT。

可更新和可插入视图

有些视图是可更新的，对它们的引用可以用来指定要在data change语句中更新的表。也就是说，您可以在UPDATE、DELETE或INSERT等语句中使 用它们来更新底层表的内容。派生表也可以在多表UPDATE和DELETE语句中指定，但只能用于读取数据以指定要更新或删除的行。通常，视图引用 必须是可更新的，这意味着它们可以被合并而不是被物化。复合视图有更复杂的规则。

对于可更新的视图，视图中的行与底层表中的行之间必须存在一对一的关系。还有一些其他的构造使得视图不可更新。更具体地说，如果视图包 含以下任何内容，则视图不可更新：
.聚合函数(SUM()， MIN()， MAX（)， COUNT()，等等）

.DISTINCT

.HAVING

.UNION 或UNION ALL

.选择列表中的子查询。在MySQL 5.7.11之前，select列表中的子查询对于INSERT会失败，但是对于UPDATE， DELETE是可以的。从MySQL 5.7.11 开始，对于非依赖子查询仍然如此。对于选择列表中的依赖子查询，不允许使用数据更改语句

.某些连接（请参阅本节后面的其他连接讨论）

.在FROM子句中引用不可更新视图

.仅引用文字值（在本例中，没有要更新的基础表）

.ALGORITHM = TEMPTABLE（使用临时表总是使视图不可更新）

.对基表任意列的多个引用（INSERT失败，UPDATE、DELETE正常）

视图中生成的列被认为是可更新的，因为可以对其进行分配。但是，如果显式更新这样的列，则唯一允许的值是DEFAULT。

假设可以用MERGE算法处理多表视图，那么有时多表视图可能是可更新的。为此，视图必须使用内部连接（而不是外部连接或UNION）。而且，视 图定义中只能更新一个表，因此SET子句必须只命名视图中一个表中的列。使用UNION ALL的视图是不允许的，即使它们理论上可能是可更新的。

关于可插入性（可通过INSERT语句进行更新），如果可更新视图还满足以下对视图列的附加要求，则可插入视图：
.不能有重复的视图列名。

.视图必须包含基表中没有默认值的所有列。

.视图列必须是简单的列引用。它们不能是表达式，例如：
3.14159
col1 + 3
UPPER(col2)
col3 / col4
(subquery)

MySQL在创建视图时设置了一个标志，称为视图更新标志。如果更新和删除（以及类似的操作）对视图是合法的，则该标志设置为YES （true） 。否则，该标志设置为NO （false）。INFORMATION_SCHEMA.VIEWS表中的IS_UPDATABLE列显示该标志的状态。

如果一个视图依赖于一个或多个其他视图，并且其中一个底层视图被更新，则IS_UPDATABLE标志可能不可靠。不管IS_UPDATABLE值是多少，服务 器都会跟踪视图的可更新性，并正确拒绝对不可更新视图的数据更改操作。如果视图的IS_UPDATABLE值由于底层视图的更改而变得不准确，可以 通过删除和重新创建视图来更新该值。

视图的可更新性可能会受到updatable_views_with_limit系统变量值的影响。

对于下面的讨论，假设存在这些表和视图：

mysql> CREATE TABLE t1 (x INTEGER);
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE t2 (c INTEGER);
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE VIEW vmat AS SELECT SUM(x) AS s FROM t1;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE VIEW vup AS SELECT * FROM t2;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE VIEW vjoin AS SELECT * FROM vmat JOIN vup ON vmat.s=vup.c;
Query OK, 0 rows affected (0.01 sec)

INSERT， UPDATE， DELETE语句允许如下：
.INSERT: INSERT语句的插入表可能是合并的视图引用。如果视图是连接视图，则视图的所有组件都必须是可更新的（而不是具体化的）。对于 多表可更新视图，如果插入到单个表中，则INSERT可以工作。

这个语句是无效的，因为连接视图的一个组件是不可更新的：

mysql> INSERT INTO vjoin (c) VALUES (1);
ERROR 1471 (HY000): The target table vjoin of the INSERT is not insertable-into

这个语句是有效的；视图不包含物化组件：

mysql> INSERT INTO vup (c) VALUES (1);
Query OK, 1 row affected (0.00 sec)

.UPDATE：在UPDATE语句中要更新的表可能是合并的视图引用。如果视图是连接视图，则视图的至少一个组件必须是可更新的（这与插入不同） 。

在多表UPDATE语句中，语句的更新表引用必须是基表或可更新视图引用。未更新的表引用可能是物化视图或派生表。

这个语句是有效的；列c来自连接视图的可更新部分：

mysql> UPDATE vjoin SET c=c+1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

这个语句是无效的；列x来自不可更新部分：

mysql> UPDATE vjoin SET x=x+1;
ERROR 1054 (42S22): Unknown column 'x' in 'field list'

这个说法是有效的；多表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列的表上插入一个insertable视图不会改变LAST_INSERT_ID（）的值，因为将默认 值插入不属于视图的列的副作用不应该是可见的。

带有CHECK选项子句的视图
可以为可更新视图提供WITH CHECK选项子句，以防止插入到select_statement中的WHERE子句不为true的行。它还阻止对WHERE子句为true但更新 会导致它不为true的行进行更新（换句话说，它阻止了可见的行）
从更新到不可见行)。

在用于可更新视图的WITH CHECK选项子句中，当根据另一个视图定义视图时，LOCAL 和CASCADED确定检查测试的范围。当两个关键字都没有给出 时，默认值会CASCADED。

在MySQL 5.7.6之前，WITH CHECK OPTION测试是这样的：
.
使用LOCAL时，检查视图WHERE子句，但不检查底层视图。

.使用CASCADED，检查视图WHERE子句，然后检查递归到底层视图，为它们添加WITH CASCADED CHECK OPTION(为了检查的目的；它们的定义保持 不变)，并应用相同的规则。

.如果没有检查选项，则不会检查视图WHERE子句，也不会检查底层视图。

从MySQL 5.7.6开始，WITH CHECK OPTION测试是标准兼容的（与以前的LOCAL和no check子句的语义不同）：
.使用LOCAL，检查视图WHERE子句，然后检查递归到底层视图并应用相同的规则。

.使用CASCADED，检查视图WHERE子句，然后检查递归到底层视图，为它们添加WITH CASCADED CHECK OPTION(为了检查的目的；它们的定义保持 不变)，并应用相同的规则。

.如果没有检查选项，则不检查视图WHERE子句，然后检查递归到底层视图，并应用相同的规则。

考虑以下表和一组视图的定义：

mysql> CREATE TABLE t1 (a INT);
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE VIEW v1 AS SELECT * FROM t1 WHERE a < 2
    -> WITH CHECK OPTION;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE VIEW v2 AS SELECT * FROM v1 WHERE a > 0
    -> WITH LOCAL CHECK OPTION;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE VIEW v3 AS SELECT * FROM v1 WHERE a > 0
    -> WITH CASCADED CHECK OPTION;
Query OK, 0 rows affected (0.00 sec)

这里v2和v3视图是根据另一个视图v1定义的。在MySQL 5.7.6之前，因为v2有一个LOCAL检查选项，插入只针对v2检查进行测试。v3有一个 cascade检查选项，因此插入不仅要根据v3检查进行测试，还要根据底层视图的检查进行测试。下面的语句说明了这些区别：

mysql> INSERT INTO v2 VALUES (2);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO v3 VALUES (2);
ERROR 1369 (HY000): CHECK OPTION failed 'test.v3'

从MySQL 5.7.6开始，LOCAL的语义与以前不同：v2的插入是根据它来检查的LOCAL检查选项，然后（与5.7.6之前不同），检查递归到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'

视图元数据
视图元数据获取方式如下：
.查询INFORMATION_SCHEMA数据库的VIEWS表。

.使用SHOW CREATE VIEW语句。

使用事件调度程序
MySQL事件调度程序管理事件的调度和执行，即根据调度运行的任务。下面的讨论涵盖了事件调度程序，并分为以下几个部分：

事件调度程序概述
MySQL事件是根据计划运行的任务。因此，我们有时将它们称为计划事件。当您创建事件时，您正在创建一个命名的数据库对象，其中包含一个 或多个SQL语句，这些SQL语句将以一个或多个常规间隔执行，开始和结束于特定的日期和时间。从概念上讲，这类似于Unix crontab（也称为“ cron job”）或Windows Task Scheduler的思想

这种类型的计划任务有时也称为“时间触发器”，这意味着这些对象是由时间推移触发的。虽然这基本上是正确的，但我们倾向于使用事件这个 术语，以避免与触发器类型混淆。更确切地说，事件不应与“临时触发器”混淆。触发器是一个数据库对象，其语句是为了响应在给定表上发生 的特定类型的事件而执行的，而（预定）事件是一个对象，其语句是为了响应指定时间间隔的通过而执行的。

虽然SQL标准中没有事件调度的规定，但在其他数据库系统中有先例，您可能会注意到这些实现与MySQL服务器中的实现之间存在一些相似之处。

MySQL事件有以下主要特性和属性：
.在MySQL中，一个事件是由它的名称和它所分配的模式唯一标识的。

.事件根据时间表执行特定的操作。这个操作由一条SQL语句组成，它可以是BEGIN…END语句块。事件发生的时间可以是一次性的，也可以是周期 性的。一次性事件只执行一次。周期性事件以固定的时间间隔重复其操作，可以为周期性事件分配特定的开始日和时间、结束日和时间，或两者 都指定，或两者都不指定。（默认情况下，一个循环事件的时间表在创建时就开始了，并无限期地持续下去，直到它被禁用或删除。）

如果重复事件在其调度间隔内未终止，则结果可能是该事件的多个实例同时执行。如果不希望出现这种情况，应该建立一种机制来防止同时多实 例执行。例如，可以使用GET_LOCK（）函数，或者行锁或表锁。

.用户可以使用专门用于这些目的的 SQL 语句创建、修改和删除计划事件。语法无效的事件创建和修改语句会以适当的错误消息失败。用户可以 在事件的操作中包含需要其实际上并不具备的权限的语句。事件创建或修改语句会成功，但事件的操作会失败。

.事件的许多属性可以使用SQL语句设置或修改。这些属性包括事件的名称、时间、持久性（即是否在其调度过期后保留）、状态（启用或禁用） 、要执行的操作以及分配给它的模式。

事件的默认定义者是创建该事件的用户，除非该事件已被更改，在这种情况下，定义者是发出影响该事件的最后alter event语句的用户。在为 其定义事件的数据库上具有event特权的任何用户都可以修改事件。

.事件的操作语句可以包含存储例程中允许的大多数SQL语句。

事件调度程序配置
事件由一个特殊的事件调度程序线程执行；当我们引用事件调度程序时，我们实际上引用的是这个线程。运行时，具有PROCESS特权的用户可以 在SHOW PROCESSLIST的输出中看到事件调度器线程及其当前状态，如下面的讨论所示。

全局event_scheduler系统变量决定事件调度程序是否已启用并在服务器上运行。它有以下3个值之一，影响事件调度，如下所述：
.OFF:事件调度程序已停止。事件调度器线程没有运行，没有显示在SHOW PROCESSLIST的输出中，也没有执行计划的事件。OFF是 event_scheduler的默认值。

当事件调度程序停止时（event_scheduler为OFF），可以通过将event_scheduler的值设置为ON来启动它。（见下一条。）

.ON：事件调度程序已启动；事件调度程序线程运行并执行所有计划的事件。

当事件调度器打开时，事件调度器线程会作为守护进程在SHOW PROCESSLIST的输出中列出，其状态如下所示：

mysql> SHOW PROCESSLIST\G
*************************** 1. row ***************************
     Id: 166
   User: root
   Host: mysqlcs:41344
     db: test
Command: Query
   Time: 0
  State: starting
   Info: SHOW PROCESSLIST
1 row in set (0.01 sec)

mysql> select @@event_scheduler;
+-------------------+
| @@event_scheduler |
+-------------------+
| OFF               |
+-------------------+
1 row in set (0.00 sec)

mysql> set global event_scheduler=ON;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW PROCESSLIST\G
*************************** 1. row ***************************
     Id: 166
   User: root
   Host: mysqlcs:41344
     db: test
Command: Query
   Time: 0
  State: starting
   Info: SHOW PROCESSLIST
*************************** 2. row ***************************
     Id: 167
   User: event_scheduler
   Host: localhost
     db: NULL
Command: Daemon
   Time: 7
  State: Waiting on empty queue
   Info: NULL
2 rows in set (0.00 sec)

可以通过将event_scheduler的值设置为OFF来停止事件调度。

.DISABLED：此值使事件调度程序不可操作。事件调度程序为DISABLED时，事件调度器线程不会运行（因此不会出现在SHOW PROCESSLIST的输出 中）。此外，事件调度程序状态不能在运行时更改。

如果事件调度器的状态没有设置为DISABLED， event_scheduler可以在ON和OFF之间切换（使用set）。在设置该变量时，也可以使用0表示关闭 ，1表示开启。因此，以下4条语句中的任何一条都可以在mysql客户端中开启事件调度器：

SET GLOBAL event_scheduler = ON;
SET @@global.event_scheduler = ON;
SET GLOBAL event_scheduler = 1;
SET @@global.event_scheduler = 1;

类似地，下面这4个语句中的任何一个都可以用来关闭事件调度器：

SET GLOBAL event_scheduler = OFF;
SET @@global.event_scheduler = OFF;
SET GLOBAL event_scheduler = 0;
SET @@global.event_scheduler = 0;

尽管ON和OFF具有等效的数值，但SELECT或SHOW VARIABLES总是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)。

MySQL在INFORMATION_SCHEMA数据库中提供了一个EVENTS表。通过查询该表，可以获取服务器上已定义的定时事件信息。

事件的语法
MySQL提供了几个SQL语句来处理计划事件：
.使用CREATE EVENT语句定义新事件。

.现有事件的定义可以通过ALTER event语句进行更改。

.当不再需要或不再需要计划的事件时，它的定义器可以使用DROP EVENT语句从服务器中删除它。事件是否持续到其调度结束后，还取决于它的 on COMPLETION子句（如果有的话）。

对定义事件的数据库具有事件权限的任何用户都可以删除事件。

事件元数据
事件元数据获取方式如下：
.查询mysql数据库event表。

.查询INFORMATION_SCHEMA数据库的EVENTS表。

.使用SHOW CREATE EVENT语句。

.使用SHOW EVENTS语句。

事件调度程序时间表示
MySQL中的每个会话都有一个会话时区（STZ）。这是会话time_zone值，在会话开始时从服务器的全局time_zone值初始化，但可以在会话期间更 改。

执行CREATE EVENT或ALTER EVENT语句时当前的会话时区用于解释事件定义中指定的时间。这就变成了事件时区（ETZ）；也就是说，用于事件调 度并在事件执行时在事件内生效的时区。

用于在mysql.event表中表示事件信息。execute_at、starts和ends时间被转换为UTC，并与事件时区一起存储。这使得事件执行可以按照定义进 行，而不需要考虑对服务器时区或夏令时效果的任何后续更改。上一次执行时间也存储在UTC中。

如果您从 `mysql.event` 中选择信息，刚才提到的时间将作为 UTC 值检索。这些时间也可以通过从 `INFORMATION_SCHEMA.EVENTS` 表中选择 或使用 `SHOW EVENTS` 获取，但它们会以 ETZ 值的形式报告。从这些来源获取的其他时间表明事件创建或最后修改的时间；这些会以 STZ 值 的形式显示。

事件调度程序状态
事件调度程序将有关事件执行的信息写入MySQL服务器的错误日志，并以错误或警告结束。

要获取事件调度程序的状态信息以进行调试和故障排除，请运行mysqladmin debug;运行此命令后，服务器的错误日志包含与事件调度程序相关 的输出，类似于以下所示：

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 : init_scheduler:313
LUA : init_scheduler:318
WOC : NO
Workers : 0
Executed : 0
Data locked: NO
Event queue status:
Element count : 1
Data locked : NO
Attempting lock : NO
LLA : init_queue:148
LUA : init_queue:168
WOC : NO
Next activation : 0000-00-00 00:00:00

在作为事件调度器执行的事件的一部分发生的语句中，诊断消息（不仅是错误，还有警告）被写入错误日志，在Windows上，则写入应用程序的 事件日志。对于频繁执行的事件，可能会产生很多日志消息。例如，对于SELECT … INTO var_list语句，如果查询没有返回任何行，则会出现 错误码为1329的警告（没有数据），并且变量值保持不变。如果查询返回多行，则会发生1172错误（Result包含多行）。对于任何一种情况，你 都可以通过声明一个条件处理程序来避免记录警告；

对于可能检索多行的语句，另一种策略是使用LIMIT 1将结果集限制为单行。

事件调度程序和MySQL特权
要启用或禁用计划事件的执行，必须设置全局event_scheduler系统变量的值。这需要SUPER特权。

EVENT 权限控制事件的创建、修改和删除。可以使用 GRANT 语句授予此权限。例如，以下 GRANT 语句将名为 myschema 的模式上的 EVENT 权 限授予用户 jon@ghidora：

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），你可以看到事件正在执行，但是它试图执行的动作失败了：

2025-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'
2025-09-24T12:41:31.262022Z 25 [Note] Event Scheduler:
[jon@ghidora].[myschema.e_store_ts] event execution failed.
2025-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'
2025-09-24T12:41:41.272761Z 26 [Note] Event Scheduler:
[jon@ghidora].[myschema.e_store_ts] event execution failed.

由于该用户很可能没有访问错误日志的权限，因此可以通过直接执行事件的action语句来验证事件的action语句是否有效：

检查INFORMATION_SCHEMA.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语句。在这个例子中，模式myschema上的EVENT特权从jon@ghidora用户帐户中移除：

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继续执行，每7秒向mytable中插入一行。如果root发出了以下语句中的任 何一个，也会产生相同的结果：

.DROP USER jon@ghidora;

.RENAME USER jon@ghidora TO someotherguy@ghidora;

您可以在发出DROP USER或RENAME USER语句之前和之后通过检查mysql.event表或INFORMATION_SCHEMA。EVENTS表来验证这是正确的。

事件定义存储在mysql.event表。要删除由其他用户帐户创建的事件，MySQL root用户（或其他具有必要权限的用户）可以从该表中删除行。例 如，要删除前面显示的e_insert事件，root用户可以使用以下语句：

DELETE FROM mysql.event
WHERE db = 'myschema'
AND definer = 'jon@ghidora'
AND name = 'e_insert';

从mysql.event表中删除行时，匹配事件名称、数据库模式名称和用户帐户是非常重要的。。这是因为相同的用户可以在不同的模式中创建相同 名称的不同事件。

用户的“EVENT”权限存储在“mysql.user”和“mysql.db”表的“Event_priv”列中。在这两种情况下，该列都只包含“Y”或“N”这两个值 之一。“N”是默认值。“mysql.user.Event_priv”仅在给定用户具有全局“EVENT”权限时（即使用“GRANT EVENT ON *.*”授予权限）才被 设置为“Y”。对于模式级别的“EVENT”权限，“GRANT”会在“mysql.db”中创建一行，并将该行的“Db”列设置为模式名称，“User”列设 置为用户名称，“Event_priv”列设置为“Y”。通常情况下，绝不需要直接操作这些表，因为“GRANT EVENT”和“REVOKE EVENT”语句会执行 对它们的所需操作。

五个状态变量提供与事件相关的操作计数。如下所示:
.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%”。

使用触发器
触发器是一个与表相关联的命名数据库对象，在表发生特定事件时激活。触发器的一些用途是检查将要插入到表中的值，或者对更新中涉及的值 进行计算。

定义了一个触发器，当一条语句插入、更新或删除关联表中的行时激活。这些行操作是触发事件。例如，可以通过INSERT或LOAD DATA语句插入 行，并且为每个插入行激活一个INSERT触发器。触发器可以设置为在触发事件之前或之后激活。例如，可以在插入到表中的每一行之前或更新每 一行之后激活触发器。

MySQL触发器仅在SQL语句对表进行更改时激活。这包括对基于可更新视图的基表的更改。如果api没有将SQL语句传输到MySQL服务器，则触发器 不会激活对表的更改。这意味着使用NDB API进行的更新不会激活触发器。

在INFORMATION_SCHEMA或performance_schema表中更改不会激活触发器。那些表实际上是视图，视图上不允许有触发器。

下面几节描述创建和删除触发器的语法，展示如何使用它们的一些示例，并指出如何获取触发器元数据。

额外资源
.在使用触发器时，您可以找到使用触发器的触发器用户论坛。

.有关MySQL中触发器的常见问题的答案

.对触发器的使用有一些限制

.触发器的二进制日志记录按所述进行

触发器语法和示例
要创建触发器或删除触发器，请使用create trigger或drop trigger语句

下面是一个简单的示例，它将触发器与表关联起来，以激活INSERT操作。触发器充当累加器，将插入到表的某一列中的值相加。

mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
Query OK, 0 rows affected (0.01 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，表示“要插入到新行中的金额列 的值”。

要使用触发器，将累加器变量设置为零，执行INSERT语句，然后查看变量之后的值：

mysql> SET @sum = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO account VALUES(137,14.98),(141,1937.50),(97,-100.00);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT @sum AS 'Total amount inserted';
+-----------------------+
| Total amount inserted |
+-----------------------+
|               1852.48 |
+-----------------------+
1 row in set (0.00 sec)

在本例中，执行INSERT语句后@sum的值为14.98 + 1937.50 – 100，即1852.48。

要销毁触发器，使用DROP trigger语句。如果触发器不在默认模式中，则必须指定模式名：

mysql> DROP TRIGGER test.ins_sum;
Query OK, 0 rows affected (0.00 sec)

如果删除一个表，那么该表的任何触发器也会被删除。

触发器名称存在于模式名称空间中，这意味着在一个模式中所有触发器都必须具有唯一的名称。不同模式中的触发器可以具有相同的名称。

从 MySQL 5.7.2 版本开始，可以为给定的表定义多个具有相同触发事件和操作时间的触发器。例如，您可以为一个表定义两个 BEFORE UPDATE 触发器。默认情况下，具有相同触发事件和操作时间的触发器按照创建的顺序激活。要影响触发器的顺序，请在 FOR EACH ROW 之后指定一个子 句，该子句指示 FOLLOWS 或 PRECEDES 以及具有相同触发事件和操作时间的现有触发器的名称。使用 FOLLOWS 时，新触发器在现有触发器之后 激活。使用 PRECEDES 时，新触发器在现有触发器之前激活。

例如，下面的触发器定义为account表定义了另一个BEFORE INSERT触发器：

mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account FOR EACH ROW SET @sum = @sum + NEW.amount;
Query OK, 0 rows affected (0.00 sec)

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之后创建的。

在MySQL 5.7.2之前，对于一个给定的表，不能有多个触发器具有相同的触发事件和操作时间。例如，一个表不能有两个BEFORE UPDATE触发器。 为了解决这个问题，你可以定义一个执行多个语句的触发器，在FOR EACH FOW之后使用BEGIN…END复合语句构造。（本节后面会给出一个例子。 ）

在触发器主体中，OLD和NEW关键字使您能够访问受触发器影响的行中的列。OLD和NEW是MySQL对触发器的扩展；它们不区分大小写。

在 INSERT 触发器中，只能使用 NEW.col_name；不存在旧行。在 DELETE 触发器中，只能使用 OLD.col_name；不存在新行。在 UPDATE 触发器 中，可以使用 OLD.col_name 来引用行更新前的列，使用 NEW.col_name 来引用行更新后的列。

以OLD命名的列是只读的。您可以引用它（如果您有SELECT权限），但不能修改它。如果您对以NEW命名的列具有SELECT权限，则可以引用该列。 在BEFORE触发器中，您还可以使用SET NEW.col_name = value更改其值，如果您对它有UPDATE权限。这意味着您可以使用触发器来修改要插入新 行或用于更新行的值。（这样的SET语句在AFTER触发器中没有作用，因为行更改已经发生了。）

在BEFORE触发器中，AUTO_INCREMENT列的NEW值是0，而不是实际插入新行时自动生成的序列号。

通过使用BEGIN … END构造时，你可以定义一个执行多个语句的触发器。在BEGIN语句块中，还可以使用存储例程允许的其他语法，如条件语句 和循环语句。然而，就像存储例程一样，如果你使用mysql程序定义了一个执行多条语句的触发器，那么就有必要重新定义mysql语句的定界符， 这样你就可以使用；触发器定义中的语句定界符。下面的例子说明了这些要点。它定义了一个更新触发器，检查用于更新每一行的新值，并将该 值修改为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;//
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

单独定义存储过程，然后使用简单的CALL语句从触发器调用它，这样更容易。如果你想在多个触发器中执行相同的代码，这也是有利的。

当触发器被激活时，可以在语句中显示的内容是有限制的
.触发器不能使用CALL语句调用向客户机返回数据或使用动态SQL的存储过程。(允许存储过程通过OUT或INOUT参数)。

.触发器不能使用显式或隐式开始或结束事务的语句，例如START TRANSACTION， COMMIT， ROLLBACK。（回滚到SAVEPOINT是允许的，因为它不 结束事务。）
MySQL在触发器执行过程中处理错误如下：

.如果BEFORE触发失败，则不执行对应行的操作。

.当尝试插入或修改该行时，无论随后的尝试是否成功，都会激活BEFORE触发器。

.只有当任何BEFORE触发器和行操作成功执行时，才会执行AFTER触发器。

.BEFORE或AFTER触发器期间的错误将导致触发触发器调用的整个语句失败。

.对于事务性表，语句失败应导致回滚该语句执行的所有更改。触发器失败会导致语句失败，因此触发器失败也会导致回滚。对于非事务性表， 不能进行这样的回滚，因此尽管语句失败，但在错误点之前执行的任何更改仍然有效。

触发器可以通过名称直接引用表，例如下面这个例子中的testref触发器：

mysql> CREATE TABLE test1(a1 INT);
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE test2(a2 INT);
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE test4(
    -> a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> b4 INT DEFAULT 0
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter |
mysql> 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;
    -> |
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> INSERT INTO test3 (a3) VALUES
    -> (NULL), (NULL), (NULL), (NULL), (NULL),
    -> (NULL), (NULL), (NULL), (NULL), (NULL);
Query OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> INSERT INTO test4 (a4) VALUES
    -> (0), (0), (0), (0), (0), (0), (0), (0), (0), (0);
Query OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 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)

触发元数据
可以通过以下方式获取触发器的元数据：
.查询INFORMATION_SCHEMA数据库中的TRIGGERS表

.使用SHOW CREATE TRIGGER语句。

.使用SHOW TRIGGERS语句

分区和锁定
对于像MyISAM这样的存储引擎，在执行DML或DDL语句时实际上会执行表级别的锁，在旧版本的MySQL（5.6.5及更早的版本）中，这样的语句会影 响分区表，并对整个表施加锁；也就是说，所有分区都被锁定，直到语句完成。在MySQL 5.7中，分区锁修剪在很多情况下消除了不必要的锁， 大多数读取或更新分区的MyISAM表的语句只会锁定受影响的分区。例如，一个分区的MyISAM表的SELECT只锁定那些实际上包含满足SELECT语句 WHERE条件的行的分区。

对于使用存储引擎（如InnoDB）来影响分区表的语句来说，这不是问题。InnoDB采用行级锁，在分区修剪之前实际上不执行（或需要执行）锁。

接下来的几段将讨论分区锁修剪对使用存储引擎（使用表级锁）的各种MySQL语句的影响。

对DML语句的影响
SELECT语句（包括那些包含union或join的语句）只锁定那些实际需要读取的分区。这也适用于SELECT … PARTITION。

UPDATE只对没有更新分区列的表进行锁修剪。

REPLACE和INSERT只对那些有要插入或替换行的分区锁定。但如果对任何分区列生成了AUTO_INCREMENT值，那么所有分区都会被锁定。

只要未更新分区列，INSERT… ON DUPLICATE KEY UPDATE 就会被修剪。

INSERT … SELECT只锁定源表中需要读取的那些分区，尽管目标表中的所有分区都是锁定的。

不能修剪由LOAD DATA语句对分区表施加的锁。

在使用分区表的任何分区列时，存在BEFORE INSERT或BEFORE UPDATE触发器，这意味着更新表的INSERT和UPDATE语句上的锁不能被修剪，因为触 发器可以修改其值：在表的任何分区列上都有一个BEFORE INSERT触发器，这意味着由INSERT或REPLACE设置的锁不能被修剪，因为BEFORE INSERT触发器可能会在插入行之前更改该行的分区列，从而迫使该行进入不同的分区。分区列上的BEFORE UPDATE触发器意味着UPDATE或 INSERT… ON DUPLICATE KEY UPDATE无法被修剪。

受影响的DDL语句
CREATE VIEW不会引起任何锁。

ALTER TABLE … EXCHANGE PARTITION修剪锁；只有交换的表和交换的分区被锁定。

ALTER TABLE … TRUNCATE PARTITION修剪锁；只有要清空的分区被锁定。

此外，ALTER TABLE语句在表级别上获取元数据锁。

其它语句
LOCK TABLES不能修剪分区锁。

CALL stored_procedure（expr）支持锁修剪，但是对expr进行评估却没有。

DO和SET语句不支持分区锁修剪。

存储引擎分区限制
以下限制适用于使用具有用户定义表分区的存储引擎。

MERGE存储引擎。用户定义分区与合并存储引擎不兼容。不能对使用MERGE存储引擎的表进行分区。不能合并分区表。

FEDERATED存储引擎。不支持FEDERATED表的分区；不可能创建分区的FEDERATED表。

CSV存储引擎。不支持使用CSV存储引擎的分区表；不可能创建分区的CSV表。

InnoDB存储引擎。InnoDB外键和MySQL分区不兼容。分区后的InnoDB表不能有外键引用，也不能有被外键引用的列。包含外键或被外键引用的 InnoDB表不能进行分区。

InnoDB不支持使用多个磁盘进行子分区。（目前只有MyISAM支持。）

另外，ALTER TABLE … OPTIMIZE PARTITION在使用InnoDB存储引擎的分区表上不能正确工作。使用ALTER TABLE … REBUILD PARTITION和 ALTER TABLE … ANALYZE PARTITION来代替。

自定义分区和NDB存储引擎（NDB集群）。按键分区（包括线性键）是NDB存储引擎唯一支持的分区类型。通常情况下，在NDB集群中不可能使用除 [LINEAR]键以外的任何分区类型来创建NDB集群表，并且尝试会失败并抛出错误。

例外情况（不用于生产环境）：可以通过将NDB集群SQL节点上的新系统变量设置为on来覆盖此限制。如果您选择这样做，您应该知道生产环境不 支持使用[LINEAR] KEY以外的分区类型的表。在这种情况下，用户可以创建和使用非KEY或LINEAR KEY分区类型的表，但风险自负。

可以为一个NDB表定义的最大分区数取决于集群中的数据节点和节点组的数量、正在使用的NDB集群软件的版本以及其他因素。

从MySQL NDB Cluster 7.5.2开始，在一个NDB表中，每个分区最多可以存储的固定大小的数据量是128 TB。以前是16 GB。

不允许使用CREATE TABLE和ALTER TABLE语句导致用户分区的NDB表不满足以下两个要求之一或两个都不满足，并返回错误：
1.表必须有显式主键。

2.表的分区表达式中列出的所有列都必须是主键的一部分。

例外。如果用户分区的NDB表是使用空列列表创建的（即使用PARTITION BY KEY（）或PARTITION BY LINEAR KEY()），那么不需要显式的主键。

升级分区表。在执行升级时，必须转储并重新加载使用非NDB存储引擎的KEY分区表。

所有分区使用相同的存储引擎。一个分区表的所有分区必须使用相同的存储引擎，而且整个表必须使用相同的存储引擎。此外，如果没有在表级 别上指定引擎，则在创建或修改已分区表时必须执行以下任意一种操作：
.不为任何分区或子分区指定任何引擎

.为所有分区或子分区指定引擎

分区键、主键和唯一键
这里将讨论分区键与主键和唯一键的关系。控制这种关系的规则可以表达如下：分区表的分区表达式中使用的所有列必须是表可能具有的每个唯一 键的一部分。

换句话说，表上的每个唯一键都必须使用表的分区表达式中的每一列。(这也包括表的主键，因为根据定义，它是唯一的键。本节稍后将讨论这 种特殊情况。)例如，下列创建表的语句都是无效的：

mysql> 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;
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function
mysql> 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;
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

在每种情况下，建议的表都至少有一个唯一的键，不包括分区表达式中使用的所有列。

下列语句都是有效的，表示了相应的无效表创建语句的一种工作方式：

mysql> 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;
Query OK, 0 rows affected (0.02 sec)

mysql> 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;
Query OK, 0 rows affected (0.01 sec)

下面的例子显示了在这种情况下产生的错误：

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 1503 (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.03 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)
);

由于每个主键根据定义都是唯一键，因此此限制还包括表的主键（如果有的话）。例如，下面两个语句是无效的：

mysql> 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;
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function
mysql> 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;
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

在这两种情况下，主键都不包括分区表达式中引用的所有列。但是，下面两个语句都是有效的：

mysql> 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;
Query OK, 0 rows affected (0.02 sec)

mysql> 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;
Query OK, 0 rows affected (0.02 sec)

如果一张表没有唯一键（包括没有主键），那么这个限制就不适用了，只要列类型与分区类型兼容，就可以在分区表达式中使用任意一列或多列 。
出于同样的原因，用户不能在分区表中添加唯一键，除非这个键包含了表的分区表达式所使用的所有列。考虑如下所示创建的分区表：

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.02 sec)

可以使用ALTER TABLE语句为t_no_pk添加一个主键：

mysql> ALTER TABLE t_no_pk ADD PRIMARY KEY(c1);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE t_no_pk DROP PRIMARY KEY;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE t_no_pk ADD PRIMARY KEY(c1, c2);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE t_no_pk DROP PRIMARY KEY;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

然而，下一条语句失败了，因为c1是分区键的一部分，但不是建议的主键的一部分：

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.01 sec)

下面的ALTER TABLE语句返回错误，因为添加的列不是表中任何唯一键的一部分：

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.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

在np_pk的情况下，唯一可以用作分区表达式一部分的列是id；如果用户希望在分区表达式中使用任何其他列对该表进行分区，则必须首先修改表，要么将所需的列添加到主键中，要么完全删除主键。

分区的限制和约束条件
这里将讨论MySQL分区支持的当前限制和约束条件。

禁止结构。分区表达式中不允许使用以下结构：
.存储过程、存储函数、自定义函数或插件。
.声明的变量或用户变量

算术和逻辑运算符。分区表达式中允许使用算术运算符+、-和*。但是，结果必须是整数值或NULL （线性键分区除外）
还支持DIV操作符，不允许使用/操作符。（Bug #30188, Bug #33182）
位操作符|、&、^、< <、>>和~不允许出现在分区表达式中。
HANDLER语句。以前，分区表不支持HANDLER语句。这个限制从MySQL 5.7.1开始被移除。

服务器SQL模式。使用用户定义分区的表在创建时不会保留有效的SQL模式。正如5.1.8节所讨论的，许多MySQL函数和操作符的结果可能会根据服 务器SQL模式而改变。因此，在创建分区表之后的任何时候更改SQL模式都可能导致这些表的行为发生重大变化，并且很容易导致数据损坏或丢失 。出于这些原因，强烈建议在创建分区表后不要更改服务器 SQL模式。

例如:下面的示例说明了由于服务器SQL模式的更改而导致的分区表行为的一些变化：
1.错误处理。假设您创建了一个分区表，其分区表达式为列DIV 0或列MOD 0，如下所示：

mysql> CREATE TABLE tn (c1 INT)
    -> PARTITION BY LIST(1 DIV c1) (
    -> PARTITION p0 VALUES IN (NULL),
    -> PARTITION p1 VALUES IN (1)
    -> );
Query OK, 0 rows affected (0.03 sec)

MySQL的默认行为是对除零的结果返回NULL，不会产生任何错误：

mysql> SELECT @@sql_mode;
+------------+
| @@sql_mode |
+------------+
|            |
+------------+
1 row in set (0.00 sec)

mysql> INSERT INTO tn VALUES (NULL), (0), (1);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

但是，更改服务器SQL模式，将除零视为错误，并强制执行严格的错误处理，会导致相同的INSERT语句失败，如下所示：

mysql> SET sql_mode='STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> INSERT INTO tn VALUES (NULL), (0), (1);
ERROR 1365 (22012): Division by 0

2.表的可访问性。有时，服务器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.01 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.02 sec)

如果在创建表后删除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

服务器SQL模式也会影响分区表的复制。主服务器和从服务器上不同的SQL模式可能导致分区表达式的计算方式不同；这可能导致给定表的主副本 和从副本的分区数据分布不同，甚至可能导致在主副本上成功插入分区表的操作在从副本上失败。为了达到最佳效果，应该始终在主服务器和从 服务器上使用相同的服务器SQL模式。

性能考虑。下面列出了分区操作对性能的一些影响：
.文件系统操作。分区和重分区操作(ALTER TABLE
with PARTITION BY …, REORGANIZE PARTITION, or REMOVE PARTITIONING）依赖于文件系统操作的实现。这意味着这些操作的速度受到文件 系统类型和特征、磁盘速度、交换空间、操作系统的文件处理效率以及MySQL服务器选项和与文件处理相关的变量等因素的影响。尤其，大家应 该确保启用了large_files_support，并正确设置了open_files_limit。对于使用MyISAM存储引擎的分区表，增加myisam_max_sort_file_size可 以提高性能；通过启用innodb_file_per_table，涉及InnoDB表的分区和重分区操作可能会更加高效。

.MyISAM和分区文件描述符使用情况。对于分区的 MyISAM 表，MySQL 为每个分区使用 2 个文件描述符，对于每个处于打开状态的此类表都是如 此。这意味着在对分区的 MyISAM 表执行操作时，所需的文件描述符数量要比与之相同的未分区表多得多，尤其是在执行 ALTER TABLE 操作时 。

假设MyISAM表t有100个分区，例如下面的SQL语句创建的表：

mysql> CREATE TABLE t (c1 VARCHAR(50))
    -> ENGINE=MyISAM
    -> PARTITION BY KEY (c1) PARTITIONS 100;
Query OK, 0 rows affected, 1 warning (0.02 sec)

为简洁起见，我们对这个例子中显示的使用键分区表，但这里描述的文件描述符的使用适用于所有分区的MyISAM表，无论采用哪种分区类型。使 用其他存储引擎（如InnoDB）的分区表不受此影响。

现在假设您希望重新分区t，使它有101个分区，使用下面的语句：

mysql> ALTER TABLE t PARTITION BY KEY (c1) PARTITIONS 101;
Query OK, 0 rows affected, 1 warning (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 1

为了处理ALTER TABLE语句，MySQL使用了402个文件描述符，即100个原始分区各2个，101个新分区各2个。这是因为在重组表数据期间，必须同 时打开所有分区（旧分区和新分区）。建议，如果您希望执行这样的操作，您应该确保——open-files-limit不要设置得太低，以容纳它们。

.表锁。通常，在表上执行分区操作的进程需要对表使用写锁。从这样的表中读取相对不受影响；挂起的INSERT和UPDATE操作在分区操作完成后 立即执行。

.存储引擎。分区操作、查询和更新操作通常在MyISAM表中比在InnoDB或NDB表中更快。

.索引;分区修剪。与非分区表一样，正确使用索引可以显著加快对分区表的查询速度。此外，设计分区表并对这些表进行查询以利用分区修剪可 以显著提高性能。

.加载数据的性能。在MySQL 5.7中，LOAD DATA使用缓冲来提高性能。您应该知道，缓冲区在每个分区中使用130 KB内存来实现这一点。

最大分区数。
对于一个给定的表，不使用NDB存储引擎的最大可能分区数是8192。这个数字包括子分区。

对于使用NDB存储引擎的表，用户定义的最大可能分区数是根据使用的NDB集群软件版本、数据节点数量等因素来确定的。

如果在创建具有大量分区（但小于最大分区数）的表时，您会遇到类似Got error … from storage engine: Out of resources
when opening file，可以通过增加open_files_limit系统变量的值来解决这个问题。但这取决于操作系统，在所有平台上可能不可行，也不可 取。在某些情况下，由于其他原因，使用大量（数百个）分区可能也是不可取的，因此使用更多分区并不会自动带来更好的结果。

不支持查询缓存。
对于分区表不支持查询缓存，并且对于涉及分区表的查询自动禁用查询缓存。不能为此类查询启用查询缓存。

按分区键缓存。
MySQL 5.7支持分区的MyISAM表的键缓存，在缓存语句中使用CACHE INDEX 和LOAD INDEX INTO CACHE。可以为一个、几个或所有分区定义键缓存 ，并且可以将一个、几个或所有分区的索引预装到键缓存中。

分区InnoDB表不支持外键。
使用InnoDB存储引擎的分区表不支持外键。更具体地说，这意味着以下两种说法是正确的：
1.使用用户定义的分区的InnoDB表的定义不能包含外键引用；定义中包含外键引用的InnoDB表不能被分区。

2.InnoDB表定义中不能包含用户分区表的外键引用；用户定义分区的InnoDB表不能包含外键引用的列。

刚才列出的限制范围包括所有使用InnoDB存储引擎的表。不允许CREATE TABLE和ALTER TABLE语句导致表违反这些限制。

ALTER TABLE … ORDER BY.ALTER TABLE…ORDER BY column语句对已分区的表运行会导致仅在每个分区内对行进行排序。

修改主键对REPLACE语句的影响。
在某些情况下可以修改表的主键。请注意，如果您的应用程序使用REPLACE语句，并且这样做，这些语句的结果可能会被彻底改变。

全文索引
分区表不支持全文索引或搜索，即使是使用InnoDB或MyISAM存储引擎的分区表。

空间列。具有空间数据类型（如点或几何）的列不能在分区表中使用。

临时表
临时表不能分区。(错误# 17497)

日志表
不能对日志表进行分区；ALTER TABLE … PARTITION BY …在这样的表上的语句失败并报错。

分区键的数据类型。
分区键必须是一个整数列或一个解析为整数的表达式。不能使用包含枚举列的表达式。列或表达式的值也可以是NULL。

这个限制有两个例外：
1.当按[LINEAR] KEY进行分区时，可以使用除TEXT或BLOB以外的任何有效MySQL数据类型的列作为分区键，因为MySQL内部的键散列函数会根据这 些类型生成正确的数据类型。例如，下面两个CREATE TABLE语句是有效的：

mysql> CREATE TABLE tkc (c1 CHAR)
    -> PARTITION BY KEY(c1)
    -> PARTITIONS 4;
Query OK, 0 rows affected (0.04 sec)

mysql> CREATE TABLE tkc (c1 CHAR)
    -> PARTITION BY KEY(c1)
    -> PARTITIONS 4;
Query OK, 0 rows affected (0.01 sec)

2.当按范围列或列表列分区时，可以使用string、DATE和DATETIME列。例如，下面的CREATE TABLE语句都是有效的。

mysql> 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)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> 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)
    -> );
Query OK, 0 rows affected (0.03 sec)

上述两种异常都不适用于BLOB或TEXT列类型。

子查询
分区键可能不是子查询，即使该子查询解析为整数值或NULL。

子分区的问题。
子分区必须使用散列或键分区。只有范围分区和列表分区可以分区。哈希分区和键分区不能分区。

SUBPARTITION BY KEY要求显式指定子分区的列或列s，不像按键分区的情况，可以省略（默认使用表的主键列）。考虑下面这条语句创建的表：

mysql> CREATE TABLE ts (
    -> id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> name VARCHAR(30)
    -> );
Query OK, 0 rows affected (0.01 sec)

你可以创建一个具有相同列的表，并按KEY进行分区，使用如下语句：

mysql> drop table ts;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE ts (
    -> id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> name VARCHAR(30)
    -> )
    -> PARTITION BY KEY()
    -> PARTITIONS 4;
Query OK, 0 rows affected (0.02 sec)

前面的语句被看作是这样写的，表的主键列被用作分区列：

mysql> drop table ts;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE ts (
    -> id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> name VARCHAR(30)
    -> )
    -> PARTITION BY KEY(id)
    -> PARTITIONS 4;
Query OK, 0 rows affected (0.01 sec)

但是，下面的语句尝试使用默认列作为子分区列创建子分区表失败，并且必须指定该列才能成功，如下所示：

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 ')
SUBPARTITIONS 4
(
PARTITION p0 VALUES LESS THAN (100),
PARTITION p1 VALUES LES' at line 6


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.03 sec)

这是一个已知的问题（见Bug #51470）。

数据目录和索引目录选项。
当与分区表一起使用时，DATA DIRECTORY和INDEX DIRECTORY受到以下限制：
.表级数据目录和索引目录选项被忽略（见Bug #32091）。

.在Windows上，MyISAM表的单个分区或子分区不支持数据目录和索引目录选项。但是，你可以将数据目录用于InnoDB表的单个分区或子分区。

修复和重建分区表。
对于已分区的表，支持CHECK TABLE、OPTIMIZE TABLE、ANALYZE TABLE、REPAIR TABLE语句。

此外，用户还可以使用ALTER TABLE … REBUILD PARTITION重建分区表的一个或多个分区；ALTER TABLE … REORGANIZE PARTITION也会导致 重新构建分区。
从MySQL 5.7.2开始，在子分区中支持ANALYZE， CHECK， OPTIMIZE， REPAIR和TRUNCATE操作。在MySQL 5.7.5之前，REBUILD也被接受，尽管这 没有影响。

分区表不支持Mysqlcheck、myisamchk和myisampack。

导出选项（刷新表）。

在MySQL 5.7.4及更早版本中，不支持FLUSH TABLES语句的FOR EXPORT选项。(Bug# 16943907)