MySQL排序规则：COLLATE 及中文排序

官方API

COLLATE API

简介

COLLATE：排序规则。通常是和数据编码（CHARSET）相关的，字符串的物理存储由排序规则控制。
COLLATE是一个子句，可应用于数据库定义或列定义以定义排序规则，或应用于字符串表达式以应用排序规则投影.
可以用来比较一些复杂排序。如VARCHAR，CHAR，TEXT类型，不区分大小写等的列，都需要有一个COLLATE类型来告知MySQL如何对该列进行排序和比较。简而言之，COLLATE会影响到ORDER BY语句的顺序，会影响到WHERE条件中大于小于号筛选出来的结果，会影响DISTINCT、GROUP BY、HAVING语句的查询结果，会影响任意查询排序的结果。

使用方法

设置级别及其优先级

设置COLLATE可以在示例级别、库级别、表级别、列级别、以及SQL指定。

#库级别
CREATE DATABASE <db_name> DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
#表级别
CREATE TABLE(
...
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
#列级别
#如果列级别没有设置CHARSET和COLATE，则列级别会继承表级别的CHARSET与COLLATE。
CREATE TABLE (
`field1` VARCHAR（64） CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
……
) ……

排序规则命名约定

命名约定

后缀	含义
_as	口音敏感
_cs	区分大小写
_bin	指定使用向后兼容的二进制排序顺序

查询排序相关

#查询所有的字符集
SHOW CHARACTER SET;
#查询所有的排序集
SHOW COLLATION;
#查询'utf8mb4'编码时可用的排序集
SHOW COLLATION WHERE CHARSET = 'utf8mb4';

#举例:名称根据中文排序(不考虑多音字)
#升序
SELECT * FROM TABLE_NAME ORDER BY CONVERT(NAME USING GBK) COLLATE GBK_CHINESE_CI ASC
#降序
SELECT * FROM TABLE_NAME ORDER BY CONVERT(NAME USING GBK) COLLATE GBK_CHINESE_CI DESC