mysql中load infile的时候怎么把空串以null值插入？

[yhcnux]

我用mysql的load data infile语句将一个文本中的数据整体插入到数据库中，但是原来为空的值进入数据库中却不是null，而是当数据类型为float或整型的时候，为0，varchar是空串。我想把为空的文本插为null，怎么做？
另外说一句，列都是允许为null值的，而且默认值是null
谢谢

[hy2003fly]

你的版本是多少？我用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: 0

mysql> select * from aa;
+----+--------+
| id | name |
+----+--------+
| 1 | 王小二 |
| 2 | NULL |
| 3 | NULL |
| 4 | 张三 |
+----+--------+
4 rows in set (0.00 sec)

[hy2003fly]

你的版本是多少？我用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: 0

mysql> select * from aa;
+----+--------+
| id | name |
+----+--------+
| 1 | 王小二 |
| 2 | NULL |
| 3 | NULL |
| 4 | 张三 |
+----+--------+
4 rows in set (0.00 sec)