DEALLOCATE PREPARE

语法说明

DEALLOCATE PREPARE 语句的作用是释放使用 PREPARE 生成的预编译语句。在释放预编译语句后，再次执行预编译的语句会导致错误。若创建了过多预编译的语句并且没有使用 DEALLOCATE PREPARE 语句进行释放，那么系统变量会强制执行预编译语句上限 max_prepared_stmt_count 提示。

语法结构

  1. {DEALLOCATE | DROP} PREPARE stmt_name

参数释义

参数说明
stmt_name预编译的 SQL 语句的名称

示例

  1. > CREATE TABLE numbers(pk INTEGER PRIMARY KEY, ui BIGINT UNSIGNED, si BIGINT);
  2. > INSERT INTO numbers VALUES (0, 0, -9223372036854775808), (1, 18446744073709551615, 9223372036854775807);
  3. > SET @si_min = -9223372036854775808;
  4. > SET @si_max = 9223372036854775807;
  5. > PREPARE s2 FROM 'SELECT * FROM numbers WHERE si=?';
  6. Query OK, 0 rows affected (0.00 sec)
  8. > EXECUTE s2 USING @si_min;
  9. +------+------+----------------------+
  10. | pk   | ui   | si                   |
  11. +------+------+----------------------+
  12. |    0 |    0 | -9223372036854775808 |
  13. +------+------+----------------------+
  14. 1 row in set (0.01 sec)
  16. > EXECUTE s2 USING @si_max;
  17. +------+----------------------+---------------------+
  18. | pk   | ui                   | si                  |
  19. +------+----------------------+---------------------+
  20. |    1 | 18446744073709551615 | 9223372036854775807 |
  21. +------+----------------------+---------------------+
  22. 1 row in set (0.01 sec)
  24. > DEALLOCATE PREPARE s2;
  25. Query OK, 0 rows affected (0.00 sec)