首先打开命令窗口,输入命令:mysql -h localhost -u selffabu -p
连接成功后,进行下面的操作
MySQL中导出CSV格式数据的SQL语句样本如下：
Sql代码 select*fromtest_infointooutfile'/tmp/test.csv'fieldsterminatedby','optionallyenclosedby'"'escapedby'"'linesterminatedby'\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语句样本如下,要导入的文件编码格式是UTF-8：
Sql代码 loaddata localinfile'/tmp/test.csv'intotabletest_infofieldsterminatedby','optionallyenclosedby'"'escapedby'"'linesterminatedby'\n';
load data local infile '/tmp/test.csv' into table test_info fields terminated by ',' optionally enclosed by '"' escaped by '"' lines terminated by '\n';
里面最关键的部分就是格式参数
[sql]fieldsterminatedby','optionallyenclosedby'"'escapedby'"'linesterminatedby'\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]usetest;createtabletest_info(idintegernotnull,contentvarchar(64)notnull,primarykey(id));deletefromtest_info;insertintotest_infovalues(,'hello,linesupedseped"end');select*fromtest_info;select*fromtest_infointooutfile'/tmp/test.csv'fieldsterminatedby','optionallyenclosedby'"'escapedby'"'linesterminatedby'\r\n';deletefromtest_info;loaddatainfile'/tmp/test.csv'intotabletest_infofieldsterminatedby','optionallyenclosedby'"'escapedby'"'linesterminatedby'\r\n';select*fromtest_info;
use test;create table test_info (id integernot null,content varchar(64) not null,primary key (id));delete from test_info;insert into test_info values (, 'hello, linesupedseped"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],"hello,linesupedseped""end"
,"hello, linesupedseped""end"
在Linux下如果经常要进行这样的导入导出操作，当然最好与Shell脚本结合起来，为了避免每次都要写格式参数，可以把这个串保存在变量中，如下所示：(文件mysql.sh)
Bash代码 #!/bin/sh#Copyright(c)codingstandards.Allrightsreserved.#file:mysql.sh#description:Bash中操作MySQL数据库#license:LGPL#author:codingstandards#email:codingstandards@#version:1.0#date:.02.28#MySQL中导入导出数据时，使用CSV格式时的命令行参数#在导出数据时使用：select...from...[where...]intooutfile'/tmp/data.csv'$MYSQL_CSV_FORMAT;#在导入数据时使用：loaddatainfile'/tmp/data.csv'intotable...$MYSQL_CSV_FORMAT;#CSV标准文档：RFC4180MYSQL_CSV_FORMAT="fieldsterminatedby','optionallyenclosedby'\"'escapedby'\"'linesterminatedby'\r\n'"
#!/bin/sh# Copyright (c) codingstandards. All rights reserved.# file: mysql.sh# description: Bash中操作MySQL数据库# license: LGPL# author: codingstandards# email: codingstandards@# version: 1.0# date: .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 4180MYSQL_CSV_FORMAT="fields terminated by ',' optionally enclosed by '\"' escaped by '\"' lines terminated by '\r\n'
转自:/sara_yhl/article/details/6850107
