有一个txt文件内容如下:
| 584658 | 0 | 'BLOCKID' | 10124 | 1 | 1309922849 |希望导入一个表中,其中'BLOCKID'对应的字段是enum类型,使用load data导入
load data local infile 'C:\\user_tags.txt' into table `user_tags` fields escaped by '' enclosed by '' terminated by '|' lines terminated by '\r\n' (`id`, `user_id`, `guest_id`, `guest_tag`, `tag_text`, `tag_count`, `stamp`)表如下:
CREATE TABLE `user_tags` (
`id` int(10) unsigned NOT NULL auto_increment,
`user_id` int(10) unsigned default NULL,
`guest_id` varchar(50) NOT NULL,
`guest_tag` enum('BLOCKID','USETYPE) NOT NULL,
`tag_text` varchar(50) NOT NULL,
`tag_count` int(10) unsigned NOT NULL,
`stamp` int(10) unsigned NOT NULL
)结果其他字段正常导入,guest_tag则不能导入,请大家想想办法。谢谢。还是过以下形式的数据,也都不行:
| 584658 | 0 | BLOCKID | 10124 | 1 | 1309922849 |
| 584658 | 0 | 1 | 10124 | 1 | 1309922849 |将guest_tag字段改为varchar类型则可以正常导入。
| 584658 | 0 | 'BLOCKID' | 10124 | 1 | 1309922849 |希望导入一个表中,其中'BLOCKID'对应的字段是enum类型,使用load data导入
load data local infile 'C:\\user_tags.txt' into table `user_tags` fields escaped by '' enclosed by '' terminated by '|' lines terminated by '\r\n' (`id`, `user_id`, `guest_id`, `guest_tag`, `tag_text`, `tag_count`, `stamp`)表如下:
CREATE TABLE `user_tags` (
`id` int(10) unsigned NOT NULL auto_increment,
`user_id` int(10) unsigned default NULL,
`guest_id` varchar(50) NOT NULL,
`guest_tag` enum('BLOCKID','USETYPE) NOT NULL,
`tag_text` varchar(50) NOT NULL,
`tag_count` int(10) unsigned NOT NULL,
`stamp` int(10) unsigned NOT NULL
)结果其他字段正常导入,guest_tag则不能导入,请大家想想办法。谢谢。还是过以下形式的数据,也都不行:
| 584658 | 0 | BLOCKID | 10124 | 1 | 1309922849 |
| 584658 | 0 | 1 | 10124 | 1 | 1309922849 |将guest_tag字段改为varchar类型则可以正常导入。
解决方案 »
- 数据库表是只读属性的
- 查询语句求助
- select语句的时间查看,能不能做到更精确
- mysql存储长文本问题
- mysql数据 创建用户分配权限出错 GRANT ALL PRIVILEGES ON *.* TO 'shsadmin'@'%'
- 求助!!MySQL语句
- mysql里面怎么写sql语句实现添加注释到字段上?前提是不晓得该字段类型
- php连接mysql出现了问题,帮忙解决一下.谢谢
- Linux下改变MySQL的数据库目录后启动不了???
- MySQL数据库使用何种JDBC DRIVER比较好?
- mysql中datetime格式(0000-00-00 00:00:00)如何加减?
- left join和right join的区别在哪里
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`user_id` INT(10) UNSIGNED DEFAULT NULL,
`guest_id` VARCHAR(50) NOT NULL,
`guest_tag` ENUM('BLOCKID','USETYPE') NOT NULL,
`tag_text` VARCHAR(50) NOT NULL,
`tag_count` INT(10) UNSIGNED NOT NULL,
`stamp` INT(10) UNSIGNED NOT NULL,KEY(id)
)LOAD DATA INFILE 'r:\\temp\\aa.txt' INTO TABLE `user_tags`
FIELDS
TERMINATED BY '|' (`user_id`, `guest_id`, `guest_tag`, `tag_text`, `tag_count`, `stamp`);
aa.txt:
584658|0|1|10124|1|1309922849ENUM以索引号导入
仔细看看别人的回复,
aa.txt:
584658|0|1|10124|1|1309922849另,最好测试 一下你自己建表SQL
584658|0|1|10124|1|1309922849
与
| 584658 | 0 | 1 | 10124 | 1 | 1309922849 |我前后多了|线,导致多出2例,但按规则,第一列对应到id自增段,被忽略了,最后一列多出,也被自动忽略了。事实也证明了这点,有无前后分割符,都成功的导入了数据。只有中间enum字段不正确。所以,只想着是不是因为前后多了分割符的话,方向都不对。
-> FIELDS
-> TERMINATED BY '|' (`user_id`, `guest_id`, `guest_tag`, `tag_text`, `tag_c
ount`, `stamp`);
Query OK, 1 row affected (0.03 sec)
Records: 1 Deleted: 0 Skipped: 0 Warnings: 0mysql> SELECT * FROM user_tags;
+----+---------+----------+-----------+----------+-----------+------------+
| id | user_id | guest_id | guest_tag | tag_text | tag_count | stamp |
+----+---------+----------+-----------+----------+-----------+------------+
| 20 | 584658 | 0 | BLOCKID | 10124 | 1 | 1309922849 |
+----+---------+----------+-----------+----------+-----------+------------+
1 row in set (0.00 sec)mysql>