MySQL PREPARE STATEMENT

预处理语句

PREPARE、EXECUTE、DEALLOCATE PREPARE

1.1 基本用法

CREATE PROCEDURE test()
BEGIN
SET @selectsql = 'select * from app_user';
-- 预定义SQL
PREPARE stmt FROM @selectsql;
-- 执行SQL
EXECUTE stmt;
-- 释放连接
DEALLOCATE PREPARE stmt;
END;

1.2 传参用法

CREATE PROCEDURE test()
BEGIN
SET @selectsql = 'select * from app_user where age > ? and age < ?';
SET @a = 18;
SET @b = 30;
-- 预定义SQL
PREPARE stmt FROM @selectsql;
-- 传入两个会话变量来填充SQL中的 ? 并执行SQL
EXECUTE stmt USING @a, @b;
-- 释放连接
DEALLOCATE PREPARE stmt;
END;