MySQL 10 大隱藏技巧：XML 輸出、REPAIR TABLE、myisampack、CSV 匯出、IF/CASE

MySQL 10 大隱藏技巧：XML 輸出、REPAIR TABLE、myisampack、CSV 匯出、IF/CASE

MySQL 除基本 CRUD 外，藏有許多提升開發運維效率的進階功能。本文精選 10 個實用技巧，涵蓋輸出格式、索引重建、壓縮、流程控制、資料匯出等場景。

1. XML 格式輸出查詢結果

# 啟動客戶端時加參數
mysql --xml -u root -p
# 或單次查詢
mysql -u root -p --xml -e "SELECT * FROM stories;"

輸出標準 XML，便於程式解析、異系統整合：

1
This is a test


2
This is the second test

2. 快速重建全文索引：REPAIR TABLE QUICK

修改全文搜尋參數 (如 ft_min_word_len) 後，需重建索引。REPAIR TABLE ... QUICK 僅重建索引檔，不動資料檔，速度極快：

mysql> REPAIR TABLE content QUICK;
+-----------+--------+----------+----------+
| Table     | Op     | Msg_type | Msg_text |
+-----------+--------+----------+----------+
| content   | repair | status   | OK       |
+-----------+--------+----------+----------+

適用 MyISAM 表；InnoDB 請用 OPTIMIZE TABLE 或 ALTER TABLE ... ENGINE=InnoDB。

3. 壓縮唯讀 MyISAM 表：myisampack

歷史歸檔、日誌、報表等唯讀 MyISAM 表，可用 myisampack 離線壓縮，節省 40%~60% 磁碟空間：

shell> myisampack /var/lib/mysql/dbname/mytable.MYI
Compressing mytable.MYD: (146 records)
- Calculating statistics
- Compressing file
41.05%

注意：壓縮後表變為唯讀，不可 INSERT/UPDATE/DELETE。需修改時先 myisamchk -r 解壓縮。

4. SQL 內建流程控制：IF()、CASE WHEN

MySQL 支援標準 SQL CASE 及擴充 IF()，可直接在查詢中實現條件邏輯：

-- IF 函數 (三元運算子)
SELECT IF(priv = 1, 'admin', 'guest') AS user_type FROM privs WHERE username = 'joe';

-- CASE 搜尋式 (多條件)
SELECT
  CASE
    WHEN score >= 90 THEN 'A'
    WHEN score >= 80 THEN 'B'
    WHEN score >= 70 THEN 'C'
    ELSE 'D'
  END AS grade
FROM exam_results;

-- CASE 簡單式 (單值比對)
SELECT
  CASE status
    WHEN 1 THEN '啟用'
    WHEN 0 THEN '停用'
    ELSE '未知'
  END AS status_desc
FROM users;

5. CSV 格式匯出表資料 (mysqldump)

直接產生 Excel/BI 工具可讀的 CSV，無需中介轉換：

mysqldump -T /export/path \
  --fields-terminated-by=',' \
  --fields-enclosed-by='"' \
  --lines-terminated-by='\n' \
  -u root -p db_name table_name

產生 /export/path/table_name.txt (CSV 內容) 與 table_name.sql (建表語句)。

6. 快速複製表結構 (不含資料)

CREATE TABLE new_table LIKE old_table;
-- 或指定條件 (永遠不成立)
CREATE TABLE new_table SELECT * FROM old_table WHERE 1 <> 1;

7. 完整複製表 (結構 + 資料 + 索引)

CREATE TABLE new_table SELECT * FROM old_table;

注意：不複製外鍵、觸發器、分區、預設值屬性。完整克隆請用 mysqldump + source。

8. 表重命名 (原子操作)

RENAME TABLE old_name TO new_name;
-- 或
ALTER TABLE old_name RENAME TO new_name;

9. 修改欄位型別/名稱 (CHANGE vs MODIFY)

-- 僅修改型別/屬性
ALTER TABLE t MODIFY col VARCHAR(100) NOT NULL;

-- 修改名稱 + 型別/屬性 (需重複指定型別)
ALTER TABLE t CHANGE old_col new_col VARCHAR(100) NOT NULL;

10. 分析索引效率：EXPLAIN 必看欄位

EXPLAIN SELECT * FROM orders WHERE user_id = 100 AND status = 1;