PostgreSQL 向 PostgreSQL 添加不区分大小写的 collation

在本文中，我们将介绍如何向 PostgreSQL 添加不区分大小写的排序规则（collation）。

阅读更多：PostgreSQL 教程

什么是 collation

Collation 是指在数据库中对字符数据进行排序和比较的规则。在默认情况下，PostgreSQL 使用的是区分大小写的排序规则。这意味着在进行排序或比较时，字母大小写会被明确地考虑在内。然而，在某些情况下，我们希望忽略字符的大小写，将它们视为等效的。

区分大小写和不区分大小写的排序

让我们以一个简单的例子来说明区分大小写和不区分大小写的排序规则之间的差异。假设我们有一个包含如下数据的表：

CREATE TABLE users (
    id SERIAL,
    name VARCHAR(100)
);

INSERT INTO users (name) VALUES ('Alice');
INSERT INTO users (name) VALUES ('bob');
INSERT INTO users (name) VALUES ('Charlie');
INSERT INTO users (name) VALUES ('david');

默认情况下，使用区分大小写的排序规则对数据进行排序，查询语句为：

SELECT * FROM users ORDER BY name;

结果将会是：

1 | Alice
3 | Charlie
2 | bob
4 | david

然而，如果我们想要不区分大小写地排序，即将大写和小写字母视为相同，在 PostgreSQL 中，我们需要添加一个新的 collation。

添加不区分大小写的 collation

为了向 PostgreSQL 添加不区分大小写的排序规则，我们需要执行以下步骤：

1. 创建新的 collation 文件

   首先，我们需要创建一个新的 collation 文件，其中包含不区分大小写的排序规则定义。

   $ mkdir ~/collation
   $ cd ~/collation
   $ touch case_insensitive.sortrules
   

   然后，我们需要编辑 case_insensitive.sortrules 文件，并添加以下内容：

   <icu:charset name="case_insensitive" />
   <icu:collation name="case_insensitive">
       <icu:provider class="icu"/>
       <icu:locale/>
       <icu:strength>primary</icu:strength>
       <icu:alternate>shifted</icu:alternate>
       <icu:sortvariable>space</icu:sortvariable>
       <icu:variableTop></icu:variableTop>
   </icu:collation>
   ```

   这个文件中定义了一个名为 "case_insensitive" 的 collation，它使用了 ICU(国际化组件库) 提供的排序规则。

2. 生成新的 collation 数据库扩展

   接下来，我们需要使用 `pg_collation` 生成新的 collation 数据库扩展。

   ```bash
   pg_collation --collation-dir=~/collation --encoding=UTF8 --no-oids case_insensitive
   ```

   这将在 `~/collation` 目录中生成一个名为 `case_insensitive--1.0.sql` 的文件，其中包含了必要的 SQL 命令。

3. 安装新的 collation 数据库扩展

   最后，我们需要安装并启用新的 collation 数据库扩展。

   ```bash psql -U postgres -d your_database -f case_insensitive--1.0.sql
   ```

   在此命令中，`your_database` 是您要添加新 collation 规则的数据库的名称。

完成以上步骤后，我们现在可以在我们的查询中使用新的 collation 规则了。

## 使用不区分大小写的 collation

为了在查询中使用新的不区分大小写的 collation 规则，我们需要使用 `COLLATE` 关键字。

让我们使用我们之前的用户表作为例子。默认情况下，使用区分大小写的排序规则进行排序的查询为：

```sql
SELECT * FROM users ORDER BY name COLLATE "default";

如果我们要使用我们刚刚添加的不区分大小写的 collation 规则进行排序，查询语句将会是：

SELECT * FROM users ORDER BY name COLLATE "case_insensitive";

结果将会是：

1 | Alice
2 | bob
3 | Charlie
4 | david

我们可以看到，现在名字的大小写并不会影响排序的结果。

总结

通过向 PostgreSQL 添加不区分大小写的 collation 规则，我们可以在查询中忽略字符的大小写，将其视为等效的。这对于某些特定的应用场景非常有用，例如在用户登录时的用户名验证。通过使用新的 collation 规则，我们可以轻松地将大小写不敏感的排序功能引入 PostgreSQL 数据库中。

希望本文对你了解如何向 PostgreSQL 添加不区分大小写的 collation 规则有所帮助！