执行MySQL LOAD DATA INFILE后数据表仍为空的问题求助

问题背景 #

执行以下MySQL代码导入FDA 2022年Q4的ASCII格式FAERS数据，语句无报错且LOAD DATA INFILE提示成功，但查询faersmedinfo.demo22q4时表内无数据。已确认数据文件未损坏，字段与行分隔符设置看似正确。

执行的SQL代码 #

CREATE TABLE IF NOT EXISTS `demo22q4`(
  `primaryid` INT NOT NULL,
  `caseid` VARCHAR(20) NOT NULL,
  `caseversion` INT NOT NULL,
  `i_f_code` VARCHAR(1) NOT NULL,
  `event_dt` VARCHAR(20),
  `mfr_dt` VARCHAR(20),
  `init_fda_dt` VARCHAR(20),
  `fda_dt` VARCHAR(20),
  `rept_cod` VARCHAR(20),
  `auth_num` VARCHAR(20),
  `mfr_num` VARCHAR(100),
  `mfr_sndr` VARCHAR(100),
  `lit_ref` VARCHAR(100),
  `age` INT,
  `age_cod` VARCHAR(10),
  `age_grp` VARCHAR(5),
  `sex` VARCHAR(5),
  `e_sub` VARCHAR(5),
  `wt` INT NULL DEFAULT NULL,
  `wt_cod` VARCHAR(10),
  `rept_dt` VARCHAR(20),
  `to_mfr` VARCHAR(5),
  `occp_cod` VARCHAR(5),
  `reporter_country` VARCHAR(50),
  `occr_country` VARCHAR(50)
);
LOAD DATA INFILE 'C:\\ProgramData\\MySQL\\MySQL Server 8.0\\Uploads\\DEMO22Q4.txt'
INTO TABLE demo22q4
FIELDS TERMINATED BY "$"
LINES TERMINATED BY "\r\n"
IGNORE 1 ROWS;

SELECT * FROM faersmedinfo.demo22q4;

数据文件样例（前三行） #

表头：primaryid$caseid$caseversion$i_f_code$event_dt$mfr_dt$init_fda_dt$fda_dt$rept_cod$auth_num$mfr_num$mfr_sndr$lit_ref$age$age_cod$age_grp$sex$e_sub$wt$wt_cod$rept_dt$to_mfr$occp_cod$reporter_country$occr_country
行1：100115733$10011573$3$F$20130801$20221118$20140314$20221123$EXP$$CA-MERCK-1403CAN005674$MERCK$$75$YR$$M$Y$58.05$KG$20221123$$HP$CA$CA
行2：1002130537$10021305$37$F$20210415$20220928$20140319$20221005$EXP$$PHHY2014CA019281$NOVARTIS$$43$YR$$M$Y$$$20221005$$HP$CA$CA
行3：100234223$10023422$3$F$20120301$20221003$20140320$20221007$EXP$$US-ROCHE-1362929$ROCHE$$67$YR$$F$Y$$$20221007$$LW$US$

排查方向及解决方法 #

1. 数据类型不匹配导致整行被过滤 #

数据样例中wt字段的行1值为58.05，但表结构将wt定义为INT类型，若MySQL开启了严格模式（sql_mode包含STRICT_TRANS_TABLES或STRICT_ALL_TABLES），浮点数转整数会触发错误，导致整行数据被拒绝插入。

• 验证：执行SELECT @@sql_mode;查看当前模式，确认是否包含严格模式。
• 解决：修改wt字段类型为支持小数的格式，重新导入：

  ALTER TABLE demo22q4 MODIFY COLUMN wt DECIMAL(10,2) NULL DEFAULT NULL;
  

2. 行分隔符设置错误 #

Windows系统文本行分隔符通常为\r\n，但部分工具生成的文件可能使用Unix格式的\n，导致MySQL无法正确识别行边界。

• 验证：用Notepad++打开数据文件，查看右下角的换行格式标识（CRLF为Windows格式，LF为Unix格式）。
• 解决：根据实际格式修改LOAD DATA的行分隔符：

  LOAD DATA INFILE 'C:\\ProgramData\\MySQL\\MySQL Server 8.0\\Uploads\\DEMO22Q4.txt'
  INTO TABLE demo22q4
  FIELDS TERMINATED BY "$"
  LINES TERMINATED BY "\n"
  IGNORE 1 ROWS;
  

3. 数据库上下文错误，数据导入到其他库 #

LOAD DATA若未指定库名，会默认导入到当前连接的数据库中，而查询时指定了faersmedinfo.demo22q4，可能存在数据导入到其他库同名表的情况。

• 验证：执行USE faersmedinfo; SELECT COUNT(*) FROM demo22q4;查看目标库表数据量，同时检查其他库是否存在同名表。
• 解决：导入时明确指定库名：

  LOAD DATA INFILE 'C:\\ProgramData\\MySQL\\MySQL Server 8.0\\Uploads\\DEMO22Q4.txt'
  INTO TABLE faersmedinfo.demo22q4
  FIELDS TERMINATED BY "$"
  LINES TERMINATED BY "\r\n"
  IGNORE 1 ROWS;
  

4. 文件权限或路径隐含问题 #

虽然提示导入成功，但MySQL服务账号可能没有读取文件的权限，或文件路径存在隐性错误（如转义符问题）。

• 验证：查看MySQL错误日志（通常在C:\ProgramData\MySQL\MySQL Server 8.0\Data目录下的.err文件），检查是否有权限或文件不存在的提示；同时确认数据文件大小非空。
• 解决：确保MySQL服务运行账号对Uploads目录有读取权限，或更换文件到权限开放的目录，同时确认路径转义符\\书写正确。

5. 字段映射隐式错误 #

即使字段数量匹配，隐式字段映射可能出现错位，导致数据无法正确插入。

• 解决：在LOAD DATA中明确指定字段映射列表，避免隐式匹配错误：

  LOAD DATA INFILE 'C:\\ProgramData\\MySQL\\MySQL Server 8.0\\Uploads\\DEMO22Q4.txt'
  INTO TABLE faersmedinfo.demo22q4
  FIELDS TERMINATED BY "$"
  LINES TERMINATED BY "\r\n"
  IGNORE 1 ROWS
  (primaryid, caseid, caseversion, i_f_code, event_dt, mfr_dt, init_fda_dt, fda_dt, rept_cod, auth_num, mfr_num, mfr_sndr, lit_ref, age, age_cod, age_grp, sex, e_sub, wt, wt_cod, rept_dt, to_mfr, occp_cod, reporter_country, occr_country);
  

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