MySQL存储过程执行无错但临时表无数据，求排查Cursor或INSERT问题

问题描述 #

编写的存储过程执行无报错，但临时表_tmp始终没有数据，疑惑问题出在游标读取还是INSERT语句，代码如下：

DELIMITER //

CREATE PROCEDURE GetColumnMaxLengths(IN schema_name 
VARCHAR(255), IN table_name VARCHAR(255))
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE column_name VARCHAR(255);
DECLARE data_type VARCHAR(255);
DECLARE column_type VARCHAR(255);

DECLARE cur CURSOR FOR
    SELECT
        `COLUMN_NAME`,
        `DATA_TYPE`,
        `COLUMN_TYPE`
    FROM
        `INFORMATION_SCHEMA`.`COLUMNS`
    WHERE
        `TABLE_SCHEMA` = schema_name
        AND
        `TABLE_NAME` = table_name
        AND
        `DATA_TYPE` NOT IN (
            "date","time","year","datetime","timestamp",
            "enum","set",
            "geometry","point","linestring","polygon",
            "multipoint","multilinestring","multipolygon","geometrycollection"
         );

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

DROP TEMPORARY TABLE IF EXISTS `_tmp`;
CREATE TEMPORARY TABLE `_tmp` (
    `column_name` VARCHAR(255) NOT NULL,
    `data_type` VARCHAR(255) NOT NULL,
    `column_type` VARCHAR(255) NOT NULL,
    `max_value` VARCHAR(255) NOT NULL,
    PRIMARY KEY (`column_name`)
) ENGINE = InnoDB;

OPEN cur;

read_loop:
LOOP
    FETCH cur INTO column_name, data_type, column_type;
    IF done THEN
        LEAVE read_loop;
    END IF;

    SET @sql_query = CONCAT('SELECT MAX(LENGTH(`', column_name, '`)) INTO @max_value FROM `', schema_name, '`.`', table_name, '`;');
    PREPARE stmt FROM @sql_query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    INSERT INTO `_tmp` (`column_name`, `data_type`, `column_type`, `max_value`) VALUES(column_name, data_type, column_type, @max_value);
END LOOP read_loop;

CLOSE cur;

SELECT `column_name`, `data_type`, `column_type`, `max_value` FROM `_tmp`;

DROP TEMPORARY TABLE IF EXISTS `_tmp`;

END;
//

DELIMITER ;

排查方向及解决方法 #

1. 游标未返回任何数据 #

这是最常见的原因，先验证游标查询是否有结果：

• 单独执行游标内的SELECT语句，传入实际调用存储过程时的schema_name和table_name参数，例如：

  SELECT COLUMN_NAME, DATA_TYPE, COLUMN_TYPE
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = '你的库名'
    AND TABLE_NAME = '你的表名'
    AND DATA_TYPE NOT IN (
        'date','time','year','datetime','timestamp',
        'enum','set',
        'geometry','point','linestring','polygon',
        'multipoint','multilinestring','multipolygon','geometrycollection'
     );
  

• 检查要点：
  • 参数是否大小写正确（MySQL在Linux等区分大小写的系统中，库表名大小写敏感）
  • 目标表的列是否都被DATA_TYPE的排除列表覆盖了

2. INSERT因NOT NULL约束失败 #

临时表的max_value字段设置为NOT NULL，但如果目标列的所有值都是NULL，MAX(LENGTH(column_name))会返回NULL，此时INSERT会违反NOT NULL约束，但存储过程未捕获这类错误，导致这条INSERT静默失败（不会终止存储过程，但数据不会写入临时表）。

解决方法二选一：

• 临时表字段允许NULL：

  CREATE TEMPORARY TABLE `_tmp` (
      `column_name` VARCHAR(255) NOT NULL,
      `data_type` VARCHAR(255) NOT NULL,
      `column_type` VARCHAR(255) NOT NULL,
      `max_value` VARCHAR(255) NULL, -- 修改为允许NULL
      PRIMARY KEY (`column_name`)
  ) ENGINE = InnoDB;
  

• 处理NULL值，将其转换为合法的非NULL值：

  INSERT INTO `_tmp` (`column_name`, `data_type`, `column_type`, `max_value`)
  VALUES(column_name, data_type, column_type, IFNULL(@max_value, '0'));
  

3. 调试辅助 #

可以在循环内添加调试输出，确认游标是否在正常迭代：

read_loop:
LOOP
    FETCH cur INTO column_name, data_type, column_type;
    IF done THEN
        LEAVE read_loop;
    END IF;
    -- 添加调试输出，查看当前处理的列名
    SELECT '当前处理列：' AS debug, column_name;

    SET @sql_query = CONCAT('SELECT MAX(LENGTH(`', column_name, '`)) INTO @max_value FROM `', schema_name, '`.`', table_name, '`;');
    PREPARE stmt FROM @sql_query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    -- 查看@max_value的值
    SELECT '@max_value的值：' AS debug, @max_value;

    INSERT INTO `_tmp` (`column_name`, `data_type`, `column_type`, `max_value`) VALUES(column_name, data_type, column_type, IFNULL(@max_value, '0'));
END LOOP read_loop;

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