MySQL 大小写敏感性解析：如何控制大小写比较

1. 介绍

在使用 MySQL 时，您可能会遇到想要进行不区分大小写的搜索，或者相反，比较结果并未如预期那样的情况。例如，某些情况下用户名、电子邮件地址或产品代码应视为区分大小写，而在其他情况下则不应区分。

事实上，许多搜索 “mysql case insensitive” 的用户都在思考：

• 如何执行不区分大小写的搜索？
• 为什么我的环境在大小写敏感性方面表现异常？
• 我该如何修改设置或 SQL 语句以避免这些问题？

这些都是常见的疑问。

本文将从基础到实用技巧，系统阐述 MySQL 如何处理大小写字母。我们将覆盖常用的做法，如排序规则（collation）设置、LOWER()/UPPER() 函数以及 BINARY 属性，并配以示例和重要注意事项。这样内容不仅适合初学者，也对在生产环境中工作的系统管理员和工程师有帮助。

阅读完本文后，您将能够自信地在 MySQL 中控制不区分大小写的搜索，避免数据库操作和开发环境中出现意外问题。接下来，我们将首先探讨 MySQL 在根本上是如何处理大小写字母的。

2. MySQL 中大小写敏感性的基础

在 MySQL 中，字符串比较时是否将大小写字母视为不同，并不是自动决定的。其行为由一种称为 “排序规则（collation）” 的设置控制。排序规则定义了在数据库中比较和排序字符串时使用的规则。

2.1 数据库、表和列层面的排序规则

MySQL 的排序规则可以在数据库层、表层和列层级进行层级配置。例如，您可以在创建数据库时指定默认的排序规则，然后在表或列层面进一步覆盖它。

如果未显式指定排序规则，则使用服务器全局的默认值（通常是 utf8mb4_general_ci 或 latin1_swedish_ci，具体取决于环境）。在许多情况下，这个默认值是 不区分大小写 的（后缀为 _ci）。

2.2 “_ci” 与 “_cs” 的区别

排序规则名称常以 _ci 或 _cs 结尾：

• _ci（case‑insensitive，大小写不敏感）：大写字母和小写字母被视为相同。
• _cs（case‑sensitive，大小写敏感）：大写字母和小写字母被视为不同。

例如，utf8mb4_general_ci 进行不区分大小写的比较，而 utf8mb4_bin（二进制比较）则严格区分大小写字母。

2.3 不同字符串数据类型的注意事项

CHAR、VARCHAR、TEXT 等字符串数据类型通常受所定义的排序规则影响。相对地，BINARY、VARBINARY、BLOB 类型始终使用二进制比较，也就是说它们始终是 区分大小写 的。这一点需要特别留意。

2.4 与操作系统和版本相关的情况

在某些情况下，标识符（如表名、列名）的大小写处理可能会因 MySQL 版本和操作系统的文件系统而异。但本文主要聚焦于数据值（字符串比较）的大小写敏感性问题。

综上所述，MySQL 中的大小写敏感性由排序规则控制，并且可以在数据库、表和列层面灵活配置。

3. 如何执行不区分大小写的搜索

在 MySQL 中执行不区分大小写的搜索，可以通过排序规则设置和查询设计灵活实现。本节将介绍三种在实际环境中常用的代表性做法，阐述它们的特性及重要注意事项。

3.1 检查并更改默认排序规则

在许多 MySQL 环境中，默认的排序规则已经设置为不区分大小写（_ci）。例如 utf8mb4_general_ci 和 latin1_swedish_ci。

检查排序规则设置的示例 SQL：

SHOW VARIABLES LIKE 'collation%';

检查表/列排序规则的示例：

SHOW FULL COLUMNS FROM users;

更改排序规则设置的示例 SQL：

-- Entire database
ALTER DATABASE dbname CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

-- Per table
ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

-- Per column
ALTER TABLE users MODIFY username VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

使用此配置，使用普通运算符如 = 或 LIKE 的搜索将自动以不区分大小写的方式进行。

3.2 在查询中使用 COLLATE

即使默认排序规则是区分大小写的（例如 _cs 或 _bin），您仍可能只想对特定搜索执行不区分大小写的比较。在这种情况下，您可以在 SQL 语句中直接指定 COLLATE。

示例：

SELECT * FROM users WHERE username COLLATE utf8mb4_general_ci = 'Sato';

这使您能够仅在该查询中使用指定的排序规则执行不区分大小写的搜索。当您不想影响现有数据或其他应用逻辑时，这非常有用。

3.3 使用 LOWER()/UPPER() 进行比较

另一种方法是使用 LOWER() 或 UPPER() 函数对存储的值和搜索关键字进行规范化。通过将所有内容转换为小写（或大写），即可实现不区分大小写的行为。

示例：

SELECT * FROM users WHERE LOWER(username) = LOWER('Sato');

但是，有 重要的注意事项：

• 使用函数可能导致索引无法被使用，从而减慢搜索速度。
• 如果您的表包含大量数据，通过排序规则处理通常在性能上更好。

通过选择合适的方法，您可以自信地在 MySQL 中执行不区分大小写的搜索。

4. 当需要区分大小写的比较时

许多系统需要对用户名、密码或产品代码等值进行严格的区分大小写处理。由于 MySQL 在许多配置中默认采用不区分大小写的行为，您应了解在需要时如何强制区分大小写。

4.1 使用 BINARY 操作符

执行区分大小写比较的最简便方法之一是使用 BINARY 操作符。当您使用 BINARY 时，值会被视为二进制（逐字节）字符串，大小写差异会被严格识别。

示例：

SELECT * FROM users WHERE BINARY username = 'Sato';

此查询仅返回用户名完全匹配 Sato 的行。像 sato 或 SATO 这样的值将不匹配。

4.2 将列的排序规则设置为 _bin 或 _cs

您也可以将列定义本身更改为使用区分大小写的排序规则，例如 utf8mb4_bin 或 utf8mb4_cs。这可确保比较始终区分大小写。

示例：

ALTER TABLE users MODIFY username VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

有了此设置，即使使用 = 或 LIKE 的普通比较也会严格区分大小写字母。

4.3 常见使用场景与关键考虑因素

• 对 密码、密钥和标识符 建议使用区分大小写的比较。
• 根据策略，电子邮件地址或用户 ID 可能需要区分大小写的处理（国际标准将电子邮件地址的本地部分视为区分大小写，尽管许多系统在实践中采用不区分大小写）。
• 如果在已有数据库中更改排序规则，请务必先备份并在测试环境中验证行为。

4.4 常见故障场景

• 默认排序规则是大小写不敏感的，因此会出现意外匹配。
• 应用程序假设大小写敏感的行为，但数据库以大小写不敏感的方式比较值，导致错误。
• 迁移或升级期间的排序规则更改会导致现有数据出现意外行为。

当需要大小写敏感行为时，适当使用 BINARY 操作符和排序规则设置，以确保安全和准确的数据处理。

5. 实际示例和重要注意事项

在 MySQL 中执行大小写敏感或不敏感搜索时，了解常见现实世界场景和性能影响很重要。本节从操作角度总结了实际查询示例、性能考虑以及多语言（例如日语）字符串处理。

5.1 LIKE 和 IN 子句的行为

• LIKE 子句 在许多排序规则（例如 _ci）中，使用 LIKE 的部分匹配也是大小写不敏感的。

  SELECT * FROM users WHERE username LIKE 'S%';
  

在这种情况下，诸如 Sato、sato 和 SATO 的值都会匹配。

• IN 子句 IN 操作符也遵循列的排序规则设置。

  SELECT * FROM users WHERE username IN ('Sato', 'sato');
  

对于 _ci 列，诸如 Sato、sato 和 SATO 的值可能会全部匹配。对于 _bin，仅返回精确匹配。

5.2 对索引和性能的影响

• 使用 LOWER()/UPPER() 函数 使用 LOWER() 或 UPPER() 时，通常不会使用索引，因为在比较前转换了列值。这可能导致全表扫描。对于大型数据集，这会显著降低性能。
• 排序规则和索引 使用标准排序规则（例如 _ci 或 _bin）定义的列可以正常使用索引。如果性能至关重要，请仔细设计列定义和查询结构。

5.3 修改现有系统时的注意事项

• 更改数据库或列的排序规则可能会 重建索引并更改比较结果 。彻底测试和备份是必不可少的。
• 在生产或大规模系统中，在应用更改之前，始终在测试环境中验证更改。

5.4 多字节（日语和其他语言）注意事项

• 诸如 utf8mb4_general_ci 和 utf8mb4_unicode_ci 的排序规则支持多语言数据，包括日语，并以类似于英语的方式处理字母字符的大小写敏感性。
• 然而，特殊符号、历史字符或某些 Unicode 变体可能会根据排序规则以不同的方式比较。如果您的系统严重依赖日语或多语言数据，请考虑使用 utf8mb4_unicode_ci 并了解排序规则之间的差异。

5.5 迁移或版本升级期间的问题

• MySQL 版本的更改可能会更改默认排序规则或比较逻辑。
• 在迁移期间，可能会出现意外的行为差异。始终查看官方文档并评估系统范围的影响。

在现实世界操作中，仅仅配置大小写敏感是不够的。您还必须考虑 排序规则设计、查询结构、性能影响和迁移相关风险 。在修改现有系统或支持多语言环境时，建议额外谨慎。

6. [Column] 字符串为什么有时大小写敏感，有时不敏感？

MySQL 为什么有时区分大写和小写字母，有时不区分？

在本节中，我们解释这种行为背后的技术背景，并与其他数据库进行比较。

6.1 排序规则如何工作

在 MySQL 中，字符串比较由“排序规则”控制。

排序规则定义了字符串如何比较和排序。主要类型包括：

• _ci（不区分大小写）：大写字母和小写字母被视为相同。例如：utf8mb4_general_ci
• _cs（区分大小写）：大写字母和小写字母被视为不同。例如：utf8mb4_0900_as_cs
• _bin（二进制）：严格的逐字节比较。例如：utf8mb4_bin

在 MySQL 中，校对规则可以在列、表或数据库级别指定。因此，相同的字符串可能会因校对设置不同而被视为区分大小写或不区分大小写。

6.2 不同操作系统和文件系统的差异（标识符）

另一个重要的考虑因素是 表名和列名（标识符）如何处理。

根据存储引擎和操作系统的不同，MySQL 可能会将表名视为区分大小写或不区分大小写。

• Linux（大多数文件系统）：区分大小写（大写和小写被视为不同）。
• Windows（NTFS）：不区分大小写（大写和小写被视为相同）。

虽然这与数据值比较无关，但在开发或系统迁移过程中可能会导致意外行为。

6.3 MySQL 版本之间的变化

不同的 MySQL 版本可能使用不同的默认校对规则和比较算法。

例如，从 MySQL 8.0 开始，Unicode 支持得到改进，默认校对规则也更为精确。因此，比较结果可能与早期版本不同。

6.4 与其他数据库的差异

• PostgreSQL 默认情况下比较是区分大小写的。可以使用 ILIKE 运算符进行不区分大小写的搜索。
• SQL Server 校对规则在安装或创建数据库时指定。许多环境中常见不区分大小写的设置。

正如您所见，不同数据库系统的大小写敏感行为各不相同。在迁移系统或与其他数据库集成时请谨慎。

总之，MySQL 的大小写敏感或不敏感行为由多种因素决定，包括校对规则、操作系统和版本。了解这些因素有助于在开发和迁移过程中避免意外问题。

7. 常见问题解答 (FAQ)

Q1：更改校对规则会对现有数据产生什么影响？

A：
更改校对规则后，字符串的比较和排序方式会从此时起发生变化。实际存储的数据值不会改变。但搜索结果和排序顺序可能会与之前的行为不同。索引可能需要重新构建，这会暂时影响性能。对于大型数据库，请务必先备份并在预演环境中彻底测试更改后再应用到生产环境。

Q2：如果使用 LOWER() 或 UPPER()，索引会被使用吗？

A：
一般来说，当使用 LOWER() 或 UPPER() 等函数时，列值会在比较前被转换。由于这种转换，索引通常不会被使用。因此，在大数据集上搜索性能可能会显著下降。如果性能很重要，请考虑调整校对规则或使用 COLLATE 子句代替。

Q3：LIKE 查询也是不区分大小写的吗？

A：
在大多数不区分大小写的校对规则（以 _ci 结尾）中，使用 LIKE 的部分匹配也会不区分大小写。然而，如果列使用 _bin 或 _cs 校对规则，比较将严格区分大小写。请始终确认列的校对设置。

Q4：我可以在列级别配置不区分大小写的行为吗？

A：
可以。定义或修改列时，可以使用 COLLATE 属性为该列指定特定的校对规则，仅对该列生效。

示例：

ALTER TABLE users MODIFY username VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

这允许您对特定列应用不同的比较规则。

Q5：不区分大小写的行为是否适用于日语或多语言数据？

A:
是的。诸如 utf8mb4_general_ci 和 utf8mb4_unicode_ci 的校对规则支持多语言数据，包括日语，并且在不区分大小写的方式下处理字母。然而，某些特殊字符、符号或历史形式可能会根据校对规则产生不同的比较结果。在处理多样字符集时请保持谨慎。

Q6: MySQL 5.x 与 8.x 在不区分大小写的行为上是否有差异？

A:
是的。不同版本可能使用不同的默认校对规则和 Unicode 实现。例如，MySQL 8.0 推荐使用 utf8mb4_0900_ai_ci，它提供了更高的比较精度。升级时请务必查阅官方文档并进行行为测试。

Q7: BINARY 运算符与校对设置之间有什么区别？

A:
BINARY 运算符仅对特定表达式执行严格的逐字节比较。相比之下，在列或表级别设置校对规则会在该列或表的所有操作中强制统一的比较规则。

经验法则：

• 当需要临时进行严格比较时使用 BINARY。
• 当希望在系统范围内保持一致的比较行为时使用校对设置。

本 FAQ 覆盖了常见的实际问题和疑问。如果您有其他关注点，欢迎通过评论或联系表单提出。

8. 摘要

MySQL 中的大小写敏感性可以通过校对设置灵活控制。是否在比较时区分大小写等需求取决于系统设计和运营策略。

本文涵盖了以下内容：

• MySQL 中大小写敏感性的基本处理方式
• 如何进行不区分大小写和区分大小写的比较
• 实际示例及运营注意事项
• 技术背景以及与其他数据库的差异
• 常见故障排查场景及解决方案

由于校对可以在数据库、表和列级别进行配置，依据需求选择合适的方案至关重要。

通过正确使用校对设置、LOWER()/UPPER() 函数、BINARY 运算符以及 COLLATE 子句，您可以避免意外问题并保持行为的一致性。

最后，在大型系统中修改设置或升级版本时，请务必在应用更改前进行备份和测试。

拥有对校对的深入理解，您可以更安全、高效地使用 MySQL。

9. 参考链接与官方文档

如果您想进一步了解 MySQL 中的大小写敏感性和校对，或核实官方规范，请参考以下可靠资源。

9.1 官方 MySQL 文档

• MySQL :: 字符集和校对支持
• MySQL :: 列字符集和校对

9.2 与其他主流数据库的比较

• PostgreSQL :: 校对支持
• SQL Server :: 校对和 Unicode 支持

9.4 重要说明

• 校对行为可能会因 MySQL 版本 而变化。请始终查阅对应您所安装版本的文档。
• 大型系统可能存在自定义的运营规则或例外情况。必要时请审阅内部文档和系统设计规范。

使用官方手册和可信的技术资源来深化理解并适当地配置 MySQL。
如果遇到问题，请参考上述文档以确定最佳解决方案。