mysql ip地址 分割_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)