MySQL 內建函數巧用：IP 地址與數字互轉、時間格式化、表類型轉換等實用技巧

MySQL 提供豐富的內建函數，善用可大幅簡化開發與運維工作。本文彙整高頻實用技巧。

1. IP 地址與整數互轉

• INET_ATON('192.168.1.1') → 3232235777（IP 轉無號整數）
• INET_NTOA(3232235777) → '192.168.1.1'（整數轉 IP）
• 儲存建議：使用 INT UNSIGNED（4 位元組）即可容納 IPv4，無需 BIGINT。

2. 數字格式化

• FORMAT(12345, 0) → '12,345'（千分位分隔）
• FORMAT(12345, 2) → '12,345.00'（保留兩位小數）

3. 時間戳記格式化

SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(), '%Y %D %M %h:%i:%s %x');
-- 結果範例：2004 3rd August 03:35:48 2004

4. 資料表引擎轉換

-- 需 Perl DBI/DBD::mysql 模組
mysql_convert_table_format --user=root --password='xx' --type=myisam test_db table_name

5. 修改欄位名稱

ALTER TABLE tb_name CHANGE old_col new_col 定義...;

6. 使用者變數累加計算

SELECT @var1 := a1 + a2 AS a_sum,
       @var2 := b1 + b2 AS b_sum,
       @var1 + @var2 AS total_sum
FROM test_table;

7. ENUM 欄位快速翻轉

UPDATE rule SET enable = IF(enable = '0', '1', '0') WHERE ...;
-- enable 為 ENUM('0','1') NOT NULL DEFAULT '0'

8. 避免長連線佔用連線數

SET GLOBAL wait_timeout = 10;
SET GLOBAL interactive_timeout = 10;
-- 10 秒無查詢自動斷開 sleep 連線

9. 自訂 mysql 客戶端提示符

export MYSQL_PS1="(\u:\h:)\d> "
-- 登入後顯示：(root:localhost:)db_name>

10. 碎片整理

11. 版本演進關鍵點備忘

• MySQL 5.0.3+：VARCHAR 尾部空格不再截斷。
• MySQL 4.1+：字串長度按「字元」而非「位元組」計算。
• 5.0.3-5.0.5 建立的 DECIMAL 欄位升級到 5.0.6+ 可能崩潰，需重建。
• 5.0.12+：自然連結與 USING 遵循 SQL:2003 標準，結果欄位數與順序可能改變。