MySQL产品名称的自然排序

Question

我有一个表，在标题列中有以下内容

a short name
z short name
Oase AquaMax ECO Premium 10000 Pump
Oase AquaMax ECO Premium 12000 Pump
Oase AquaMax ECO Premium 16000 Pump
Oase AquaMax ECO Premium 4000 Pump
Oase AquaMax ECO Premium 6000 Pump
Oase AquaMax ECO Premium 8000 Pump

我需要按照您期望的方式来订购这些产品，如下所示

a short name
Oase AquaMax ECO Premium 4000 Pump
Oase AquaMax ECO Premium 6000 Pump
Oase AquaMax ECO Premium 8000 Pump
Oase AquaMax ECO Premium 10000 Pump
Oase AquaMax ECO Premium 12000 Pump
Oase AquaMax ECO Premium 16000 Pump
z short name

但当我这么做的时候：

select `title` from `products` order by `title` ASC

它们按此顺序出现。

a short name
Oase AquaMax ECO Premium 10000 Pump
Oase AquaMax ECO Premium 12000 Pump
Oase AquaMax ECO Premium 16000 Pump
Oase AquaMax ECO Premium 4000 Pump
Oase AquaMax ECO Premium 6000 Pump
Oase AquaMax ECO Premium 8000 Pump
z short name

我也尝试过：

select `title` from `products` order by `title` + 0 ASC

但这也是同样的事情，如何才能克服这个问题，使它们以正确的顺序出现？

Answer 1

试一试

select title from products order by length(title),title ASC

并且不要使用单引号将表和字段名称括起来。

Answer 2

您可以使用此查询将标题列转换为ASC顺序：

SELECT title
FROM temp
ORDER BY CONVERT( SUBSTRING( title, 26, 9 ) , UNSIGNEDINTEGER ) 

Answer 3

所以主要的问题是你的字符串中有一个整数，你想在你的查询中使用它作为辅助排序。

这里的问题是，MySQL并不只是简单地支持从字符串中提取该数字(这里使用正则表达式会更好，正如本文question中所讨论的那样)。另一种解决方案可能是，当第一个数字显示为here时，尝试找到第一个数字的位置(使用locate)。

如果标题中最多有一个数字，则以下查询有效：

SELECT s FROM
  -- this subselect returns positions for the first number in the title
  (SELECT *, LEAST( 
    if (LOCATE('0',title)>0,LOCATE('0',title),999),
    if (LOCATE('1',title)>0,LOCATE('1',title),999),
    if (LOCATE('2',title)>0,LOCATE('2',title),999),
    if (LOCATE('3',title)>0,LOCATE('3',title),999),
    if (LOCATE('4',title)>0,LOCATE('4',title),999),
    if (LOCATE('5',title)>0,LOCATE('5',title),999),
    if (LOCATE('6',title)>0,LOCATE('6',title),999),
    if (LOCATE('7',title)>0,LOCATE('7',title),999),
    if (LOCATE('8',title)>0,LOCATE('8',title),999),
    if (LOCATE('9',title)>0,LOCATE('9',title),999)
  ) AS firstint_pos FROM products) AS t
ORDER BY
  -- first order by the string before the integer value
  IF (firstint_pos<999, SUBSTR(title,1, firstint_pos), s),
  -- then order by the integer value
  CONVERT(SUBSTR(title, firstint_pos), SIGNED);

或者，您可以向表中添加某种sortno列并对其进行维护……