Restrictions on MySQL storage subroutines and triggers

Some of the limitations described in this article apply to all storage subroutines, that is, stored programs and stored functions. Some restrictions apply only to stored functions and not to stored programs.

All restrictions on the storage function also apply to the triggering program.

NOTE: If the SQL statement, such as SELECT ... The INTO statement contains a reference to a column with the same name and a declared local variable, and MySQL interprets the reference as the name of the variable. This is a non-standard behavior, which is usually a column name, followed by SQL variables and parameters.

The store subroutine cannot contain any SQL statements. In a storage subroutine, you prohibit the use of the following statement:

·CHECK TABLES
·LOCK TABLES, UNLOCK TABLES
·LOAD DATA, LOAD TABLE

· SQL preprocessing statements (PREPARE, EXECUTE, deallocate PREPARE). Implied meaning: Dynamic SQL statements cannot be used in stored subroutines (where dynamic statements can be constructed as strings and then executed). Starting with the MySQL 5.0.13, this limit is relaxed for stored programs, but it still applies to stored functions and triggers.

· OPTIMIZE TABLE

For stored functions (instead of stored programs), the following additional statements are prohibited:

• A statement that performs an explicit or implicit commit or rollback operation.

• The statement that returns the result set. Includes a SELECT statement without an info clause, and a show statement. Able to use Select ... into, or a function that uses the cursor and FETCH statements to handle the result set.

· Flush statement. Note that although you can use flush in a stored program, you cannot call such a stored program from a stored function or from a trigger program.

Note that although some restrictions are normally applicable to stored functions and triggers, they do not apply to stored programs, and if they are invoked from a stored function or trigger, these restrictions apply to the stored program.

Using a stored subroutine can cause replication problems.

INFORMATION_SCHEMA does not yet contain a parameters table, so for applications that need to get subroutine parameter information at run time, you must take appropriate circumvention errors, such as parsing the output of the show create statement.

There is no storage subroutine debugging tool.

The storage subroutine uses the materialized cursor instead of the intrinsic cursor (generating the result set on the server side and caching the result set, and then returning it by row when the client obtains the result set).

The call statement cannot be processed in advance. Both the server-side preprocessing statement and the SQL preprocessing statement are established.

To prevent interaction between server threads, when a client issues a statement, the server uses available subroutine and trigger snapshots for statement execution. That is, the server calculates a list of stored programs, functions, and triggers that can be used during statement execution, loads them, and then enters the statement execution. This means that, while the statement executes, it does not see changes made by other threads to the program.