如何利用MySQL有效的儲存IP地址

前幾天，阿淼的一個朋友去面試，他回來告訴我，面試官問他 IP 地址是怎麼存在資料庫的？他當時也沒多想，直接就回答的存字串啊（心想：這麼簡單的問題，怕不是看不起我吧）

前面這段權當看看，畢竟 IP地址 本來就是一個字串，存放在資料庫作為字元穿型別，也是無可厚非的。但是，阿淼我可是一個喜歡換位思考的人，站在面試官的角度，你覺得我會問這麼一個低階的問題麼？ 那麼檔案當然是否定的。所以，面試官想知道的是你對這個問題會不會有深度思考，從此來一定程度的判斷你在平常的開發中只是一個單純的 “搬磚” 的碼農，還是一個有靈魂的 Coder 。

序言

針對這個問題，首先宣告一下，將 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;

/**
 * @author ：mmzsblog
 * @description：Ipv4地址的轉換
 * @date ：2020/5/27 22:43
 */
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) {
        // 初始化位元組陣列，定義長度為4
        byte[] 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 set

mysql> 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 set

mysql> 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地址 轉換成數字儲存是一種不錯的選擇麼，畢竟資料庫都幫我們考慮到了這一點，也間接性的證明了這的確是一種可行、並且不錯的儲存方式。

參考

• 1、如何把IP地址儲存到Int型別變數中https://www.jianshu.com/p/1090044c3522
• 2、論IP地址在資料庫中應該用何種形式儲存https://www.cnblogs.com/skynet/archive/2011/01/09/1931044.html
• 3、儲存整數IP地址的MySQL效能優勢https://bafford.com/2009/03/09/mysql-performance-benefits-of-storing-integer-ip-addresses/