mysql - LOAD DATA INFILE 跳过记录

Question

我正在尝试将数据从 CSV 文件加载到 MySQL 数据库中，并注意到导入文件时似乎会跳过大量记录。

数据来自政府来源，格式非常奇怪，在不寻常的地方使用单引号等。以下是未插入记录的示例:

"'050441'","STANFORD HOSPITAL","CA","H_HSP_RATING_7_8","How do patients rate the hospital overall?","Patients who gave a rating of'7' or '8' (medium)","22","300 or more","37",""

但是，该记录确实被插入:

"'050441'","STANFORD HOSPITAL","CA","H_HSP_RATING_0_6","How do patients rate the hospital overall?","Patients who gave a rating of '6' or lower (low)","8","300 or more","37",""

我用来加载数据的 SQL 在这里:

mysql> load data infile "c:\\HQI_HOSP_HCAHPS_MSR.csv" into table hospital_qualit
y_scores fields terminated by "," enclosed by '"' lines terminated by "\n" IGNOR
E 1 LINES;

有人知道为什么会发生这种情况吗？似乎只有部分记录实际上被正确插入。

Answer 1

执行此处提到的失败记录的查询后。

mysql> load data ....;

执行下面的代码

mysql> show warnings; 

它会显示错误，为什么查询失败？并继续下一步来解决它。

我用你的数据做了一个小测试，效果很好:

CREATE TABLE `test2` (
  `id` int(11) NOT NULL,
  `no` varchar(20) NOT NULL,
  `name` varchar(20) NOT NULL,
  `country` varchar(20) NOT NULL,
  `rating` varchar(20) NOT NULL,
  `rate_desc1` varchar(100) NOT NULL,
  `rate_desc2` varchar(100) NOT NULL,
  `no1` int(11) NOT NULL,
  `desc3` varchar(20) NOT NULL,
  `no2` int(11) NOT NULL,
  `desc4` varchar(20) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1

/var/lib/mysql/test/test.csv 包含工作 n 个非工作记录:

1,"'050441'","斯坦福医院","CA","H_HSP_RATING_7_8","患者对医院的总体评价如何？","给出评分为 '7' 或 '8' 的患者 (中)","22","300 或更多","37",""2,"'050441'","斯坦福医院","CA","H_HSP_RATING_0_6","患者对医院的整体评价如何？","评分为 '6' 或更低(低)的患者","8","300 或更多","37",""

mysql> load data infile "test.csv" into table test2 fields terminated by "," enclosed by '"' lines terminated by "\n" IGNORE 1 LINES;

结果:查询正常，2 行受影响(0.05 秒)
记录:2 删除:0 跳过:0 警告:0

Conclusion: So, i think the difference is only in the datatypes of the columns n see the warnings genereated