MySQL存储过程备份脚本调用报错：PREPARE/EXECUTE使用问题排查

你的存储过程触发Error Code: 1064且提示near 'NULL'的核心原因是游标未执行FETCH操作就直接进入循环，导致局部变量tableName初始为NULL，拼接SQL时生成了包含NULL的无效语句。

具体问题点： #

• 打开游标后，没有先获取第一条表名就进入REPEAT循环，此时tableName是默认的NULL值，拼接出来的@sqlstmt会变成类似CREATE TABLE customerinfo_bak.NULL_backup_20240520 LIKE customerinfo.NULL的无效SQL，触发语法错误。
• 循环内部处理完一个表后，没有再次FETCH下一个表名，即使初始获取了数据，也会陷入死循环或重复处理同一个表。

修正后的存储过程代码： #

DELIMITER $$
CREATE PROCEDURE backup_tables()
BEGIN
  DECLARE DONE INT DEFAULT FALSE;
  DECLARE tableName VARCHAR(255);
  DECLARE cursorController CURSOR FOR SELECT table_name FROM information_schema.tables WHERE table_schema = 'customerinfo';
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET DONE = TRUE;
  
  OPEN cursorController;
  -- 先获取第一条表名，初始化tableName变量
  FETCH cursorController INTO tableName;
  
  CREATE DATABASE IF NOT EXISTS customerinfo_bak;
  REPEAT
  
    SET @backup_table = CONCAT('customerinfo_bak.', tableName, '_backup_', DATE_FORMAT(CURDATE(), '%Y%m%d'));
    SET @sqlstmt = CONCAT('CREATE TABLE ', @backup_table, ' LIKE customerinfo.', tableName);
    PREPARE stmt FROM @sqlstmt;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    SET @sqlstmt = CONCAT('INSERT INTO ', @backup_table, ' SELECT * FROM customerinfo.', tableName);
    PREPARE stmt FROM @sqlstmt;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
  
    -- 每次循环结束后获取下一个表名，直到游标耗尽
    FETCH cursorController INTO tableName;
  UNTIL DONE 
  END REPEAT;
  
  CLOSE cursorController;

END$$
DELIMITER ;

关键修改说明： #

1. 打开游标后立即执行第一次FETCH：FETCH cursorController INTO tableName;，确保进入循环时tableName已经被赋值为有效的表名。
2. 循环末尾添加FETCH操作：每次处理完当前表后，获取下一个表名，当游标没有更多数据时，CONTINUE HANDLER会把DONE设为TRUE，循环终止。
3. 执行存储过程时，注意调用的是正确的schema下的存储过程：如果存储过程创建在customerinfo下，应该用CALL customerinfo.backup_tables();，如果是customerinfo_bak下则用你原来的调用语句，确保存储过程所在schema正确。

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