您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 實用技巧集錦：XML輸出、索引重建、表壓縮、CSV匯出、Strict模式

編輯：MySQL 綜合教學

前言

MySQL 隱藏許多實用功能，善用能大幅提升開發維運效率。本文匯總 8 個鮮為人知但極具實戰價值的技巧。

技巧 1：XML 格式輸出查詢結果

# 命令列啟動參數
mysql --xml -u root -p

# 查詢結果為 XML

  
    1
    This is a test
  
  
    2
    This is the second test

適用：程式解析、異構系統整合、自動化測試驗證
對比：--html、--table (預設)、--vertical

技巧 2：快速重建全文索引 (REPAIR TABLE QUICK)

-- 修改 ft_min_word_len、stopwords 等全文參數後
REPAIR TABLE content QUICK;
-- 僅重建索引樹，不複製資料，極快
-- 等同於 myisamchk -r -q

適用：MyISAM 全文索引參數調整後
InnoDB 5.6+ 全文索引修改參數需 ALTER TABLE ... DROP INDEX ..., ADD FULLTEXT ...

技巧 3：壓縮唯讀 MyISAM 表 (myisampack)

# 停止服務或鎖表
mysqladmin flush-tables

# 壓縮
myisampack /var/lib/mysql/mydb/movies.MYI
# Compressing movies.MYD: (146 records) - 41.05%

# 解壓 (需修改時)
myisamchk --unpack /var/lib/mysql/mydb/movies.MYI

壓縮率 40-70%，極大節省空間
**壓縮後唯讀**，需解壓才能寫入
適用：歸檔表、歷史日誌、唯讀查找表
InnoDB 使用 ROW_FORMAT=COMPRESSED 透明壓縮

技巧 4：SQL 內建流程控制 (IF / CASE)

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

-- CASE 運算式
SELECT 
  CASE 
    WHEN score >= 90 THEN 'A'
    WHEN score >= 80 THEN 'B'
    WHEN score >= 60 THEN 'C'
    ELSE 'D'
  END AS grade
FROM scores;

-- CASE 語句 (僅儲存程序)
CASE x
  WHEN 1 THEN ...
  WHEN 2 THEN ...
  ELSE ...
END CASE;

將邏輯下推資料庫層，減少應用層處理、網路傳輸
適用：報表查詢、ETL 轉換、動態排序

技巧 5：CSV 格式匯出 (mysqldump -T)

# 匯出為 CSV (欄位逗號分隔、雙引號包裹)
mysqldump -T /tmp/export_dir \
  --fields-terminated-by=',' \
  --fields-enclosed-by='"' \
  --fields-escaped-by='\\' \
  --lines-terminated-by='\n' \
  -u root -p mydb mytable

# 產生兩檔：
# mytable.txt  (資料)
# mytable.sql  (建表語句)

# 直接匯入 Excel/試算表/異構資料庫

比 SQL 匯出快、檔案小、通用性極強
需 FILE 權限、secure_file_priv 允許目錄

技巧 6：Strict SQL Mode 防止髒資料

# 啟動參數
mysqld --sql_mode="STRICT_ALL_TABLES"

# 或運行時設定 (Session/Global)
SET SESSION sql_mode = 'STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO';

# 效果：
-- 截斷資料報錯而非警告
-- 非法日期 (0000-00-00) 拒絕
-- 除零報錯
-- 隱式轉換嚴格檢查

生產環境**強烈建議啟用**，及早發現資料品質問題
遷移舊系統時需先清理資料、調整應用

技巧 7：監控服務器狀態 (SHOW STATUS)

SHOW GLOBAL STATUS;

-- 關鍵指標
Aborted_clients        -- 中斷連線數
Aborted_connects       -- 連線失敗數
Connections            -- 總嘗試連線數
Max_used_connections   -- 歷史峰值並發
Threads_connected      -- 當前連線數
Threads_running        -- 當前執行查詢數
Questions              -- 查詢總數
Slow_queries           -- 慢查詢數
Innodb_buffer_pool_read_requests
Innodb_buffer_pool_reads          -- 緩衝池未命中
Innodb_row_lock_waits             -- 行鎖等待次數
Innodb_row_lock_time_avg          -- 平均鎖等待時間
Uptime               -- 運行秒數

定期採集、趨勢分析、告警閾值設定
搭配 SHOW ENGINE INNODB STATUS 深度診斷

技巧 8：自動產生建表語句 (SHOW CREATE TABLE)

SHOW CREATE TABLE products\G

-- 輸出完整可執行 DDL
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

版控、遷移、文檔產生、異構同步的基礎
搭配 mysqldump --no-data 批量匯出結構

技巧 9：自訂提示符 (Prompt)

mysql> prompt \u@\h [\d]>\_
PROMPT set to '\u@\h [\d]>\_'
root@localhost [mydb]>

-- 常用代碼
\u  用戶名
\h  主機名
\d  當前資料庫
\D  完整日期
\t  時間
\_  空格
\R  提示符重置

多環境操作防誤、審計追蹤
寫入 ~/.my.cnf [mysql] prompt=... 永久生效

技巧 10：資料庫大小快速統計

-- 各資料庫大小 (MB)
SELECT table_schema AS 'Database',
       ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)'
FROM information_schema.tables
GROUP BY table_schema
ORDER BY SUM(data_length + index_length) DESC;

-- 單表大小
SELECT table_name,
       ROUND((data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)',
       table_rows
FROM information_schema.tables
WHERE table_schema = 'mydb'
ORDER BY (data_length + index_length) DESC;