MySQL存储过程如何动态构建SQL语句_PREPARE语句使用教程

MySQL动态SQL必须用PREPARE+EXECUTE，不能直接执行拼接字符串；PREPARE仅编译校验，EXECUTE才执行；需显式DEALLOCATE或动态命名避免句柄冲突；USING传参更安全但仅支持值替换，表名列名等须拼接并校验字符集。

MySQL动态SQL必须用PREPARE + EXECUTE，没有“字符串拼接完直接执行”这回事

很多开发者容易踩的第一个坑，就是误以为把SQL字符串拼好就能直接运行。比如，你写了SET @sql = 'SELECT * FROM users WHERE id = 1'; EXECUTE @sql;，结果MySQL会毫不客气地报错。原因很简单：EXECUTE指令只认预处理语句的句柄，不认识普通的字符串变量。所以，这条路是走不通的，必须老老实实走PREPARE stmt_name FROM @sql这个流程，先把字符串编译成一个可执行的stmt对象。

• 这里要明确一点：PREPARE本身并不执行SQL，它只做语法检查和执行计划缓存。如果表不存在或者字段名写错了，错误会在这一步就抛出来。
• 同一个stmt_name如果被重复PREPAREDEALLOCATE也能重用。但这里有个大坑，后面会详细说。
• 另外，不能在PREPARE语句里直接使用存储过程的参数。你得先把参数值拼接到@sql字符串里，或者用后面要讲的USING子句来传参。

用USING传参比拼接更安全，但只支持IN参数且不能传表名/列名

手动拼接@sql字符串是个高危操作。手一抖漏了个单引号，或者用户输入里带了'，SQL注入的风险就来了。相比之下，用USING子句进行参数绑定就安全得多，MySQL会自动帮你处理转义问题：

SET @sql = 'SELECT * FROM users WHERE status = ? AND created_at > ';
PREPARE stmt FROM @sql;
EXECUTE stmt USING @status, @min_time;

不过，需要警惕的是：这个占位符的能力是有限的。它只能替换值（比如WHERE条件右边的值、ORDER BY里的数字），绝对不能用来替换表名、列名、ORDER BY的字段名或者LIMIT的偏移量。这些动态部分，你还是得老老实实拼接到@sql字符串里去，并且，务必自己做好合法性校验。

• 参数的类型是由变量值决定的。比如SET @x = 123和SET @x = '123'，虽然值看起来一样，但生成的占位符行为可能不同。
• USING后面跟的变量名必须带@符号，而且不能直接使用存储过程的参数名（比如p_id）。你得先把它赋值给一个用户变量，比如@id := p_id。
• 还有一个细节：如果USING传递的变量值是NULL，那么对应的SQL条件会变成IS NULL，而不是= NULL。这个逻辑上的差异，有时候会导致意想不到的结果。

PREPARE在存储过程中不能跨IF分支复用同名stmt，否则报Unknown prepared statement

来看一个典型的陷阱。下面这段代码，乍一看似乎没什么问题：

IF p_type = 'user' THEN
  SET @sql = 'SELECT * FROM users';
ELSE
  SET @sql = 'SELECT * FROM orders';
END IF;
PREPARE stmt FROM @sql;
EXECUTE stmt;

但在实际运行时，第二次调用（比如p_type从'user'变成了'orders'）很可能就会失败。MySQL会报错说stmt已经存在。关键在于，当已经存在同名的预处理语句时，PREPARE stmt FROM ...并不会隐式地帮你释放掉旧的，而是直接抛出错误。所以，必须显式地进行清理。

• 有些方案建议在PREPARE前加一段复杂的判断，比如检查是否存在再DEALLOCATE。其实没必要这么麻烦，更干脆的做法是直接写DEALLOCATE PREPARE stmt;，然后再PREPARE。
• 更稳妥的一种策略是使用动态的stmt名称，比如SET @stmt_name = CONCAT('stmt_', UNIX_TIMESTAMP()); PREPARE @stmt_name FROM @sql;。但问题来了，EXECUTE指令不支持用变量作为语句名。这就需要用到MySQL 8.0.16+的EXECUTE IMMEDIATE，或者一些不推荐的变通写法。
• 对于大多数简单场景，最省心的办法就是：在每个逻辑分支里，都老老实实地DEALLOCATE掉旧的，再PREPARE新的。

字符集不一致会导致PREPARE报Illegal mix of collations

动态SQL另一个恼人的错误，就是字符集混乱。当你拼接@sql字符串时，如果混用了不同字符集的字符串（比如表注释用的是utf8mb4，但数据库连接默认是latin1），在PREPARE阶段就会卡住，报出“非法排序规则混合”的错误。这在用CONCAT()拼接包含中文的字段名或条件值时尤其常见。

• 首先，确保客户端连接字符集是utf8mb4。可以在连接时指定参数charset=utf8mb4，或者在代码里执行SET NAMES utf8mb4;。
• 其次，所有参与拼接的变量，最好都显式转换一下字符集：CONVERT(p_table_name USING utf8mb4)。
• 避免在CONCAT()函数里直接书写中文字符串。如果非要写，可以用_utf8mb4'中文'这样的前缀来明确声明字符集。
• 从INFORMATION_SCHEMA系统表中查询到的表名、列名，默认会带有数据库的字符集属性。为了统一，可以在使用时加上COLLATE utf8mb4_0900_as_cs之类的子句进行强制转换。

话说回来，动态SQL最麻烦的地方，从来都不是语法本身。而是错误总是在运行时才暴露，而且错误堆栈往往不指向你写的那行PREPARE，而是指向MySQL内部的解析器。所以，一个非常实用的建议是：在执行PREPARE之前，先用SELECT @sql;把拼接好的SQL语句打印出来看一眼。这个简单的习惯，能帮你省下大量的调试时间。