SQL排序规则（collation）用于定义字符数据的比较和排序规则，直接影响查询时的排序结果、字符串比较逻辑以及不同字符集数据的兼容性。合理的设置排序规则能让数据查询和比对符合业务预期。
排序规则的基础概念
排序规则通常包含字符集和排序规则两个核心部分，字符集决定可以存储哪些字符，排序规则决定这些字符的排序和比较逻辑。常见的排序规则命名格式为字符集_语言_后缀，比如utf8mb4_general_ci中，utf8mb4是字符集，general是通用排序规则，ci表示大小写不敏感（case insensitive）。
不同层级的排序规则设置方法
1. 数据库层级设置
创建数据库时可以直接指定默认排序规则，后续该库下创建的表如果没有单独指定排序规则，会默认继承数据库的排序规则。
-- 创建使用utf8mb4字符集、大小写不敏感排序规则的数据库 CREATE DATABASE test_db DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_general_ci; -- 查看已有数据库的排序规则 SELECT DEFAULT_COLLATION_NAME FROM information_schema.SCHEMATA WHERE SCHEMA_NAME = 'test_db';
2. 表层级设置
建表时可以单独为表指定排序规则，优先级高于数据库层级的设置。
-- 创建表时指定排序规则
CREATE TABLE user_info (
id INT PRIMARY KEY AUTO_INCREMENT,
user_name VARCHAR(50)
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- 修改已有表的排序规则
ALTER TABLE user_info
CONVERT TO CHARACTER SET utf8mb4
COLLATE utf8mb4_general_ci;
3. 字段层级设置
可以为单个字段单独设置排序规则，优先级最高，会覆盖表和数据库的设置。
-- 建表时为单个字段指定排序规则
CREATE TABLE product (
id INT PRIMARY KEY AUTO_INCREMENT,
product_name VARCHAR(100) COLLATE utf8mb4_bin, -- 大小写敏感
description TEXT
);
-- 修改已有字段的排序规则
ALTER TABLE product
MODIFY product_name VARCHAR(100)
CHARACTER SET utf8mb4
COLLATE utf8mb4_bin;
高频场景实例讲解
场景1：中文按拼音排序
默认排序规则可能无法正确对中文按拼音排序，需要指定支持中文拼音的排序规则，比如utf8mb4_zh_0900_as_cs。
-- 创建支持中文拼音排序的表
CREATE TABLE student (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20)
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_zh_0900_as_cs;
-- 插入测试数据
INSERT INTO student (name) VALUES ('张三'), ('李四'), ('王五'), ('赵六');
-- 按拼音正序排序
SELECT name FROM student ORDER BY name;
场景2：大小写敏感的字符串查询
如果业务需要区分字符串的大小写，需要将排序规则设置为_bin后缀或者_cs（case sensitive）后缀的规则。
-- 创建大小写敏感的字段
CREATE TABLE account (
id INT PRIMARY KEY AUTO_INCREMENT,
account_id VARCHAR(20) COLLATE utf8mb4_bin
);
-- 插入数据
INSERT INTO account (account_id) VALUES ('Admin'), ('admin');
-- 精确查询大小写匹配的账号
SELECT * FROM account WHERE account_id = 'Admin';
场景3：临时查询时指定排序规则
如果不需要修改表结构，也可以在查询时临时指定排序规则，满足单次查询的特殊需求。
-- 临时指定排序规则进行大小写敏感查询 SELECT * FROM account WHERE account_id COLLATE utf8mb4_bin = 'admin'; -- 临时指定排序规则进行中文排序 SELECT name FROM student ORDER BY name COLLATE utf8mb4_zh_0900_as_cs;
排序规则冲突的解决方法
当进行多表关联查询或者字符串比较时，如果两个字段的排序规则不一致，会出现排序规则冲突的错误。此时可以通过统一排序规则解决。
-- 假设table1.name是utf8mb4_general_ci，table2.name是utf8mb4_bin，关联时指定统一排序规则 SELECT * FROM table1 t1 JOIN table2 t2 ON t1.name COLLATE utf8mb4_general_ci = t2.name;
注意事项
- 排序规则的修改可能会影响已有的索引，修改前建议先备份数据，确认业务影响。
- 不同数据库版本支持的排序规则可能存在差异，比如MySQL 8.0之后新增了很多中文相关的排序规则，低版本可能不支持。
- 如果业务涉及多语言场景，建议选择通用性强的utf8mb4字符集搭配对应的排序规则，避免字符存储异常。