主页 / dba / 问题 / 144181
Accepted
onedaywhen
Asked: 2016-07-19 03:02:40 +0800 CST

是否有任何 DBMS 具有区分大小写和不区分重音的排序规则？

  • 772

请注意，此问题与供应商/版本无关

在我看来，作为一名说英语的人（打字员、作家），期望单词大小写正确但不一定有正确的口音朝着正确的方向前进是合理的：

当我在香榭丽舍大街餐厅与酒店领班克洛伊（Chloe）聊天时，在等待加孔（garcon）拿我的炒墨西哥胡椒酱时……

你明白了。

所以今天我想我想要一个搜索条件来使用区分大小写但不区分重音的排序规则，但找不到。这是否有充分的理由，或者我的只是一个罕见的用例？

这是我正在查看的一些文档的示例（尽管认为与供应商/版本无关）：

SQL Server 排序规则名称 (SQL Server 2008 R2)

database-agnostic collation

2 个回答

  1. Best Answer

    TL;博士

    没有“与供应商无关”的排序规则视图，甚至也没有“与版本无关”，因为它们的实现——包括哪些方面可以变得不敏感以及它们的命名约定——是特定于供应商的，并且会随着时间而变化.

    这是我发现的摘要，详细信息在该行下方较长的部分中：

    RDBMS        Naming-             Combinations    Case-Sensitive and
             convention          of options?     Accent-Insensitive support?
-------      ------------        -------------   -----
SQL Server   _CS, _AI, etc       Yes             Latin1_General_100_CS_AI

DB2          _E{x}, _S{y}, etc   Yes             CLDR181_EO_S1

PostgreSQL   locale: en_US       N/A             unaccent(), not via Collation

MySQL        _cs, maybe _ai      No              No: _cs implies _as & _ci implies _ai
                                                 Yes? Create your own Collation :-)

Oracle       only _CI & _AI      No              No: _AI always implies _CI

SAP ASE      arbitrary: turdict  N/A             No: "AI" always implies "CI"

Informix     locale.codepage     N/A             No: no "AI" via Collations

    正如您在图表中看到的，七个 RDBMS 中的两个通过排序规则原生支持“区分大小写和不区分重音”操作，尽管它们具有不同的命名约定（以及其他几个功能差异）。

    一个 RDBMS——PostgreSQL——本身并不支持这种组合，但您仍然可以通过使用unaccent()附加功能去除重音符号来实现它。

    最后四个 RDBMS，其中两个具有类似的选项命名约定，既不原生支持这种组合，也似乎没有一种方法可以在不编写自己的函数来删除重音符号/变音符号的情况下完成此操作。MySQL 允许创建您自己的排序规则，但这需要您将其添加到源代码控制并将其合并到您的测试和部署过程中，以便它可以应用于所有环境中的所有服务器（但仍然是一个非常酷和灵活的选项） . SAP ASE 提到 SAP可以提供额外的 Unicode 排序顺序，但没有提到他们可能愿意提供什么。

    关于：

    这有充分的理由还是我的只是一个罕见的用例？

    我可以说，在对这个答案进行研究时，我遇到了很多人希望 MySQL 不区分大小写和区分重音，但很少有人（如果有的话）要求您想要的组合。

    我想要一个搜索条件来使用区分大小写但不区分重音的排序规则，但找不到。
    ...
    这个问题与供应商/版本无关

    您的搜索没有成功，因为根据排序规范查找 RDBMS 没有任何意义。这不是排序规则的工作方式。尽管您希望将此视为与供应商无关，但现实情况是排序规则（至少是我们与之交互的部分）非常特定于供应商，并且并不总是适合您正在搜索的方案.

    字符串比较和排序非常复杂，并且有不同的方式来执行这些规则。一种方法是使用考虑到一个或多个规则的映射。因此，区分大小写和重音的 Sensitive 和 Insensitive 的四种组合将等同于四个单独的映射。例如，您在SQL Server Collat​​ion Name的 MSDN 页面上看到了这一点。如果向下滚动，您将看到图表的左列是Sort Order ID。每个 Collat​​ion 都有不同的 ID：SQL_Latin1_General_Cp1_CI_AS= 52 而SQL_Latin1_General_Cp1_CS_AS= 51，即使唯一的区别在于区分大小写。

    或者，它可以是基于规则的，例如 Unicode 通过 Unicode Collat​​ion Algorithm (UCA) 提供的功能。在这种方法中，默认情况下，每个字符都被赋予一个或多个权重。然后，每种文化/语言环境都可以选择覆盖任何这些权重、删除规则或添加规则。该算法会考虑任何特定于语言环境的规则，然后可能会根据选择的任何选项（敏感性，在进行区分大小写的排序时哪个大小写优先等）来操纵这些权重。这就是为什么进行 Unicode 排序比非 Unicode 排序慢一点的原因之一。

    要了解实际有多少选项（即实际复杂性），请查看 ICU（Unicode 国际组件）项目中的这个演示：

    ICU 整理演示

    有 8 个单独的选项要指定，其中一些在您正在考虑的排序规则名称规范的多个元素中表示（例如CS，、、、CI等AS）AI。考虑到有多少变化，使用每个组合都有自己的 ID 的映射文件方法会产生数千个文件。每当这些特定语言发生更改或发现错误时，其中许多文件都需要更新。这可能就是为什么在 SQL Server 2012 中只有 75 种类型的排序规则（即名称以 开头的排序规则SQL_）。因此没有 的组合_CS_AI。

    以及为什么您无法为基于 UCA 的排序规则找到该组合？好吧，SQL Server 2012 中有 3810 个排序规则不以 开头SQL_，因此总共有 3885 个排序规则。该列表似乎太长，无法在网页上完全列举。但这并不能完全解释为什么您找不到其他供应商的这种组合。

    除了已经提到的（即要实现的组合太多，以及要列出的实现太多）之外，您仍然需要与特定于供应商的实现作斗争。含义：并非所有供应商都允许定制所有这些选项，并且首先没有排序规则的标准命名约定。此外，并非所有供应商都将排序选项视为排序规则的一部分：PostgreSQL 排序规则是所选语言环境的默认排序，您需要使用它ILIKE来进行不区分大小写的比较。有关特定于供应商的信息，请参见下文。

    SQL 服务器（微软）

    您在这两个 MSDN 文档页面上看到的内容与@MartinSmith 在对该问题的评论中提供的查询之间的区别（下面稍作修改）：

    SELECT *
FROM   sys.fn_helpcollations()
WHERE  [name] LIKE '%[_]CS[_]AI%';

    是这两个 MSDN 页面专门指的是非常不推荐使用的 SQL Server 排序规则，而作为该查询结果显示的排序规则（其中 888 个从 SQL Server 2012，SP3 开始）是 Windows 排序规则。

    从 SQL Server 2000 开始，旧的 SQL Server 排序规则（在 SQL Server 能够利用 Windows 排序规则之前创建）已被弃用，并且不会使用新规则或功能进行更新。例如，从 SQL Server 2012 开始，添加了一组排序规则，支持正确处理补充字符的内置函数（即 UCS-2 中最初定义的“基本”65,536 个字符之外的剩余 UTF-16 字符)。这些较新的排序规则以_SC（如在补充字符中）结尾。

    最好不要使用 SQL Server 排序规则——名称以 . 开头的排序规则SQL_。因此，您确实可以访问大量支持您正在寻找的选项组合的排序规则（即区分大小写和不区分重音）。只要可用，最好也使用一端，_SC只要它具有您想要的所有其他选项。

    虽然 SQL Server 确实使用_CS_AI命名约定，但没有列出所有 3810（自 SQL Server 2012 起）Windows 排序规则。只有Windows 排序规则名称页面列出了所有语言环境和版本，以及命名约定的工作方式，但仅此而已。

    SQL Server 还支持切换宽度和假名敏感度。

    MySQL（被甲骨文收购）

    MySQL 5.7 版文档声明它确实支持_ai、_as、_ci和_cs后缀（并且_bin为了完整性），但也声明：

    对于未指定区分重音的非二进制排序规则名称，它由区分大小写决定。也就是说，如果排序规则名称不包含_aior _as，_ci则顾名思义_ai，_cs顾名思义_as。

    例如，latin1_general_ci不区分大小写（隐式不区分重音），区分大小写（隐式区分latin1_general_cs重音）

    这当然意味着可以有一个latin1_general_cs_ai排序规则。但是，我有权访问的 MySQL 5.5.50 服务器没有任何具有多个后缀的排序规则，我看到的唯一后缀是：、、和总共 198 个排序规则。我使用SHOW COLLATION命令列出它们。_cs_ci_bin

    因此，虽然听起来 MySQL 使用了类似的命名约定（至少就这两个选项而言），但我找不到与您正在寻找的内容匹配的排序规则。但是，有可能去掉重音符号（和其他变音符号）并使用_cs排序规则来获得你想要的东西（类似于你在 PostgreSQL 中的做法——见下文）。但我不确定这个选项，目前没有时间进一步研究。

    或者，您可以创建自己的排序规则来完全按照您的意愿行事。与其他 RDBMS 不同，MySQL 似乎使添加自己的排序规则变得相当简单，在这种情况下，您可以完全控制每个字符的权重。有关详细信息，请参阅向 8 位字符集添加简单排序规则和向 Unicode 字符集添加 UCA 排序规则。

    有关 MySQL 如何处理不同类型的排序规则的更多信息，请参阅他们的排序规则实现类型页面。

    PostgreSQL

    PostgreSQL 中的排序规则似乎远没有那么灵活。您仅指定文化/语言环境：en_US、、de_DE等。有关详细信息，请参阅他们的文档页面以获取排序规则支持。因此，默认情况下，您会获得特定于文化的覆盖，但排序规则对所有内容都敏感（顺便说一下，这与“二进制”排序规则不同）。

    您可以使用ILIKE（第 9.7.1 节）来获得不区分大小写的功能，但它们没有类似的区分重音的运算符。但是，我发现它们确实具有非重音功能，可用于去除重音和其他变音符号。请注意，此功能是附加提供的模块，因此不一定存在于任何要使用的特定 PostgreSQL 服务器中。最近链接的文档指出：

    从源代码分发构建时，这些组件不会自动构建，除非您构建“世界”目标
    ......
    如果您使用的是 PostgreSQL 的预打包版本，这些模块通常作为单独的子包提供，例如postgresql 贡献。

    如果您没有并且想要它，请参阅该文档以获取有关如何获取该功能的说明。

    更多信息也可以在以下 Stack Overflow 答案中找到：

    PostgreSQL 是否支持“不区分重音”排序规则？

    DB2 (IBM)

    与 Microsoft SQL Server 类似，DB2 有两种排序规则：

    • “SYSTEM”排序规则，使用以下格式指定：SYSTEM_{codepage}_[optional-territory]. 这些不是很灵活，并且似乎不支持对大小写、重音或任何内容进行定制敏感性。您可以在此处找到支持的排序规则列表：支持的区域代码和代码页

    • 基于 Unicode 排序算法 (UCA) 的排序规则。这些确实支持相当多的剪裁。有关如何配置行为、命名约定和有效语言环境列表的详细信息，请参阅他们的基于 Unicode 排序算法的排序规则页面。请注意，在表 1 中，第三行（“案例级别”）中的示例以：

      将 Case Level 属性设置为 on 并将 Strength 属性设置为 primary level 将忽略重音但不会忽略大小写。

      这正是你要找的。但是，它的语法是： CLDR181_EO_S1. 这就是为什么您的搜索没有找到与 DB2 相关的任何内容的原因。

    甲骨文

    Oracle 10g 增加了对重音不敏感比较和排序的支持。然而：

    • 他们只能选择表示“不敏感”的操作：_CI和_AI
    • 您一次只能指定其中一个选项
    • 不区分大小写选项 -- _CI-- 仍然区分重音
    • 不区分重音选项 -- _AI-- “也始终不区分大小写。” （引自他们的文档，链接如下）

    有关更多详细信息和示例，请参阅他们的语言排序和字符串搜索文档页面。

    SAP ASE（以前的 Sybase ASE，又名 Sybase）

    ASE 支持每个语言环境/字符集的以下一种或多种敏感度组合：

    • 区分大小写，区分重音
    • 不区分大小写，区分重音
    • 不区分大小写，区分重音，优先排序
    • 不区分大小写，不区分重音

    您可以在他们的选择默认排序顺序页面上查看语言环境、字符集和可用排序顺序之间的关系。您可以在其Collat​​ion Names and IDs页面上查看完整的排序规则列表。

    它们的排序规则命名约定是任意的，因为它们都是 4 - 8 个字符，并尝试捕获语言环境名称或代码页以及某种排序意义。例如：

    altnoacc== "CP 850 Alternative – 无重音"
    rusdict== "俄语词典排序"
    dynix== "汉语拼音排序"

    他们的Selecting the Default Unicode Sort Order页面上有一条注释说明：

    您可以使用目录中的外部文件添加排序顺序$/collate/Unicode。名称和排序规则 ID 存储在syscharsets. 不必输入外部 Unicode 排序顺序的名称，syscharsets您就可以设置默认的 Unicode 排序顺序。
    ...
    外部 Unicode 排序顺序由 SAP 提供。不要尝试创建外部 Unicode 排序顺序。

    尚不清楚 SAP 是否会提供外部排序顺序以允许区分大小写和不区分重音。也许有一天我会给他们发电子邮件，询问是否可以要求。

    为了获得所需的敏感度组合，您应该能够创建一个标量用户定义函数来去除重音符号和其他变音符号。

    Informix（被 IBM 收购）

    Informix 似乎大多只支持 Collat​​ion 的默认排序和比较行为。因此排序规则只是语言环境和字符集。区分大小写是在数据库级别处理的，默认情况下它们是区分大小写的。您可以通过在语句中指定 NLSCASE INSENSITIVE将数据库（不是表、列、查询甚至谓词）设置为不区分大小写。CREATE DATABASE

    虽然每个客户端连接都可以覆盖数据库排序规则（区域设置和字符集），但似乎没有办法覆盖区分大小写的设置。并且，该NLSCASE选项的名称中有“NLS”是有原因的：它只影响NCHAR和NVARCHAR数据；CHAR并且VARCHAR始终区分大小写。

    没有解决重音敏感性问题，也没有内置功能可以去除重音/变音符号。

    Informix 排序规则命名约定是：

    <lang>_<country>.<code set>

    在哪里：

    • <lang>= 2 个字母或 3 个字母的语言代码
    • <country>= 2 个字母的国家或地区代码
    • <code set>= 以下列 3 种等效方式之一指定的代码页：
      • 名称： 8859-1
      • IBM CCSID 号的十进制值： 819
      • IBM CCSID 号的十六进制值： 0333

    因此，以下三个语言环境规范都指的是完全相同的语言环境：

    • fr_fr.8859-1
    • fr_fr.819
    • fr_fr.0333

    欲了解更多信息，请参阅：

    • 33

  2. 选项名称 描述 NLS_LANG 当前语言、地域和数据库字符集，由会话范围的全球化参数确定。NLS_LANGUAGE 会话的当前语言。NLS_SORT 排序或比较文本时使用的字符值序列。

    要检查当前的 NLS 设置，请键入：

    从 v$NLS_PARAMETERS 中选择 *；

    • -3

相关问题