MySQL 分解IP地址

备注:测试数据库版本为MySQL 8.0

一.需求

将一个IP地址分解到列中，考虑下面列出的IP地址:
111.22.3.4

要得到如下所示的查询结构:
A B C D
111 22 3 4

二.解决方案

依旧是MySQl自带的substring_index函数即可解决

代码:

select substring_index(ip,'.',1) A,
       substring_index(substring_index(ip,'.',2),'.',-1) B,
       substring_index(substring_index(ip,'.',3),'.',-1) C,
       substring_index(ip,'.',-1) D
from  
(select '111.22.3.4' as ip) c

测试记录:

mysql> select substring_index(ip,'.',1) A,
    ->        substring_index(substring_index(ip,'.',2),'.',-1) B,
    ->        substring_index(substring_index(ip,'.',3),'.',-1) C,
    ->        substring_index(ip,'.',-1) D
    -> from
    -> (select '111.22.3.4' as ip) c;
+------+------+------+------+
| A    | B    | C    | D    |
+------+------+------+------+
| 111  | 22   | 3    | 4    |
+------+------+------+------+
1 row in set (0.00 sec)

也可以考虑使用正则表达式
代码:

SELECT regexp_substr(ip, '[^.]+', 1, 1) a,
       regexp_substr(ip, '[^.]+', 1, 2) b,
       regexp_substr(ip, '[^.]+', 1, 3) c,
       regexp_substr(ip, '[^.]+', 1, 4) d
  FROM (SELECT '111.22.3.4' AS ip FROM dual) tmp;

测试记录:

mysql> SELECT regexp_substr(ip, '[^.]+', 1, 1) a,
    ->        regexp_substr(ip, '[^.]+', 1, 2) b,
    ->        regexp_substr(ip, '[^.]+', 1, 3) c,
    ->        regexp_substr(ip, '[^.]+', 1, 4) d
    ->   FROM (SELECT '111.22.3.4' AS ip FROM dual) tmp;
+-----+----+---+---+
| a   | b  | c | d |
+-----+----+---+---+
| 111 | 22 | 3 | 4 |
+-----+----+---+---+
1 row in set (0.00 sec)