如何在Doctrine中对带字母后缀的数字字段实现正确排序？

问题根源是字符串默认按字典序排序：9999的首字符9ASCII码比10000的首字符1大，所以会排在前面。要实现符合数字逻辑的排序，核心是先提取字段里的数字部分排序，再处理字母后缀的顺序，以下是几种可行方案：

方案一：利用数据库函数直接排序（推荐，性能最优） #

不同数据库的字符串处理函数不同，对应Doctrine Query Builder的写法如下：

MySQL/MariaDB 环境 #

用SUBSTRING_INDEX分割字符串提取数字部分，转成整数排序，同时保留原字段排序处理同数字不同后缀的情况：

public function findAllOrderedByNumber()
{
    return $this->createQueryBuilder('a')
        // 提取数字部分并转成无符号整数，HIDDEN避免该字段出现在结果集
        ->addSelect('CAST(SUBSTRING_INDEX(a.nr, \'a\', 1) AS UNSIGNED) AS HIDDEN numericPart')
        ->orderBy('numericPart', 'ASC')
        // 数字相同时，按原字符串排序（比如10510a排在10510b前面）
        ->addOrderBy('a.nr', 'ASC')
        ->getQuery()
        ->getResult();
}

PostgreSQL 环境 #

用正则匹配开头的数字并转成整数：

public function findAllOrderedByNumber()
{
    return $this->createQueryBuilder('a')
        ->addSelect('SUBSTRING(a.nr FROM \'^\\d+\')::integer AS HIDDEN numericPart')
        ->orderBy('numericPart', 'ASC')
        ->addOrderBy('a.nr', 'ASC')
        ->getQuery()
        ->getResult();
}

方案二：PHP内存中排序（仅适合小数据量） #

如果数据量很小，可先查询所有数据，再在PHP里手动排序：

public function findAllOrderedByNumber()
{
    $results = $this->createQueryBuilder('a')
        ->getQuery()
        ->getResult();

    usort($results, function($a, $b) {
        // 提取字段中的数字部分转成整数
        $numA = (int) preg_replace('/[^0-9]/', '', $a->getNr());
        $numB = (int) preg_replace('/[^0-9]/', '', $b->getNr());
        
        if ($numA !== $numB) {
            return $numA - $numB;
        }
        // 数字相同则按原字符串比较后缀
        return strcmp($a->getNr(), $b->getNr());
    });

    return $results;
}

注意：数据量大时禁用此方案，会占用大量内存且排序效率极低。

方案三：自定义Doctrine DQL函数（跨数据库场景） #

如果项目需要兼容多数据库，可以自定义一个DQL函数，封装不同数据库的数字提取逻辑，实现Query Builder里的统一调用。此步骤稍繁琐，适合有明确跨库需求的场景，具体可参考Doctrine官方文档的自定义DQL函数规范（多数场景用方案一即可满足需求）。

内容的提问来源于stack exchange，提问作者tw_in_double_trouble