数据库教程11：关于ORDER BY排序时的Collation和PostgreSQL实现不区分大小写的推荐通用方案

引言

在数据库查询中，排序（ORDER BY）是常见的操作之一，它决定了查询结果的展示顺序。然而，当涉及到字符串类型的排序时，一个常常被忽视但又极其重要的因素是Collation（排序规则）。Collation决定了字符串如何被比较和排序，包括是否区分大小写、是否考虑重音符号等。本文将深入探讨Collation在ORDER BY排序中的作用，并特别针对PostgreSQL数据库，提供实现不区分大小写排序的推荐通用方案。

Collation基础

什么是Collation？

Collation是一组规则，用于定义字符串比较和排序的方式。在不同的数据库系统中，Collation的实现和名称可能有所不同，但其核心功能是一致的：确定字符串的相对顺序。Collation可以基于语言、地区或特定的排序需求来定制，例如，是否区分大小写、是否忽略空格或标点符号等。

Collation在ORDER BY中的作用

当使用ORDER BY对字符串列进行排序时，数据库会根据该列的Collation设置来决定排序顺序。如果Collation设置为区分大小写，那么“Apple”和“apple”将被视为不同的字符串，并按照它们在字典中的顺序排列（通常是大写字母排在小写字母之前）。相反，如果Collation设置为不区分大小写，那么“Apple”和“apple”将被视为相同的字符串，在排序时不会因大小写差异而改变顺序。

PostgreSQL中的Collation

PostgreSQL的Collation支持

PostgreSQL提供了丰富的Collation支持，允许用户根据需要定制字符串的比较和排序行为。PostgreSQL使用操作系统的本地化支持来实现Collation，这意味着Collation的设置通常与操作系统的语言和地区设置相关联。

查看和设置Collation

在PostgreSQL中，可以使用\l命令（在psql中）或查询pg_collation系统目录来查看可用的Collation。要设置或更改表的列或查询的Collation，可以在创建表时指定Collation，或在查询中使用COLLATE子句。

实现不区分大小写的排序

为什么需要不区分大小写的排序？

在许多应用场景中，不区分大小写的排序更为实用。例如，在搜索用户输入时，用户可能不关心搜索结果的大小写形式。此外，在展示数据时，不区分大小写的排序可以提供更一致的视觉体验。

PostgreSQL中的实现方案

方案一：使用CITEXT数据类型

PostgreSQL提供了一个扩展citext，它定义了一个名为citext的数据类型，该类型在内部自动将字符串转换为小写（或大写）进行比较和排序。使用citext可以简化不区分大小写的排序实现。

安装和使用：

1. 确保已安装citext扩展：CREATE EXTENSION IF NOT EXISTS citext;
2. 在创建表时，将需要不区分大小写排序的列定义为citext类型。
3. 使用ORDER BY对该列进行排序时，将自动实现不区分大小写的排序。

示例：

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username CITEXT NOT NULL
);
-- 插入数据
INSERT INTO users (username) VALUES ('Alice'), ('alice'), ('BOB'), ('bob');
-- 不区分大小写的排序查询
SELECT * FROM users ORDER BY username;

方案二：使用COLLATE子句

如果不想使用citext扩展，或者需要在特定查询中临时改变Collation，可以使用COLLATE子句。PostgreSQL允许在查询中指定Collation，以覆盖列或表的默认Collation设置。

示例：

-- 假设有一个普通的text类型列
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);
-- 插入数据
INSERT INTO products (name) VALUES ('Apple'), ('apple'), ('Banana'), ('banana');
-- 使用COLLATE子句实现不区分大小写的排序
SELECT * FROM products ORDER BY name COLLATE "en_US.utf8" COLLATE "C.utf8" NOT CASE SENSITIVE; -- 注意：PostgreSQL中直接这样写并不正确，需使用特定Collation名或自定义函数
-- 更实际的做法是使用一个已知的不区分大小写的Collation（如果存在），或通过函数转换
-- 更实际的示例（假设有一个合适的不区分大小写的Collation）
-- SELECT * FROM products ORDER BY name COLLATE "en_US.utf8" COLLATE "ci"; -- "ci"通常不是直接可用的，需根据实际Collation名调整
-- 使用函数转换的替代方案（不推荐作为通用方案，因为性能可能受影响）
SELECT * FROM products ORDER BY LOWER(name);

注意：上述COLLATE "en_US.utf8" COLLATE "C.utf8" NOT CASE SENSITIVE写法并不直接适用于PostgreSQL，因为PostgreSQL的Collation语法不支持这种直接的“不区分大小写”指定。通常，你需要知道一个具体的不区分大小写的Collation名称（如果操作系统或PostgreSQL配置中提供了），或者使用函数如LOWER()或UPPER()来转换字符串，但这可能影响性能。

推荐通用方案：使用自定义函数或视图

对于需要频繁进行不区分大小写排序的场景，推荐创建一个自定义函数或视图，该函数或视图在内部处理大小写转换，从而简化查询。

示例（使用视图）：

CREATE VIEW products_lower_name AS
SELECT id, LOWER(name) AS lower_name, name AS original_name
FROM products;
-- 然后可以对lower_name进行排序
SELECT original_name FROM products_lower_name ORDER BY lower_name;

示例（使用函数）：

CREATE OR REPLACE FUNCTION sort_by_lower_name(products) RETURNS TEXT AS $$
BEGIN
    RETURN LOWER($1.name);
END;
$$ LANGUAGE plpgsql IMMUTABLE;
-- 使用函数进行排序（需要配合其他查询技巧，因为ORDER BY不能直接使用函数返回值作为列名）
-- 更实际的做法是使用子查询或CTE
WITH sorted_products AS (
    SELECT id, name, LOWER(name) AS lower_name
    FROM products
)
SELECT id, name FROM sorted_products ORDER BY lower_name;

结论

Collation在ORDER BY排序中扮演着至关重要的角色，它决定了字符串如何被比较和排序。对于需要不区分大小写排序的场景，PostgreSQL提供了多种实现方案，包括使用citext数据类型、COLLATE子句（需知道具体的不区分大小写的Collation名）、以及通过自定义函数或视图来简化查询。根据实际应用场景和性能需求，选择最适合的方案将大大提高数据库查询的效率和准确性。