首先我想指出我已经尝试了几乎所有的方法。自过去 8 小时以来，我一直在尝试按顺序列出我的 list ，并且我已经应用了此处找到的多种解决方案。

这里是 SQL Fiddle与示例数据。我找到了 a page设法以正确的顺序对我的列表进行排序，即:

1
2
2.B3
5
9
10 A-1
10 A-3
10 B-4
10 B-5
11
12
B3-43
B3-44
B3 - 48
B3 - 49
Basztowa 3
Basztowa 4
Basztowa 5
Basztowa 7
Basztowa 9
D.1
D.2
D.10
D.11
D.12
Kabaty ul. Pod lipą 4

但我无法使用 MySQL 重现它。

我将不胜感激任何帮助，因为我没有更多的想法。我考虑使用 PHP 对我的列表进行排序，但据我所知，DBMS 已针对此类操作进行了优化，因此如果可能的话，我希望避免使用 PHP 进行排序。

@更新

感谢@Jakumi，我创建了两个函数来帮助我解决问题。

您需要创建一个列来以排序友好的格式(zeropadded_name)存储您的值，在更新时创建触发器并在 name 时插入以填充 zeropadded_name 改变，仅此而已!现在只需按 zeropadded_name 订购即可享用!

辅助函数

1. regex_replace - 它的任务是通过删除所有非字母数字字符来帮助我们清理值。
2. lpad_numbers - 填充字符串中的每个数字。它有点难看，因为我不太了解 MySQL 的功能，但是嘿，它运行起来非常快。

例子:

SELECT lpad_numbers(regex_replace('[^a-zA-Z0-9]', ' ', 'B3 - A-5'));
#B0003A0005

DROP FUNCTION IF EXISTS regex_replace;
CREATE FUNCTION `regex_replace`(
  pattern     VARCHAR(1000)
              CHARSET utf8
              COLLATE utf8_polish_ci,
  replacement VARCHAR(1000)
              CHARSET utf8
              COLLATE utf8_polish_ci,
  original    VARCHAR(1000)
              CHARSET utf8
              COLLATE utf8_polish_ci
) RETURNS varchar(1000) CHARSET utf8
    DETERMINISTIC
BEGIN
    DECLARE temp VARCHAR(1000)
    CHARSET utf8
    COLLATE utf8_polish_ci;
    DECLARE ch VARCHAR(1)
    CHARSET utf8
    COLLATE utf8_polish_ci;
    DECLARE i INT;
    SET i = 1;
    SET temp = '';
    IF original REGEXP pattern
    THEN
      loop_label: LOOP
        IF i > CHAR_LENGTH(original)
        THEN
          LEAVE loop_label;
        END IF;
        SET ch = SUBSTRING(original, i, 1);
        IF NOT ch REGEXP pattern
        THEN
          SET temp = CONCAT(temp, ch);
        ELSE
          SET temp = CONCAT(temp, replacement);
        END IF;
        SET i = i + 1;
      END LOOP;
    ELSE
      SET temp = original;
    END IF;
    RETURN temp;
  END;

DROP FUNCTION IF EXISTS lpad_numbers;
CREATE FUNCTION `lpad_numbers`(str VARCHAR(256)) RETURNS varchar(256) CHARSET utf8 COLLATE utf8_polish_ci
BEGIN
    DECLARE i, len SMALLINT DEFAULT 1;
    DECLARE ret VARCHAR(256) DEFAULT '';
    DECLARE num VARCHAR(256) DEFAULT '';
    DECLARE c CHAR(1);

    IF str IS NULL
    THEN
      RETURN "";
    END IF;

    SET len = CHAR_LENGTH(str);
    REPEAT
      BEGIN
        SET c = MID(str, i, 1);
        IF c BETWEEN '0' AND '9'
        THEN
          SET num = c;
          SET i = i + 1;
          REPEAT
            BEGIN
              SET c = MID(str, i, 1);
              SET num = CONCAT(num, c);
              SET i = i + 1;
            END;
          UNTIL c NOT BETWEEN '0' AND '9' END REPEAT;
          SET ret = CONCAT(ret, LPAD(num, 4, '0'));
        ELSE
          SET ret = CONCAT(ret, c);
          SET i = i + 1;
        END IF;
      END;
    UNTIL i > len END REPEAT;
    RETURN ret;
  END;

最佳答案

根据底层结构拆分

从技术上讲，mysql 排序机制可以正常工作，但您的字符串格式错误。您的数据的底层结构类似于以下内容(保留原始列以便于与示例关联):

alpha1   num1 alpha2 num2 ...   Original      
            1                   1             
            2                   2             
            2      B    3       2.B3          
            5                   5             
            9                   9             
           10      A    1       10 A-1        
           10      A    3       10 A-3        
           10      B    4       10 B-4        
           10      B    5       10 B-5        
           11                   11            
           12                   12            
B           3          43       B3-43         
B           3          44       B3-44         
B           3          48       B3 - 48       
B           3          49       B3 - 49       
Basztowa    3                   Basztowa 3    
Basztowa    4                   Basztowa 4    
Basztowa    5                   Basztowa 5    
Basztowa    7                   Basztowa 7    
Basztowa    9                   Basztowa 9    
D           1                   D.1           
D           2                   D.2           
D          10                   D.10          
D          11                   D.11          
D          12                   D.12          

如果您现在使用 ORDER BY alpha1, num1, alpha2, num2 对它们进行排序，它们将按您希望的方式排序。但是已经“格式化”的版本(Original 列)不容易排序，因为应该按字母排序的部分和应该按数字排序的部分混合在一起。

零填充

有一个不太广泛的替代方案，只需要一个额外的列，假设没有数字超过 10000，现在您可以用零填充版本替换每个数字(不是数字!)，所以 10 A-1 将变为 0010A0001(显然是 0010 和 A 和 0001)，但我没有看到这是在 ORDER BY 语句中即时生成的。

但是对于这个例子，zeropadded 版本(假设:每个数字 <>

Original      Zeropadded 
1             0001       
2             0002       
2.B3          0002B0003  
5             0005       
9             0009       
10 A-1        0010A0001  
10 A-3        0010A0003  
10 B-4        0010B0004  
10 B-5        0010B0005  
11            0011       
12            0012       
B3-43         B00030043  
B3-44         B00030043  
B3 - 48       B00030048  
B3 - 49       B00030049  
Basztowa 3    Baztowa0003
Basztowa 4    Baztowa0004
Basztowa 5    Baztowa0005
Basztowa 7    Baztowa0007
Basztowa 9    Baztowa0009
D.1           D0001      
D.2           D0002      
D.10          D0010      
D.11          D0011      
D.12          D0012      

可以使用 ORDER BY zeropadded 按照您的意愿进行排序。

所以最后，您可能必须在 php 中进行排序或创建更多列来帮助您通过重新格式化/清理/拆分输入进行排序。

更新

零填充解释(简化)

zeropadding 背后的主要思想是数字的自然格式不同于它们在计算机中的格式。在计算机中，数字 2 实际上是 数字序列 0..0002(因此包含前导零)类似于 10 (0..0010)。当计算机比较数字时，它会从左到右，直到找到不同的数字:

0...0002
0...0010
======!.    (the ! marks the point where the first digit is different)

然后它将确定哪个数字更大或更小。在这种情况下 0 < 1，因此="" 2=""><>

现在，字符串在技术上是一个字符序列。字符串比较的工作方式略有不同。比较两个字符串时，它们不会(左)填充，因此每个字符串的第一个字符实际上是第一个字符而不是填充(例如空格)。所以从技术上讲，string A10 是字符序列 A、1 和 0 .并且由于使用了字符串比较，它比 A2“更小”，因为字符串比较没有将数字视为数字而是字符(即数字):

A10
A2
=!     (the ! marks the point where the first character is different)

并且因为 1 2 作为字符，A10 A2。现在为了避免这个问题，我们强制字符串中数字的格式与数字比较中的格式相同，方法是将数字填充到相同的长度，即根据它们的 place value 对齐数字。 :

A0010
A0002
===!.  (the ! marks the point where the first character is different)

现在它实际上与您在数值比较中期望的相同。但是，您必须对数字的最大长度做出一些假设，以便您可以适本地选择填充。如果没有这个假设，您就会遇到问题。

剩下的唯一(逻辑)点:当被比较的字符串有一个字母字符而另一个有一个数字时，填充会发生什么变化？答案是:没什么。我们不会将数字变成字母，数字比字母小，所以在这种情况下一切都保持相同的顺序。

zeropadding 的作用是:我们将字符串中的“数字”比较调整为类似于实数比较，通过根据其值对齐数字字符。

关于php - 如何在mysql中对字母数字数据进行排序？，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/39022123/