[轉貼]將 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

roundcubemail webmail

Database Configuration Next thing we need to do is decide what database backend we'll use. The most common is MySQL but others are PostgreSQL and SQLite. So once you decide, create a database with any name you want and grant privileges to a separate database user. It's recommended not to use an existing user or root. With MySQL you can set up the database by issuing the following commands: CREATE DATABASE roundcubemail; GRANT ALL PRIVILEGES ON roundcubemail.* TO username@localhost IDENTIFIED BY 'password'; (of course you have to replace the database, username and password accordingly) See the INSTALL file for information about setting up PostgreSQL or SQLite If you are using MySQL, be sure to flush the users privileges when you add a new user or you will get a database connection error: FLUSH PRIVILEGES; Note that preconfigured database tables are included in the SQL folder. Import or restore your version or you may get a 500 Error. ======================== QUOTA /etc/dovecot.conf protocol imap { mail_plugins = quota imap_quota } plugin { quota = fs:user } https://wiki1.dovecot.org/Quota/FS