备注:测试数据库版本为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)