• 全部
  • 问答

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

yhcnux 2006-05-24 07:40:41
我用mysql的load data infile语句将一个文本中的数据整体插入到数据库中,但是原来为空的值进入数据库中却不是null,而是当数据类型为float或整型的时候,为0,varchar是空串。我想把为空的文本插为null,怎么做?
另外说一句,列都是允许为null值的,而且默认值是null
谢谢
...全文
196 点赞 收藏 2
写回复
2 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
hy2003fly 2006-05-24
你的版本是多少?我用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 2006-05-24
你的版本是多少?我用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)
回复
相关推荐
发帖
MySQL
创建于2007-09-28

5.5w+

社区成员

MySQL相关内容讨论专区
申请成为版主
帖子事件
创建了帖子
2006-05-24 07:40
社区公告
暂无公告