用的命令没有问题
mysqlimport --fields-optionally-enclosed-by=""" --fields-terminated-by=, --lines-terminated-by="\r\n" -uroot -p database table.csv但是每次只能导入一条记录,然后得手动删除这条记录再运行命令时还是只能导入一条记录。不知道为什么,谁能给帮忙解决一下,谢谢
mysqlimport --fields-optionally-enclosed-by=""" --fields-terminated-by=, --lines-terminated-by="\r\n" -uroot -p database table.csv但是每次只能导入一条记录,然后得手动删除这条记录再运行命令时还是只能导入一条记录。不知道为什么,谁能给帮忙解决一下,谢谢
用二进制编辑器打开你的CSV内容,看每行结束时到底是些什么?不同操作系统产生的换行不太一致。有些是 0a0d,有些只有 0d, 还有些会出现 0d0a
我去掉了STRICT_TRANS_TABLES,因为插入的时候说Data truncated for column at row 1,那个列是integer的,值也是整数,出现这种错误,我网上查了说去掉STRICT_TRANS_TABLES,就可以,但去掉后只能插入一行。
-> (
-> id INT,
-> c1 varchar(20),
-> c2 date
-> );
Query OK, 0 rows affected (0.09 sec)
t1.csv
---------------------------
1,hhhhhh,2009-10-11
2,kusr,2009-10-11
3,avdd,2009-10-11C:\>debug t1.csv
-d
13C9:0100 31 2C 68 68 68 68 68 68-2C 32 30 30 39 2D 31 30 1,hhhhhh,2009-10
13C9:0110 2D 31 31 0D 0A 32 2C 6B-75 73 72 2C 32 30 30 39 -11..2,kusr,2009
13C9:0120 2D 31 30 2D 31 31 0D 0A-33 2C 61 76 64 64 2C 32 -10-11..3,avdd,2
13C9:0130 30 30 39 2D 31 30 2D 31-31 00 00 00 00 00 00 00 009-10-11.......
13C9:0140 00 00 00 00 00 00 00 00-00 00 00 00 00 00 00 00 ................
13C9:0150 00 00 00 00 00 00 00 00-00 00 00 00 00 00 00 00 ................
13C9:0160 00 00 00 00 00 00 00 00-00 00 00 00 00 00 00 00 ................
13C9:0170 00 00 00 00 00 00 00 00-00 00 00 00 00 00 00 00 ................
C:\Program Files\MySQL\MySQL Server 5.1\bin>mysqlimport --fields-terminated-by=,
--lines-terminated-by="\r\n" -uroot -pppwwe test t1.csv
test.t1: Records: 3 Deleted: 0 Skipped: 0 Warnings: 0mysql> select * from t1;
+------+--------+------------+
| id | c1 | c2 |
+------+--------+------------+
| 1 | hhhhhh | 2009-10-11 |
| 2 | kusr | 2009-10-11 |
| 3 | avdd | 2009-10-11 |
+------+--------+------------+
3 rows in set (0.00 sec)[/code]