MySQL触发器手动测试正常但CSV导入后失效问题排查

问题背景 #

你创建了作用于categories表的link_with_restaurant BEFORE INSERT触发器，意图通过restaurants表的唯一name字段自动填充外键restaurant_id，手动插入数据时触发器正常工作，但通过CSV导入数据时完全失效。你尝试在触发器中添加trim()处理restaurant_name但无效果，CSV格式本身较为简单。

触发器代码如下：

create trigger `link_with_restaurant` before insert on `categories`
for each row begin
    declare uid text;
    select id into uid from restaurants where name=NEW.restaurant_name;
    set NEW.restaurant_id = uid;
end;

可能的原因与解决办法 #

1. CSV包含不可见空白字符（非半角空格） #

trim()仅能去除字符串首尾的半角空格（CHAR(32)），但CSV文件中可能存在其他不可见空白字符，比如制表符（CHAR(9)）、换行符（CHAR(10)）、回车符（CHAR(13)）或全角空格，导致restaurant_name与restaurants表中的name无法匹配。

修改触发器，添加对这类字符的清理：

create trigger `link_with_restaurant` before insert on `categories`
for each row begin
    declare uid text;
    -- 清理常见不可见空白字符与全角空格
    set NEW.restaurant_name = REPLACE(REPLACE(REPLACE(NEW.restaurant_name, CHAR(9), ''), CHAR(10), ''), CHAR(13), '');
    set NEW.restaurant_name = REPLACE(NEW.restaurant_name, '　', ''); -- 替换全角空格
    set NEW.restaurant_name = TRIM(NEW.restaurant_name); -- 最后清理半角空格
    select id into uid from restaurants where name=NEW.restaurant_name;
    set NEW.restaurant_id = uid;
end;

如果你的MySQL版本支持REGEXP_REPLACE（8.0及以上），可以用更简洁的写法：

set NEW.restaurant_name = TRIM(REGEXP_REPLACE(NEW.restaurant_name, '[[:space:]]', ''));

2. 导入时的字符集不匹配 #

如果CSV文件的字符集与数据库/表的字符集不一致，会导致restaurant_name出现乱码或隐形字符，进而匹配失败。

使用LOAD DATA INFILE导入时显式指定字符集：

LOAD DATA INFILE '/path/to/your/file.csv'
INTO TABLE categories
CHARACTER SET utf8mb4 -- 与你的表字符集保持一致
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS; -- 如果CSV有表头，添加此行

如果使用可视化工具（如phpMyAdmin、Navicat）导入，在导入设置中确认字符集选项与表字符集一致。

3. 触发器未处理匹配失败的场景 #

如果CSV中存在restaurants表中不存在的restaurant_name，uid会被设为NULL，导致restaurant_id为空，看起来像是触发器失效。添加错误提示可以快速定位问题：

create trigger `link_with_restaurant` before insert on `categories`
for each row begin
    declare uid text;
    set NEW.restaurant_name = TRIM(REGEXP_REPLACE(NEW.restaurant_name, '[[:space:]]', ''));
    select id into uid from restaurants where name=NEW.restaurant_name;
    
    -- 匹配失败时抛出错误，便于排查具体数据
    IF uid IS NULL THEN
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = CONCAT('无法找到对应餐厅：', NEW.restaurant_name);
    END IF;
    
    set NEW.restaurant_id = uid;
end;

4. 导入时的字段映射错误 #

确认导入工具是否正确将CSV列映射到categories表的对应字段。如果列顺序不匹配，可能会将其他列的值传入restaurant_name字段，导致匹配失败。例如CSV的第一列是category_name，但导入时被映射到restaurant_name，自然无法找到对应餐厅。

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