mysql - 对 varchar 列中包含文本和数字的数据进行排序

Question

我在 MySQL 数据库中有一个 varchar 列，它存储一些可以包含任何字符和数字的文本。

该模式使用 utf8 作为字符集，使用 utf8_unicode_ci 作为排序规则。 InnoDB 用作数据库引擎，因为我需要使用事务。

我想做的是能够以自然的方式ORDER BY 该列。例如，这里有一些示例数据:

12234 some random text
my text 23
mytext3
123456abcd
text23
text1
text111
text33

将测试数据插入测试表后，我运行查询并ORDER BY varchar 列ASC:

SELECT * FROM `test` WHERE 1 ORDER BY data ASC;

问题是没有考虑数字的“大小”:

12234 some random text
123456abcd
my text 23
mytext3
text1
text111 <-------
text23
text33

然后我在这里找到了这个 question，它提供了一个(几乎)解决方案:

SELECT * FROM `test` WHERE 1 ORDER BY data * 1 DESC;

123456abcd             <------ These 2 should
12234 some random text <------ be swapped
my text 23
mytext3
text23
text1
text33
text111

上面的另一个缺点是上面不能使用任何索引，因为每一行都有一个操作。

我也不确定一旦我们引入非拉丁字符会产生什么样的效果。

有没有一种高效的方法可以让 MySQL 执行自然语言排序？

Answer 1

你问:

   Is there a performant way to get MySQL to perform natural language sorting?

简短的回答:没有。

更长的答案:

大概您希望 MySQL 以一种处理非数字文本的方式来整理您的日期，使用所选的排序规则和数字文本，就好像它是二进制数字一样。

您将需要创建某种代理键用于整理，并将其存储在自己的列中。您可以通过像这样转换每个文本字符串并将原始字符串和代理键字符串加载到 dbms 中来实现此目的。

Original Text                   Surrogate Key Text
12234 some random text          0000012234 some random text
my text 23                      my text 0000000023
mytext3                         mytext0000000003
123456abcd                      0000123456abcd
text23                          text0000000023
text1                           text0000000001
text111                         text0000000111
text33                          text0000000033
1text123                        0000000001text0000000123
2text124                        0000000002text0000000124

请注意，在本示例中，样本中的每个数字数据 block 都填充为十位十进制数。

当您加载 DBMS 时，这在您的应用程序中是最容易做到的。您可以编写一个简单的字符串解析例程来完成它。您没有告诉我们您使用的是什么应用程序语言。

如果您在每次查询中只整理有限数量的记录，也可以在检索记录后在您的应用程序中执行此操作。