MySQL 資料庫鮮為人知的實用技巧

以 XML 格式檢視查詢結果

使用 --xml 選項啟動 MySQL 命令列客戶端，可將查詢結果以 XML 格式（而非傳統表格）顯示，方便與其他程式整合。範例：

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

輸出為 XML 結構，包含欄位名與值。

快速重建索引

若想變更伺服器全文搜尋變數，通常需重建全文索引以確保更新映射，這非常耗時。快速解法是使用 REPAIR TABLE 命令：

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

壓縮特定表類型

若處理唯讀 MyISAM 表，MySQL 允許壓縮以節省磁碟空間，使用 myisampack：

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

在 SQL 中使用流程控制

MySQL 支援 SQL 查詢中的 IF 與 CASE 結構。範例：

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

以 CSV 格式匯出表資料

mysqldump 輸出檔含完整 SQL 命令，適合導入 MySQL。若目標程式（如 Excel）無法與 SQL 互通，可指定 CSV 格式：

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

將在當前目錄產生文字檔，包含以逗號分隔的記錄。

啟用 Strict 模式減少「壞」資料

MySQL 伺服器可在多種模式下運行。預設無模式。透過啟動參數啟用「strict」模式：

shell> mysqld --sql_mode="STRICT_ALL_TABLES" &

Strict 模式下，伺服器會中止不合法查詢並回傳錯誤，停用許多自動修正功能，並執行更嚴格的資料檢查。

監控伺服器狀態

執行 SHOW STATUS 獲得伺服器運行統計報告，包含連線數、查詢數、正常運行時間等：

mysql> SHOW STATUS;
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| Aborted_clients  | 0     |
| Aborted_connects | 0     |
| ...              | ...   |
| Uptime           | 851   |
+------------------+-------+

自動取得 CREATE TABLE 代碼

執行 SHOW CREATE TABLE 可自動獲得重建特定表的 SQL 命令：

mysql> SHOW CREATE TABLE products;
| Table    | Create Table                                                                    |
+----------+-----------------------------------------+
| products | 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 |

建立更實用的命令提示符

預設提示符為 mysql>。可用特定轉義序列自訂提示符，包含當前用戶、主機、資料庫：

mysql> prompt \u:\d>
PROMPT set to '\u:\d>'
root@localhost:/db1>

完整支援列表請參閱 MySQL 文件。