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