ITPub博客

首页 > 数据库 > 数据库开发技术 > 使用文本导入/导出数据

使用文本导入/导出数据

原创 数据库开发技术 作者:mckobe23 时间:2007-06-03 15:02:06 0 删除 编辑

制造大量数据时使用文本文件导入导出可以大大提升工作效率.

表USERS

mysql> select * from users ;

+----+-------+---------+------+
| id | name | address | flag |
+----+-------+---------+------+
| 1 | named | beijing | 1 |
| 2 | named | beijing | 2 |
| 3 | named | beijing | 3 |
| 4 | named | beijing | 4 |
| 5 | named | beijing | 5 |
| 6 | named | beijing | 6 |
| 7 | named | beijing | 7 |
| 8 | named | beijing | 8 |
| 9 | named | beijing | 9 |
| 10 | named | beijing | 10 |
+----+-------+---------+------+
10 rows in set (0.00 sec)


一 导出数据

1.mysql > select id,name,address from users limit 5 into outfile '/data/users.txt' fields terminated by ',' OPTIONALLY ENCLOSED BY '"'  LINES TERMINATED BY '\n';

Query OK, 10 rows affected (0.00 sec)

2.# more /data/users.txt

1,named,beijing
2,named,beijing
3,named,beijing
4,named,beijing
5,named,beijing


二 导入数据

1.mysql > load data infile '/data/users.txt' into table user1 fields terminated by ',' lines terminated by ' ' (id,name,address) OPTIONALLY ENCLOSED BY '"'  LINES TERMINATED BY '\n';

Query OK, 10 rows affected (0.00 sec)
Records: 10 Deleted: 0 Skipped: 0 Warnings: 0

2.mysql> select * from user1;
+----+-------+---------+------+
| id | name | address | flag |
+----+-------+---------+------+
| 1 | named | beijing | NULL |
| 2 | named | beijing | NULL |
| 3 | named | beijing | NULL |
| 4 | named | beijing | NULL |
| 5 | named | beijing | NULL |
+----+-------+---------+------+
10 rows in set (0.00 sec)

[@more@]

FIELDS TERMINATED BY 大多使用逗号( , ) 竖线( | )分割.

注:导入导出时FIELDS TERMINATED BY 要统一.

来自 “ ITPUB博客 ” ,链接:https://blog.itpub.net/9183234/viewspace-917213/ ,如需转载,请注明出处,否则将追究法律责任。

上一篇: 数据库导入/导出
下一篇: 清除日志文件
请登录后发表评论 登录
全部评论

注册时间:2009-06-12

  • 博文量
    40
  • 访问量
    587271