如何在MySQL中按字符串中的数字排序

在管理数据库时，我们经常遇到需要按嵌入在字符串中的数字进行排序的情况。这在实际应用中尤为常见，比如文件名、代码版本号等字段中通常包含数字，而这些数字往往是排序的关键。本文将详细介绍如何在MySQL中利用正则表达式提取字符串中的数字并按这些数字进行排序，以一个具体的例子来说明，使得即使是数据库操作的初学者也能轻松理解和应用。

场景示例

假设你管理的数据库中有一个表 sys_oss，它记录了多媒体文件的信息。表中有一个字段 original_name，其中包含了文件的命名信息，格式大致为“中文_数字.mp4”。现在，我们的任务是按照文件名中的数字顺序对这些记录进行排序。

示例数据

让我们先看几个 original_name 的示例值：

• 中文_1.mp4
• 中文_12.mp4
• 中文_2.mp4
• 中文_10.mp4

如果按照字符串默认的排序方式，排序结果将会是：

1. 中文_1.mp4
2. 中文_10.mp4
3. 中文_12.mp4
4. 中文_2.mp4
   

这显然不符合数字自然排序的逻辑，因为字符串排序是按字符编码顺序逐一比较的。我们的目标是按照数字部分的实际数值进行排序，即：

1. 中文_1.mp4
2. 中文_2.mp4
3. 中文_10.mp4
4. 中文_12.mp4
   

使用 REGEXP_SUBSTR 函数提取并排序

在MySQL 8.0及以上版本中，我们可以使用 REGEXP_SUBSTR() 函数来提取字符串中的数字部分。这个函数允许我们使用正则表达式来指定我们想要匹配的模式。在这个例子中，我们使用正则表达式 \\d+ 来匹配一个或多个数字。

以下是完整的SQL查询，用于实现按数字排序：

SELECT *
FROM sys_oss
WHERE original_name LIKE '%中文%'
ORDER BY CAST(REGEXP_SUBSTR(original_name, '\\d+') AS UNSIGNED);

这条SQL语句做了以下几件事：

• WHERE original_name LIKE '%中文%'：筛选出所有文件名包含“中文”的记录。
• REGEXP_SUBSTR(original_name, '\\d+')：从 original_name 中提取第一组连续的数字。
• CAST(... AS UNSIGNED)：将提取出的字符串转换成无符号整数，以便按数字进行排序。

结论

使用 REGEXP_SUBSTR 提取数字并结合 CAST 函数转换类型，使我们能够按照数字的实际数值对字符串进行排序。这种技巧不仅适用于文件名，也可以广泛应用于任何包含数字的字符串字段排序，如订单编号、版本号等