SQL经典案例之常见的字符集和排序规则

SQL经典案例之常见的字符集和排序规则+中文的排序方式

• 常见字符集（Character Set）
• ASCII
• GB2312
• Unicode
• 排序规则（Collation）
• 排序规则的作用
• 常见的排序规则属性
• 常见排序规则示例

在创建数据库或表时，通常会指定一个字符集和排序规则：

• 字符集：决定数据库可以存储哪些字符。
• 排序规则：决定字符的比较和排序方式。

常见字符集（Character Set）

ASCII

• ASCII：
  • 最早的字符集，仅支持英文字符、数字和部分控制字符。
  • 使用 7 位编码，共 128 个字符。
• ISO-8859-1（Latin-1）：
  • 扩展了 ASCII，支持西欧语言字符。
  • 使用 8 位编码，共 256 个字符。

GB2312

• GBK：
  • 支持简体中文，每个中文字符占用 2 个字节
  • 使用 2 字节编码，兼容 GB2312
• GB18030：
  • 支持简体中文的扩展字符集，兼容 GBK
  • 使用 1、2 或 4 字节编码

Unicode

• UTF-8：
  • 支持全球所有语言的字符，是 Unicode 的一种实现方式，每个中文字符占用 3 个字节
  • 可变长度编码（1 到 4 字节），兼容 ASCII
• UTF-16：
  • 另一种 Unicode 实现方式，使用 2 或 4 字节编码
  • 适合存储大量非 ASCII 字符

排序规则（Collation）

排序规则定义了字符的比较和排序方式。它基于字符集，但可以进一步指定语言、区域和排序规则的具体行为。

排序规则的作用

• 决定字符串的比较方式（如大小写敏感、重音敏感等）。
• 影响 ORDER BY、GROUP BY 和索引的排序行为。
• 影响字符串的匹配和搜索（如 LIKE 和 = 操作）。

常见的排序规则属性

• 大小写敏感（Case Sensitivity）：
  • 是否区分大小写（如 A 和 a 是否相同）。
  • 示例：utf8_general_ci（不区分大小写），utf8_bin（区分大小写）。
• 重音敏感（Accent Sensitivity）：
  • 是否区分重音字符（如 é 和 e 是否相同）。
  • 示例：utf8_unicode_ci（不区分重音），utf8_unicode_cs（区分重音）。
• 语言和区域设置：
  • 根据特定语言或区域的规则进行排序（如德语、法语等）。
  • 示例：utf8_german2_ci（德语排序规则）。

常见排序规则示例

• Oracle：默认AL32UTF8字符编码，中文按照偏旁部首排序
  – NLS_SORT：定义字符串的比较和排序规则，是否区分大小写、是否区分重音等
  – NLS_COMP：定义字符串比较的行为，通常与 NLS_SORT 配合使用

-- 查看当前排序规则：
SELECT * FROM NLS_SESSION_PARAMETERS WHERE PARAMETER IN ('NLS_SORT', 'NLS_COMP');

-- 基于二进制值排序
ALTER SESSION SET NLS_SORT = 'BINARY';	-- 区分大小写的排序
ALTER SESSION SET NLS_SORT = 'BINARY_CI'; -- 不区分大小写的排序
-- 基于 Unicode 标准进行排序
ALTER SESSION SET NLS_SORT = 'UTF8_UNICODE_CI'; -- 不区分大小写
ALTER SESSION SET NLS_SORT = 'UTF8_UNICODE_CS'; -- 区分大小写
-- 基于语言排序规则
ALTER SESSION SET NLS_SORT = 'GERMAN';   -- 德语排序规则
ALTER SESSION SET NLS_SORT = 'FRENCH';   -- 法语排序规则

-- SQL语句指定中文排序规则
SELECT last_name FROM employees
ORDER BY NLSSORT(last_name, 'NLS_SORT=SCHINESE_PINYIN_M');
-- SCHINESE_PINYIN_M: 中文拼音排序
-- SCHINESE_RADICAL_M: 偏旁部首排序
-- SCHINESE_STROKE_M: 笔画排序

NLSSORT()是Oracle提供的一个系统函数，用于返回按照指定排序规则编码的字符序列

-- 查询数据库的字符集
SELECT * FROM nls_database_parameters WHERE parameter = 'NLS_CHARACTERSET';

-- 查询会话的字符集和排序规则
SELECT * FROM nls_session_parameters WHERE parameter IN ('NLS_CHARACTERSET', 'NLS_SORT');

-- 查询实例的字符集和排序规则
SELECT * FROM nls_instance_parameters WHERE parameter IN ('NLS_CHARACTERSET', 'NLS_SORT');

• MySQL：8.0默认utf8mb4字符编码，中文按照偏旁部首排序
  • utf8mb4_general_ci：不区分大小写和重音。
  • utf8mb4_bin：区分大小写和重音。
  • utf8mb4_unicode_ci：基于 Unicode 规则，支持多语言。

-- SQL语句指定中文排序规则
SELECT last_name FROM employees
ORDER BY CONVERT(last_name using GBK);

CONVERT()是MySQL提供的一个系统函数，用于转换数据的字符集编码

-- 查询当前数据库的字符集和排序规则
SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';

-- 查询表的字符集和排序规则
SHOW CREATE TABLE your_table_name;

-- 查询列的字符集和排序规则
SHOW FULL COLUMNS FROM your_table_name;

• SQL Server：字符集和排序规则是一个概念，默认按安装OS区域设置，中国地区默认Chinese_PRC_CS，中文按照偏旁部首排序
  • Latin1_General_CI_AS：不区分大小写，区分重音。
  • SQL_Latin1_General_CP1_CI_AS：基于 Latin1 字符集，不区分大小写，区分重音。

-- SQL语句指定Chinese_PRC_CI_AI_KS_WS中文拼音排序规则
SELECT last_name FROM employees
ORDER BY last_name COLLATE Chinese_PRC_CI_AI_KS_WS;

COLLATE关键字实现其他方式的中文排序

-- 查询服务器级别的排序规则
SELECT SERVERPROPERTY('Collation');

-- 查询数据库级别的排序规则
SELECT name, collation_name 
FROM sys.databases 
WHERE name = 'your_database_name';

-- 查询表或列的排序规则（注意：表或列本身不直接存储排序规则，但可以从数据类型推断）
SELECT COLUMN_NAME, COLLATION_NAME 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'your_table_name';

• PostgreSQL：默认UTF-8字符编码，中文按照偏旁部首排序

-- SQL语句指定zh_CN中文拼音排序规则
SELECT last_name FROM employees
ORDER BY last_name COLLATE "zh_CN";

COLLATE关键字可以用于转换数据的字符集编码

-- 数据库编码和排序规则查询
select datname,pg_encoding_to_char(encoding),datcollate,datctype from pg_database;
select datname,pau.rolname,pg_encoding_to_char(encoding),datcollate,datctype 
from pg_database pdb,pg_authid pau where pdb.datdba=pau.oid;