你有这么高效的MySQL版本号排序的SQL,记住我给出的原理。入门学习MySQL的时候,就是给我讲课的老师,就是这么给我讲的:MySQL执行SQL语句过程
在当前系统中,我们遇到一个关于版本检查接口返回结果排序不准确的问题。具体表现为,当接口返回多个软件版本号进行排序时,版本号“1.0.12”被错误地置于“1.0.2”之后等类似这样的情况,这种情况明显违背了通常的版本比较逻辑。这一现象揭示了接口在处理多级版本号排序时存在算法缺陷或逻辑误解,导致无法正确识别和比较版本间的实际层级关系。
为清晰阐述问题细节,以下是具体问题表现的进一步说明:
为解决上述问题,我们需要对版本检查接口进行优化,确保其在返回版本列表时能准确遵循标准的版本号比较规则,即:
优化版本检查接口的关键在于重构其内部的版本号比较与排序逻辑,确保其严格遵循标准的版本号解析与比较规则,提供准确无误的版本排序结果,进而消除因版本识别混乱带来的各种潜在问题,提升系统的整体稳定性和用户体验。
测试数据脚本如下:
/*
Navicat MySQL Data Transfer
Source Server :
Source Server Version :
Source Host :
Source Database :
Target Server Type : MYSQL
Target Server Version : 50736
File Encoding : 65001
Date: 2024-04-19 10:59:44
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for test_version
-- ----------------------------
DROP TABLE IF EXISTS `test_version`;
CREATE TABLE `test_version` (
`version` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of test_version
-- ----------------------------
INSERT INTO `test_version` VALUES ('1.0.2');
INSERT INTO `test_version` VALUES ('1.0.12');
INSERT INTO `test_version` VALUES ('2.0.34');
INSERT INTO `test_version` VALUES ('2.0.38');
INSERT INTO `test_version` VALUES ('2.10.1');
INSERT INTO `test_version` VALUES ('3.6.1');
INSERT INTO `test_version` VALUES ('3.5.8');
INSERT INTO `test_version` VALUES ('3.5.13');
使用自定义排序函数
创建一个自定义函数,将版本号转换为整数,以便在排序时使用整数比较。
DELIMITER $$
CREATE FUNCTION VERSION_TO_INT(version VARCHAR(255)) RETURNS INT DETERMINISTIC
BEGIN
DECLARE major, minor, patch INT;
SET major = SUBSTRING_INDEX(version, '.', 1);
SET minor = SUBSTRING_INDEX(SUBSTRING_INDEX(version, '.', 2), '.', -1);
SET patch = SUBSTRING_INDEX(version, '.', -1);
RETURN major * 10000 + minor * 100 + patch;
END $$
DELIMITER ;
DELIMITER
定义一个新的语句结束符,以便在定义函数时使用。CREATE FUNCTION
创建一个名为VERSION_TO_INT
的自定义函数,该函数接受一个VARCHAR(255)
类型的参数,并返回一个INT
类型的值。SUBSTRING_INDEX
函数提取版本号的主要、次要和补丁部分,并将它们转换为整数。RETURN
语句返回计算出的整数值。DELIMITER ;
将语句结束符重置为默认值。ORDER BY
子句和自定义函数VERSION_TO_INT
对版本号进行排序。SELECT * FROM test_version
ORDER BY VERSION_TO_INT(version) DESC;
输出:
--------------------------------------------
3.6.1
3.5.13
3.5.8
2.10.1
2.0.38
2.0.34
1.0.12
1.0.2
--------------------------------------------
使用内置函数SUBSTRING_INDEX
和CAST
使用SUBSTRING_INDEX
函数提取版本号的主要、次要和补丁部分,并使用CAST
函数将它们转换为无符号整数,然后在ORDER BY
子句中使用这些整数进行排序。
SELECT * FROM your_table_name
ORDER BY
CAST(SUBSTRING_INDEX(version_column_name, '.', 1) AS UNSIGNED),
CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(version_column_name, '.', 2), '.', -1) AS UNSIGNED),
CAST(SUBSTRING_INDEX(version_column_name, '.', -1) AS UNSIGNED);
SUBSTRING_INDEX
函数提取版本号的主要、次要和补丁部分。CAST
函数将提取到的部分转换为无符号整数。ORDER BY
子句中使用这些整数进行排序。SELECT * FROM test_version
ORDER BY
CAST(SUBSTRING_INDEX(version, '.', 1) AS UNSIGNED) DESC,
CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(version, '.', 2), '.', -1) AS UNSIGNED) DESC,
CAST(SUBSTRING_INDEX(version, '.', -1) AS UNSIGNED) DESC ;
使用内置函数SUBSTRING_INDEX
和CONVERT
与方案二类似,但使用CONVERT
函数将提取到的部分转换为无符号整数。
SELECT * FROM your_table_name
ORDER BY
CONVERT(SUBSTRING_INDEX(version_column_name, '.', 1), UNSIGNED),
CONVERT(SUBSTRING_INDEX(SUBSTRING_INDEX(version_column_name, '.', 2), '.', -1), UNSIGNED),
CONVERT(SUBSTRING_INDEX(version_column_name, '.', -1), UNSIGNED);
SUBSTRING_INDEX
函数提取版本号的主要、次要和补丁部分。CONVERT
函数将提取到的部分转换为无符号整数。ORDER BY
子句中使用这些整数进行排序。SELECT * FROM test_version
ORDER BY
CONVERT(SUBSTRING_INDEX(version, '.', 1), UNSIGNED) DESC,
CONVERT(SUBSTRING_INDEX(SUBSTRING_INDEX(version, '.', 2), '.', -1), UNSIGNED) DESC,
CONVERT(SUBSTRING_INDEX(version, '.', -1), UNSIGNED) DESC;
使用内置函数INET_ATON
(适用于最多三位数的版本号)
将版本号使用INET_ATON
函数将其转换为整数,最后在ORDER BY
子句中使用这些整数进行排序。
SELECT * FROM your_table_name
ORDER BY INET_ATON(version_column_name);
INET_ATON
函数将替换后的版本号转换为整数。ORDER BY
子句中使用这些整数进行排序。INET_ATON
是 MySQL 中的一个函数,用于将 IPv4 地址转换为无符号整数(32 位)。其名称是 Internet Address to Number
的缩写,表示将 Internet 地址转换为数字。具体来说,INET_ATON
函数接受一个形如 "xxx.xxx.xxx.xxx"
的 IPv4 地址字符串,并返回对应的 32 位无符号整数。
例如,IPv4 地址 "192.168.1.1"
将被转换为整数 3232235777。
使用 INET_ATON
函数有几个方面需要注意:
INET_ATON
函数要求输入的 IPv4 地址必须符合标准的 IPv4 地址格式,即由四个用点分隔的十进制数字组成,每个数字范围在 0 到 255 之间。INET_ATON
函数返回一个无符号整数,表示输入的 IPv4 地址对应的 32 位整数。如果输入的地址不是有效的 IPv4 地址,则返回 NULL。INET_ATON
函数可以将字符串形式的 IPv4 地址转换为整数,但它并不适用于所有的数字转换需求SELECT *,INET_ATON(version) FROM test_version
ORDER BY INET_ATON(version) DESC;
---------------------------------------------
3.6.1 50724865
3.5.13 50659341
3.5.8 50659336
2.10.1 34209793
2.0.38 33554470
2.0.34 33554466
1.0.12 16777228
1.0.2 16777218
----------------------------------------------
在MySQL中,查询效率通常取决于多个因素,如索引的使用、数据类型的选择、函数的使用等
这种方案的查询效率可能较低,因为它使用了自定义函数。自定义函数在每一行数据上都需要执行,这可能导致性能下降,尤其是在处理大量数据时。此外,自定义函数可能不会使用索引,从而进一步降低查询效率。
SUBSTRING_INDEX
和CAST
或CONVERT
这两种方案的查询效率可能较高,因为它们使用了内置函数。内置函数通常比自定义函数更高效,因为它们是由MySQL引擎实现的,可以更好地利用底层的优化。此外,这两种方案可以利用列上的索引(如果存在)来提高查询效率。
INET_ATON
(适用于最多三位数的版本号)这种方案的查询效率可能较高,因为它使用了内置函数。然而,它的适用性受到了版本号位数的限制。此外,INET_ATON
函数是用于将IPv4地址转换为整数的函数,虽然它可以用于处理最多三位数的版本号,但这种用法可能会让人困惑。
总之,方案二和方案三的查询效率可能更高,因为它们使用了内置函数。方案一的查询效率可能较低,因为它使用了自定义函数。方案四的查询效率可能取决于版本号的位数,但在某些情况下可能是可接受的。在实际应用中,可能需要根据具体情况和需求来选择最合适的方案。同时,为了提高查询效率,可以考虑将原始的版本号列转换为更易于排序的格式,例如将其存储为整数或使用其他方法进行规范化。
使用MySQL的EXPLAIN
语句和BENCHMARK
函数
EXPLAIN
语句可以帮助了解查询的执行计划,包括使用的索引、扫描的行数等。可以使用EXPLAIN
语句分析每种方案的查询计划,以了解它们在执行过程中的性能差异。
例如:
EXPLAIN SELECT * FROM test_version
ORDER BY VERSION_TO_INT(version) DESC;
EXPLAIN SELECT * FROM test_version
ORDER BY
CAST(SUBSTRING_INDEX(version, '.', 1) AS UNSIGNED) DESC,
CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(version, '.', 2), '.', -1) AS UNSIGNED) DESC,
CAST(SUBSTRING_INDEX(version, '.', -1) AS UNSIGNED) DESC ;
EXPLAIN SELECT * FROM test_version
ORDER BY
CONVERT(SUBSTRING_INDEX(version, '.', 1), UNSIGNED) DESC,
CONVERT(SUBSTRING_INDEX(SUBSTRING_INDEX(version, '.', 2), '.', -1), UNSIGNED) DESC,
CONVERT(SUBSTRING_INDEX(version, '.', -1), UNSIGNED) DESC;
EXPLAIN SELECT * FROM test_version
ORDER BY INET_ATON(version) DESC;
在使用MySQL的EXPLAIN语句分析查询计划时,以下关键字是重要的:
➡️ type:表示连接类型。连接类型从最优到最差的顺序是:system > const > eq_ref > ref > range > index > ALL。ALL表示全表扫描,这通常是性能低下的表现。
➡️ possible_keys:表示MySQL可以使用哪些索引来优化查询。这并不意味着MySQL一定会使用这些索引,而只是表示它们是可用的。
➡️ key:表示MySQL实际使用的索引。如果key列为NULL,则表示查询没有使用索引。
➡️ key_len:表示MySQL使用的索引的长度。较长的索引通常意味着更精确的查找,但也可能导致性能下降。
➡️ ref:表示索引的哪个部分被用于查找。如果ref列为const,则表示查询使用了常量值;如果为NULL,则表示查询使用了全表扫描。
➡️ rows:表示MySQL预计要检查的行数。这个值可以帮助了解查询的效率。较低的行数通常意味着更高的效率。
➡️ filtered:表示按表条件筛选的行的百分比。100%表示所有行都符合条件,0%表示没有行符合条件。这个值可以帮助了解查询的效率。
➡️ Extra:包含未列出在其他列中的额外信息。例如,Using temporary表示查询使用了临时表;Using filesort表示查询需要额外的文件排序操作。这些信息可以帮助了解查询的性能瓶颈。
在分析EXPLAIN输出时,应该关注这些关键字,以了解查询的执行计划和性能。根据这些信息,可以优化查询和索引,以提高查询效率。在实际应用中,可能需要根据具体情况调整这些关键字的重要性。同时,为了提高查询效率,可以考虑将原始的版本号列转换为更易于排序的格式,例如将其存储为整数或使用其他方法进行规范化。
BENCHMARK
函数可以帮助测量查询的执行时间。可以使用BENCHMARK
函数运行每种方案的查询多次,以了解它们在实际执行中的性能差异。
例如:
SELECT BENCHMARK(100000000, (SELECT COUNT(*) FROM test_version
ORDER BY VERSION_TO_INT(version) DESC));
SELECT BENCHMARK(100000000, ( SELECT COUNT(*) FROM test_version
ORDER BY
CAST(SUBSTRING_INDEX(version, '.', 1) AS UNSIGNED) DESC,
CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(version, '.', 2), '.', -1) AS UNSIGNED) DESC,
CAST(SUBSTRING_INDEX(version, '.', -1) AS UNSIGNED) DESC ));
SELECT BENCHMARK(100000000, ( SELECT COUNT(*) FROM test_version
ORDER BY
CONVERT(SUBSTRING_INDEX(version, '.', 1), UNSIGNED) DESC,
CONVERT(SUBSTRING_INDEX(SUBSTRING_INDEX(version, '.', 2), '.', -1), UNSIGNED) DESC,
CONVERT(SUBSTRING_INDEX(version, '.', -1), UNSIGNED) DESC ));
SELECT BENCHMARK(100000000, ( SELECT COUNT(*) FROM test_version
ORDER BY INET_ATON(version) DESC ));
执行结果:
可以看出,第二种方式和第三种方式执行结果相近,第一种因为使用了临时表的原因,执行效率要差一些;第四种效率上较高,但是具有局限性。
[SQL]SELECT BENCHMARK(100000000, (SELECT COUNT(*) FROM test_version
ORDER BY VERSION_TO_INT(version) DESC));
受影响的行: 0
时间: 2.162s
[SQL]
SELECT BENCHMARK(100000000, ( SELECT COUNT(*) FROM test_version
ORDER BY
CAST(SUBSTRING_INDEX(version, '.', 1) AS UNSIGNED) DESC,
CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(version, '.', 2), '.', -1) AS UNSIGNED) DESC,
CAST(SUBSTRING_INDEX(version, '.', -1) AS UNSIGNED) DESC ));
受影响的行: 0
时间: 2.110s
[SQL]
SELECT BENCHMARK(100000000, ( SELECT COUNT(*) FROM test_version
ORDER BY
CONVERT(SUBSTRING_INDEX(version, '.', 1), UNSIGNED) DESC,
CONVERT(SUBSTRING_INDEX(SUBSTRING_INDEX(version, '.', 2), '.', -1), UNSIGNED) DESC,
CONVERT(SUBSTRING_INDEX(version, '.', -1), UNSIGNED) DESC ));
受影响的行: 0
时间: 2.113s
[SQL]
SELECT BENCHMARK(100000000, ( SELECT COUNT(*) FROM test_version
ORDER BY INET_ATON(version) DESC ));
受影响的行: 0
时间: 2.049s
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。