MySQL中MBT格式编码的自然排序功能失效如何解决

DevOps 小满

2026-9-25

解决方案

你的编号规则是固定格式MBT[数字1]-[数字2]N-[数字3]，只需要把三段数字分别提取后按数值排序即可，正确的SQL写法如下：

SELECT post_title FROM `wp_posts` 
WHERE post_type = 'product' AND post_title LIKE 'MBT%' 
ORDER BY
  -- 第一段排序：提取MBT后到第一个横杠前的数值排序
  CAST(SUBSTRING(SUBSTRING_INDEX(post_title, '-', 1), 4) AS UNSIGNED) ASC,
  -- 第二段排序：提取两个横杠中间的内容去掉N后转数值排序
  CAST(TRIM(TRAILING 'N' FROM SUBSTRING_INDEX(SUBSTRING_INDEX(post_title, '-', 2), '-', -1)) AS UNSIGNED) ASC,
  -- 第三段排序：提取最后一个横杠后的数值排序
  CAST(SUBSTRING_INDEX(post_title, '-', -1) AS UNSIGNED) ASC;

如果是在WordPress的查询中调用，排序段的写法为：

$order_by = "
  CAST(SUBSTRING(SUBSTRING_INDEX({$wpdb->posts}.post_title, '-', 1), 4) AS UNSIGNED) ASC,
  CAST(TRIM(TRAILING 'N' FROM SUBSTRING_INDEX(SUBSTRING_INDEX({$wpdb->posts}.post_title, '-', 2), '-', -1)) AS UNSIGNED) ASC,
  CAST(SUBSTRING_INDEX({$wpdb->posts}.post_title, '-', -1) AS UNSIGNED) ASC
";