Skip to main content

区分大小写

区分大小写会影响数据的过滤和排序，由你的 数据库整理 决定。根据你的设置，排序和过滤数据会产生不同的结果：

¥Case sensitivity affects filtering and sorting of data, and is determined by your database collation. Sorting and filtering data yields different results depending on your settings:

行动区分大小写不区分大小写
升序Apple、Banana、apple pie、banana pieApple、apple pie、Banana、banana pie
比赛 "apple"appleApple、apple

如果你使用关系数据库连接器，Prisma 客户端 会尊重你的数据库排序规则。支持使用 Prisma 客户端进行不区分大小写的过滤和排序的选项和建议取决于你的 数据库提供商。

¥If you use a relational database connector, Prisma Client respects your database collation. Options and recommendations for supporting case-insensitive filtering and sorting with Prisma Client depend on your database provider.

如果你使用 MongoDB 连接器，Prisma 客户端 使用 RegEx 规则来启用不区分大小写的过滤。连接器不使用 MongoDB 排序规则。

¥If you use the MongoDB connector, Prisma Client uses RegEx rules to enable case-insensitive filtering. The connector does not use MongoDB collation.

注意：关注 GitHub 上不区分大小写的排序 的进展。

¥Note: Follow the progress of case-insensitive sorting on GitHub.

数据库排序规则和区分大小写​

¥Database collation and case sensitivity

info

在 Prisma 客户端上下文中，以下部分仅涉及关系数据库连接器。

¥In the context of Prisma Client, the following section refers to relational database connectors only.

排序规则指定数据在数据库中的排序和比较方式，其中包括大小写。排序规则是你在设置数据库时选择的内容。

¥Collation specifies how data is sorted and compared in a database, which includes casing. Collation is something you choose when you set up a database.

以下示例演示了如何查看 MySQL 数据库的排序规则：

¥The following example demonstrates how to view the collation of a MySQL database:

SELECT @@character_set_database, @@collation_database;
Show CLI results
  +--------------------------+----------------------+
  | @@character_set_database | @@collation_database |
  +--------------------------+----------------------+
  | utf8mb4                  | utf8mb4_0900_ai_ci   |
  +--------------------------+----------------------+

示例排序规则 utf8mb4_0900_ai_ci 为：

¥The example collation, utf8mb4_0900_ai_ci, is:

  • 不区分重音 (ai)

    ¥Accent-insensitive (ai)

  • 不区分大小写 (ci)。

    ¥Case-insensitive (ci).

这意味着 prisMa 将匹配 prisma、PRISMA、priSMA 等：

¥This means that prisMa will match prisma, PRISMA, priSMA, and so on:

SELECT id, email FROM User WHERE email LIKE "%prisMa%"
Show CLI results
 +----+-----------------------------------+
 | id | email                             |
 +----+-----------------------------------+
 | 61 | alice@prisma.io                   |
 | 49 | birgitte@prisma.io                |
 +----+-----------------------------------+

使用 Prisma 客户端进行相同的查询：

¥The same query with Prisma Client:

const users = await prisma.user.findMany({
  where: {
    email: {
      contains: 'prisMa',
    },
  },
  select: {
    id: true,
    name: true,
  },
})

不区分大小写的过滤选项​

¥Options for case-insensitive filtering

使用 Prisma Client 支持不区分大小写过滤的推荐方法取决于你的底层提供商。

¥The recommended way to support case-insensitive filtering with Prisma Client depends on your underlying provider.

PostgreSQL 提供商​

¥PostgreSQL provider

PostgreSQL 默认使用 确定性校对，这意味着过滤是区分大小写的。要支持不区分大小写的过滤，请针对每个字段使用 mode: 'insensitive' 属性。

¥PostgreSQL uses deterministic collation by default, which means that filtering is case-sensitive. To support case-insensitive filtering, use the mode: 'insensitive' property on a per-field basis.

在过滤器上使用 mode 属性，如下所示：

¥Use the mode property on a filter as shown:

const users = await prisma.user.findMany({
  where: {
    email: {
      endsWith: 'prisma.io',
      mode: 'insensitive', // Default value: default
    },
  },
})

也可以看看：过滤（不区分大小写的过滤）

¥See also: Filtering (Case-insensitive filtering)

注意事项​

¥Caveats

  • 你不能将不区分大小写的过滤与 C 排序规则一起使用

    ¥You cannot use case-insensitive filtering with C collation

  • citext 列始终不区分大小写，并且不受 mode 影响

    ¥citext columns are always case-insensitive and are not affected by mode

表现​

¥Performance

如果你严重依赖不区分大小写的过滤，请考虑使用 在 PostgreSQL 数据库中创建索引 来提高性能：

¥If you rely heavily on case-insensitive filtering, consider creating indexes in the PostgreSQL database to improve performance:

MySQL 提供商​

¥MySQL provider

MySQL 默认使用不区分大小写的排序规则。因此，使用 Prisma Client 和 MySQL 进行过滤默认不区分大小写。

¥MySQL uses case-insensitive collation by default. Therefore, filtering with Prisma Client and MySQL is case-insensitive by default.

mode: 'insensitive' 属性不是必需的，因此在生成的 Prisma 客户端 API 中不可用。

¥mode: 'insensitive' property is not required and therefore not available in the generated Prisma Client API.

注意事项​

¥Caveats

  • 你必须使用不区分大小写 (_ci) 排序规则才能支持不区分大小写的筛选。Prisma 客户端不支持 MySQL 提供程序的 mode 过滤器属性。

    ¥You must use a case-insensitive (_ci) collation in order to support case-insensitive filtering. Prisma Client does no support the mode filter property for the MySQL provider.

MongoDB 提供商​

¥MongoDB provider

要支持不区分大小写的过滤，请在每个字段的基础上使用 mode: 'insensitive' 属性：

¥To support case-insensitive filtering, use the mode: 'insensitive' property on a per-field basis:

const users = await prisma.user.findMany({
  where: {
    email: {
      endsWith: 'prisma.io',
      mode: 'insensitive', // Default value: default
    },
  },
})

MongoDB 使用 RegEx 规则进行不区分大小写的过滤。

¥The MongoDB uses a RegEx rule for case-insensitive filtering.

SQLite 提供者​

¥SQLite provider

默认情况下，Prisma Client 在 SQLite 数据库中创建的文本字段不支持不区分大小写的过滤。在 SQLite 中，只有 ASCII 字符的不区分大小写比较 是可能的。

¥By default, text fields created by Prisma Client in SQLite databases do not support case-insensitive filtering. In SQLite, only case-insensitive comparisons of ASCII characters are possible.

要在每个列上启用对不区分大小写的过滤的有限支持（仅限 ASCII），你需要在定义文本列时添加 COLLATE NOCASE。

¥To enable limited support (ASCII only) for case-insensitive filtering on a per-column basis, you will need to add COLLATE NOCASE when you define a text column.

向新列添加不区分大小写的筛选。​

¥Adding case-insensitive filtering to a new column.

要向新列添加不区分大小写的过滤，你需要修改 Prisma Client 创建的迁移文件。

¥To add case-insensitive filtering to a new column, you will need to modify the migration file that is created by Prisma Client.

采用以下 Prisma Schema 模型：

¥Taking the following Prisma Schema model:

model User {
  id    Int    @id
  email String
}

并使用 prisma migrate dev --create-only 创建以下迁移文件：

¥and using prisma migrate dev --create-only to create the following migration file:

-- CreateTable
CREATE TABLE "User" (
    "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    "email" TEXT NOT NULL
);

你需要将 COLLATE NOCASE 添加到 email 列才能实现不区分大小写的过滤：

¥You would need to add COLLATE NOCASE to the email column in order to make case-insensitive filtering possible:

-- CreateTable
CREATE TABLE "User" (
    "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    //highlight-next-line
    "email" TEXT NOT NULL COLLATE NOCASE
);

向现有列添加不区分大小写的筛选。​

¥Adding case-insensitive filtering to an existing column.

由于无法在 SQLite 中更新列，因此只能通过创建空白迁移文件并将数据迁移到新表来将 COLLATE NOCASE 添加到现有列。

¥Since columns cannot be updated in SQLite, COLLATE NOCASE can only be added to an existing column by creating a blank migration file and migrating data to a new table.

采用以下 Prisma Schema 模型：

¥Taking the following Prisma Schema model:

model User {
  id    Int    @id
  email String
}

并使用 prisma migrate dev --create-only 创建一个空的迁移文件，你需要重命名当前的 User 表并使用 COLLATE NOCASE 创建一个新的 User 表。

¥and using prisma migrate dev --create-only to create an empty migration file, you will need to rename the current User table and create a new User table with COLLATE NOCASE.

-- UpdateTable
ALTER TABLE "User" RENAME TO "User_old";

CREATE TABLE "User" (
    "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    "email" TEXT NOT NULL COLLATE NOCASE
);

INSERT INTO "User" (id, email)
SELECT id, email FROM "User_old";

DROP TABLE "User_old";

微软 SQL Server 提供商​

¥Microsoft SQL Server provider

Microsoft SQL Server 默认使用不区分大小写的排序规则。因此，使用 Prisma Client 和 Microsoft SQL Server 进行过滤默认情况下不区分大小写。

¥Microsoft SQL Server uses case-insensitive collation by default. Therefore, filtering with Prisma Client and Microsoft SQL Server is case-insensitive by default.

mode: 'insensitive' 属性不是必需的，因此在生成的 Prisma 客户端 API 中不可用。

¥mode: 'insensitive' property is not required and therefore not available in the generated Prisma Client API.