MySQL排序规则:COLLATE及中文排序【转】

1、简介

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

2、设置级别及其优先级

设置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 '',
……
) ……

排序规则命名约定

命名约定

image.png

查询排序相关

#查询所有的字符集
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

查看原文：原文链接