CSV导入MySQL表时如何将NA值替换为NULL并过滤主键为NA的行

解决方法 #

第一步：先确认MAMP环境配置 #

先执行SQL语句查看本地文件导入权限是否开启：

SHOW GLOBAL VARIABLES LIKE 'local_infile';

如果返回值为OFF，执行以下语句开启：

SET GLOBAL local_infile = 1;

如果是用命令行登录MySQL导入，登录时加上参数：

mysql -u 用户名 -p --local-infile=1

第二步：修改LOAD DATA导入语句 #

你可以通过变量接收CSV字段、搭配NULLIF函数转换NA值、加WHERE条件过滤无效行，修改后的示例如下：

LOAD DATA LOCAL INFILE 'filename.csv' 
INTO TABLE table_name 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"' 
IGNORE 1 ROWS
-- 按你CSV的列顺序、对应表字段自定义临时变量，数量和列数保持一致
(@primary_key, @int_col1, @int_col2, @str_col)
SET
  -- 主键字段把NA转成NULL，后面过滤用
  primary_key = NULLIF(TRIM(@primary_key), 'NA'),
  -- 其他int字段统一把NA转成NULL
  int_col1 = NULLIF(TRIM(@int_col1), 'NA'),
  int_col2 = NULLIF(TRIM(@int_col2), 'NA'),
  -- 字符串字段按需赋值，不需要转NA就直接写
  str_col = @str_col
-- 过滤掉主键为NA的行，避免无效数据导入
WHERE primary_key IS NOT NULL;

注意事项 #

• 临时变量的顺序必须和CSV的列顺序完全对应，不需要入库的列可以用@dummy这类变量接收后不做赋值处理即可
• 加TRIM是为了避免CSV里的NA前后带空格导致匹配失败，如果你确定CSV里的NA没有多余空格可以去掉
• 如果是用MAMP自带的phpMyAdmin导入，要确认phpMyAdmin配置文件中相关执行时长、权限参数配置足够，避免导入超时或权限报错。

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