MySQL中导出CSV格式数据的SQL
MySQL中导出CSV格式数据的SQL语句样本如下：
Sql代码
select*fromtest_info
intooutfile'/tmp/test.csv'
fieldsterminatedby','optionallyenclosedby'"'escapedby'"'
linesterminatedby'\r\n';
select*fromtest_info
intooutfile'/tmp/test.csv'
fieldsterminatedby','optionallyenclosedby'"'escapedby'"'
linesterminatedby'\r\n';MySQL中导入CSV格式数据的SQL语句样本如下：
Sql代码
loaddatainfile'/tmp/test.csv'
intotabletest_info
fieldsterminatedby','optionallyenclosedby'"'escapedby'"'
linesterminatedby'\r\n';
loaddatainfile'/tmp/test.csv'
intotabletest_info
fieldsterminatedby','optionallyenclosedby'"'escapedby'"'
linesterminatedby'\r\n';里面最关键的部分就是格式参数
Sql代码
fieldsterminatedby','optionallyenclosedby'"'escapedby'"'
linesterminatedby'\r\n'
fieldsterminatedby','optionallyenclosedby'"'escapedby'"'
linesterminatedby'\r\n'这个参数是根据RFC4180文档设置的，该文档全称CommonFormatandMIMETypeforComma-SeparatedValues(CSV)Files，其中详细描述了CSV格式，其要点包括：
(1)字段之间以逗号分隔，数据行之间以\r\n分隔；
(2)字符串以半角双引号包围，字符串本身的双引号用两个双引号表示。
文件：test_csv.sql
Sql代码
usetest;
createtabletest_info(
idintegernotnull,
contentvarchar(64)notnull,
primarykey(id)
);
deletefromtest_info;
insertintotest_infovalues(2010,'hello,line
suped
seped
"
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;
usetest;
createtabletest_info(
idintegernotnull,
contentvarchar(64)notnull,
primarykey(id)
);
deletefromtest_info;
insertintotest_infovalues(2010,'hello,line
suped
seped
"
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;
文件：test.csv
Text代码
2010,"hello,line
suped
seped
""
end"
2010,"hello,line
suped
seped
""
end"
在Linux下如果经常要进行这样的导入导出操作，当然最好与Shell脚本结合起来，为了避免每次都要写格式参数，可以把这个串保存在变量中，如下所示：(文件mysql.sh)
Bash代码
#!/bin/sh
#Copyright(c)2010codingstandards.Allrightsreserved.
#file:mysql.sh
#description:Bash中操作MySQL数据库
#license:LGPL
#author:codingstandards
#email:[email protected]
#version:1.0
#date:2010.02.28
#MySQL中导入导出数据时，使用CSV格式时的命令行参数
#在导出数据时使用：select...from...[where...]intooutfile'/tmp/data.csv'$MYSQL_CSV_FORMAT;
#在导入数据时使用：loaddatainfile'/tmp/data.csv'intotable...$MYSQL_CSV_FORMAT;
#CSV标准文档：RFC4180
MYSQL_CSV_FORMAT="fieldsterminatedby','optionallyenclosedby'\"'escapedby'\"'linesterminatedby'\r\n'"
#!/bin/sh
#Copyright(c)2010codingstandards.Allrightsreserved.
#file:mysql.sh
#description:Bash中操作MySQL数据库
#license:LGPL
#author:codingstandards
#email:[email protected]
#version:1.0
#date:2010.02.28
#MySQL中导入导出数据时，使用CSV格式时的命令行参数
#在导出数据时使用：select...from...[where...]intooutfile'/tmp/data.csv'$MYSQL_CSV_FORMAT;
#在导入数据时使用：loaddatainfile'/tmp/data.csv'intotable...$MYSQL_CSV_FORMAT;
#CSV标准文档：RFC4180
MYSQL_CSV_FORMAT="fieldsterminatedby','optionallyenclosedby'\"'escapedby'\"'linesterminatedby'\r\n'"
使用示例如下：(文件test_mysql_csv.sh)
Bash代码
#!/bin/sh
./opt/shtools/commons/mysql.sh
#MYSQL_CSV_FORMAT="fieldsterminatedby','optionallyenclosedby'\"'escapedby'\"'linesterminatedby'\r\n'"
echo"MYSQL_CSV_FORMAT=$MYSQL_CSV_FORMAT"
rm/tmp/test.csv
mysql-p--default-character-set=gbk-t--verbosetest<<EOF
usetest;
createtableifnotexiststest_info(
idintegernotnull,
contentvarchar(64)notnull,
primarykey(id)
);
deletefromtest_info;
insertintotest_infovalues(2010,'hello,line
suped
seped
"
end'
);
select*fromtest_info;
--select*fromtest_infointooutfile'/tmp/test.csv'fieldsterminatedby','optionallyenclosedby'"'escapedby'"'linesterminatedby'\r\n';
select*fromtest_infointooutfile'/tmp/test.csv'$MYSQL_CSV_FORMAT;
deletefromtest_info;
--loaddatainfile'/tmp/test.csv'intotabletest_infofieldsterminatedby','optionallyenclosedby'"'escapedby'"'linesterminatedby'\r\n';
loaddatainfile'/tmp/test.csv'intotabletest_info$MYSQL_CSV_FORMAT;
select*fromtest_info;
EOF
echo"=====contentin/tmp/test.csv====="
cat/tmp/test.csv
#!/bin/sh
./opt/shtools/commons/mysql.sh
#MYSQL_CSV_FORMAT="fieldsterminatedby','optionallyenclosedby'\"'escapedby'\"'linesterminatedby'\r\n'"
echo"MYSQL_CSV_FORMAT=$MYSQL_CSV_FORMAT"
rm/tmp/test.csv
mysql-p--default-character-set=gbk-t--verbosetest<<EOF
usetest;
createtableifnotexiststest_info(
idintegernotnull,
contentvarchar(64)notnull,
primarykey(id)
);
deletefromtest_info;
insertintotest_infovalues(2010,'hello,line
suped
seped
"
end'
);
select*fromtest_info;
--select*fromtest_infointooutfile'/tmp/test.csv'fieldsterminatedby','optionallyenclosedby'"'escapedby'"'linesterminatedby'\r\n';
select*fromtest_infointooutfile'/tmp/test.csv'$MYSQL_CSV_FORMAT;
deletefromtest_info;
--loaddatainfile'/tmp/test.csv'intotabletest_infofieldsterminatedby','optionallyenclosedby'"'escapedby'"'linesterminatedby'\r\n';
loaddatainfile'/tmp/test.csv'intotabletest_info$MYSQL_CSV_FORMAT;
select*fromtest_info;
EOF
echo"=====contentin/tmp/test.csv====="
cat/tmp/test.csv