call 存储过程时必须声明表示符_23.7 存储过程的二进制日志

参考官方文档：

https://dev.mysql.com/doc/refman/5.7/en/stored-programs-logging.html

二进制日志包含有关修改数据库内容的SQL语句的信息。 该信息以描述修改的“事件”的形式存储。 二进制日志有两个重要目的：

• 对于复制，二进制日志在主复制服务器上用作要发送到从属服务器的语句的记录。 主服务器将其二进制日志中包含的事件发送到其从属服务器，这些服务器执行这些事件以对主服务器进行相同的数据更改。
• 某些数据恢复操作需要使用二进制日志。 还原备份文件后，将重新执行备份后记录的二进制日志中的事件。 这些事件使数据库从备份更新到时间点。

但是，如果在语句级别进行日志记录，则存储的程序（存储过程和函数，触发器和事件）存在某些二进制日志记录问题：

• 在某些情况下，语句可能会影响主服务器和从服务器上的不同行集。
• 在从属服务器上执行的复制语句由从属SQL线程处理（具有最高权限）。 程序可以在主服务器和从服务器上遵循不同的执行路径，因此用户可以编写包含危险语句的例程，该例程将仅在从服务器上执行，并由具有完全权限的线程处理。
• 如果修改数据的存储程序是不确定的，则它不可重复。 这可能导致主站和从站上的数据不同，或导致还原的数据与原始数据不同。

通常，当SQL语句级别（基于语句的二进制日志记录）发生二进制日志记录时，会导致此处描述的问题。 如果使用基于行的二进制日志记录，则日志包含由于执行SQL语句而对各个行所做的更改。 执行例程或触发器时，将记录行更改，而不是进行更改的语句。 对于存储过程，这意味着不记录CALL语句。 对于存储的函数，将记录函数内的行更改，而不是函数调用。 对于触发器，将记录触发器所做的行更改。 在slave端，只能看到行更改，而不是存储的程序调用。

混合格式二进制日志记录（binlog_format = MIXED）使用基于语句的二进制日志记录，除了一些基于行的二进制日志记录才能够产生正确结果的情况除外。 对于混合格式，当存储的函数，存储过程，触发器，事件或预准备语句包含对基于语句的二进制日志记录不安全的任何内容时，整个语句将标记为不安全并以行格式记录。 用于创建和删除过程，函数，触发器和事件的语句始终是安全的，并以语句格式记录。

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

• 要创建或更改存储的函数，除了通常需要的CREATE ROUTINE或ALTER ROUTINE权限外，还必须具有SUPER权限。 （根据函数定义中的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的函数去检查是否没有产生非确定性结果的语句。

• 当您尝试执行存储函数时，如果设置了binlog_format = STATEMENT，则必须在函数定义中指定DETERMINISTIC关键字。 如果不是这种情况，则会生成错误并且函数不会运行，除非指定log_bin_trust_function_creators = 1来覆盖此检查（请参见下文）。 对于递归函数调用，仅在最外层调用时需要DETERMINISTIC关键字。 如果正在使用基于行或混合二进制日志记录，则即使在没有DETERMINISTIC关键字的情况下定义函数，也会接受并复制该语句。
• 因为MySQL在创建时不检查函数是否确实是确定性的，所以使用DETERMINISTIC关键字调用存储函数可能会对基于语句的日志格式执行不安全的操作，或者调用包含不安全语句的函数或过程。 如果在设置binlog_format = STATEMENT时发生这种情况，则会发出警告消息。 如果正在使用基于行或混合二进制日志记录，则不会发出警告，并且将以基于行的格式复制语句。
• 要放松函数创建的前置条件（必须具有SUPER权限并且必须将函数声明为确定性或不修改数据），请将全局log_bin_trust_function_creators系统变量设置为1.默认情况下，此变量的值为0 ，但你可以改变它：

mysql> SET GLOBAL log_bin_trust_function_creators = 1;

您还可以在启动服务器时使用--log-bin-trust-function-creators = 1选项设置此变量。

如果未启用二进制日志记录，则log_bin_trust_function_creators不适用。 创建函数不需要SUPER，除非如前所述，函数定义中的DEFINER值需要它。

• 有关可能对复制不安全的内置函数的信息（因此导致使用它们的存储函数也不安全）

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

触发器可以更新表，因此如果您没有所需的权限，则CREATE TRIGGER会出现类似于存储函数的错误消息。 在从属端，slave使用触发器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（）语句将写入二进制日志。 假设在master上执行以下语句：

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函数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将一个SET语句写入二进制日志，以确保该变量存在于从站上，其值与主站上的值相同。 例如，如果语句引用变量@my_var，则该语句将在二进制日志的开头，其中value是master上@my_var的值：

SET @my_var = value;

• 存储过程调用可以在已提交或回滚的事务中进行。 也就是说，服务器在实际执行和修改数据的过程中记录这些语句，并根据需要记录BEGIN，COMMIT和ROLLBACK语句。 例如，如果过程仅更新事务表并在回滚的事务中执行，则不会记录这些更新。 如果过程在已提交的事务中发生，则会记录BEGIN和COMMIT语句以及更新。 对于在回滚事务中执行的存储过程，如果语句以独立方式执行，则使用相同的规则记录其语句：
  • 不记录事务表的更新。
  • 将记录对非事务表的更新，因为回滚不会取消它们。
  • 记录事务和非事务表混合的更新，包括BEGIN和ROLLBACK，以便从服务器进行与主服务器相同的更改和回滚。

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