是的。MySQL 支持两种类型的存储例程：存储过程和存储函数。Yes. MySQL supports two types of stored routines, stored procedures, and stored functions.

请参阅 第 27.2 节，“使用存储例程”。See Section 27.2, “Using Stored Routines”.

有的。请参阅 https://forums.mysql.com/list.php?98。Yes. See https://forums.mysql.com/list.php?98.

不幸的是，官方规范并不免费提供（ANSI 提供购买服务）。然而，有一些书籍，例如 Peter Gulutzan 和 Trudy Pelzer 的 SQL-99 Complete, Really，提供了对标准的全面概述，包括存储过程的介绍。Unfortunately, the official specifications are not freely available (ANSI makes them available for purchase). However, there are books, such as SQL-99 Complete, Really by Peter Gulutzan and Trudy Pelzer, that provide a comprehensive overview of the standard, including coverage of stored procedures.

为你的存储例程使用清晰的命名方案始终是一个好习惯。你可以使用 CREATE [FUNCTION|PROCEDURE]、ALTER [FUNCTION|PROCEDURE]、DROP [FUNCTION|PROCEDURE] 以及 SHOW CREATE [FUNCTION|PROCEDURE] 来管理存储过程。你可以使用 INFORMATION_SCHEMA 数据库中的 ROUTINES 表获取现有存储过程的信息（参见 第 28.3.30 节，“INFORMATION_SCHEMA ROUTINES 表”）。It is always good practice to use a clear naming scheme for your stored routines. You can manage stored procedures with CREATE [FUNCTION|PROCEDURE], ALTER [FUNCTION|PROCEDURE], DROP [FUNCTION|PROCEDURE], and SHOW CREATE [FUNCTION|PROCEDURE]. You can obtain information about existing stored procedures using the ROUTINES table in the INFORMATION_SCHEMA database (see Section 28.3.30, “The INFORMATION_SCHEMA ROUTINES Table”).

是的。对于名为dbname的数据库，在INFORMATION_SCHEMA.ROUTINES表上使用此查询：Yes. For a database named dbname, use this query on the INFORMATION_SCHEMA.ROUTINES table:

SELECT ROUTINE_TYPE, ROUTINE_NAME
    FROM INFORMATION_SCHEMA.ROUTINES
    WHERE ROUTINE_SCHEMA='dbname';

有关更多信息，请参见第 28.3.30 节，“INFORMATION_SCHEMA.ROUTINES 表”。For more information, see Section 28.3.30, “The INFORMATION_SCHEMA ROUTINES Table”.

可以使用 SHOW CREATE FUNCTION（针对存储函数）或 SHOW CREATE PROCEDURE（针对存储过程）查看存储例程的主体。更多信息请参见 第 15.7.7.10 节，“SHOW CREATE PROCEDURE 语句”。The body of a stored routine can be viewed using SHOW CREATE FUNCTION (for a stored function) or SHOW CREATE PROCEDURE (for a stored procedure). See Section 15.7.7.10, “SHOW CREATE PROCEDURE Statement”, for more information.

存储过程存储在 mysql.routines 和 mysql.parameters 表中，这些表是数据字典的一部分。您不能直接访问这些表。相反，请查询 INFORMATION_SCHEMA ROUTINES 和 PARAMETERS 表。请参见 第 28.3.30 节，“INFORMATION_SCHEMA ROUTINES 表”，以及 第 28.3.20 节，“INFORMATION_SCHEMA PARAMETERS 表”。Stored procedures are stored in the mysql.routines and mysql.parameters tables, which are part of the data dictionary. You cannot access these tables directly. Instead, query the INFORMATION_SCHEMA ROUTINES and PARAMETERS tables. See Section 28.3.30, “The INFORMATION_SCHEMA ROUTINES Table”, and Section 28.3.20, “The INFORMATION_SCHEMA PARAMETERS Table”.

您也可以使用 SHOW CREATE FUNCTION 来获取有关存储函数的信息，以及使用 SHOW CREATE PROCEDURE 来获取有关存储过程的信息。参见 第 15.7.7.10 节，“SHOW CREATE PROCEDURE 语句”。You can also use SHOW CREATE FUNCTION to obtain information about stored functions, and SHOW CREATE PROCEDURE to obtain information about stored procedures. See Section 15.7.7.10, “SHOW CREATE PROCEDURE Statement”.

不可以。MySQL 不支持此功能。No. This is not supported in MySQL.

可以。Yes.

存储过程可以执行 SQL 语句，例如 UPDATE，从而触发触发器的激活。A stored procedure can execute an SQL statement, such as an UPDATE, that causes a trigger to activate.

可以。存储过程可以根据需要访问一个或多个表。Yes. A stored procedure can access one or more tables as required.

可以。MySQL 实现了 SQL 标准的 SIGNAL 和 RESIGNAL 语句。请参见 第 15.6.7 节，“条件处理”。Yes. MySQL implements the SQL standard SIGNAL and RESIGNAL statements. See Section 15.6.7, “Condition Handling”.

MySQL 根据 SQL 标准实现了 HANDLER 定义。详情请参见 第 15.6.7.2 节，“DECLARE ... HANDLER 语句”。MySQL implements HANDLER definitions according to the SQL standard. See Section 15.6.7.2, “DECLARE ... HANDLER Statement”, for details.

存储过程可以这样做，但存储函数不行。如果在存储过程中执行普通的 SELECT，结果集会直接返回给客户端。要实现这一点，你需要使用 MySQL 4.1（或更高版本）的客户端/服务器协议。这意味着，例如在 PHP 中，你需要使用 mysqli 扩展而不是旧的 mysql 扩展。Stored procedures can, but stored functions cannot. If you perform an ordinary SELECT inside a stored procedure, the result set is returned directly to the client. You need to use the MySQL 4.1 (or higher) client/server protocol for this to work. This means that, for example, in PHP, you need to use the mysqli extension rather than the old mysql extension.

没有。No.

MySQL 中没有相应的等效功能。There is no equivalent in MySQL.

没有。No.

游标只能在存储过程中使用。Cursors are only available inside stored procedures.

Cursors are only available inside stored procedures. However, if you do not open a cursor on a SELECT, the result is sent directly to the client. You can also SELECT INTO variables. See 第 15.2.13 节，“SELECT 语句”.Cursors are only available inside stored procedures. However, if you do not open a cursor on a SELECT, the result is sent directly to the client. You can also SELECT INTO variables. See Section 15.2.13, “SELECT Statement”.

是的，你可以在存储过程中执行此操作，但不能在存储函数中执行。如果你在存储过程中执行普通的SELECT，结果集将直接返回给客户端。为此，必须使用 MySQL 4.1（或更高版本）客户端/服务器协议。这意味着，例如在 PHP 中，你需要使用mysqli扩展，而不是旧的mysql扩展。Yes, you can do this in a stored procedure, but not in a stored function. If you perform an ordinary SELECT inside a stored procedure, the result set is returned directly to the client. You must use the MySQL 4.1 (or above) client/server protocol for this to work. This means that, for example, in PHP, you need to use the mysqli extension rather than the old mysql extension.

可以。然而，你不能在存储函数中执行事务操作。Yes. However, you cannot perform transactional operations within a stored function.

是的，通过存储过程和函数执行的标准操作会从复制源服务器复制到副本。有关一些限制的详细描述，请参见第 27.7 节，“存储程序二进制日志记录”。Yes, standard actions carried out in stored procedures and functions are replicated from a replication source server to a replica. There are a few limitations that are described in detail in Section 27.7, “Stored Program Binary Logging”.

是的，通过正常的 DDL 语句在复制源服务器上创建的存储过程和函数会复制到副本，使这些对象在两台服务器上都存在。对存储过程和函数的ALTER和DROP语句也会被复制。Yes, creation of stored procedures and functions carried out through normal DDL statements on a replication source server are replicated to a replica, so that the objects exist on both servers. ALTER and DROP statements for stored procedures and functions are also replicated.

MySQL 会记录存储过程中的每个 DML 事件，并将这些单独的操作复制到副本。实际用于执行存储过程的调用不会被复制。MySQL records each DML event that occurs in a stored procedure and replicates those individual actions to a replica. The actual calls made to execute stored procedures are not replicated.

更改数据的存储函数会作为函数调用进行记录，而不是记录函数内部发生的 DML 事件。Stored functions that change data are logged as function invocations, not as the DML events that occur inside each function.

是的。因为副本有权限执行从源的二进制日志中读取的任何语句，所以在将存储函数用于复制时存在特殊的安全限制。如果复制或二进制日志记录（用于时间点恢复的目的）处于活动状态，那么 MySQL DBA 有两种安全选项可供选择：Yes. Because a replica has authority to execute any statement read from a source's binary log, special security constraints exist for using stored functions with replication. If replication or binary logging in general (for the purpose of point-in-time recovery) is active, then MySQL DBAs have two security options open to them:

存储过程中嵌入的非确定性（随机）或基于时间的操作可能无法正确复制。由于其本质，随机生成的结果不可预测，也无法完全重现；因此，复制到副本的随机操作不会反映在源上执行的操作。将存储函数声明为DETERMINISTIC或者将log_bin_trust_function_creators系统变量设置为 0，可以防止随机操作生成随机值时被调用。Nondeterministic (random) or time-based actions embedded in stored procedures may not replicate properly. By their very nature, randomly produced results are not predictable and cannot be exactly reproduced; therefore, random actions replicated to a replica do not mirror those performed on a source. Declaring stored functions to be DETERMINISTIC or setting the log_bin_trust_function_creators system variable to 0 keeps random operations producing random values from being invoked.

此外，基于时间的操作无法在副本上重现，因为存储过程中此类操作的时间无法通过用于复制的二进制日志再现。二进制日志仅记录 DML 事件，并不考虑时间约束。In addition, time-based actions cannot be reproduced on a replica because the timing of such actions in a stored procedure is not reproducible through the binary log used for replication. It records only DML events and does not factor in timing constraints.

最后，在大规模 DML 操作（例如批量插入）期间发生错误的非事务表可能会出现复制问题，即源可能会部分更新 DML 活动，但由于发生错误，副本没有进行更新。一种解决方法是使用 IGNORE 关键字执行函数的 DML 操作，这样对源产生错误的更新将被忽略，而不会产生错误的更新会复制到副本。Finally, nontransactional tables for which errors occur during large DML actions (such as bulk inserts) may experience replication issues in that a source may be partially updated from DML activity, but no updates are done to the replica because of the errors that occurred. A workaround is for a function's DML actions to be carried out with the IGNORE keyword so that updates on the source that cause errors are ignored and updates that do not cause errors are replicated to the replica.

影响复制的相同限制也会影响时间点恢复。The same limitations that affect replication do affect point-in-time recovery.

您可以选择基于语句的复制或基于行的复制。最初的复制实现是基于语句的二进制日志。基于行的二进制日志解决了前面提到的限制。You can choose either statement-based replication or row-based replication. The original replication implementation is based on statement-based binary logging. Row-based binary logging resolves the limitations mentioned earlier.

混合复制也可用（通过使用 --binlog-format=mixed 启动服务器）。这种混合形式的复制 “知道” 是否可以安全地使用基于语句的复制，或需要基于行的复制。Mixed replication is also available (by starting the server with --binlog-format=mixed). This hybrid form of replication “knows” whether statement-level replication can safely be used, or row-level replication is required.

更多信息，请参见 第 19.2.1 节，“复制格式”。For additional information, see Section 19.2.1, “Replication Formats”.