MySQL的VARCHAR排序(MySql varchar ordering)

我有完全相同的问题，因为其他人的另一个问题。 我阅读了大量有关该论坛之类的东西，但无法找到答案。 我使用LPAD，因为我有一个最接近的结果我的需求，但它仍然不是我想要的。

ORDER BY LPAD(`my_value`, '500', '0') ASC

记录现在看起来是这样的：

Mova 16
Mova 110
Mova 125
Mova 140
Mova 180
Mova 160N

但我需要他们在这个顺序：

Mova 16
Mova 110
Mova 125
Mova 140
Mova 160N
Mova 180

所以，我怎样才能做到这一点？

我必须指出，有一种其他记录在此表中，如：

"GIACOMINI" R780 DN15 v/2 kampinis
"GIACOMINI" R780 DN20 v/3 kampinis
"GIACOMINI" R780 DN25 v/1 kampinis

等等等等......这些应该适合在这种情况下，太。

Answer 1:

我的想法是你的列转换成固定长度的字符串，字母数字字符和块对齐的数字，如

Input string                       | aligned string
---------------------------------------------------------------------------------------
Mova 16                            | MOVA......0000000016
Mova 110                           | MOVA......0000000110
Mova 180                           | MOVA......0000000180
Mova 160N                          | MOVA......0000000160N.........
"GIACOMINI" R780 DN15 v/2 kampinis | GIACOMINI.R.........0000000780DN........000..etc.
"GIACOMINI" R780 DN20 v/3 kampinis | GIACOMINI.R.........0000000780DN........000..etc.

然后我们可以使用对齐串行进行排序，而是以获得结果，我们需要创建一个自定义函数：

DROP FUNCTION IF EXISTS strformat;
SET GLOBAL  log_bin_trust_function_creators=TRUE; 
DELIMITER |
CREATE FUNCTION strformat(str VARCHAR(128))
RETURNS VARCHAR(128)
BEGIN
  DECLARE i INT;
  DECLARE last INT;
  DECLARE curr INT;
  DECLARE res VARCHAR(128);
  DECLARE block VARCHAR(10);

  SET res='';
  SET block='';
  SET i=1;

  IF LENGTH(str) = 0 THEN
    RETURN NULL;
  END IF;

  WHILE i <= LENGTH(str) DO
    SET curr=IF(MID(str, i, 1) RLIKE '[A-Z,a-z]', 1,
                  IF(MID(str, i, 1) RLIKE '[0-9]', 2, 0));

    IF (block='') OR (last=curr) THEN
      SET block = CONCAT(block,
                    IF((curr=1) OR (curr=2), MID(str, i, 1), ''));
    ELSE
      IF last=2 THEN
        SET res = CONCAT(res,
                    REPEAT('0', 10 - LENGTH(block)), block);
      ELSE
        SET res = CONCAT(res,
                    block, REPEAT(' ', 10 - LENGTH(block)));
      END IF;
      SET block = IF((curr=1) OR (curr=2), MID(str, i, 1), '');
    END IF;

    SET last=curr;
    SET i = i + 1;
  END WHILE;

  IF curr=1 THEN
    SET res = CONCAT(res, block, REPEAT(' ', 10 - LENGTH(block)));
  ELSEIF curr=2 THEN
    SET res = CONCAT(res, REPEAT('0', 10 - LENGTH(block)), block);
  END IF;

  RETURN UCASE(res);
END;
|
DELIMITER ;

（这里的每个块必须被限制为10个字符）。

然后，您可以订购方式：

ORDER BY strformat(`my_value`)

Answer 2:

这个怎么样？

SELECT   *
FROM     yourtable
ORDER BY CAST(SUBSTRING(yourcolumn,LOCATE(' ',yourcolumn)+1) AS SIGNED) ASC;

• SQLFIDDLE DEMO

结果：

| YOURCOLUMN |
--------------
|    Mova 16 |
|   Mova 110 |
|   Mova 125 |
|   Mova 140 |
|  Mova 160N |
|   Mova 180 |

文章来源: MySql varchar ordering