MySQL LOAD DATA LOCAL INFILE 只导入一行
我们有一个包含数千条记录的 CSV 文件.我想通过 phpmyadmin 将这些行导入 MySQL 表中.这是使用的命令:
We have a CSV file with thousands of records in it. I want to import these rows into a MySQL table via phpmyadmin. here is the command used:
load data local infile '/var/www/html/deansgrads_201280.csv'
into table ttu_nameslist
fields terminated by ','
enclosed by '"'
lines terminated by '
'
(firstname, middlename, lastname, city, county, state, termcode, category)
表中有一个 ID 字段设置为自动递增.当我们执行这个 SQL 时，只有第一行被导入到表中.
There is an ID field in the table that is set to auto-increment. When we execute this SQL only the first line is imported into the table.
输入数据文件行:
"Aaron","Al","Brockery","Cookeville","Putnam","TN","201280","deanslist"
"Aaron","Dan","Mickel","Lebanon","Wilson","TN","201280","deanslist"
表结构:
CREATE TABLE `ttu_nameslist` (
`id` int(11) NOT NULL,
`firstname` varchar(50) NOT NULL,
`middlename` varchar(50) NOT NULL,
`lastname` varchar(50) NOT NULL,
`city` varchar(50) NOT NULL,
`county` varchar(50) NOT NULL,
`state` varchar(2) NOT NULL,
`termcode` varchar(6) NOT NULL,
`category` varchar(10) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
我做错了什么，为什么添加一行后就退出了?
What am I doing wrong why does it quit after adding one row?
推荐答案
你说 ID 字段有 AUTO_INCREMENT 属性，但在 CREATE TABLE 语句.这是问题的一部分.
You say that the ID field has the AUTO_INCREMENT attribute, but there's no mention of it in the CREATE TABLE statement. This is part of the problem.
另一部分是那些截断警告.CSV 文件中的某些行可能包含太长而无法放入列的数据.将这些文本列的大小增加到更大的值(假设为 200)，然后重试.
The other part is those truncation warnings. Some of the rows in the CSV file probably contain data that is too long to fit inside the columns. Increase the size of those text columns to a bigger value (let's say 200) and try again.
您绝对确定 CSV 文件有效吗?(又名每行具有相同数量的值等).您可能应该检查这些字符串是否包含逗号 (,)，尽管这应该不是问题.
Are you absolutely sure that the CSV file is valid ? (a.k.a. each row has the same number of values etc.). You should probably check if those strings contain commas (,), although that shouldn't be an issue.
相关文章