MySQL 進階冷知識：XML 輸出、快速重建索引、壓縮表、CSV 匯出、Strict 模式、狀態監控、SHOW CREATE TABLE、自訂提示符

1. XML 格式輸出查詢結果

mysql --xml -e "SELECT * FROM test.stories"

輸出結構化 XML，便於程式解析、異系統整合。

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

REPAIR TABLE content QUICK;

修改 ft_min_word_len 等全文參數後，比 ALTER TABLE ... DROP INDEX ... ADD FULLTEXT 快得多，只重建索引不動資料。

3. 壓縮唯讀 MyISAM 表：myisampack

myisampack movies.MYI
# Compressing movies.MYD: (146 records) - 41.05%

壓縮後表變**唯讀**，適合歸檔、歷史日誌、字典表。解壓用 myisamchk -u。

4. SQL 流程控制：IF / CASE

SELECT IF(priv=1, 'admin', 'guest') AS usertype FROM privs WHERE username='joe';

SELECT CASE 
    WHEN score >= 90 THEN 'A'
    WHEN score >= 80 THEN 'B'
    ELSE 'C'
END AS grade FROM exams;

5. CSV 格式匯出：mysqldump -T

mysqldump -T /tmp/export_dir --fields-terminated-by=',' --fields-enclosed-by='"' mydb mytable

產生 mytable.txt（資料）與 mytable.sql（結構），直接用 Excel 開啟。

6. 啟用 Strict SQL Mode 防髒資料

mysqld --sql_mode="STRICT_ALL_TABLES"
# 或 my.ini: sql_mode=STRICT_ALL_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO

效果：
- 截斷資料報錯而非警告
- 非法日期（0000-00-00）拒絕寫入
- 除零報錯
- 隱式型別轉換嚴格檢查

7. 監控伺服器狀態：SHOW STATUS

SHOW STATUS LIKE 'Uptime';
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Slow_queries';
SHOW STATUS LIKE 'Innodb_buffer_pool_reads';

關鍵指標：Aborted_connects、Select_scan（全表掃描）、Sort_merge_passes（排序溢寫磁碟）、Innodb_row_lock_waits。

8. 取得建表 DDL：SHOW CREATE TABLE

SHOW CREATE TABLE products\G

輸出完整 CREATE TABLE 陳述式，含引擎、字元集、分區、索引定義，適合版控、遷移、文件化。

9. 自訂互動提示符

mysql> PROMPT \u@\h:\d> 
# 顯示 user@host:db> 
root@localhost:test>

支援序列：\u用戶、\h主機、\d資料庫、\D完整日期、\t時間、\s分號計數。