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