MariaDB存储过程中如何通过动态SQL给局部变量赋值

MariaDB 中PREPARE预处理的动态SQL运行在独立语句作用域，无法直接访问存储过程内通过DECLARE定义的局部变量，因此动态SQL字符串内的INTO v_amount_of_samples_that_require_revision无法被执行引擎识别，最终抛出1327未声明变量错误。

原代码还存在两处隐藏语法问题：

• 表名拼接后与WHERE关键字之间缺少空格，最终生成的SQL会出现FROM 你的表名WHERE的非法写法
• 若REQUIRES_REVISION_STATE是存储过程内定义的常量或局部变量，动态SQL同样无法直接读取，需要提前拼接进SQL字符串或通过会话变量传递

方案一：会话变量中转（全MariaDB/MySQL版本兼容） #

使用@前缀的会话级用户变量作为结果中转，这类变量在当前数据库连接的全作用域可见，动态SQL执行完成后，再将会话变量的值赋值给存储过程局部变量即可。

修正后可运行代码：

DECLARE v_amount_of_samples_that_require_revision INTEGER;

-- 拼接动态SQL，结果写入会话临时变量
SET @run_sql = CONCAT(
  'SELECT COUNT(sample_id) INTO @temp_count
   FROM `', v_table_name, '`
   WHERE state = ''', REQUIRES_REVISION_STATE, '''
   AND form_id = ', p_form_id
);

PREPARE stmt FROM @run_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 将会话变量值赋值给存储过程局部变量
SET v_amount_of_samples_that_require_revision = @temp_count;
-- 清理会话变量，避免连接复用产生脏数据
SET @temp_count = NULL, @run_sql = NULL;

提示：如果v_table_name、REQUIRES_REVISION_STATE来自外部用户输入，拼接前需要做标识符/值转义，避免SQL注入风险。

方案二：EXECUTE INTO直接赋值（仅支持MariaDB 10.5+版本） #

MariaDB 10.5版本新增了EXECUTE ... INTO扩展语法，不需要在动态SQL内部写INTO子句，执行预处理语句时可以直接将结果写入存储过程局部变量，写法更简洁：

DECLARE v_amount_of_samples_that_require_revision INTEGER;

SET @run_sql = CONCAT(
  'SELECT COUNT(sample_id)
   FROM `', v_table_name, '`
   WHERE state = ''', REQUIRES_REVISION_STATE, '''
   AND form_id = ', p_form_id
);

PREPARE stmt FROM @run_sql;
-- 直接将查询结果赋值给局部变量
EXECUTE stmt INTO v_amount_of_samples_that_require_revision;
DEALLOCATE PREPARE stmt;

SET @run_sql = NULL;

注意：该语法为MariaDB独有特性，不兼容MySQL，如果业务需要跨数据库兼容请选择第一种方案。

内容的提问来源于stack exchange，提问作者Martín Lehoczky