MySQL IP 地址轉換函數 INET_ATON() 與 INET_NTOA() 完整指南

函數定義

• INET_ATON('a.b.c.d') → 無號整數 (網絡字節序：a×256³ + b×256² + c×256 + d)
• INET_NTOA(int) → 'a.b.c.d' 字串

手動轉換演示

192.168.33.123
= 192×256³ + 168×256² + 33×256 + 123
= 192×16777216 + 168×65536 + 33×256 + 123
= 3221225472 + 11010048 + 8448 + 123
= 3232244091

驗證：SELECT INET_ATON('192.168.33.123'); -- 3232244091

短格式支援

SELECT INET_ATON('127.1');       -- 2130706433 (等同 127.0.0.1)
SELECT INET_ATON('127.0.0.1');   -- 2130706433

儲存建議

• 欄位型別：**INT UNSIGNED** (4 字節)，範圍 0~4294967295 覆蓋全 IPv4 空間。
• 若用有號 INT，>127.*.*.* 會溢出為負數或截斷。
• IPv6 需 VARBINARY(16) + INET6_ATON/NTOA (MySQL 5.6+)。

自定義函數對比 (效能差 10 倍+)

-- 手寫轉換 (不推薦)
CREATE FUNCTION transIPtoInt(ip CHAR(31)) RETURNS INT
BEGIN
  DECLARE v1,v2,v3,v4 INT;
  SET v1 = SUBSTRING_INDEX(ip, '.', 1) << 24;
  SET v2 = SUBSTRING_INDEX(SUBSTRING_INDEX(ip, '.', 2), '.', -1) << 16;
  SET v3 = SUBSTRING_INDEX(SUBSTRING_INDEX(ip, '.', -2), '.', 1) << 8;
  SET v4 = SUBSTRING_INDEX(ip, '.', -1);
  RETURN v1+v2+v3+v4;
END;

實戰查詢模式

精確匹配 (走索引)

SELECT * FROM ip_table WHERE ip = INET_ATON('192.168.1.200');

網段範圍查詢 (走索引)

SELECT INET_NTOA(ip) FROM ip_table
WHERE ip BETWEEN INET_ATON('192.168.1.0') AND INET_ATON('192.168.1.255');

LIKE 模糊查詢 (不走索引，全表掃描)

SELECT INET_NTOA(ip) FROM ip_table WHERE INET_NTOA(ip) LIKE '192.168.1.%';

效能優勢總結

時間戳補充

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

結語

**IPv4 儲存首選 INT UNSIGNED + INET_ATON/NTOA**：空間省 75%、索引小、比較快、範圍查詢原生支援。棄用字串儲存，拒絕自定義轉換函數。