如何修改MySQL查询，使含字母数字的房间号按预期排序？

云原生阿岚

2026-7-7

问题描述

需要对房间号列表进行排序，预期规则如下：

纯数字房间号排在最前面（如1-10）
带字母前缀的房间号，先按字母分组，组内再按数字排序（如A1-A10、B1-B10）

编写的MySQL代码如下：

create table units (id integer, unit_number varchar(100));

insert into units (id, unit_number) values (1, 'A1');
insert into units (id, unit_number) values (2, 'A2');
insert into units (id, unit_number) values (3, 'A3');
insert into units (id, unit_number) values (4, 'A4');
insert into units (id, unit_number) values (5, 'A5');
insert into units (id, unit_number) values (6, 'A6');
insert into units (id, unit_number) values (7, 'A7');
insert into units (id, unit_number) values (8, 'A8');
insert into units (id, unit_number) values (9, 'A9');
insert into units (id, unit_number) values (10, 'A10');
insert into units (id, unit_number) values (11, 'B1');
insert into units (id, unit_number) values (12, 'B2');
insert into units (id, unit_number) values (13, 'B3');
insert into units (id, unit_number) values (14, 'B4');
insert into units (id, unit_number) values (15, 'B5');
insert into units (id, unit_number) values (16, 'B6');
insert into units (id, unit_number) values (17, 'B7');
insert into units (id, unit_number) values (18, 'B8');
insert into units (id, unit_number) values (19, 'B9');
insert into units (id, unit_number) values (20, 'B10');

select * from units ORDER BY LENGTH(unit_number), unit_number;

执行上述查询后，A10会排在B9之后，不符合预期需求，需要改写查询语句实现正确排序。

解决方案

原排序逻辑的问题在于：LENGTH(unit_number)会让长度更短的字符串优先，A10长度为3，B9长度为2，所以B9会排在A10前面。要实现预期排序，需要把房间号拆分为字母前缀和数字部分，分别排序：

提取字母前缀：用正则替换掉所有数字，得到纯字母前缀（纯数字房间号的前缀为空字符串）
提取数字部分：用正则替换掉所有非数字字符，转换为数值类型，确保按数字大小排序
排序优先级：先按字母前缀排序（空前缀的纯数字房间号排在最前），再按数字部分的数值排序

修改后的查询语句如下：

SELECT *
FROM units
ORDER BY
  -- 空前缀（纯数字）排在前面，然后按字母前缀排序
  REGEXP_REPLACE(unit_number, '[0-9]+', ''),
  -- 提取数字部分并转为数值，按数字大小排序
  CAST(REGEXP_REPLACE(unit_number, '[^0-9]+', '') AS UNSIGNED);

如果你的MySQL版本不支持REGEXP_REPLACE（低于8.0），可以用SUBSTRING结合LOCATE来拆分：

SELECT *
FROM units
ORDER BY
  -- 提取字母前缀：从开头到第一个数字的位置之前
  IF(LOCATE('0', unit_number) > 0, LEFT(unit_number, LOCATE('0', unit_number)-1),
     IF(LOCATE('1', unit_number) > 0, LEFT(unit_number, LOCATE('1', unit_number)-1),
        IF(LOCATE('2', unit_number) > 0, LEFT(unit_number, LOCATE('2', unit_number)-1),
           IF(LOCATE('3', unit_number) > 0, LEFT(unit_number, LOCATE('3', unit_number)-1),
              IF(LOCATE('4', unit_number) > 0, LEFT(unit_number, LOCATE('4', unit_number)-1),
                 IF(LOCATE('5', unit_number) > 0, LEFT(unit_number, LOCATE('5', unit_number)-1),
                    IF(LOCATE('6', unit_number) > 0, LEFT(unit_number, LOCATE('6', unit_number)-1),
                       IF(LOCATE('7', unit_number) > 0, LEFT(unit_number, LOCATE('7', unit_number)-1),
                          IF(LOCATE('8', unit_number) > 0, LEFT(unit_number, LOCATE('8', unit_number)-1),
                             IF(LOCATE('9', unit_number) > 0, LEFT(unit_number, LOCATE('9', unit_number)-1),
                                unit_number)))))))))),
  -- 提取数字部分并转为数值
  CAST(SUBSTRING(unit_number, 
    IF(LOCATE('0', unit_number) > 0, LOCATE('0', unit_number),
       IF(LOCATE('1', unit_number) > 0, LOCATE('1', unit_number),
          IF(LOCATE('2', unit_number) > 0, LOCATE('2', unit_number),
             IF(LOCATE('3', unit_number) > 0, LOCATE('3', unit_number),
                IF(LOCATE('4', unit_number) > 0, LOCATE('4', unit_number),
                   IF(LOCATE('5', unit_number) > 0, LOCATE('5', unit_number),
                      IF(LOCATE('6', unit_number) > 0, LOCATE('6', unit_number),
                         IF(LOCATE('7', unit_number) > 0, LOCATE('7', unit_number),
                            IF(LOCATE('8', unit_number) > 0, LOCATE('8', unit_number),
                               IF(LOCATE('9', unit_number) > 0, LOCATE('9', unit_number),
                                  LENGTH(unit_number)+1)))))))))) AS UNSIGNED);

执行修改后的查询后，会得到符合预期的排序：A1-A10依次排列，B1-B10依次排列，且A组整体在B组之前。

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