共計(jì) 4900 個(gè)字符，預(yù)計(jì)需要花費(fèi) 13 分鐘才能閱讀完成。
MySQL 中怎么導(dǎo)出 CSV 格式數(shù)據(jù)，相信很多沒(méi)有經(jīng)驗(yàn)的人對(duì)此束手無(wú)策，為此本文總結(jié)了問(wèn)題出現(xiàn)的原因和解決方法，通過(guò)這篇文章希望你能解決這個(gè)問(wèn)題。
MySQL 中導(dǎo)出 CSV 格式數(shù)據(jù)的 SQL 語(yǔ)句樣本如下：
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 中導(dǎo)入 CSV 格式數(shù)據(jù)的 SQL 語(yǔ)句樣本如下：
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 里面最關(guān)鍵的部分就是格式參數(shù)
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 這個(gè)參數(shù)是根據(jù) RFC4180 文檔設(shè)置的，該文檔全稱(chēng) Common Format and MIME Type for Comma-Separated Values (CSV) Files，其中詳細(xì)描述了 CSV 格式，其要點(diǎn)包括：
(1) 字段之間以逗號(hào)分隔，數(shù)據(jù)行之間以 \r\n 分隔；
(2) 字符串以半角雙引號(hào)包圍，字符串本身的雙引號(hào)用兩個(gè)雙引號(hào)表示。
文件：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 下如果經(jīng)常要進(jìn)行這樣的導(dǎo)入導(dǎo)出操作，當(dāng)然最好與 Shell 腳本結(jié)合起來(lái)，為了避免每次都要寫(xiě)格式參數(shù)，可以把這個(gè)串保存在變量中，如下所示：(文件 mysql.sh)
Bash 代碼
#!/bin/sh
# Copyright (c) 2010 codingstandards. All rights reserved.
# file: mysql.sh
# description: Bash 中操作 MySQL 數(shù)據(jù)庫(kù)
# license: LGPL
# author: codingstandards
# email:
# version: 1.0
# date: 2010.02.28
# MySQL 中導(dǎo)入導(dǎo)出數(shù)據(jù)時(shí)，使用 CSV 格式時(shí)的命令行參數(shù)
# 在導(dǎo)出數(shù)據(jù)時(shí)使用：select … from … [where …] into outfile /tmp/data.csv $MYSQL_CSV_FORMAT;
# 在導(dǎo)入數(shù)據(jù)時(shí)使用：load data infile /tmp/data.csv into table … $MYSQL_CSV_FORMAT;
# CSV 標(biāo)準(zhǔn)文檔：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 數(shù)據(jù)庫(kù)
# license: LGPL
# author: codingstandards
# email:
# version: 1.0
# date: 2010.02.28
# MySQL 中導(dǎo)入導(dǎo)出數(shù)據(jù)時(shí)，使用 CSV 格式時(shí)的命令行參數(shù)
# 在導(dǎo)出數(shù)據(jù)時(shí)使用：select … from … [where …] into outfile /tmp/data.csv $MYSQL_CSV_FORMAT;
# 在導(dǎo)入數(shù)據(jù)時(shí)使用：load data infile /tmp/data.csv into table … $MYSQL_CSV_FORMAT;
# CSV 標(biāo)準(zhǔn)文檔：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
看完上述內(nèi)容，你們掌握 MySQL 中怎么導(dǎo)出 CSV 格式數(shù)據(jù)的方法了嗎？如果還想學(xué)到更多技能或想了解更多相關(guān)內(nèi)容，歡迎關(guān)注丸趣 TV 行業(yè)資訊頻道，感謝各位的閱讀！
