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

MobiusStrip發表於2020-05-30

前幾天,阿淼的一個朋友去面試,他回來告訴我,面試官問他 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_ATONINET_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_ATONINET6_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地址 轉換成數字儲存是一種不錯的選擇麼,畢竟資料庫都幫我們考慮到了這一點,也間接性的證明了這的確是一種可行、並且不錯的儲存方式。

參考

相關文章