MySQL中字母数字文件名的自然排序问题

你之前用的Cast(page_name as Unsigned)根本提取不到有效数字——page_name开头是字母，转成无符号整数直接变成0，等于没按页码排序。

要实现先按电子书编号分组，再按页码数字升序排列，得把page_name里的电子书标识和页码拆出来，用下面的SQL：

SELECT * 
FROM `eb_contents` 
ORDER BY 
  SUBSTRING_INDEX(SUBSTRING_INDEX(page_name, '-', 2), '-', -1), -- 提取中间的电子书编号（比如00GUKP1142）
  CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(page_name, '-', -1), '.', 1) AS UNSIGNED); -- 提取页码数字（比如7、10）

为啥这么写： #

• 拆分电子书编号：先用SUBSTRING_INDEX(page_name, '-', 2)拿到EBook-00GUKP1142，再用SUBSTRING_INDEX(..., '-', -1)抠出中间的00GUKP1142，按这个排序就能把同一本电子书的页归到一块。
• 拆分页码：先用SUBSTRING_INDEX(page_name, '-', -1)拿到7.jpg，再用SUBSTRING_INDEX(..., '.', 1)得到纯数字7，转成整数后就能实现数字排序——这样7就会排在10前面，而不是字符串排序时的10在7前面。

如果需要先按电子书编号里的前缀数字（比如00、02、04）排序，再按完整编号排，用这个优化版：

SELECT * 
FROM `eb_contents` 
ORDER BY 
  CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(page_name, 'GUKP', 1), '-', -1) AS UNSIGNED), -- 提取00、02这类数字排序
  SUBSTRING_INDEX(SUBSTRING_INDEX(page_name, '-', 2), '-', -1), -- 再按完整电子书编号排序
  CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(page_name, '-', -1), '.', 1) AS UNSIGNED);

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