带有加载数据 infile 的 MySQL 导入 CSV 文件缺少备用行

Question

我正在使用以下命令从 CSV 文件导入数据。

load data infile  'l:\health_card.csv'
into table health_card
fields terminated by ','
enclosed by '"'
LINES TERMINATED BY '\n'
ignore 1 lines
( @date,  health_card_number, patient_name,age,street_address,mobile,@birth_date,@anniversary,card_rate,card_amount,relation_name)
set date = STR_TO_DATE(@date, '%m.%d.%Y'),
birth_date = STR_TO_DATE(@birth_date, '%m.%d.%Y'),
anniversary = STR_TO_DATE(@anniversary, '%m.%d.%Y')

建表语句是这样的:

CREATE TABLE `health_card` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `health_card_number` varchar(30) DEFAULT NULL,
 `patient_name` varchar(50) DEFAULT NULL,
 `general_regn_number` varchar(30) DEFAULT NULL,
 `date` date DEFAULT NULL,
 `relation_name` varchar(300) DEFAULT NULL,
 `relation` varchar(30) DEFAULT NULL,
 `age` int(11) DEFAULT NULL,
 `birth_date` date DEFAULT NULL,
 `anniversary` date DEFAULT NULL,
 `address_relation` varchar(256) DEFAULT NULL,
 `street_address` varchar(50) DEFAULT NULL,
 `village` varchar(50) DEFAULT NULL,
 `city` varchar(30) DEFAULT NULL,
 `state` varchar(30) DEFAULT NULL,
 `pincode` mediumint(9) DEFAULT NULL,
 `email` varchar(50) DEFAULT NULL,
 `mobile` varchar(15) DEFAULT NULL,
 `card_rate` decimal(10,0) DEFAULT NULL,
 `card_amount` decimal(10,0) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `patient_name` (`patient_name`)
) ENGINE=InnoDB AUTO_INCREMENT=513 DEFAULT CHARSET=utf8

导入文件的示例数据如下:

"date","health_card_number","patient_name","age","street_address","mobile","birth_date","anniversary","card_rate","card_amount","relation_name"
"28.10.14","2821","Shyamsundar Agrawal","63","Khetrajpur, SBP","9438625411","","","100","100","W- Kantabai , S- Amit, D/L- Vinata, G/S- Harsh, G/D- Khusi"
"28.10.14","2822","Bisakha Kharsel","62","Fasimal, SBP","9777310693","05.01.1952","08.03.1970","100","100","H- Gulbadan, S-Bipra, D/L- Sulochana, G/S- Rajkumar, G/D-Jhili"
"29.10.14","2823","Upendra Prasad Gupta","39","Baraipali,SBP","9861358110","04.01.1973","","100","100","W- Reena Devi, F- Hira Prasad Gupta, M- Shanti Devi, B- Rajendra, B- Mukesh"
"29.10.14","2824","Kamaljit Singh","34","Baraipali, SBP","9861018383","04.08.1980","07.02.2000","100","100","W- Navneet KAUR, F- Sikandar Singh, M-Kuldeep, S- Jaspreet, D-Gurpreet"

数据正在导入，但它只导入交替行。

我做错了什么？

Answer 1

确定找到了解决方案。遇到同样问题的任何人都会发现它很有帮助。

改变以这样结尾的行，所有行都被正确导入。

LINES TERMINATED BY '\r\n'