MySQL执行LOAD DATA INFILE无响应未导入CSV数据问题求助

问题排查与解决步骤 #

• 1. 跳过CSV表头行
     你当前的导入语句未跳过第一行的字段说明行，该行数据类型和表字段不匹配会导致整行/批量导入失败，修改语句增加IGNORE 1 LINES参数：

LOAD DATA LOCAL INFILE 'C:\\Users\\Usuario\\Desktop\\CovidDeaths.csv' 
INTO TABLE coviddeaths
COLUMNS TERMINATED BY ';'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES; -- 新增这行跳过表头

• 2. 适配日期格式与多余分隔符
     你的CSV中日期为DD/MM/YYYY格式，和MySQL默认的YYYY-MM-DD日期格式不兼容，需要增加格式转换逻辑；同时你提供的CSV样例每行末尾多了一个分号，会产生多余的空字段，需要用临时变量接收忽略，修改语句如下（字段顺序需和CSV列顺序一致）：

LOAD DATA LOCAL INFILE 'C:\\Users\\Usuario\\Desktop\\CovidDeaths.csv' 
INTO TABLE coviddeaths
COLUMNS TERMINATED BY ';'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(iso_code,continent,location,@date,population,total_cases,new_cases,new_cases_smoothed,total_deaths,new_deaths,new_deaths_smoothed,total_cases_per_million,new_cases_per_million,new_cases_smoothed_per_million,total_deaths_per_million,new_deaths_per_million,new_deaths_smoothed_per_million,reproduction_rate,icu_patients,icu_patients_per_million,hosp_patients,hosp_patients_per_million,weekly_icu_admissions,weekly_icu_admissions_per_million,weekly_hosp_admissions,weekly_hosp_admissions_per_million, @dummy) 
SET date = STR_TO_DATE(@date, '%d/%m/%Y');

• 3. 检查local_infile权限开启状态
     执行以下语句确认本地文件导入权限已开启：

-- 检查权限状态，结果需为ON
SHOW VARIABLES LIKE 'local_infile';
-- 若为OFF执行开启命令
SET GLOBAL local_infile = 1;

如果使用图形化连接工具（如MySQL Workbench、Navicat），需要在连接配置的高级参数中添加OPT_LOCAL_INFILE=1，重启连接后再执行导入。

• 4. 查看导入警告信息
     每次执行完LOAD DATA语句后，立即执行以下命令查看具体的导入异常提示，可精确定位字段不匹配、值越界、主键重复等问题：

SHOW WARNINGS;

• 5. 重复数据处理
     你之前已通过向导导入了11312条数据，如果表中有主键/唯一键约束，重复的行导入时会被直接跳过，你可以选择先清空原有数据后全量导入，或者增加IGNORE参数跳过重复行：

-- 增加IGNORE参数跳过重复行
LOAD DATA LOCAL INFILE 'C:\\Users\\Usuario\\Desktop\\CovidDeaths.csv' 
IGNORE INTO TABLE coviddeaths
COLUMNS TERMINATED BY ';'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(iso_code,continent,location,@date,population,total_cases,new_cases,new_cases_smoothed,total_deaths,new_deaths,new_deaths_smoothed,total_cases_per_million,new_cases_per_million,new_cases_smoothed_per_million,total_deaths_per_million,new_deaths_per_million,new_deaths_smoothed_per_million,reproduction_rate,icu_patients,icu_patients_per_million,hosp_patients,hosp_patients_per_million,weekly_icu_admissions,weekly_icu_admissions_per_million,weekly_hosp_admissions,weekly_hosp_admissions_per_million, @dummy)
SET date = STR_TO_DATE(@date, '%d/%m/%Y');

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