如何实现Sphinx Search中字符串属性的自然排序？

你遇到的这个问题是字符串排序里典型的字典序 vs 自然排序冲突——Sphinx默认对sql_attr_string类型的属性采用字典序排序，所以会出现100b排在2a前面的情况，毕竟字典序里1的优先级比2高。

下面给你几个可行的解决方案，按推荐程度排序：

1. 用Sphinx的自然排序校对规则（最简单，优先推荐） #

如果你的Sphinx版本是3.0.3及以上，直接在ORDER BY里加COLLATE natural就能搞定：

SELECT * FROM myindex ORDER BY myattribute COLLATE natural ASC;

这个校对规则会自动识别字符串里的数字段，按照数字的实际大小排序，正好能得到你想要的1a, 2a, 3a, 100b, 101c结果。

2. 拆分属性为数字+字符串（兼容性拉满，性能更优） #

要是你的Sphinx版本比较老，不支持COLLATE natural，可以在索引阶段把myattribute拆成两个属性：一个存数字部分（整数类型），一个存剩下的字符串部分。

第一步：修改索引配置

在你的Sphinx配置文件里，调整sql_query来提取这两个部分：

sql_query = SELECT id, myattribute, 
  CAST(REGEXP_SUBSTR(myattribute, '^[0-9]+') AS UNSIGNED) AS attr_num,
  REGEXP_SUBSTR(myattribute, '[^0-9]+$') AS attr_str
FROM your_source_table;

sql_attr_uint = attr_num  # 把数字部分存为整数属性
sql_attr_string = attr_str  # 非数字部分存为字符串属性

（注：这里用的是MySQL的REGEXP_SUBSTR，如果是其他数据库，替换成对应的字符串提取函数就行，比如PostgreSQL用SUBSTRING配合正则）

第二步：查询时按拆分后的属性排序

SELECT * FROM myindex ORDER BY attr_num ASC, attr_str ASC;

这种方法的优势是排序性能更好（整数排序比字符串快），而且能兼容所有Sphinx版本。

3. 查询时动态提取排序（应急方案，适合小数据量） #

如果暂时没法改索引配置，也可以在查询时用Sphinx内置函数动态提取数字部分来排序：

SELECT * FROM myindex 
ORDER BY CAST(REGEXTRACT(myattribute, '^[0-9]+') AS UNSIGNED) ASC, 
         REGEXTRACT(myattribute, '[^0-9]+$') ASC;

REGEXTRACT是Sphinx自带的正则提取函数，能从字符串里匹配出指定模式的内容。不过这种方法每次查询都要做字符串处理，性能不如提前索引属性，适合临时测试或者数据量不大的场景。

内容的提问来源于stack exchange，提问作者koseduhemak