mysql - 当列可以以字母开头或结尾时，使用MySQL对varchar列进行数字排序并强制转换为无符号

Question

我遇到了一些我不知道该怎么处理的事情。我正在建立一个数据库来存储运动卡上的信息，当我想查看某些卡时，我在排序方面有点问题。
对于背景，每张卡（数据库中的行）都有年份信息、设置卡的来源、卡上的玩家和卡号（还有更多的信息，但这里仅此相关）。当我看到结果时，我希望按年份排序，然后设置，然后是玩家，然后是卡号。除了卡号之外，一切都很好，因为年份只是一个整数，set和player都是varchar，所以很容易排序。不过，卡号是我遇到的一些问题。
卡号列是varchar，因为卡号可以包含字母、数字和破折号。最常见的情况是，卡号是一个直号码（即1、2、3、4、5）、直字母（Ex-a、Ex-B、Ex-C）、一个数字后跟一个字母（1a、1b、2、3a、3b、3c）或一个字母后跟一个数字（A1、A2、A3、A4、A5）。这就是我目前设置SQL字符串排序部分的方式：

order by year desc, cardset asc, subset asc, cast(cardNum as unsigned) asc;

大多数事情都处理得很好。但我遇到的问题是当一组卡片的卡号中有相同的字母，然后有一个数字。我希望排序基本上忽略前导字母，然后只按数字排序。但是，有时它做得不对，特别是当有超过5张卡片需要排序时。
具体来说，它错误地将一些具有以下卡号的卡按以下顺序排序：
BCP61型
BCP97型
密件抄送32
密件抄送135
当它应该导致：
密件抄送32
BCP61型
BCP97型
密件抄送135
它目前正在对一个或多个数字进行正确的排序（即1、2、3、4、5或1、2、3a、3b、4、5a、5b）。我不知道有任何问题与它排序直接字母不正确，但我目前也很少有这种测试用例，所以我不确定它是否是100%。
除了不知道如何在不弄乱其他排序的情况下修改我的SQL sort语句之外，我也不知道上面BCP示例的顺序是什么。对如何纠正有什么想法吗？我想在我们找到数字之前尽量忽略卡号中的字母，但这会导致卡号中只有字母的卡出现重大问题。所以我有点困了。
绝对最坏的情况下，我可能会把卡号列分成两个不同的列，一个用于更具描述性的部分，另一个用于我想排序的部分。结果可能会很好，但要把东西拿回来需要很多工作！
编辑-以下是一些详细信息，包括my DB中的数据（很抱歉设置了格式，不知道如何在此处创建表）：

| year | cardSet | subset                     | cardNum |
| 2016 | Bowman  |                            | 52      |
| 2016 | Bowman  |                            | 54      |
| 2016 | Bowman  |                            | 147     |
| 2016 | Bowman  | Chrome Prospects           | BCP32   |
| 2016 | Bowman  | Chrome Prospects           | BCP61   |
| 2016 | Bowman  | Chrome Prospects           | BCP97   |
| 2016 | Bowman  | Chrome Prospects           | BCP135  |
| 2016 | Topps   |                            | 1       |
| 2016 | Topps   |                            | 2a      |
| 2016 | Topps   |                            | 2b      |
| 2016 | Topps   |                            | 3       |

我希望我的排序按以下顺序显示结果：
2016鲍曼52
2016鲍曼54
2016鲍曼147
2016 Bowman Chrome前景BCP32
2016 Bowman Chrome前景BCP61
2016 Bowman Chrome前景BCP97
2016 Bowman Chrome前景BCP125
2016年第一名
2016年排名2a
2016年排名前2b
2016年第三名
但是，下面是我在上面的排序语句中得到的结果：
2016鲍曼52
2016鲍曼54
2016鲍曼147
2016 Bowman Chrome前景BCP62
2016 Bowman Chrome前景BCP97
2016 Bowman Chrome前景BCP32
2016 Bowman Chrome前景BCP125
2016年第一名
2016年排名2a
2016年排名前2b
2016年第三名
它只使用数字来处理卡号，或者数字后面跟着字母就可以了，但是当卡号以字母开头，后面跟着数字时，它往往会把事情搞砸。
我已经尝试在注释中使用length（）技巧，因此我的SQL的排序部分是：

order by year desc, cardset asc, subset asc, length(cardNum), cardNum asc

这确实解决了我在上面描述的问题，但是在我的例子中，它弄乱了“Topps”部分——它会把字母跟在数字后面的卡片放在最后，不管是什么。这是我得到的顺序：
2016鲍曼52
2016鲍曼54
2016鲍曼147
2016 Bowman Chrome前景BCP32
2016 Bowman Chrome前景BCP61
2016 Bowman Chrome前景BCP97
2016 Bowman Chrome前景BCP125
2016年第一名
2016年第三名
2016年排名2a
2016年排名前2b

Answer 1

MariaDB 10和MySQL 8支持REGEXP_REPLACE。使用它，可以定义自定义函数：

DROP FUNCTION IF EXISTS zerofill_numbers;
CREATE FUNCTION zerofill_numbers(str TEXT, len TINYINT)
    RETURNS text
    NO SQL
    DETERMINISTIC
    RETURN REGEXP_REPLACE(
        REGEXP_REPLACE(str, '(\\d+)', LPAD('\\1', len+2, 0)),
        REPLACE('0*(\\d{$len})', '$len', len),
        '\\1'
    );

现在给出以下测试数据：

DROP TABLE IF EXISTS `strings`;
CREATE TABLE IF NOT EXISTS `strings` (`str` text);
INSERT INTO `strings` (`str`) VALUES
    ('Bowman 52'),
    ('Bowman 54'),
    ('Bowman 147'),
    ('Bowman Chrome Prospects BCP32'),
    ('Bowman Chrome Prospects BCP61'),
    ('Bowman Chrome Prospects BCP97'),
    ('Bowman Chrome Prospects BCP125'),
    ('Topps 1'),
    ('Topps 3'),
    ('Topps 2a'),
    ('Topps 2b'),
    ('v9.9.3'),
    ('v9.10.3'),
    ('v11.3.4'),
    ('v9.9.11'),
    ('v11.3'),
    ('0.9'),
    ('0.11'),
    ('s09'),
    ('s11'),
    ('s0'),
    ('v9.0.1');

我们可以用以下方法来分类：

SELECT s.str
FROM strings s
ORDER BY zerofill_numbers(s.str, 10)

结果如下：

0.9
0.11
Bowman 52
Bowman 54
Bowman 147
Bowman Chrome Prospects BCP32
Bowman Chrome Prospects BCP61
Bowman Chrome Prospects BCP97
Bowman Chrome Prospects BCP125
s0
s09
s11
Topps 1
Topps 2a
Topps 2b
Topps 3
v9.0.1
v9.9.3
v9.9.11
v9.10.3
v11.3
v11.3.4

函数将用零填充字符串中的任何数字，直到它具有定义的长度。
注1：这将不会对十进制数进行正确排序（请参见 0.9和 0.11）。你也不应该试着用它来做有符号的数字。
注2：此函数基于以下答案： https://stackoverflow.com/a/46099386/5563083-因此，如果您发现此答案有用，请前往并向上投票源。
注3：如果不想定义自定义函数，可以使用相同的内联方法：

SELECT *
FROM strings
ORDER BY
  REGEXP_REPLACE(REGEXP_REPLACE(str, '(\\d+)', LPAD('\\1', 10+2, 0)), '0*(\\d{10})', '\\1')