MySQL 進階技巧：XML 輸出、索引重建、表壓縮、CSV 匯出與 Strict 模式

前言

MySQL 易學易用，但隨版本演進隱藏許多實用特性。本文介紹 7 個鮮為人知的進階技巧，提升開發運維效率。

1. 以 XML 格式輸出查詢結果

shell> mysql --xml
mysql> SELECT * FROM test.stores;


  
    1
    This is a test
    2005-07-28 00:14:57
  
  
    2
    This is the second test
    2005-07-28 00:15:11
  

適用於程式整合、API 回傳、日誌審計。

2. 快速重建全文索引

修改全文搜尋變數後需重建索引，大表耗時極長。使用 REPAIR TABLE ... QUICK 秒級完成：

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

3. 壓縮唯讀 MyISAM 表

僅限唯讀 MyISAM 表，使用 myisampack 節省磁碟空間：

shell> myisampack movies.MYI
Compressing movies.MYD: (146 records)
- Calculating statistics
- Compressing file
41.05%

壓縮後表不可寫入，需解壓才能修改。

4. SQL 中使用 IF / CASE 流程控制

mysql> SELECT IF(priv=1, 'admin', 'guest') AS usertype FROM privs WHERE username='joe';
+----------+
| usertype |
+----------+
| admin    |
+----------+

支援 CASE WHEN ... THEN ... ELSE ... END 複雜邏輯，減少應用層判斷。

5. 以 CSV 格式匯出表資料

shell> mysqldump -T . --fields-terminated-by="," mydb mytable

在當前目錄生成以逗號分隔的文字檔，可直接用 Excel/BI 工具開啟。

6. 啟用 Strict SQL Mode 防止髒資料

shell> mysqld --sql_mode="STRICT_ALL_TABLES" &

Strict 模式下：

• 資料截斷、無效日期、除零等直接報錯而非警告
• 停用許多自動修正行為
• 強制嚴格時間檢查

生產環境強烈建議啟用，配合 TRADITIONAL 模式（STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION）。

7. 監控伺服器運行狀態

mysql> SHOW STATUS;
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| Aborted_clients  | 0     |
| Aborted_connects | 0     |
| ...              | ...   |
| Uptime           | 851   |
+------------------+-------+
156 rows in set (0.16 sec)

關鍵指標：Threads_connected、Questions、Slow_queries、Innodb_buffer_pool_reads、Uptime。

8. 顯示建表 SQL（逆向工程）

mysql> SHOW CREATE TABLE products\G
Table: products
Create Table: CREATE TABLE `products` (
  `id` int(8) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL DEFAULT '',
  `price` int(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

快速獲取建表語句，便於版本控制、環境同步、文檔生成。