mysql - 按字母数字顺序排序

Question

我想在数据库中按字母数字顺序对字段进行排序。事实证明这比我想象的要棘手。这只是示例值，内容可能会有所不同，但我希望这足以让您了解想法。

我想对此列表进行排序:

11
01
1
1A
01B
20a
01a
20
1b
2b
02a

像这样:

1
01
1A
01a
1b
01B
02a
2b
11
20
20a

请注意，带或不带前导零的等效数字的相对顺序并不重要，它可以是 1 01 或 01 1。

我已经尝试过CAST(field AS UNSIGNED)但它不起作用。有想法吗？

Answer 1

如果您使用的是 MySQL 8.0 或更高版本，则可以使用 REGEXP_SUBSTR(colname, '[a-z]+$') 获取字母后缀，并使用 CAST(colname AS UNSIGNED ) 获取数字前缀。然后您可以按这些排序。

SELECT code
FROM yourTable
ORDER BY CAST(code AS UNSIGNED), REGEXP_SUBSTR(code, '[a-z]+$')

参见 What is the equivalent of REGEXP_SUBSTR in mysql?了解如何在早期版本的 MySQL 中获得类似的功能。

另一种可能性是:

ORDER BY CAST(code AS UNSIGNED), TRIM(LEADING '0' FROM code)