MySQL实现id_code字段字符优先、数字数值排序的技术问询

这个问题太常见啦——当字符串里混着文本和数字时，单纯的字典序排序肯定会出问题，毕竟字符串里的10会比2靠前。咱们可以把id_code拆成前缀文本部分和末尾数字部分，分别排序就能实现你要的效果：先按文本前缀排序，再按末尾数字的数值大小排序。

下面针对主流数据库给出具体解决方案：

MySQL/MariaDB 实现 #

利用字符串截取函数拆分前缀和数字部分：

SELECT id_code
FROM your_table
ORDER BY
  -- 提取最后一个点之前的完整文本前缀
  SUBSTRING(id_code, 1, LENGTH(id_code) - LENGTH(SUBSTRING_INDEX(id_code, '.', -1)) - 1),
  -- 提取末尾数字并转为数值类型排序
  CAST(SUBSTRING_INDEX(id_code, '.', -1) AS UNSIGNED);

原理说明 #

• SUBSTRING_INDEX(id_code, '.', -1)：快速提取最后一个点后面的数字字符串
• 前缀部分通过计算总长度减去数字部分的长度，再减去1（去掉最后一个点），确保拿到完整的文本前缀
• 把数字字符串转为无符号整数后，就能按数值大小排序，而不是字典序

PostgreSQL 实现 #

用正则表达式更灵活地拆分内容：

SELECT id_code
FROM your_table
ORDER BY
  -- 替换掉末尾的".数字"部分，得到文本前缀
  regexp_replace(id_code, '\.\d+$', '', 'g'),
  -- 捕获末尾的数字并转为整数
  (regexp_match(id_code, '\.(\d+)$'))[1]::INTEGER;

原理说明 #

• 正则\.\d+$匹配字符串末尾的"点+数字"组合，替换为空后得到纯文本前缀
• regexp_match捕获最后一个点后面的数字组，转为整数后按数值排序

SQL Server 实现 #

通过反向查找定位最后一个点的位置来拆分：

SELECT id_code
FROM your_table
ORDER BY
  -- 提取最后一个点之前的文本前缀
  LEFT(id_code, LEN(id_code) - CHARINDEX('.', REVERSE(id_code))),
  -- 提取末尾数字并转为整数
  CAST(RIGHT(id_code, CHARINDEX('.', REVERSE(id_code)) - 1) AS INT);

原理说明 #

• REVERSE(id_code)反转字符串后，CHARINDEX('.', REVERSE(id_code))找到反转后第一个点的位置，对应原字符串最后一个点的位置
• 用LEFT和RIGHT分别截取前缀文本和数字部分，转成整数后实现数值排序

用以上任意一种方案，都能得到你期望的排序结果：

GDA.DSAETA.YSTA.1、GDA.DSAETA.YSTA.2、GDAD.DAD.YM.1、GDAD.DAD.YM.2、GDAD.DAD.YM.3、GDAD.DAD.YM.10、GDAD.DAD.YM.11

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