MySQL如何有效的存儲IP地址？
前幾天，阿粉的一個朋友去面試，他回來告訴我，面試官問他 IP 地址是怎么存在數(shù)據(jù)庫的?他當(dāng)時也沒多想，直接就回答的存字符串啊(心想：這么簡單的問題，怕不是看不起我吧)
前面這段權(quán)當(dāng)看看，畢竟 IP地址 本來就是一個字符串，存放在數(shù)據(jù)庫作為字符穿類型，也是無可厚非的。但是，阿粉我可是一個喜歡換位思考的人，站在面試官的角度，你覺得我會問這么一個低級的問題么?那么檔案當(dāng)然是否定的。所以，面試官想知道的是你對這個問題會不會有深度思考，從此來一定程度的判斷你在平常的開發(fā)中只是一個單純的 "搬磚" 的碼農(nóng)，還是一個有靈魂的 Coder 。
序言
針對這個問題，首先聲明一下，將 IP地址 以字符串的形式保存在數(shù)據(jù)庫是完全沒問題的。那么你可能就有疑問了?既然沒問題，那你還在這里瞎比比什么呢?
雖然，這是一個前后矛盾的話題，但是除了存字符串以外，我們還有其他的存儲方式。例如比較常用的，將 IP地址 存成 int 型的數(shù)據(jù)，這種存儲方式雖然實現(xiàn)起來不復(fù)雜，但是能想到該方法，也從一定程度上表明了你是一個善于思考，對數(shù)據(jù)底層基礎(chǔ)把握的比較到位。
因為一個 int 型的數(shù)據(jù)占 4 個字節(jié)，每個字節(jié) 8 位，其范圍就是 0~(2^8-1)，而 ipv4地址 可以分成4段，每段的范圍是 0~255 剛剛好能存下，所以將其稍稍轉(zhuǎn)換，就巧妙的將 IP地址 用最小的空間存在了數(shù)據(jù)庫中(接下來的描述若無特殊說明，則都是指的 ipv4地址)。可能你會覺得這個小小的改變沒有什么關(guān)系，但是當(dāng)數(shù)據(jù)量越來越多的時候，15個字節(jié)和4個字節(jié)相差的數(shù)據(jù)量會讓你吃驚。所以在設(shè)計數(shù)據(jù)庫的時候，字段類型用合適的，夠用就行，能省則省。
正如在《高性能MySQL 第3版》第4.1.7節(jié)時，作者建議當(dāng)存儲IPv4地址時，應(yīng)該使用32位的無符號整數(shù)(UNSIGNED INT)來存儲IP地址，而不是使用字符串的原理一樣。
相對字符串存儲，使用無符號整數(shù)來存儲有如下的好處：
- 節(jié)省空間，不管是數(shù)據(jù)存儲空間，還是索引存儲空間
- 便于使用范圍查詢(BETWEEN...AND)，且效率更高
通常，在保存IPv4地址時，一個IPv4最小需要7個字符，最大需要15個字符，所以，使用VARCHAR(15)即可。MySQL在保存變長的字符串時，還需要額外的一個字節(jié)來保存此字符串的長度。而如果使用無符號整數(shù)來存儲，只需要4個字節(jié)即可。另外還可以使用4個字段分別存儲IPv4中的各部分，但是通常這不管是存儲空間和查詢效率應(yīng)該都不是很高(雖然有的場景適合使用這種方式存儲)。不過使用無符號整數(shù)來存儲也有不便于閱讀和需要手動轉(zhuǎn)換的缺點。
工具類實現(xiàn)轉(zhuǎn)換
而要實現(xiàn)將IP地址存儲成 int 型保存在數(shù)據(jù)庫中，一種是通過java代碼中的 移位操作 和 & 計算得到相應(yīng)的值：
- package com.java.mmzsit;
- /**
- * @author ：mmzsblog
- * @description：Ipv4地址的轉(zhuǎn)換
- * @date ：2020/5/27 22:43
- */
- public class Ipv4Covert {
- public static void main(String[] args) {
- String ip = "10.108.149.219";
- // step1: 分解IP字符串，并對應(yīng)寫對字節(jié)數(shù)組
- byte[] ip1 = ipToBytes(ip);
- // step2: 對字節(jié)數(shù)組里的每個字節(jié)進行左移位處理，分別對應(yīng)到整型變量的4個字節(jié)
- int ip2 = bytesToInt(ip1);
- System.out.println("整型ip ----> " + ip2);
- // step3: 對整型變量進行右位移處理，恢復(fù)IP字符串
- String ip3 = intToIp(ip2);
- System.out.println("字符串ip---->" + ip3);
- }
- /**
- * 把IP地址轉(zhuǎn)化為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數(shù)組
- */
- public static byte[] ipToBytes(String ipAddr) {
- // 初始化字節(jié)數(shù)組，定義長度為4
- byte[] ret = new byte[4];
- try {
- String[] ipArr = ipAddr.split("\\.");
- // 將字符串?dāng)?shù)組依次寫入字節(jié)數(shù)組
- 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);
- }
- }
- /**
- * 根據(jù)位運算把 byte[] -> int
- * 原理：將每個字節(jié)強制轉(zhuǎn)化為8位二進制碼，然后依次左移8位，對應(yīng)到Int變量的4個字節(jié)中
- */
- public static int bytesToInt(byte[] bytes) {
- // 先移位后直接強轉(zhuǎn)的同時指定位數(shù)
- 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) {
- // 先強轉(zhuǎn)二進制，再進行移位處理
- return new StringBuilder()
- // 右移3個字節(jié)（24位），得到IP地址的第一段也就是byte[0],為了防止符號位是1也就是負(fù)數(shù)，最后再一次& 0xFF
- .append(((ipInt & 0xFF000000) >> 24) & 0xFF).append('.')
- .append((ipInt & 0xFF0000) >> 16).append('.')
- .append((ipInt & 0xFF00) >> 8).append('.')
- .append((ipInt & 0xFF))
- .toString();
- }
- }
其實這是一種二進制的思維，也是計算技術(shù)中廣泛采用的一種數(shù)制，雖然平時用的不多，但是熟練掌握后，有助于加強我們對機器語言的理解和提升我們的編碼水平，特別是面對資源緊張(運存)的場景時，有助于我們分析和優(yōu)化問題。
數(shù)據(jù)庫函數(shù)實現(xiàn)轉(zhuǎn)換
另一種方式就是通過數(shù)據(jù)庫自帶的函數(shù) INET_ATON 和 INET_NTOA 進行轉(zhuǎn)化：
- mysql> SELECT INET_ATON('192.168.0.1');
- +--------------------------+
- | INET_ATON('192.168.0.1') |
- +--------------------------+
- | 3232235521 |
- +--------------------------+
- 1 row in set
- mysql> SELECT INET_NTOA(3232235521);
- +-----------------------+
- | INET_NTOA(3232235521) |
- +-----------------------+
- | 192.168.0.1 |
- +-----------------------+
- 1 row in set
如果是 IPv6地址 的話，則使用函數(shù) INET6_ATON 和 INET6_NTOA 進行轉(zhuǎn)化：
- mysql> SELECT HEX(INET6_ATON('1030::C9B4:FF12:48AA:1A2B'));
- +----------------------------------------------+
- | HEX(INET6_ATON('1030::C9B4:FF12:48AA:1A2B')) |
- +----------------------------------------------+
- | 1030000000000000C9B4FF1248AA1A2B |
- +----------------------------------------------+
- 1 row in set
- mysql> SELECT INET6_NTOA(UNHEX('1030000000000000C9B4FF1248AA1A2B'));
- +-------------------------------------------------------+
- | INET6_NTOA(UNHEX('1030000000000000C9B4FF1248AA1A2B')) |
- +-------------------------------------------------------+
- | 1030::c9b4:ff12:48aa:1a2b |
- +-------------------------------------------------------+
- 1 row in set
然后數(shù)據(jù)庫定義為 varbinary 類型，分配 128bits 空間(因為 ipv6采用的是128bits，16個字節(jié));或者定義為 char 類型，分配 32bits 空間。
用數(shù)據(jù)庫的函數(shù)的話，只需要在 IP地址 入庫時，稍稍做一下轉(zhuǎn)換即可，方便快捷；而且到了這里，你不覺得將IP地址轉(zhuǎn)換成數(shù)字存儲是一種不錯的選擇么，畢竟數(shù)據(jù)庫都幫我們考慮到了這一點，也間接性的證明了這的確是一種可行、并且不錯的存儲方式。
