MySQL 对类似 IP字段排序问题

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了MySQL 对类似 IP字段排序问题，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含2903字，纯文字阅读大概需要5分钟。

内容图文

MySQL 对类似 IP字段排序问题

问题描述

想对一张带有 IP 字段的表，对 IP 字段进行升序排序，方便查看每个段的 IP 信息。

表结构和表数据如下：

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for t_ip
-- ----------------------------
DROP TABLE IF EXISTS `t_ip`;
CREATE TABLE `t_ip`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ip` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 10 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of t_ip
-- ----------------------------
INSERT INTO `t_ip` VALUES (1, '192.168.10.8');
INSERT INTO `t_ip` VALUES (2, '192.168.10.9');
INSERT INTO `t_ip` VALUES (3, '192.168.10.10');
INSERT INTO `t_ip` VALUES (4, '192.168.10.1');
INSERT INTO `t_ip` VALUES (5, '192.168.8.8');
INSERT INTO `t_ip` VALUES (6, '192.167.10.8');
INSERT INTO `t_ip` VALUES (7, '192.167.8.8');
INSERT INTO `t_ip` VALUES (8, '92.168.10.8');
INSERT INTO `t_ip` VALUES (9, '92.68.10.8');

SET FOREIGN_KEY_CHECKS = 1;

数据库查询截图如下：

如果按照 IP 字段升序，查询效果如下：

问题原因

由于 IP 字段是varchar 类型，MySQL在进行排序的时候，并不会将 IP中的 “192”和“92” 自动识别为数字进行处理，而是作为字符串处理了。

因此，会查询到的结果达不到预期。

问题解决

如果想要将92开头的IP，排在192开头的之前，并且每个. 分隔的段都要保持数字上的升序，需要对 IP 字段按照 . 进行分解为4段，逐次排序处理。

处理 SQL 如下：

select ip from t_ip  
ORDER BY 
CONVERT(SUBSTRING_INDEX(ip, '.', 1), SIGNED),
CONVERT(SUBSTRING_INDEX(SUBSTRING_INDEX(ip, '.', 2), '.', -1), SIGNED), 
CONVERT(SUBSTRING_INDEX(SUBSTRING_INDEX(ip, '.', 3), '.', -1), SIGNED), 
CONVERT(SUBSTRING_INDEX(ip, '.', -1), SIGNED);

处理后的效果截图如下（此时已经达到了预期的排序效果）：

【说明】 

 此处用到了MySQL的两个库函数：

1） CONVERT(expr,type)： 此函数的作用是将表达式转换为特定类型

如：上面用到的 CONVERT(SUBSTRING_INDEX(ip, '.', 1), SIGNED) 是将ip字段中获取的第一位，转换为整数。

2）SUBSTRING_INDEX(str,delim,count)：此函数的作用是将一个字段串，按照某个标识字符串截取，并获取第几个之前

如：SUBSTRING_INDEX(ip, '.', 1) 是将IP按照 '.' 分隔，获取第一位。如果是ip是 192.168.10.8，获取的就是 192

       SUBSTRING_INDEX(ip, '.', -1) 是将IP按照 '.' 分隔，获取最后一位。如果是ip是 192.168.10.8，获取的就是 8

额外补充

如果IP字段中存在多IP，如：192.168.10.2,194.16.12.123，要求排序的时候，按照逗号前的第一个IP参与排序。需要如何实现呢？

如果还是按照上面的SQL，发现不能适配该场景，效果如下：

此时，需要对多IP的场景的逗号稍微处理下即可，处理SQL如下：

select ip from t_ip  
ORDER BY 
CONVERT(SUBSTRING_INDEX(SUBSTRING_INDEX(ip, ',', 1), '.', 1), SIGNED),
CONVERT(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(ip, ',', 1), '.', 2), '.', -1), SIGNED), 
CONVERT(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(ip, ',', 1), '.', 3), '.', -1), SIGNED), 
CONVERT(SUBSTRING_INDEX(SUBSTRING_INDEX(ip, ',', 1), '.', -1), SIGNED);

该SQL的查询效果如下：

内容总结

以上是互联网集市为您收集整理的MySQL 对类似 IP字段排序问题全部内容，希望文章能够帮你解决MySQL 对类似 IP字段排序问题所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端