Doctrine ORM 使用 BINARY 实现大小写敏感查询教程

1. DQL 中的大小写敏感性挑战

在数据库查询中，字符串比较的默认行为（大小写敏感或不敏感）通常取决于数据库的配置、字符集和排序规则（collation）。以 MySQL 为例，默认情况下，许多字符串比较是大小写不敏感的。然而，在某些业务场景下，我们可能需要进行严格的大小写敏感匹配，例如用户登录时的用户名验证，或查询特定编码的标识符。在原生 SQL 中，我们可以通过 BINARY 关键字强制执行大小写敏感比较，例如：

SELECT id FROM Records WHERE name = BINARY 'My Record';

当尝试在 Doctrine Query Builder 或 DQL 中直接使用 BINARY 关键字时，例如 r.name = BINARY :name，Doctrine ORM 默认情况下并不能识别 BINARY 作为一个合法的 DQL 函数或关键字，导致查询失败。这是因为 Doctrine DQL 旨在提供一个与底层数据库无关的抽象层，它只支持其自身定义的核心函数集。

2. 解决方案：集成 DoctrineExtensions 库

为了在 Doctrine DQL 中引入 BINARY 这样的非原生函数，我们需要利用 Doctrine 的“自定义 DQL 用户函数”机制。Doctrine 官方文档推荐使用 beberlei/DoctrineExtensions 库，它提供了一系列常用的 MySQL、PostgreSQL 等数据库特有的函数实现，包括我们所需的 BINARY。

2.1 安装 DoctrineExtensions

首先，通过 Composer 将 beberlei/DoctrineExtensions 库安装到您的项目中：

composer require beberlei/doctrineextensions

2.2 配置 DQL 用户函数

安装完成后，您需要将 BINARY 函数注册为 Doctrine DQL 的一个字符串函数。这通常在 Symfony 项目的 config/packages/doctrine.yaml 配置文件中完成。对于其他框架或纯 Doctrine 项目，配置方式可能略有不同，但核心思想是相同的：在 Doctrine ORM 配置中注册 DQL 函数。

# config/packages/doctrine.yaml

doctrine:
    orm:
        # ... 其他 ORM 配置
        dql:
            string_functions:
                binary: DoctrineExtensions\Query\Mysql\Binary

在此配置中，我们将 binary 注册为一个 DQL 字符串函数，并将其映射到 DoctrineExtensions\Query\Mysql\Binary 类。这样，当 Doctrine 解析 DQL 查询时，遇到 BINARY 关键字作为函数调用时，就会知道如何处理它。

3. 在 Doctrine Query Builder 中使用 BINARY

完成上述配置后，您就可以在 Doctrine Query Builder 或 DQL 中像使用原生 SQL BINARY 一样进行大小写敏感查询了。

以下是一个使用 Query Builder 进行大小写敏感查询的示例：

 *
 * @method Records|null find($id, $lockMode = null, $lockVersion = null)
 * @method Records|null findOneBy(array $criteria, array $orderBy = null)
 * @method Records[]    findAll()
 * @method Records[]    findBy(array $criteria, array $orderBy = null, $limit = null, $offset = null)
 */
class RecordsRepository extends ServiceEntityRepository
{
    public function __construct(ManagerRegistry $registry)
    {
        parent::__construct($registry, Records::class);
    }

    /**
     * 根据名称进行大小写敏感查询
     *
     * @param string $name 要查询的名称
     * @return array
     */
    public function findRecordsCaseSensitive(string $name): array
    {
        return $this->createQueryBuilder('r')
            ->select('r.id', 'r.name') // 选择需要的字段
            ->where('BINARY(r.name) = :name') // 使用 BINARY 函数包裹列名
            ->setParameter('name', $name)
            ->getQuery()
            ->getResult();
    }
}

// 假设在某个控制器或服务中调用
// $recordRepository = $entityManager->getRepository(Records::class);
// $results = $recordRepository->findRecordsCaseSensitive('My Record');
// foreach ($results as $record) {
//     echo "ID: " . $record['id'] . ", Name: " . $record['name'] . "\n";
// }

重要提示：在 DQL 中，BINARY 需要被作为一个函数来调用，即 BINARY(r.name)，而不是直接作为操作符 r.name = BINARY :name。尽管在原生 SQL 中 BINARY 可以作为操作符前缀，但在 DQL 中，由于我们将其注册为函数，所以必须以函数调用的形式使用。

4. DoctrineExtensions 提供的其他实用函数

beberlei/DoctrineExtensions 库不仅提供了 BINARY 函数，还包含了大量其他有用的数据库特定函数，涵盖了日期时间、数值和字符串操作。这极大地扩展了 Doctrine DQL 的表达能力，使得开发者能够更灵活地利用底层数据库的强大功能。

例如，对于 MySQL，该库提供了以下类型的函数：

• 日期时间函数: ADDTIME, DATE_FORMAT, DATEDIFF, HOUR, MONTHNAME, NOW, UNIX_TIMESTAMP 等。
• 数值函数: ACOS, CEIL, FLOOR, LOG, ROUND, POWER, STDDEV 等。
• 字符串函数: CONCAT_WS, GROUP_CONCAT, MD5, REPLACE, SHA1, UUID_SHORT 等。

您可以在其 GitHub 仓库中查阅完整的函数列表（通常在 config/mysql.yml 或类似文件中），并根据项目需要注册和使用它们。

5. 注意事项与最佳实践

• 数据库兼容性: beberlei/DoctrineExtensions 提供了针对不同数据库（如 MySQL, PostgreSQL, Oracle）的函数实现。请确保您注册的函数与您使用的数据库类型相匹配。本教程中的 DoctrineExtensions\Query\Mysql\Binary 专为 MySQL 设计。
• 性能考量: 在 WHERE 子句中使用 BINARY(column) 可能会阻止数据库利用该列上的索引，因为它对列的值进行了函数操作。这意味着查询可能需要进行全表扫描，从而影响查询性能。
  • 优化建议: 如果性能成为关键因素，可以考虑在数据库层面调整列的字符集或排序规则 (collation) 以实现默认的大小写敏感性，或者创建函数索引（如果数据库支持）。例如，将列的 collation 设置为 utf8mb4_bin 或 latin1_bin。
• 替代方案: 对于某些简单的大小写敏感查询，如果您不希望引入额外库，可以考虑使用 LOWER() 或 UPPER() 函数将两边都转换为统一的大小写再进行比较。然而，这种方法要求原始数据也进行同样转换，或者接受性能损耗，且不如 BINARY 提供最直接且准确的字节级比较。

6. 总结

通过集成 beberlei/DoctrineExtensions 库并正确配置自定义 DQL 用户函数，我们成功地在 Doctrine Query Builder 和 DQL 中实现了 BINARY 操作符的功能，从而能够执行精确的大小写敏感字符串查询。这一方法不仅解决了特定场景下的查询需求，也展示了 Doctrine ORM 强大的可扩展性，允许开发者根据项目需要引入更多数据库特有的功能。掌握自定义 DQL 函数的使用，将使您的 Doctrine 应用更加灵活和强大。