MySQL 5.1.72-community下特定格式地址列排序方法问询

我之前处理过类似的字符串拆分排序需求，针对你用的MySQL 5.1.72版本（这个版本没有REGEXP_SUBSTR、REGEXP_REPLACE这类便捷函数），我们可以通过嵌套字符串函数拆分出排序所需的字段，完美实现你要的排序规则。

核心思路 #

你的address列有两种格式：

• 格式1：cellname{p}#{q}T{r}（如elephant4#3T201）
• 格式2：cellname{p}T{r}（如swan2T101）

我们需要拆分出三个排序维度：

1. cellname：纯文本部分，按文本字典序排序
2. p：cellname后的数字，按数值大小排序
3. q：#和T之间的数字（格式2的q视为0），按数值大小排序

实现SQL #

下面是完整的查询语句，我用派生表提前计算出重复用到的中间值，让排序逻辑更清晰：

SELECT t.address
FROM (
    SELECT 
        address,
        -- 提取纯文本cellname：去掉所有数字、#、T
        REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
            address, '#', ''), 'T', ''), '0', ''), '1', ''), '2', ''), '3', ''), '4', ''), '5', ''), '6', ''), '7', ''), '8', ''), '9', '') AS cellname,
        -- 找到第一个数字出现的位置
        (SELECT MIN(pos) FROM (
            SELECT LOCATE('0', address) pos UNION 
            SELECT LOCATE('1', address) UNION 
            SELECT LOCATE('2', address) UNION 
            SELECT LOCATE('3', address) UNION 
            SELECT LOCATE('4', address) UNION 
            SELECT LOCATE('5', address) UNION 
            SELECT LOCATE('6', address) UNION 
            SELECT LOCATE('7', address) UNION 
            SELECT LOCATE('8', address) UNION 
            SELECT LOCATE('9', address)
        ) AS nums WHERE pos > 0) AS first_num_pos,
        -- 确定分隔符位置：优先取#，没有则取T
        IF(LOCATE('#', address) > 0, LOCATE('#', address), LOCATE('T', address)) AS sep_pos,
        -- 提取q的值：有#则取#和T之间的数字，无#则为0
        CAST(IF(LOCATE('#', address) > 0, SUBSTRING_INDEX(SUBSTRING_INDEX(address, 'T', 1), '#', -1), 0) AS UNSIGNED) AS q
    FROM your_table -- 替换成你的表名
) t
ORDER BY
    t.cellname ASC, -- 按cellname文本排序
    CAST(SUBSTRING(t.address, t.first_num_pos, t.sep_pos - t.first_num_pos) AS UNSIGNED) ASC, -- 按p数值排序
    t.q ASC; -- 按q数值排序

效果验证 #

拿你提到的例子测试：

• elephant5#2T201、elephant14#3T201：cellname都是elephant，p分别是5和14，所以elephant5#2T201会排在前面
• swan2T101、swan3#1T101：cellname都是swan，p分别是2和3，且前者q为0，所以swan2T101排在swan3#1T101前面

这个方案完全适配MySQL 5.1.72的特性，没有用到高版本才有的函数，放心用就行。

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