您好,登錄后才能下訂單哦!
MySQL中怎么導出CSV格式數據,相信很多沒有經驗的人對此束手無策,為此本文總結了問題出現的原因和解決方法,通過這篇文章希望你能解決這個問題。
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:
# 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:
# 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__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
看完上述內容,你們掌握MySQL中怎么導出CSV格式數據的方法了嗎?如果還想學到更多技能或想了解更多相關內容,歡迎關注億速云行業資訊頻道,感謝各位的閱讀!
免責聲明:本站發布的內容(圖片、視頻和文字)以原創、轉載和分享為主,文章觀點不代表本網站立場,如果涉及侵權請聯系站長郵箱:is@yisu.com進行舉報,并提供相關證據,一經查實,將立刻刪除涉嫌侵權內容。