首页 / MYSQL / mysql命令行导入和导出数据
mysql命令行导入和导出数据
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了mysql命令行导入和导出数据,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含6980字,纯文字阅读大概需要10分钟。
内容图文
![mysql命令行导入和导出数据](/upload/InfoBanner/zyjiaocheng/475/3eb74d1014704bbe9150115ae611b4ea.jpg)
- select * from test_info
- into outfile ‘/tmp/test.csv‘
- fields terminated by ‘,‘ optionally enclosed by ‘"‘ escaped by ‘"‘
- lines terminated by ‘\r\n‘;
select * from test_info into outfile ‘/tmp/test.csv‘ fields terminated by ‘,‘ optionally enclosed by ‘"‘ escaped by ‘"‘ lines terminated by ‘\r\n‘;
MySQL中导入CSV格式数据的SQL语句样本如下:
Sql代码- load data infile ‘/tmp/test.csv‘
- into table test_info
- fields terminated by ‘,‘ optionally enclosed by ‘"‘ escaped by ‘"‘
- lines terminated by ‘\r\n‘;
- load data infile ‘/tmp/test.csv‘
- into table test_info
- fields terminated by ‘,‘ optionally enclosed by ‘"‘ escaped by ‘"‘
- lines terminated by ‘\r\n‘;
load data infile ‘/tmp/test.csv‘ into table test_info fields terminated by ‘,‘ optionally enclosed by ‘"‘ escaped by ‘"‘ lines terminated by ‘\r\n‘;
里面最关键的部分就是格式参数
Sql代码- fields terminated by ‘,‘ optionally enclosed by ‘"‘ escaped by ‘"‘
- lines terminated by ‘\r\n‘
- fields terminated by ‘,‘ optionally enclosed by ‘"‘ escaped by ‘"‘
- lines terminated by ‘\r\n‘
fields terminated by ‘,‘ optionally enclosed by ‘"‘ escaped by ‘"‘ lines terminated by ‘\r\n‘
这个参数是根据RFC4180文档设置的,该文档全称Common Format and MIME Type for Comma-Separated Values (CSV) Files,其中详细描述了CSV格式,其要点包括:
(1)字段之间以逗号分隔,数据行之间以\r\n分隔;
(2)字符串以半角双引号包围,字符串本身的双引号用两个双引号表示。
文件:test_csv.sql
Sql代码- use test;
- create table test_info (
- id integer not null,
- content varchar(64) not null,
- primary key (id)
- );
- delete from test_info;
- insert into test_info values (2010, ‘hello, line
- suped
- seped
- "
- end‘
- );
- select * from test_info;
- select * from test_info into outfile ‘/tmp/test.csv‘ fields terminated by ‘,‘ optionally enclosed by ‘"‘ escaped by ‘"‘ lines terminated by ‘\r\n‘;
- delete from test_info;
- load data infile ‘/tmp/test.csv‘ into table test_info fields terminated by ‘,‘ optionally enclosed by ‘"‘ escaped by ‘"‘ lines terminated by ‘\r\n‘;
- select * from test_info;
- use test;
- create table test_info (
- id integer not null,
- content varchar(64) not null,
- primary key (id)
- );
- delete from test_info;
- insert into test_info values (2010, ‘hello, line
- suped
- seped
- "
- end‘
- );
- select * from test_info;
- select * from test_info into outfile ‘/tmp/test.csv‘ fields terminated by ‘,‘ optionally enclosed by ‘"‘ escaped by ‘"‘ lines terminated by ‘\r\n‘;
- delete from test_info;
- load data infile ‘/tmp/test.csv‘ into table test_info fields terminated by ‘,‘ optionally enclosed by ‘"‘ escaped by ‘"‘ lines terminated by ‘\r\n‘;
- select * from test_info;
use test; create table test_info ( id integer not null, content varchar(64) not null, primary key (id) ); delete from test_info; insert into test_info values (2010, ‘hello, line suped seped " end‘ ); select * from test_info; select * from test_info into outfile ‘/tmp/test.csv‘ fields terminated by ‘,‘ optionally enclosed by ‘"‘ escaped by ‘"‘ lines terminated by ‘\r\n‘; delete from test_info; load data infile ‘/tmp/test.csv‘ into table test_info fields terminated by ‘,‘ optionally enclosed by ‘"‘ escaped by ‘"‘ lines terminated by ‘\r\n‘; select * from test_info;
文件:test.csv
Text代码- 2010,"hello, line
- suped
- seped
- ""
- end"
- 2010,"hello, line
- suped
- seped
- ""
- end"
2010,"hello, line suped seped "" end"
在Linux下如果经常要进行这样的导入导出操作,当然最好与Shell脚本结合起来,为了避免每次都要写格式参数,可以把这个串保存在变量中,如下所示:(文件mysql.sh)
Bash代码- #!/bin/sh
- # Copyright (c) 2010 codingstandards. All rights reserved.
- # file: mysql.sh
- # description: Bash中操作MySQL数据库
- # license: LGPL
- # author: codingstandards
- # email: codingstandards@gmail.com
- # version: 1.0
- # date: 2010.02.28
- # MySQL中导入导出数据时,使用CSV格式时的命令行参数
- # 在导出数据时使用:select ... from ... [where ...] into outfile ‘/tmp/data.csv‘ $MYSQL_CSV_FORMAT;
- # 在导入数据时使用:load data infile ‘/tmp/data.csv‘ into table ... $MYSQL_CSV_FORMAT;
- # CSV标准文档:RFC 4180
- MYSQL_CSV_FORMAT="fields terminated by ‘,‘ optionally enclosed by ‘\"‘ escaped by ‘\"‘ lines terminated by ‘\r\n‘"
#!/bin/sh # Copyright (c) 2010 codingstandards. All rights reserved. # file: mysql.sh # description: Bash中操作MySQL数据库 # license: LGPL # author: codingstandards # email: codingstandards@gmail.com # version: 1.0 # date: 2010.02.28 # MySQL中导入导出数据时,使用CSV格式时的命令行参数 # 在导出数据时使用:select ... from ... [where ...] into outfile ‘/tmp/data.csv‘ $MYSQL_CSV_FORMAT; # 在导入数据时使用:load data infile ‘/tmp/data.csv‘ into table ... $MYSQL_CSV_FORMAT; # CSV标准文档:RFC 4180 MYSQL_CSV_FORMAT="fields terminated by ‘,‘ optionally enclosed by ‘\"‘ escaped by ‘\"‘ lines terminated by ‘\r\n‘"
使用示例如下:(文件test_mysql_csv.sh)
Bash代码- #!/bin/sh
- . /opt/shtools/commons/mysql.sh
- # MYSQL_CSV_FORMAT="fields terminated by ‘,‘ optionally enclosed by ‘\"‘ escaped by ‘\"‘ lines terminated by ‘\r\n‘"
- echo "MYSQL_CSV_FORMAT=$MYSQL_CSV_FORMAT"
- rm /tmp/test.csv
- mysql -p --default-character-set=gbk -t --verbose test <<EOF
- use test;
- create table if not exists test_info (
- id integer not null,
- content varchar(64) not null,
- primary key (id)
- );
- delete from test_info;
- insert into test_info values (2010, ‘hello, line
- suped
- seped
- "
- end‘
- );
- select * from test_info;
- -- select * from test_info into outfile ‘/tmp/test.csv‘ fields terminated by ‘,‘ optionally enclosed by ‘"‘ escaped by ‘"‘ lines terminated by ‘\r\n‘;
- select * from test_info into outfile ‘/tmp/test.csv‘ $MYSQL_CSV_FORMAT;
- delete from test_info;
- -- load data infile ‘/tmp/test.csv‘ into table test_info fields terminated by ‘,‘ optionally enclosed by ‘"‘ escaped by ‘"‘ lines terminated by ‘\r\n‘;
- load data infile ‘/tmp/test.csv‘ into table test_info $MYSQL_CSV_FORMAT;
- select * from test_info;
- EOF
- echo "===== content in /tmp/test.csv ====="
- cat /tmp/test.csv
转自:http://blog.csdn.net/sara_yhl/article/details/6850107
mysql命令行导入和导出数据
标签:ring nal highlight sdn linu images clipboard null class
本文系统来源:http://www.cnblogs.com/nizuimeiabc1/p/6346572.html
内容总结
以上是互联网集市为您收集整理的mysql命令行导入和导出数据全部内容,希望文章能够帮你解决mysql命令行导入和导出数据所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。