execute_prepared_stmt()

• 执行预编译语句，调用该存储过程时传入一个SQL语句字符串，会创建一个prepare语句使用prepare的方式来执行该SQL语句，执行该SQL文本结束之后就会释放掉为该SQL文本创建的prepare语句，所以这个prepare语句不能被重用(因此该存储过程主要用于动态地一次性地执行SQL语句，在其他存储过程、函数中也大量地用于执行收集相关性能数据的语句)

  • 存储过程内部使用sys_execute_prepared_stmt作为prepare语句的名称。所以，如果调用该存储过程时名为sys_execute_prepared_stmt的prepare语句存在，则需要先将其销毁
  • 该存储过程在MySQL 5.7.9中新增

• 参数：

  • in_query LONGTEXT CHARACTER SET utf8：要执行的SQL语句文本字符串

• 配置选项：

  • debug，@sys.debug：如果此选项为ON，则输出调试信息， 默认为OFF

• 定义语句

DROP PROCEDURE IF EXISTS execute_prepared_stmt;

DELIMITER $$

CREATE DEFINER='root'@'localhost' PROCEDURE execute_prepared_stmt (
        IN in_query longtext CHARACTER SET UTF8
    )
    COMMENT '
            Description
            -----------

            Takes the query in the argument and executes it using a prepared statement. The prepared statement is deallocated,
            so the procedure is mainly useful for executing one off dynamically created queries.

            The sys_execute_prepared_stmt prepared statement name is used for the query and is required not to exist.


            Parameters
            -----------

            in_query (longtext CHARACTER SET UTF8):
              The query to execute.


            Configuration Options
            ----------------------

            sys.debug
              Whether to provide debugging output.
              Default is ''OFF''. Set to ''ON'' to include.


            Example
            --------

            mysql> CALL sys.execute_prepared_stmt(''SELECT * FROM sys.sys_config'');
            +------------------------+-------+---------------------+--------+
            | variable              | value | set_time            | set_by |
            +------------------------+-------+---------------------+--------+
            | statement_truncate_len | 64    | 2015-06-30 13:06:00 | NULL  |
            +------------------------+-------+---------------------+--------+
            1 row in set (0.00 sec)

            Query OK, 0 rows affected (0.00 sec)
            '
    SQL SECURITY INVOKER
    NOT DETERMINISTIC
    READS SQL DATA
BEGIN
    -- Set configuration options
    IF (@sys.debug IS NULL) THEN
        SET @sys.debug = sys.sys_get_config('debug', 'OFF');
    END IF;

    -- Verify the query exists
    -- The shortest possible query is "DO 1"
    IF (in_query IS NULL OR LENGTH(in_query) < 4) THEN
      SIGNAL SQLSTATE '45000'
          SET MESSAGE_TEXT = "The @sys.execute_prepared_stmt.sql must contain a query";
    END IF;

    SET @sys.execute_prepared_stmt.sql = in_query;

    IF (@sys.debug = 'ON') THEN
        SELECT @sys.execute_prepared_stmt.sql AS 'Debug';
    END IF;
    PREPARE sys_execute_prepared_stmt FROM @sys.execute_prepared_stmt.sql;
    EXECUTE sys_execute_prepared_stmt;
    DEALLOCATE PREPARE sys_execute_prepared_stmt;

    SET @sys.execute_prepared_stmt.sql = NULL;
END$$

DELIMITER ;

• 示例

admin@localhost : sys 09:47:41> call execute_prepared_stmt('select * from xiaoboluo.test limit 2;');
+----+---------------------+
| id | test                |
+----+---------------------+
|  1 | 2017-09-07 10:01:28 |
|  2 | 2017-09-07 10:01:28 |
+----+---------------------+
2 rows in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

• 参考链接：https://dev.mysql.com/doc/refman/5.7/en/sys-execute-prepared-stmt.html

---

上一篇： diagnostics()存储过程 | 下一篇： ps_setup_disable_background_threads()存储过程