A.4 MySQL 8.0 FAQ: Stored Procedures and Functions

Yes. MySQL 8.0 supports two types of stored routines, stored procedures and stored functions.

See Section 24.2, “Using Stored Routines”.

Yes. See https://forums.mysql.com/list.php?98.

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.

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 25.23, “The INFORMATION_SCHEMA ROUTINES Table”).

Yes. For a database named dbname, use this query on the INFORMATION_SCHEMA.ROUTINES table:

For more information, see Section 25.23, “The INFORMATION_SCHEMA ROUTINES Table”.

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 13.7.6.9, “SHOW CREATE PROCEDURE Syntax”, for more information.

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 25.23, “The INFORMATION_SCHEMA ROUTINES Table”, and Section 25.16, “The INFORMATION_SCHEMA PARAMETERS Table”.

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 13.7.6.9, “SHOW CREATE PROCEDURE Syntax”.

No. This is not supported in MySQL 8.0.

Yes.

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.

Yes. MySQL 8.0 implements the SQL standard SIGNAL and RESIGNAL statements. See Section 13.6.7, “Condition Handling”.

MySQL implements HANDLER definitions according to the SQL standard. See Section 13.6.7.2, “DECLARE ... HANDLER Syntax”, for details.

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.

Not in MySQL 8.0.

There is no equivalent in MySQL 8.0.

Not in MySQL 8.0.

In MySQL 8.0, cursors are available inside stored procedures only.

In MySQL 8.0, cursors are available inside stored procedures only. However, if you do not open a cursor on a SELECT, the result will be sent directly to the client. You can also SELECT INTO variables. See Section 13.2.10, “SELECT Syntax”.

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 will need to 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.

Yes, standard actions carried out in stored procedures and functions are replicated from a master MySQL server to a slave server. There are a few limitations that are described in detail in Section 24.7, “Stored Program Binary Logging”.

Yes, creation of stored procedures and functions carried out through normal DDL statements on a master server are replicated to a slave, so the objects will exist on both servers. ALTER and DROP statements for stored procedures and functions are also replicated.

MySQL records each DML event that occurs in a stored procedure and replicates those individual actions to a slave server. The actual calls made to execute stored procedures are not replicated.

Stored functions that change data are logged as function invocations, not as the DML events that occur inside each function.

Yes. Because a slave server has authority to execute any statement read from a master'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:

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, and therefore, random actions replicated to a slave will not mirror those performed on a master. Declaring stored functions to be DETERMINISTIC or setting the log_bin_trust_function_creators system variable to 0 will not allow random-valued operations to be invoked.

In addition, time-based actions cannot be reproduced on a slave 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.

Finally, nontransactional tables for which errors occur during large DML actions (such as bulk inserts) may experience replication issues in that a master may be partially updated from DML activity, but no updates are done to the slave 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 master that cause errors are ignored and updates that do not cause errors are replicated to the slave.

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.

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.

For additional information, see Section 17.2.1, “Replication Formats”.