你的版本是多少?我用5.0.21试了一下没有出现这样的问题,或者你加上fields terminated by enclosed by lines terminated by 试试看能不能解决这样的问题。
mysql> desc aa;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)我的sql.txt 文件内容如下:"1","王小二"
"2",""
"3",""
"4","张三"mysql> load data infile "c:\\sql.txt" into table aa fields terminated by ',' enc
losed by '"' lines terminated by '\r\n';
Query OK, 4 rows affected (0.00 sec)
Records: 4 Deleted: 0 Skipped: 0 Warnings: 0mysql> select * from aa;
+----+--------+
| id | name |
+----+--------+
| 1 | 王小二 |
| 2 | NULL |
| 3 | NULL |
| 4 | 张三 |
+----+--------+
4 rows in set (0.00 sec)
mysql> desc aa;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)我的sql.txt 文件内容如下:"1","王小二"
"2",""
"3",""
"4","张三"mysql> load data infile "c:\\sql.txt" into table aa fields terminated by ',' enc
losed by '"' lines terminated by '\r\n';
Query OK, 4 rows affected (0.00 sec)
Records: 4 Deleted: 0 Skipped: 0 Warnings: 0mysql> select * from aa;
+----+--------+
| id | name |
+----+--------+
| 1 | 王小二 |
| 2 | NULL |
| 3 | NULL |
| 4 | 张三 |
+----+--------+
4 rows in set (0.00 sec)
mysql> desc aa;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)我的sql.txt 文件内容如下:"1","王小二"
"2",""
"3",""
"4","张三"mysql> load data infile "c:\\sql.txt" into table aa fields terminated by ',' enc
losed by '"' lines terminated by '\r\n';
Query OK, 4 rows affected (0.00 sec)
Records: 4 Deleted: 0 Skipped: 0 Warnings: 0mysql> select * from aa;
+----+--------+
| id | name |
+----+--------+
| 1 | 王小二 |
| 2 | NULL |
| 3 | NULL |
| 4 | 张三 |
+----+--------+
4 rows in set (0.00 sec)