你有这么高效的MySQL版本号排序的SQL，记住我给出的原理。入门学习MySQL的时候，就是给我讲课的老师，就是这么给我讲的：MySQL执行SQL语句过程
前情提要
在当前系统中，我们遇到一个关于版本检查接口返回结果排序不准确的问题。具体表现为，当接口返回多个软件版本号进行排序时，版本号“1.0.12”被错误地置于“1.0.2”之后等类似这样的情况，这种情况明显违背了通常的版本比较逻辑。这一现象揭示了接口在处理多级版本号排序时存在算法缺陷或逻辑误解，导致无法正确识别和比较版本间的实际层级关系。
模拟数据
为清晰阐述问题细节，以下是具体问题表现的进一步说明：
问题现象：
- 👨🏻💻 不合理的版本排序实例：当接口提供的版本列表包含“1.0.12”与“1.0.2”两个版本时，返回的排序结果将“1.0.2”置于“1.0.12”之前，即：“1.0.2” > “1.0.12”。
- 👨🏻💻 预期正确排序：根据常规版本号比较规则，版本号由左至右依次代表主版本号、次版本号和修订号。在比较时，应先比较最左侧的主版本号，若相同则比较次版本号，再相同则比较修订号。依据此规则，正确的排序应为：“1.0.12” > “1.0.2”，因为“1.0.12”的主版本号“12”大于“1.0.2”的主版本号“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函数提取版本号的主要、次要和补丁部分，并将它们转换为整数。 - 将提取到的整数部分组合成一个整数，其中主要版本号乘以10000，次要版本号乘以100，补丁版本号保持不变。这样可以确保在比较时，主要版本号的优先级最高，次要版本号次之，补丁版本号最低。
- 使用
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 函数有几个方面需要注意：
- IPv4 格式要求：
INET_ATON函数要求输入的 IPv4 地址必须符合标准的 IPv4 地址格式，即由四个用点分隔的十进制数字组成，每个数字范围在 0 到 255 之间。 - 返回值：
INET_ATON函数返回一个无符号整数，表示输入的 IPv4 地址对应的 32 位整数。如果输入的地址不是有效的 IPv4 地址，则返回 NULL。 - 适用范围： 主要用于处理网络相关的数据，例如 IP 地址的存储和比较。在实际应用中，通常用于存储 IP 地址，以便在数据库中进行高效的搜索和排序。
- 不适合所有情况： 尽管
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语句分析每种方案的查询计划，以了解它们在执行过程中的性能差异。
例如：
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函数可以帮助测量查询的执行时间。可以使用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
