MySQL 實用技巧集錦：IP 轉換、格式化、時間戳、臨時變數等

前言

MySQL 內建許多實用函數與特性，善用它們能大幅簡化 SQL 撰寫並提升效能。本文整理 8 個常見場景的實戰技巧，附帶語法與範例輸出。

1. IP 位址與整數互轉

將 IPv4 位址轉為無號整數儲存，可節省空間並加速篩選排序。

• INET_ATON('192.168.1.1') → 3232235777（IP 轉數字）
• INET_NTOA(3232235777) → '192.168.1.1'（數字轉 IP）

SELECT INET_ATON('10.0.0.1'), INET_NTOA(167772161);

儲存建議：使用 INT UNSIGNED（4 位元組）即可容納完整 IPv4 範圍，無需 BIGINT。

2. 數字格式化：千分位與小數位

FORMAT(N, D) 回傳字串，適合報表顯示，不適合用於計算。

• FORMAT(12345, 0) → '12,345'
• FORMAT(12345.678, 2) → '12,345.68'

SELECT FORMAT(price, 2) AS formatted_price FROM products;

3. 時間戳與日期時間格式化

FROM_UNIXTIME(unix_ts, format) 將 Unix 時間戳轉為可讀日期；UNIX_TIMESTAMP() 取得目前時間戳。

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

常用格式代碼：%Y 四位年、%m 月(01-12)、%d 日(01-31)、%H 時(00-23)、%i 分、%s 秒。

4. 線上轉換資料表引擎

mysql_convert_table_format 需 Perl DBI/DBD::mysql 模組，可批次轉換資料表引擎（如 MyISAM → InnoDB）。

mysql_convert_table_format --user=root --password='xxx' --type=InnoDB db_name

注意：操作會鎖表，大表建議低峰期執行或使用 pt-online-schema-change。

5. 修改資料行名稱

使用 ALTER TABLE ... CHANGE，必須完整重述資料行定義。

ALTER TABLE tb_name CHANGE old_col new_col VARCHAR(100) NOT NULL DEFAULT '';

若僅修改名稱且定義不變，可用 RENAME COLUMN（MySQL 8.0+）。

6. 使用者自變數進行跨欄位運算

在單一查詢中累加中間結果，避免重複計算或應用層處理。

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

變數以 @ 開頭，生命週期為目前連線。

7. INT UNSIGNED 足以儲存 IPv4

IPv4 最大值 255.255.255.255 = 4,294,967,295，剛好在 INT UNSIGNED 範圍（0 ~ 4,294,967,295）內。切勿使用有號 INT，會導致大於 127.*.*.* 的 IP 變成負數。

8. CREATE TABLE ... SELECT 的限制

CREATE TABLE IF NOT EXISTS new_tbl SELECT * FROM src_tbl;

此語法不允許來源表同時有 INSERT 操作（會報錯）。若需並行寫入，請改用 INSERT INTO new_tbl SELECT ... 或分步驟建表再導入。

9. IF 函數快速切換 ENUM 值

適用於二狀態旗標（如啟用/停用）的單語句翻轉。

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

比 CASE WHEN 更簡潔，效能相當。

總結

這些技巧看似細節，但在日常開發、報表、資料遷移中能節省大量時間。建議收藏備查，並在測試環境驗證後再套用至生產系統。