为什么mysqldump生成的SET语句变量无@前缀导致MariaDB恢复报错？

问题根本原因 #

你遇到的语法报错不是mysqldump生成的语句有问题，是高低版本MariaDB的语法不兼容导致的：

• 你导出备份用的是MariaDB 10.5.11，该版本及所有高于10.2.3的MariaDB版本，都支持PREPARE语句直接读取存储过程内用DECLARE声明的局部变量（也就是你代码里的v_sql，局部变量本身就不需要加@前缀）
• 你恢复用的是MariaDB 10.1.48，这个版本的PREPARE语句只支持读取带@前缀的会话级用户自定义变量，不支持读取存储过程的局部变量，因此触发语法报错。

可行的解决方案 #

你可以根据实际场景选择任意一种方案处理：

• 方案1：升级目标库的MariaDB版本到10.2.3或更高，版本对齐后直接恢复备份即可，不需要修改备份文件。
• 方案2：如果不能升级目标库，手动修改备份文件里的对应存储过程代码：
  1. 删掉DECLARE v_sql TEXT;这行局部变量声明
  2. 把代码里所有的v_sql替换成@v_sql即可，修改后的代码示例如下：

DELIMITER ;;
CREATE DEFINER=`mydefiner`@`%` PROCEDURE `p_my_procedure`(
IN `myid` VARCHAR(36)
)
BEGIN
SET @v_sql = CONCAT('DROP TABLE IF EXISTS ', '`prefix_',myid,'_one`;');
PREPARE stmt FROM @v_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @v_sql = CONCAT('DROP TABLE IF EXISTS ','`prefix_',myid,'_two`;');
PREPARE stmt FROM @v_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @v_sql = CONCAT('DROP TABLE IF EXISTS ','`other_prefix_',myid,'`;');
PREPARE stmt FROM @v_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END ;;

• 方案3：如果不想手动修改备份文件，可以用10.1.x版本的mysqldump重新从原库导出备份，再导入到目标库即可。

内容的提问来源于stack exchange，提问作者August Janse