[轉貼]將 MySQL 資料表內容匯出為 CSV 檔案

1.直接將 mysql 查詢結果儲存為 TXT 檔案, 再由 Libre Office Calc 或 MS Office Excel 讀入後轉存為 CSV 檔案 2.以 select into outfile 語法匯出資料表內容, 直接在 Linux CLI 產生 CSV 檔案 操作環境: CentOS, bash, mysql / mariadb 直接將 mysql 查詢結果儲存為 TXT 檔案, 再由 Libre Office Calc 或 MS Office Excel 讀入後轉存為 CSV 檔案 Step 1. echo "select * from ps_customer" | mysql -u root -p -A prestashop > result.txt Step 2. 由 Libre Office Calc / MS Office Excel 讀入 result.txt (Tab 分隔) Step 3. 於 Libre Office Calc / MS Office Excel 另存檔案為 CSV 格式 註: 若 SQL Query 語法較複雜, 可先將 SQL 內容寫入檔案再由 mysql 讀入. 例如: mysql -u root -p -A prestashop < complex-query.sql > result.txt 以 select into outfile 語法匯出資料表內容, 直接在 Linux CLI 產生 CSV 檔案 建立工作路徑 # mkdir /tmp/csv # chown mysql /tmp/csv 範例一: 匯出所有欄位 匯出 column_name mysql> use mysql mysql> select group_concat(concat(column_name)) into outfile '/tmp/csv/header.txt' from information_schema.columns where table_name='ps_customer' and table_schema='prestashop'; 匯出 table 內容 mysql> use prestashop mysql> select * into outfile '/tmp/csv/content.txt' fields terminated by ',' enclosed by '"' lines terminated by '\n' from ps_customer; 範例二: 匯出指定欄位 匯出 column_name 與 table 內容 mysql> use prestashop mysql> select 'id_customer,firstname,lastname,email,birthday' into outfile '/tmp/csv/header.txt'; mysql> select id_customer, firstname, lastname, email, birthday into outfile '/tmp/csv/content.txt' fields terminated by ',' enclosed by '"' lines terminated by '\n' from ps_customer; 彙整 header 與 content # cd /tmp/csv/ # (sed 's/^/"/;s/$/"/;s/,/","/g' header.txt; cat content.txt) > result.csv # unix2dos result.csv Ref: •linux - How to output MySQL query results in csv format? - Stack Overflow •mysql - Include headers when using SELECT INTO OUTFILE? - Stack Overflow •Linux / Unix: Sed Substitute Multiple Patterns [ Find & Replace ] 文章來源:http://jamyy.us.to/blog/2015/09/7687.html