91超碰碰碰碰久久久久久综合_超碰av人澡人澡人澡人澡人掠_国产黄大片在线观看画质优化_txt小说免费全本

溫馨提示×

溫馨提示×

您好,登錄后才能下訂單哦!

密碼登錄×
登錄注冊×
其他方式登錄
點擊 登錄注冊 即表示同意《億速云用戶服務條款》

MySQL中怎么導出CSV格式數據

發布時間:2021-08-06 11:37:08 來源:億速云 閱讀:315 作者:Leah 欄目:數據庫

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格式數據的方法了嗎?如果還想學到更多技能或想了解更多相關內容,歡迎關注億速云行業資訊頻道,感謝各位的閱讀!

向AI問一下細節

免責聲明:本站發布的內容(圖片、視頻和文字)以原創、轉載和分享為主,文章觀點不代表本網站立場,如果涉及侵權請聯系站長郵箱:is@yisu.com進行舉報,并提供相關證據,一經查實,將立刻刪除涉嫌侵權內容。

AI

芒康县| 井研县| 慈利县| 临漳县| 广河县| 岐山县| 理塘县| 英德市| 淳安县| 汪清县| 江达县| 绥芬河市| 新泰市| 嘉定区| 巴中市| 兴文县| 乡宁县| 荃湾区| 建湖县| 留坝县| 衡水市| 太和县| 浮梁县| 剑川县| 贞丰县| 拜城县| 岚皋县| 永登县| 杭锦旗| 马公市| 黔东| 赞皇县| 金秀| 凉山| 邛崃市| 雷波县| 池州市| 中宁县| 南雄市| 灵丘县| 纳雍县|