使用适用于 Aurora PostgreSQL 的 Babelfish 处理不区分重音的排序规则

适用于 Aurora PostgreSQL 的 Babelfish 包括对 SQL Server 有线协议和 T-SQL（微软 SQL Server 中使用的查询语言）的支持。这意味着开发人员可以使用 Babelfish 在 亚马逊 Aurora PostgreSQL 兼容版上运行其现有 SQL Server 应用程序， 而无需切换数据库驱动程序或完全重写查询。

如果您正在使用拉丁语或数据库中带有重音的任何其他语言，则可能需要使用 CI_AI 归类 。 CI 代表不区分大小写，它允许您对文本进行排序和比较而不考虑大小写，对于大小写，我们还有与 C I 相反的 CS （区分大小写），它在筛选 、排序和比较文本时区分大写和小写，为了强调起见，我们还有 2 种变体 AS（区分大小写），用于区分带重音和不带重音的单词， AI （不区分重音），允许您排序和比较不考虑变音符号的文本数据（包括在字母中添加的重音符号和其他字形，例如 cedilla (q)、抑扬音 (€)、变音符号 (ö)、波浪号 (n) 等。）。如果你正在使用葡萄牙语、法语、西班牙语或其他在某些字母上使用重音的语言，这可能特别有用。常见的情况是名称，在应用程序中搜索名称时，用户可能无法键入正确的名称，最终找不到所需的记录，例如 Joao 或 Joáo、Jurgen 或 Jürgen。

适用于 Aurora 的 Babelfish PostgreSQL 支持 来 自 SQL Server 的 35 种排序规则 ，这些排序规则可用作服务器或对象排序规则。你也可以在 Babelfish 上运行查询 SE LECT* FROM fn_helpcollations ()，以获取对象支持的归类列表。

Note: Currently, you will find 141 collations supported in Babelfish by querying fn_helpcollations(), but note that not all of them can be used as the default server collation (the 35 collations which are shown in the dropdown menu in the RDS console when creating the instance), however, you can use these collations when creating your object or in your expressions.

默认排序规则为 sq l_latin1_general_cp1_ci_as。此归类表示 中的 _ as 区分重音，这意味着数据库将区分重音字符和非重音字符，例如 Jose 不等于 Jose。 但是，如果您的应用程序要求使用不区分重音 ( AI ) 排序规则，则目前 Babelfish 不支持将不区分重音作为默认排序规则，则需要使用替代排序规则。

T-SQL 和 PostgreSQL 中使用的默认排序规则并不相同，我们在筛选或排序记录时会有不同的行为，这可能会导致语义差异。

在这篇文章中，我们将介绍如何在适用于 Aurora PostgreSQL 的 Babelfish 上使用 C I_AI 和 CS_AI 排序规则，这样你就可以继续支持拉丁语语言或任何其他带口音的语言。

先决条件和限制

要在 Babelfish 中使用 CI_AI 归类，您必须满足以下先决条件：

  • 适用于 Aurora PostgreSQL 数据库集群 版本 2.1.2 (PG 14.3) 或更高版本的 Babelfish
    如果您没有在低于 2.1.2 版本的 Babelfish 上明确使用 COLLATE 子句，则可能会收到一条冲突排序规则的错误消息
  • SQL 服务器管理工作室 (SSMS)
  • 从 SSMS 连接到你的 Babelfish 集群的权限。

CI_AI /CS_AI 归类是什么？

CI_AI 排序规则是一种既不区分大小写又不区分重音的排序规则。这意味着，如果字符串具有相同的字符，则无论大小写或重音符号如何，都将被视为相等。

CI_AS 归类不区分大小写且区分重音。这意味着对字符串的大写和小写给予同等对待，但重音符号的处理却不一样。

例如，考虑以下两个字符串： José 和 j ose 。使用 CI_AS 或 CS_AS 排序规则，这两个字符串不会被视为相等。但是，如果使用 CI_AI 排序规则，它们将被视为相等，因为排序规则会忽略大小写和重音符号，从而使两个字符串完全相同。

为什么要在 Babelfish 中使用 CI_AI 归类？

你可以考虑在你的 Babelfish 项目中使用 CI_AI 归类有以下几个原因：

  • 改进了数据检索 -通过忽略大小写和重音符号， CI_AI 排 序规则可以帮助确保您的数据得到正确排序和比较，即使在排序或比较字符串的语言规则可能复杂或不一致的情况下也是如此。
  • 更好地支持多语言数据检索 — CI_AI 排 序规则在处理多语言数据时特别有用，因为它们允许您对字符串进行排序和比较，无论语言之间的语言差异中大小写或重音符号如何。
  • 用户友好性 — 使用 CI_AI 归类，您可以减少需要进行的比较次数，这有助于提高应用程序的性能。

以下脚本将创建示例数据库和表，以评估在 SQL Server 上使用 CI_AI 归类的数据库的行为：

--- creating a sample database on SQL Server
CREATE DATABASE [DB-SQL-test] COLLATE Latin1_General_CI_AI
GO
USE [DB-SQL-test]
GO
--- creating test table
CREATE TABLE tbProduct (idProd int, Item nvarchar(50))
GO
--- insert CS and CI records 
INSERT INTO tbProduct VALUES (1,'José'),(2,'Jose'),(3,'josé'),(4,'jose')
GO
-- Query filtering with uppercase and non-accented text
SELECT * FROM tbProduct WHERE Item = 'JOSE'

请注意，如果你在 Babelfish 上运行 C REATE DATABASE [db-SQL-Test] COLLATE Latin1_General_CI_AI，该命令将失败，因为数据库中不支持将归类 Latin1_ General_ci_AI 作为默认排序规则。

在 SQL Server 上执行 SELECT 查询的结果如下：

idProd Item
------ ----------
1      José
2      Jose
3      josé
4      jose

(4 rows affected)

请注意，即使是使用大写和非重音过滤器的查询，SQL Server 也会返回所有引用 JOS E 的记录 ，因为我们使用的是具有归类 CI 和 AI 的数据集。

让我们在 SQL Server 上运行另一个查询：

-- Query filtering with capitalized and accented text
USE [DB-SQL-test]
GO
SELECT * FROM tbProduct WHERE Item = 'José'

在 SQL Server 上执行 SELECT 查询的结果如下：

idProd Item
------ ----------
1      José
2      Jose
3      josé
4      jose

(4 rows affected)

请注意，该查询返回了所有记录，即使使用大写和重音过滤器，SQL Server 也会返回所有引用 José 的记录 ，因为 CI_AI 归类会忽略大小写和重音符号，从而使所有字符串完全相同。

现在让我们在 Babelfish 上运行同样的进程，但整理子句除外：

--- creating a sample database on Babelfish
CREATE DATABASE [DB-BBF-test] 
GO
USE [DB-BBF-test]
GO
--- creating test table
CREATE TABLE tbProduct (idProd int, Item nvarchar(50))
GO
--- insert CS and CI rows 
INSERT INTO tbProduct VALUES (1,'José'),(2,'Jose'),(3,'josé'),(4,'jose')
GO
-- Query filtering with uppercase and non-accented text
SELECT * FROM tbProduct WHERE Item = 'JOSE'

以下是对 Babelfish 的查询结果：

idProd Item
------ ----------
2      Jose
4      jose

(2 rows affected)

Babelfish 只返回两行，因为 Babelfish 支持 CI 排序规则，但不支持数据库级别的 AI 排序规则。

在 Babelfish 上使用 CI_AI 排序规则获取 Aurora PostgreSQL

使用 CI_AI 归类的另一种方法是在表创建期间指示正确的排序规则。您可以使用默认排序规则创建 Babelfish 实例，但是在创建表时，您需要为文本列指定正确的排序规则：

USE [DB-BBF-test]
GO
--- creating test table specifying collate clause
CREATE TABLE tbProduct2 (idProd int, Item nvarchar(50) collate sql_latin1_general_cp1_ci_ai)
GO
--- insert CS and CI records 
INSERT INTO tbProduct2 VALUES (1,'José'),(2,'Jose'),(3,'josé'),(4,'jose')
GO
-- Query filtering with uppercase and non-accented text
SELECT * FROM tbProduct2 WHERE Item = 'JOSE'

对 Babelfish 的查询结果如下：

idProd Item
------ ----------
1      José
2      Jose
3      josé
4      jose

(4 rows affected)

通过在表创建期间在文本列中指定 collate 子句，我们现在有了预期的行为：不管 “e” 是否有重音，查询都会返回所有记录。

测试联接和排序操作

让我们测试一下我们的解决方案在使用和不使用 COLLATE 子句对表进行排序和联接时的行为：

USE [DB-BBF-test]
GO
--creating table to simulate join
create table tbprice (item varchar(20), price decimal(5,2))
go
insert into tbPrice values ('jose',10.30)
go
--joining tbProduct with tbPrice
SELECT a.*, b.price FROM tbProduct a INNER JOIN tbprice b ON a.Item = b.item
Go

对 Babelfish 的查询结果如下：

idProd Item price
------ ---- -----
2      Jose 10.30
4      jose 10.30

(2 rows affected)

联接只返回了两条记录，因为我们在创建 TbPric e 时 没有 CI_AI 子句，因此联接运算符会忽略重音记录。现在，让我们使用使用 collat e CI_AI 子句的 tbProduct2 运行联接： 

USE [DB-BBF-test]
GO
SELECT a.*, b.price FROM tbProduct2 a INNER JOIN tbprice b ON a.Item = b.item

注意 ：如果你运行的是低于 2.1.2 (PG 14.3) 的 Babelfish 版本，你会收到与排序规则冲突有关的错误：

“could not determine which collation to use for string comparison”

以下是对 Babelfish 的查询结果：

idProd Item price
------ ---- -----
1      José 10.30
2      Jose 10.30
3      josé 10.30
4      jose 10.30

(4 rows affected)

通过使用其中一个具有 CI_AI 归类的表，我们可以查看所有记录（CI 和 AI）。如果我们尝试在 SQL Server 中模拟相同的行为，则会得到不同的结果：

--Execute this command on SQL Server
use [DB-SQL-test]
go
CREATE TABLE #tbTemp (id int, item NVARCHAR(50) )
INSERT INTO #tbTemp VALUES (1,'José')
SELECT a.*, b.* FROM #tbTemp a INNER JOIN tbProduct b ON a.item = b.item

在 SQL Server 上查询的结果如下所示：

Msg 468, Level 16, State 9, Line 25
Cannot resolve the collation conflict between "Latin1_General_CI_AI" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

在这种情况下，SQL Server 无法处理排序规则冲突，这将要求开发人员使用整理 子句或更改表 排序规则以避免此问题，而 Babelfish 会考虑 CI_AI 返回记录。

现在让我们来看看 SQL Server 和 Babelfish 之间的排序操作有何不同。首先，让我们观察 SQL Server 的行为：

--Execute this command at SQL Server
use [DB-SQL-test]
go
--Ascending sort
SELECT * FROM tbProduct ORDER BY Item ASC
GO
-- Descending sort
SELECT * FROM tbProduct ORDER BY Item DESC

下表显示了 SQL Server 上的查询结果。

Ascending order Descending order 
idProd Item
------ ----------
1      José
2      Jose
3      josé
4      jose

(4 rows affected)
 
idProd Item
------ ----------
1      José
2      Jose
3      josé
4      jose

(4 rows affected)

在 SQL Server 场景中，无论使用哪种排序模式（ ASC 或 DESC ），结果都是相同的。现在让我们在 Babelfish 中运行相同的脚本：

--Execute this command in Babelfish
use [DB-BBF-test]
go
-- Ascending sort - table w/o collate
SELECT * FROM tbProduct ORDER BY Item ASC
GO
-- Descending sort - table w/o collate
SELECT * FROM tbProduct ORDER BY Item DESC

下表显示了对 Babelfish 的查询结果。

Ascending order Descending order 
idProd Item
------ ----------
2      Jose
4      jose
1      José
3      josé

(4 rows affected)
 
idProd Item
------ ----------
1      José
3      josé
2      Jose
4      jose

(4 rows affected)

在 Babelfish 场景中，结果是不同的： ASC 首先 返回未加重音的记录，而 DESC 返回的是相反的 记录。 另请注意，这两个结果都与 SQL Server 的结果不同。这是因为 PostgreSQL 处理排序操作的方式。如果您在 tbProduct2 上运行与考虑 collate 子句相同的查询，则得到的结果也会有所不同：

--Execute this command in Babelfish
use [DB-BBF-test]
go
-- Ascending sort - table w/ collate
SELECT * FROM tbProduct2 ORDER BY Item ASC
GO
-- Descending sort - table w/ collate
SELECT * FROM tbProduct2 ORDER BY Item DESC

下表显示了对 Babelfish 的查询结果。

Ascending order Descending order 
idProd Item
------ ----------
1      José
2      Jose
3      josé
4      jose

(4 rows affected)
 
idProd Item
------ ----------
1      José
2      Jose
3      josé
4      jose

(4 rows affected)

在 Babelfish 场景中，当对包含整理子句的表进行查询和排序时，结果现在与 SQL Server 相同。

在 T-SQL 表达式中显式整理子句

如果您不想或无法更改表架构，则可以在查询中添加 collate 子句以使其具有相同的行为：

use [DB-BBF-test]
go
-- Query filtering with uppercase and non-accented text
SELECT * FROM tbProduct WHERE Item = 'jose' collate sql_latin1_general_cp1_ci_ai

对 Babelfish 的查询结果如下：

idProd Item
------ ----------
1      José
2      Jose
3      josé
4      jose

(4 rows affected)

你也可以使用同样的方法来联接表：

use [DB-BBF-test]
go
-- Query joining tables using collate clause
SELECT a.*, b.price FROM tbProduct a INNER JOIN tbprice b ON a.Item = b.item collate sql_latin1_general_cp1_ci_ai

对 Babelfish 的查询结果如下：

idProd Item  price
------ ----- -----
1      José  10.30
2      Jose  10.30
3      josé  10.30
4      jose  10.30

(4 rows affected)

让我们模拟排序结果的情况，在 SQL Server 上运行以下命令：

--Execute this command on SQL Server
USE [DB-SQL-test]
GO
--Creating a table and inserting some records
CREATE TABLE StudentList (FirstName nvarchar(50), LastName nvarchar(50))
GO
INSERT INTO StudentList VALUES ('Márcia','Oliveira'),('Marcia','García'),('Martha','Rivera'),('Mary','Major')
--Query to generate an ID ordered by First Name, LastName
SELECT FirstName, LastName,ROW_NUMBER() OVER(ORDER BY FirstName ASC) as 'GeneratedID' FROM StudentList

在 SQL Server 上查询的结果如下所示：

FirstName  LastName  GeneratedID
---------- --------- ------------
Márcia Oliveira  1
Marcia García    2
Martha     Rivera    3
Mary       Major     4

(4 rows affected)

现在让我们在 Babelfish 上运行同样的命令：

--Execute this command in Babelfish
USE [DB-BBF-test]
GO
--Creating a table and inserting some records
CREATE TABLE StudentList (FirstName nvarchar(50), LastName nvarchar(50))
GO
INSERT INTO StudentList VALUES ('Márcia','Oliveira'),('Marcia','García'),('Martha','Rivera'),('Mary','Major')
--Query to generate an ID ordered by First Name, LastName
SELECT FirstName, LastName, ROW_NUMBER() OVER(ORDER BY FirstName ASC) as 'GeneratedID' FROM StudentList

对 Babelfish 的查询结果如下：

FirstName  LastName  GeneratedID
---------- --------- ------------
Marcia García    1
Márcia Oliveira  2
Martha     Rivera    3
Mary       Major     4

(4 rows affected)

请注意，Babelfish 和 SQL Server 中记录的顺序并不相同，Babelfish 在排序期间考虑口音，而 SQL 没有考虑重音，如本文所述，修复此行为的一种解决方法是使用 collate 子句：

--Execute this command in Babelfish
USE [DB-BBF-test]
GO
--Fixing order with collate clause
SELECT FirstName, LastName, ROW_NUMBER() OVER(ORDER BY FirstName COLLATE sql_latin1_general_cp1_ci_ai ASC) as 'GeneratedID' FROM StudentList

对 Babelfish 的查询结果如下：

FirstName  LastName  GeneratedID
---------- --------- ------------
Márcia     Oliveira  1
Marcia     García    2
Martha     Rivera    3
Mary       Major     4

(4 rows affected)

现在我们得到了与 SQL Server 相同的结果。

同样的解决方案可以用于 ORD ER BY 子句。在 SQL 服务器上运行以下命令：

--Execute this command on SQL Server
USE [DB-SQL-test]
GO
--Querying First and Last names ordered by First Name
SELECT FirstName, LastName FROM StudentList
ORDER BY FirstName ASC

在 SQL Server 上查询的结果如下所示：

FirstName  LastName
---------- --------
Márcia     Oliveira
Marcia     García
Martha     Rivera
Mary       Major

(4 rows affected)

现在让我们在 Babelfish 上运行同样的命令：

--Execute this command in Babelfish
USE [DB-BBF-test]
GO
--Querying First and Last names ordered by First Name
SELECT FirstName, LastName FROM StudentList
ORDER BY FirstName ASC

对 Babelfish 的查询结果如下：

FirstName LastName
--------- --------
Marcia    García
Márcia    Oliveira
Martha    Rivera
Mary      Major

(4 rows affected)

与前面使用 row_num ber 的示例一样 ，请注意，Babelfish 和 SQL Server 中记录的顺序并不相同，Babelfish 在排序期间考虑的是口音，而 SQL 没有考虑重音，我们也可以使用整理子句作为解决方法： 

--Execute this command in Babelfish
USE [DB-BBF-test]
GO
--Fixing order with collate clause
SELECT FirstName, LastName FROM StudentList
ORDER BY FirstName COLLATE sql_latin1_general_cp1_ci_ai ASC

对 Babelfish 的查询结果如下：

FirstName LastName
--------- --------
Márcia    Oliveira
Marcia    García
Martha    Rivera
Mary      Major

(4 rows affected)

现在我们得到了与 SQL Server 相同的结果。

结论

在这篇文章中，我向您展示了 CI_AI 归类在使用拉丁语或任何其他带有 Babelfish 口音的语言时的重要性。通过在表创建期间使用 CI_AI 排序规则或在查询中添加 COLLATE 子句，您可以对文本数据进行排序和比较，而不考虑大小写或重音，从而使您的查询更加灵活，与现实世界更加兼容，也更易于编写查询。

如果您有任何问题、意见或建议，请发表评论。

作者简介

马塞洛·费尔南德斯 马塞洛·费尔南德斯是 亚马逊云科技 专业服务团队的高级数据库架构师，在数据库领域拥有超过 21 年的经验。在他的整个职业生涯中，Marcelo 一直致力于帮助客户应对与数据库相关的挑战，包括迁移、设计和性能优化。