本文共 4546 字，大约阅读时间需要 15 分钟。

PREPARE语句准备好一条SQL语句，并分配给这条SQL语句一个名字供之后调用。准备好的SQL语句通过EXECUTE命令执行，通过DEALLOCATE PREPARE命令释放掉。

语句的名字不区分大小写。准备好的SQL语句名字可以是字符串，也可以是用户指定的包含SQL文本的变量。PREPARE中的SQL文本必须代表一条单独的SQL语句而不能是多条SQL语句。在SQL语句中，? 字符用来作为后面执行查询使用的一个参数。? 不能加上引号，及时打算将它们绑定到字符变量中也不可以。

如果准备好的SQL语句名字已经存在，它会在新语句被准备好前释放掉。这意味着，如果一条新的语句包含错误且不能被准备好，这时会返回错误并且准备好的SQL语句将不再存在。 准备好的语句范围是创建它的会话，具有下列特点： 准备好的语句在其他会话无效； 当会话结束时，不管会话时正常结束还是异常结束，这个会话中准备好的SQL语句将不再存在。如果自动连接功能开启，客户端不会被通知连接丢失。 在存储过程或函数里面的准备好的语句，在存储过程或函数执行结束后，会继续存在，可以在存储过程或包外面继续被执行。

1. mysql> PREPARE pr1 FROM 'SELECT ?+?';    Query OK, 0 rows affected (0.01 sec)    Statement prepared    mysql> SET @a=1, @b=10 ;    Query OK, 0 rows affected (0.00 sec)    mysql> EXECUTE pr1 USING @a, @b;    +------+    | ?+?  |    +------+    | 11   |    +------+    1 row in set (0.00 sec)    mysql> EXECUTE pr1 USING 1, 2;    -- 只能使用用户变量传递。    ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the     right syntax to use near '1, 2' at line 1    mysql> DEALLOCATE PREPARE pr1;    Query OK, 0 rows affected (0.00 sec)

    

-- 参数只能使用用户变量传递。

即在execute之前  先设置值     SET @a=1, @b=10 ;

然后在使用变量 EXECUTE pr1 USING @a, @b;

 

mysql> SET @a=10;

Query OK, 0 rows affected (0.00 sec) mysql> PREPARE STMT FROM 'SELECT * FROM dept2 LIMIT ?'; Query OK, 0 rows affected (0.08 sec) Statement prepared mysql> EXECUTE STMT USING @a; +--------+-------+ | deptno | dname | +--------+-------+ |     10 | A     | |     20 | B     | |     30 | C     | |     40 | D     | |     50 | E     | |     60 | F     | |     70 | G     | |     80 | H     | |     90 | I     | |    100 | J     | +--------+-------+ 10 rows in set (0.02 sec) mysql> SET @skip=1; SET @numrows=5; Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql> PREPARE STMT FROM 'SELECT * FROM dept2 LIMIT ?, ?'; Query OK, 0 rows affected (0.00 sec) Statement prepared mysql> EXECUTE STMT USING @skip, @numrows; +--------+-------+ | deptno | dname | +--------+-------+ |     20 | B     | |     30 | C     | |     40 | D     | |     50 | E     | |     60 | F     | +--------+-------+ 5 rows in set (0.00 sec) mysql> DEALLOCATE PREPARE STMT; Query OK, 0 rows affected (0.00 sec) mysql> EXECUTE STMT USING @skip, @numrows; ERROR 1243 (HY000): Unknown prepared statement handler (STMT) given to EXECUTE mysql> PREPARE STMT FROM 'SELECT * FROM dept2 LIMIT ?, ?'; Query OK, 0 rows affected (0.00 sec) Statement prepared mysql> EXECUTE STMT USING @skip, @numrows; +--------+-------+ | deptno | dname | +--------+-------+ |     20 | B     | |     30 | C     | |     40 | D     | |     50 | E     | |     60 | F     | +--------+-------+ 5 rows in set (0.00 sec) 示例存储过程 delimiter $$ create procedure registerSQLReference(IN the_table_name VARCHAR(80), IN the_sql_script_version VARCHAR(80), IN the_sql_script_name VARCHAR(80), IN the_install_version  VARCHAR(80), IN the_execution_duration VARCHAR(80), IN the_sql_script_description VARCHAR(200)) BEGIN declare column_exist INT DEFAULT 0; declare the_query VARCHAR(500);   select '[INFO]    Check if the column C_CHANGE_DESCRIPTION exists in the table @VERSION_LEVEL_TABLE_NAME';   set column_exist = is_ChangeDescColumnExist ('@VERSION_LEVEL_TABLE_NAME');   IF column_exist = 0   THEN     select '[INFO]    VERSION LEVEL TABLE does not contain C_CHANGE_DESCRIPTION column.';     set @v_the_table_name=the_table_name;     select concat('INSERT INTO ', @v_the_table_name, ' VALUES (?, ?, ?, ?, ?) ') into the_query;     SET @stmt=the_query;     PREPARE STMT FROM @stmt;     select concat('[INFO]    the_query=', the_query );     set @v_the_sql_script_version=the_sql_script_version;     set @v_the_sql_script_name=the_sql_script_name;     set @v_date=now();     set @v_the_install_version=the_install_version;     set @v_the_execution_duration=the_execution_duration;     EXECUTE STMT using @v_the_sql_script_version , @v_the_sql_script_name, @v_date, @v_the_install_version, @v_the_execution_duration;   ELSE     select '[INFO]    VERSION LEVEL TABLE contains C_CHANGE_DESCRIPTION column.';     select concat('INSERT INTO ', the_table_name, ' VALUES (?, ?, ?, ?, ?, ?) ') into the_query;     SET @stmt=the_query;     PREPARE STMT FROM @stmt;     select concat('[INFO]    the_query=', the_query );     set @v_the_sql_script_version=the_sql_script_version;     set @v_the_sql_script_name=the_sql_script_name;     set @v_date=now();     set @v_the_install_version=the_install_version;     set @v_the_execution_duration=the_execution_duration;     set @v_the_sql_script_description=the_sql_script_description;     select concat('[INFO]    the_query=', the_query);     EXECUTE the_query using @v_the_sql_script_version , @v_the_sql_script_name, @v_date, @v_the_install_version, @v_the_execution_duration, @v_the_sql_script_description;   END IF;   DEALLOCATE PREPARE STMT; END$$ delimiter ;

转载地址：https://blog.csdn.net/weixin_37839711/article/details/81562550 如侵犯您的版权，请留言回复原文章的地址，我们会给您删除此文章，给您带来不便请您谅解！