學習 MySQL中導入導出CSV

MySQL中導出CSV格式資料的SQL語句樣本如下：

Sql代碼

[sql] view plain copy

MySQL中導入CSV格式資料的SQL語句樣本如下：

裡面最關鍵的部分就是格式參數

這個參數是根據RFC4180文檔設定的，該文檔全稱Common Format and MIME Type for Comma-Separated Values (CSV) Files，其中較長的描述了CSV格式，其要點包括：

(1)字段之間以逗号分隔，資料行之間以\r\n分隔；

(2)字元串以半角雙引号包圍，字元串本身的雙引号用兩個雙引号表示。

檔案：test_csv.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';
load data infile '/tmp/test.csv' into table test_info fields terminated by ',' optionally enclosed by '"' escaped by '"' lines terminated by '\r\n';

檔案：test.csv

Text代碼

[Text] view plain copy

在Linux下如果經常要進行這樣的導入導出操作，當然最好與Shell腳本結合起來，為了避免每次都要寫格式參數，可以把這個串儲存在變量中，如下所示：(檔案mysql.sh)

Bash代碼

#!/bin/sh
# 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)

. /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
create table if not exists 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;
-- 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;
EOF
echo "===== content in /tmp/test.csv ====="
cat /tmp/test.csv

學習 MySQL中導入 導出CSV