mysql中将数据导入csv文件以及将csv文件导入mysql
来源:互联网 发布:淘宝用户等级划分 编辑:程序博客网 时间:2024/06/06 02:29
MySQL中导出CSV格式数据的SQL语句样本如下:
- 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语句样本如下:
- 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';
注意:在ubuntu中会显示不能创建文件只需要报csv文件创建在mysql数据默认保存的路径下即可如/var/lib/mysql/目录下
具体目录可查看my.cnf中dir所指向的路径
里面最关键的部分就是格式参数
- 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
- 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
- 2010,"hello, line
- suped
- seped
- ""
- end"
- 2010,"hello, line
- suped
- seped
- ""
- end"
在Linux下如果经常要进行这样的导入导出操作,当然最好与Shell脚本结合起来,为了避免每次都要写格式参数,可以把这个串保存在变量中,如下所示:(文件mysql.sh)
- #!/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)
- #!/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
0 0
- mysql中将数据导入csv文件以及将csv文件导入mysql
- 将CSV文件导入mysql
- mysql导入csv文件数据
- mysql 导入csv 文件
- mysql导入csv文件
- Mysql 导入csv文件
- mysql导入csv文件
- Mysql导入csv文件
- MySQL导入CSV文件
- Mysql导入CSV文件
- MySQL导入csv文件
- MYSQL导入csv文件
- csv文件导入Mysql
- MySQL导入csv文件
- 将csv文件导入到mysql数据库
- 使用navicat将csv文件导入mysql
- 使用RMySQL将csv文件数据导入MySQL数据库
- 利用LOAD DATA将csv文件中的数据导入MySQL
- 请实现一个函数,将一个字符串中的空格替换成“%20”。
- Android中使用系统自带浏览器打开本地assets目录下的html文件
- plsql中暂停变量的使用,否则如果sql中出现一些 < >时无法插入
- min queue
- jdk动态代理
- mysql中将数据导入csv文件以及将csv文件导入mysql
- Cocos Creator 获取和加载资源(摘自官方文档)
- 输入一个链表,从尾到头打印链表每个节点的值。
- css不换行相关易混属性
- [IOS]Xcode 7 App Transport Security has blocked a cleartext HTTP 报错解决办法
- 单据自动转换并审核的方法示例
- Cocos Creator 模块化脚本(摘自官方文档)
- 常用正则表达式
- sed提取模式内容