使用存储过程从另一表填充数据时触发1136列数不匹配错误

错误原因分析 #

Error Code: 1136 是典型的列数不匹配问题：

• vehicles_t 表仅包含7个字段，你在INSERT语句中指定了这7个目标列
• 但存储过程里用 SELECT * FROM temp_t 会返回temp_t的全部31个字段，两者数量不匹配，导致插入失败

另外你的LOAD DATA语句存在语法错误，末尾多了一个多余的单引号，会导致数据导入失败。

分步解决方案 #

1. 修正存储过程的SELECT语句 #

将SELECT *改为明确指定与vehicles_t列对应的字段，确保列数和顺序完全匹配：

DROP PROCEDURE IF EXISTS vehicles_p;

DELIMITER $$ 
CREATE PROCEDURE vehicles_p()
BEGIN
       INSERT INTO vehicles_t(
vehicle_maker,
vehicle_model,
vehicle_color,
vehicle_model_year,
vehicle_price,
quantity,
quarter_number            
) 
SELECT 
vehicle_maker,
vehicle_model,
vehicle_color,
vehicle_model_year,
vehicle_price,
quantity,
quarter_number
FROM temp_t;
END $$
DELIMITER ;

2. 修复LOAD DATA语句的语法错误 #

去掉末尾多余的单引号，确保数据能正常导入temp_t：

TRUNCATE temp_t;

LOAD DATA LOCAL INFILE "/Users/***/Downloads/new_wheels_sales_qtr_1.csv"
INTO TABLE temp_t
FIELDS TERMINATED by ','
OPTIONALLY ENCLOSED BY '"' 
LINES TERMINATED BY '\n'
IGNORE 1 LINES;

3. 处理主键重复问题（可选但必要） #

vehicles_t的主键是vehicle_maker，如果temp_t中存在多个相同品牌的记录，直接插入会触发主键冲突错误。可以根据需求选择两种处理方式：

• 方式一：去重插入，只保留每个品牌的一条记录（比如取第一条）：

-- 修改存储过程中的SELECT语句，添加去重逻辑
SELECT 
vehicle_maker,
vehicle_model,
vehicle_color,
vehicle_model_year,
vehicle_price,
quantity,
quarter_number
FROM temp_t
GROUP BY vehicle_maker; -- 按品牌分组去重

• 方式二：冲突时更新现有记录：

-- 修改INSERT语句，添加ON DUPLICATE KEY UPDATE
INSERT INTO vehicles_t(
vehicle_maker,
vehicle_model,
vehicle_color,
vehicle_model_year,
vehicle_price,
quantity,
quarter_number            
) 
SELECT 
vehicle_maker,
vehicle_model,
vehicle_color,
vehicle_model_year,
vehicle_price,
quantity,
quarter_number
FROM temp_t
ON DUPLICATE KEY UPDATE
vehicle_model = VALUES(vehicle_model),
vehicle_color = VALUES(vehicle_color),
vehicle_model_year = VALUES(vehicle_model_year),
vehicle_price = VALUES(vehicle_price),
quantity = VALUES(quantity),
quarter_number = VALUES(quarter_number);

验证步骤 #

1. 先执行修复后的LOAD DATA语句，确认temp_t成功导入数据
2. 重新创建存储过程
3. 执行call vehicles_p();，此时应该能正常插入数据

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