MySQL中導出CSV格式數據的SQL語句樣本如下：
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語句樣本如下：
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'; 裡面最關鍵的部分就是格式參數
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' 這個參數是根據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;
文件：test.csv
Text代碼
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: [email protected]
# 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: [email protected]
# 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
#!/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
作者“wangqiaowqo”
