當前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

MySQL如何有效的存储IP地址

發布時間：2025/3/12 数据库 24 豆豆

生活随笔收集整理的這篇文章主要介紹了 MySQL如何有效的存储IP地址小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

文章目錄

- 序言
- - 工具類實現轉換
  - 數據庫函數實現轉換
一、IP地址應該怎么存
二、整數存儲 IP 地址的查詢性能實驗
- 1、測試范圍查詢：
- 2、IP精確查詢：
- 3、整理一下結果發現：
總結

首先就來闡明一下部分人得反問：為什么要問IP得知怎樣存，直接varchar類型不就得了嗎？

其實做任何程序設計都要在功能實現的基礎上最大限度的優化性能。而數據庫設計是程序設計中不可忽略的一個重要部分，所以巧存IP地址可以一定程度獲得很大提升。

在MySQL中沒有直接提供IP類型字段，但如果有兩個函數可以把IP與最大長度為10位數字類型互轉，所以使用int類型存儲IP比varchar類型存儲IP地址性能要提升很多，減少不少空間。因為varchar是可變長形，需要多余的一個字節存儲長度。另外int型在邏輯運算上要比varchar速度快。

序言

首先聲明一下，將 IP地址以字符串的形式保存在數據庫是完全沒問題的。那么你可能就有疑問了？既然沒問題，那你還在這里瞎比比什么呢？

雖然，這是一個前后矛盾的話題，但是除了存字符串以外，我們還有其他的存儲方式。例如比較常用的，將 IP地址存成 int 型的數據，這種存儲方式雖然實現起來不復雜，但是能想到該方法，也從一定程度上表明了你是一個善于思考，對數據底層基礎把握的比較到位。

因為一個 int 型的數據占 4 個字節，每個字節 8 位，其范圍就是 0~(2^8-1)，而 ipv4地址可以分成4段，每段的范圍是 0~255 剛剛好能存下，所以將其稍稍轉換，就巧妙的將 IP地址用最小的空間存在了數據庫中（接下來的描述若無特殊說明，則都是指的 ipv4地址）。可能你會覺得這個小小的改變沒有什么關系，但是當數據量越來越多的時候，15個字節和4個字節相差的數據量會讓你吃驚。所以在設計數據庫的時候，字段類型用合適的，夠用就行，能省則省。

正如在《高性能MySQL 第3版》第4.1.7節時，作者建議當存儲IPv4地址時，應該使用32位的無符號整數（UNSIGNED INT）來存儲IP地址，而不是使用字符串的原理一樣。

相對字符串存儲，使用無符號整數來存儲有如下的好處：

節省空間，不管是數據存儲空間，還是索引存儲空間
便于使用范圍查詢（BETWEEN…AND），且效率更高

通常，在保存IPv4地址時，一個IPv4最小需要7個字符，最大需要15個字符，所以，使用VARCHAR(15)即可。MySQL在保存變長的字符串時，還需要額外的一個字節來保存此字符串的長度。而如果使用無符號整數來存儲，只需要4個字節即可。另外還可以使用4個字段分別存儲IPv4中的各部分，但是通常這不管是存儲空間和查詢效率應該都不是很高（雖然有的場景適合使用這種方式存儲）。不過使用無符號整數來存儲也有不便于閱讀和需要手動轉換的缺點。

工具類實現轉換

而要實現將IP地址存儲成 int 型保存在數據庫中，一種是通過java代碼中的移位操作和 & 計算得到相應的值：

package com.java.mmzsit;/*** @description：Ipv4地址的轉換*/ public class Ipv4Covert {public static void main(String[] args) {String ip = "10.108.149.219";// step1: 分解IP字符串，并對應寫對字節數組byte[] ip1 = ipToBytes(ip);// step2: 對字節數組里的每個字節進行左移位處理，分別對應到整型變量的4個字節int ip2 = bytesToInt(ip1);System.out.println("整型ip ----> " + ip2);// step3: 對整型變量進行右位移處理，恢復IP字符串String ip3 = intToIp(ip2);System.out.println("字符串ip---->" + ip3);}/*** 把IP地址轉化為int* @param ipAddr* @return int*/public static byte[] ipToBytesByReg(String ipAddr) {byte[] ret = new byte[4];try {String[] ipArr = ipAddr.split("\\.");ret[0] = (byte) (Integer.parseInt(ipArr[0]) & 0xFF);ret[1] = (byte) (Integer.parseInt(ipArr[1]) & 0xFF);ret[2] = (byte) (Integer.parseInt(ipArr[2]) & 0xFF);ret[3] = (byte) (Integer.parseInt(ipArr[3]) & 0xFF);return ret;} catch (Exception e) {throw new IllegalArgumentException(ipAddr + " is invalid IP");}}/*** 第一步，把IP地址分解為一個btye數組*/public static byte[] ipToBytes(String ipAddr) {// 初始化字節數組，定義長度為4byte[] ret = new byte[4];try {String[] ipArr = ipAddr.split("\\.");// 將字符串數組依次寫入字節數組ret[0] = (byte) (Integer.parseInt(ipArr[0]));ret[1] = (byte) (Integer.parseInt(ipArr[1]));ret[2] = (byte) (Integer.parseInt(ipArr[2]));ret[3] = (byte) (Integer.parseInt(ipArr[3]));return ret;} catch (Exception e) {throw new IllegalArgumentException("invalid IP : " + ipAddr);}}/*** 根據位運算把 byte[] -> int* 原理：將每個字節強制轉化為8位二進制碼，然后依次左移8位，對應到Int變量的4個字節中*/public static int bytesToInt(byte[] bytes) {// 先移位后直接強轉的同時指定位數int addr = bytes[3] & 0xFF;addr |= ((bytes[2] << 8) & 0xFF00);addr |= ((bytes[1] << 16) & 0xFF0000);addr |= ((bytes[0] << 24) & 0xFF000000);return addr;}/*** 把int->string地址** @param ipInt* @return String*/public static String intToIp(int ipInt) {// 先強轉二進制，再進行移位處理return new StringBuilder()// 右移3個字節（24位），得到IP地址的第一段也就是byte[0],為了防止符號位是1也就是負數，最后再一次& 0xFF.append(((ipInt & 0xFF000000) >> 24) & 0xFF).append('.').append((ipInt & 0xFF0000) >> 16).append('.').append((ipInt & 0xFF00) >> 8).append('.').append((ipInt & 0xFF)).toString();} }

其實這是一種二進制的思維，也是計算技術中廣泛采用的一種數制，雖然平時用的不多，但是熟練掌握后，有助于加強我們對機器語言的理解和提升我們的編碼水平，特別是面對資源緊張（運存）的場景時，有助于我們分析和優化問題。

數據庫函數實現轉換

另一種方式就是通過數據庫自帶的函數 INET_ATON 和 INET_NTOA 進行轉化：

mysql> SELECT INET_ATON('192.168.0.1'); +--------------------------+ | INET_ATON('192.168.0.1') | +--------------------------+ | 3232235521 | +--------------------------+ 1 row in setmysql> SELECT INET_NTOA(3232235521); +-----------------------+ | INET_NTOA(3232235521) | +-----------------------+ | 192.168.0.1 | +-----------------------+ 1 row in set

如果是 IPv6地址的話，則使用函數 INET6_ATON 和 INET6_NTOA 進行轉化：

mysql> SELECT HEX(INET6_ATON('1030::C9B4:FF12:48AA:1A2B')); +----------------------------------------------+ | HEX(INET6_ATON('1030::C9B4:FF12:48AA:1A2B')) | +----------------------------------------------+ | 1030000000000000C9B4FF1248AA1A2B | +----------------------------------------------+ 1 row in setmysql> SELECT INET6_NTOA(UNHEX('1030000000000000C9B4FF1248AA1A2B')); +-------------------------------------------------------+ | INET6_NTOA(UNHEX('1030000000000000C9B4FF1248AA1A2B')) | +-------------------------------------------------------+ | 1030::c9b4:ff12:48aa:1a2b | +-------------------------------------------------------+ 1 row in set

然后數據庫定義為 varbinary 類型，分配 128bits 空間（因為 ipv6采用的是128bits，16個字節）；或者定義為 char 類型，分配 32bits 空間。

用數據庫的函數的話，只需要在 IP地址入庫時，稍稍做一下轉換即可，方便快捷；而且到了這里，你不覺得將 IP地址轉換成數字存儲是一種不錯的選擇么，畢竟數據庫都幫我們考慮到了這一點，也間接性的證明了這的確是一種可行、并且不錯的存儲方式。

一、IP地址應該怎么存

在MySQL中，當存儲IPv4地址時，應該使用32位的無符號整數（UNSIGNED INT）來存儲IP地址，而不是使用字符串，用UNSIGNED INT類型存儲IP 地址是一個4字節長的整數。

如果是字符串存儲IP 地址，在正常格式下，最小長度為 7 個字符 (0.0.0.0)，最大長度為 15 個 (255.255.255.255)，因此，我們通常會使用varchar(15)來存儲。同時為了讓數據庫準確跟蹤列中有多少數據，數據庫會添加額外的1字節來存儲字符串的長度。這使得以字符串表示的 IP 的實際數據存儲成本需要16字節。

這意味著如果將每個 IP 地址存儲為字符串的話，每行需要多耗費大約 10 個字節的額外資源。

如果你說磁盤夠使不是事兒，那我得告訴你，這個不僅會使數據文件消耗更多的磁盤，如果該字段加了索引，也會同比例擴大索引文件的大小，緩存數據需要使用更多內存來緩存數據或索引，從而可能將其他更有價值的內容推出緩存區。執行SQL對該字段進行CRUD時，也會消耗更多的CPU資源。

MySQL中有內置的函數，來對IP和數值進行相互轉換。

INET_ATON()

將IP轉換成整數。

算法：第一位乘256三次方+第二位乘256二次方+第三位乘256一次方 + 第四位乘256零次方

INET_NTOA()

將數字反向轉換成IP

SELECT INET_ATON('127.0.0.1');+------------------------+ | INET_ATON('127.0.0.1') | +------------------------+ | 2130706433 | +------------------------+ 1 row in set (0.00 sec)SELECT INET_NTOA('2130706433');+-------------------------+ | INET_NTOA('2130706433') | +-------------------------+ | 127.0.0.1 | +-------------------------+ 1 row in set (0.02 sec)

如果是 IPv6地址的話，可以使用函數 INET6_ATON() 和 INET6_NTOA() 來轉化：

然后將數據庫定義為 varbinary類型，分配 128bits空間（因為 ipv6采用的是128bits，16個字節）；或者定義為 char 類型，分配 32bits 空間。

二、整數存儲 IP 地址的查詢性能實驗

測試數據，用存儲過程生成了 100 萬個隨機 IP 地址；

1、測試范圍查詢：

IP轉成Int，查詢：耗時0.60s

select ip_int from T where ip_int > INET_ATON('192.0.0.0') and ip_int <=INET_ATON('192.255.255.255');1726 row in set, 1 warning (0.60 sec)

IP為字符串，查詢：耗時0.63s

select ip_varchar from T where ip_varchar like '192.%';1726 row in set, 1 warning (0.63 sec)

2、IP精確查詢：

select ip_int from T where ip_int = INET_ATON('192.168.0.0');1 row in set, 1 warning (0.00 sec) select ip_varchar from T where ip_varchar='192.168.0.0';1 row in set, 1 warning (0.00 sec)

都是0s出結果。可認為常量索引查詢，性能上無明顯差異。

3、整理一下結果發現：

范圍查詢和精確查詢：

數據量少的情況下的差距不明顯，如果數據量擴大到約1千萬行或1億行，1億行時預計范圍查詢差距能拉開到0.5s。

存儲空間節省:

按1億行算，理論上 varchar 最大15字節存儲，數值4個字節，大約節省10字節 *1億約1G空間。

加上索文件引所占的空間，一個索引也是能節省1G。約能節省2G空間。

總結

IP地址數據采用整數（UNSIGNED INT）存儲，在存儲和CPU資源使用上都少于字符串存儲形式；在歧義較大的范圍查詢中，存儲整數方式無需關系范圍中的位數問題，查詢更加直觀方便。

但整數存儲需要使用INET_ATON、INET_NTOA等特定函數處理，可讀性查，函數也會消耗額外CPU，經檢驗發現CPU開支微乎其微。

因此，需要范圍查詢，且數據量很大（如億級以上），采用數值存儲IP地址的方式更優。如果均是唯一IP精確查詢，或數據量不大，那么使用字符串操作更為簡單。

總結

以上是生活随笔為你收集整理的MySQL如何有效的存储IP地址的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。

上一篇： vue-router进阶：路由使用归纳、
下一篇： tensorflow随笔-简单CNN（卷