【mysql】IP地址整数int和varchar的转换

mysql中IP地址的存储

IP：如192.168.12.145，在存储时，若是采用varchar进行存储，存在两个主要缺点：

1. 存储空间占用较大；
2. 查询检索较慢；

解决方式：

1. 存储时：将字符串类型的IP转换为整型进行存储；
2. 查询时：将整型的IP转换为字符串；

---

Mysql自带的IP转换语句

• inet_aton:将ip地址转换成数字型
• inet_ntoa:将数字型转换成ip地址

示例1：

//使用inet_aton函数，将字符串IP转换为整型；
mysql> select inet_aton('73.115.134.73') as ip;
+------------+
| ip         |
+------------+
| 1232307785 |
+------------+

//使用inet_ntoa函数，将整型IP转换为字符串；
mysql> select inet_ntoa(1232307785) as ip;
+---------------+
| ip            |
+---------------+
| 73.115.134.73 |
+---------------+

示例2：

//不进行转换，查询结果为整型
mysql> select src_ip from natTable limit 5;  
+------------+
| src_ip     |
+------------+
| 1232307785 |
| 1232285337 |
| 1232323310 |
| 1232325234 |
| 1232326662 |
+------------+

//通过inet_ntoa函数进行转换，查询结果为IP格式的字符串
mysql> select inet_ntoa(src_ip) from natTable limit 5;
+-------------------+
| inet_ntoa(src_ip) |
+-------------------+
| 73.115.134.73     |
| 73.115.46.153     |
| 73.115.194.238    |
| 73.115.202.114    |
| 73.115.208.6      |
+-------------------+

---

自定义转换函数：

如：将1232307785转换为73.116.134.73

DELIMITER $$
CREATE FUNCTION natAndImDbTest11.ipBigIntToString ( 
    ip bigint 
) 
RETURNS CHAR(15) 
BEGIN 
    DECLARE o1 INT; 
    DECLARE o2 INT; 
    DECLARE o3 INT; 
    DECLARE o4 INT; 
    DECLARE ip_new_varchar VARCHAR(15);
 
    IF (ip > 4294967295) then 
       RETURN '255.255.255.255';
    END if;

    IF (ip <= 0) then
       RETURN '0.0.0.0' ;
    END if;

    SET o1 = ip / 16777216;
    SET ip = ip % 16777216 ;

    SET o2 = ip / 65536 ;
    SET ip = ip % 65536 ;

    SET o3 = ip / 256 ;
    SET ip = ip % 256 ;

    SET o4 = ip ;
    SET ip_new_varchar = concat( cast(o1 as char(3)),'.',cast(o2 as char(3)),'.',cast(o3 as char(3)),'.',cast(o4 as char(3)));
    
    RETURN  (ip_new_varchar);
END;
$$
DELIMITER ;

测试：

use natAndImDbTest11;
mysql> select ipBigIntToString(1232307785);
+------------------------------+
| ipBigIntToString(1232307785) |
+------------------------------+
| 73.116.134.73                |
+------------------------------+

其他说明

删除自定义方法

drop FUNCTION if exists natAndImDbTest11.ipBigIntToString;

自定义方法的局限

本打算使用 ipBigIntToString 代替 inet_ntoa 进行查询，发现不行：

mysql> select ipBigIntToString(src_ip) from natTable limit 5;
ERROR 5 (HY000): The query includes syntax that is not supported by the Infobright Optimizer. Either restructure the query with supported syntax, or enable the MySQL Query Path in the brighthouse.ini file to execute the query with reduced performance.
mysql> 

可能有其他方式可以使用ipBigIntToString替代inet_ntoa，但是目前自己未成功；