mysql execute stmt_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)