《MySQL 入门教程》第 28 篇 字符集与排序规则 PHP面试技巧 之 职场暗语知多少?! [Dubox]实测。还是原来的配方,还是一样的味道! 【剑指金九银十】程序员,如何做到不惑?(附四面拿到字节跳动offer全过程) 超厉害的阿里技术读物,1500页超全计算机系统底层解析宝典 【融中财经早报9.16】大数据杀熟行为10月1日起明令禁止 大数据时代,机器学习算法该如何升级? 怎么判断自己是否适合做程序员 一分钟告诉你什么是区块链 一个时代一个机会,DeFi的起点X-DeFi,如何玩转X-DeFi? RTX3080显卡已被用于挖矿!映众辟谣:工程师测试散热 2020-09-15 比特币相当于房地产,怎样利用比特币获得流动现金? 区块链安全事件与代码审计 fisco bcos solidity销毁合约,删除合约的方法 Linux 中 Golang 的安装和环境配置 Golang interface{} 转换为某个结构体 区块链知识系列 - Raft 共识 OpenHarmony开发者文档开源计划,快快加入吧 融合创新提升服务,大数据搭建信息网 长三角税收一体化按下“快进键” 河钢集团与海尔集团签署物联网生态战略合作协议 1一个拥有万物互联神话,让我们看物联网是如何蓬勃发展的? 重庆大数据产业研究院成立,首批专家服务团名单公布 百分点大数据技术团队:互联网舆情系统的架构实践 水泥大数据研究院郑建辉:四季度水泥价格仍有望冲击去年高位 中国物联网与绿色智慧城市发展论坛召开 欧普照明点亮智慧道路之光 Android & Kotlin:Retrofit + Hilt 实现 看妹子app Android - 控件抖动效果 DevEco studio 一直加载gradle android如何获取调试版及发布版安全码SHA1 Android手机获取IP地址的两种方法 Android Activity 启动过程详解(上) 思维破局:挣钱其实没那么难,关键是方式要对 大数据赋能全产业链 为市民提供“一杯好奶” 运用大数据实现节能减排精准化 2020最新拼多多Java面试版,五面拿到28K的Offer 中国电信(安徽)大数据产业园落户南岗科技园 打造华东区域云计算领头羊 深度解读!阿里腾讯滴滴字节首选,新一代大数据引擎Flink厉害在哪?附学习礼包 网络编程5:socket服务端和客户端代码实现 微信小程序 蓝牙重连异常 errCode:10004,errMsg:notifyBLECharacteristicValueChange:fail setNot 蓝桥杯--第八届省赛试题-电子钟程序设计 【TCP回声服务器】一篇文章帮助你看懂TCP! 关于国标GB28181流媒体协议视频平台EasyGBS设备无法接入平台的问题排查 机器学习16 -- Lifelong Learning 终生学习 Vuex中发送mock请求返回数据 JS 正则表达式详解 学习笔记 花了30分钟,给女朋友们写了个最近抖音很火的3D立体动态相册 这 16 个 CSS 伪类,助你提升布局效率! Mysql系列第八讲 详解排序和分页(order by & limit)及存在的坑 SQL语句的一些应用语句
您的位置:首页 >程序人生 >

《MySQL 入门教程》第 28 篇 字符集与排序规则

文章目录

28.1 字符集28.1.1 服务器字符集28.1.2 客户端字符集28.1.3 国家字符集 28.2 排序规则28.2.1 设置排序规则28.2.2 中文排序规则

本篇我们介绍一下 MySQL 中的字符集(Character Set)和排序规则(Collation),它们决定了系统支持的字符数据以及它们的存储方式、不同字符比较和排序的结果。

28.1 字符集

我们可以为 MySQL 服务器、数据库、表、字符类型的字段以及字符串常量指定一个字符集(Character Set)和排序规则(Collation)。其中,字符集决定了能够存储哪些字符,比如 ASCII 字符集只能存储简单的英文、数字和一些控制字符;GB2312 字符集可以存储中文;Unicode 字符集能够支持世界上的各种语言。排序规则定义了字符集中字符的排序和比较顺序,包括是否区分大小写,是否区分重音等。

MySQL 为不同的环境提供了多种字符集支持,可以使用SHOW CHARACTER SET命令查看系统支持的所有字符集。例如:

SHOW CHARACTER SET;Charset |Description|Default collation|Maxlen|--------|-------------------------------|-------------------|------|armscii8|ARMSCII-8 Armenian |armscii8_general_ci| 1|ascii |US ASCII |ascii_general_ci | 1|big5|Big5 Traditional Chinese |big5_chinese_ci| 2|...utf32 |UTF-32 Unicode |utf32_general_ci | 4|utf8|UTF-8 Unicode|utf8_general_ci| 3|utf8mb4 |UTF-8 Unicode|utf8mb4_0900_ai_ci | 4|

MySQL 8.0 开始,默认字符集改为 utf8mb4,而不是之前的 latin1;Maxlen 表示该字符集中单个字符最多可能占用的字节数,utf8mb4 中的一个字符最多占用 4 个字节,用于存储表情符号(emoji);Default collation 表示该字符集默认的排序规则,具体参考下文。

28.1.1 服务器字符集

MySQL 服务器端支持多个不同级别的字符集设置,包括服务器级别、数据库级别、表级别、字段级别以及字符串常量级别。使用 SHOW 命令查看当前的设置:

SHOW variables LIKE '%character%';Variable_name |Value |------------------------|------------------------------|character_set_client|utf8mb4 |character_set_connection|utf8mb4 |character_set_database|utf8mb4 |character_set_filesystem|binary|character_set_results ||character_set_server|utf8mb4 |character_set_system|utf8|character_sets_dir|/usr/share/mysql-8.0/charsets/|

其中,character_set_server 表示 MySQL 服务器的全局默认字符集,可以在服务器配置文件中 [mysqld] 部分的 character-set-server 选项或者启动服务时通过命令行参数 --character-set-server 进行设置,也可以在运行时动态修改。

character_set_database 表示当前默认数据库的字符集。当我们使用 CREATE DATABASE 或者 ALTER DATABASE 时,可以指定一个字符集和排序规则:

CREATE DATABASE db_name[[DEFAULT] CHARACTER SET charset_name][[DEFAULT] COLLATE collation_name]ALTER DATABASE db_name[[DEFAULT] CHARACTER SET charset_name][[DEFAULT] COLLATE collation_name]

如果没有指定字符集 CHARACTER SET 或者排序规则 COLLATE,MySQL 默认使用 character_set_server 服务器字符集(utf8mb4)和排序规则(utf8mb4_0900_ai_ci)创建或者修改数据库。

对于某个具体数据库,可以使用 SHOW CREATE DATABASE 语句或者通过 information_schema.schemata 视图查看默认的字符集和排序规则。例如:

SHOW CREATE DATABASE hrdb;Database|Create Database |--------|------------------------------------------------------------------------------------------------------------------------------|hrdb|CREATE DATABASE `hrdb` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */|SELECT default_character_set_name, default_collation_nameFROM information_schema.schemataWHERE SCHEMA_NAME = 'hrdb';DEFAULT_CHARACTER_SET_NAME|DEFAULT_COLLATION_NAME|--------------------------|----------------------|utf8mb4 |utf8mb4_0900_ai_ci|

同样,对于 CREATE TABLE 和 ALTER TABLE 语句,也可以指定一个字符集和排序规则:

CREATE TABLE table_name (column_list)[[DEFAULT] CHARACTER SET charset_name][COLLATE collation_name]]ALTER TABLE table_name[[DEFAULT] CHARACTER SET charset_name][COLLATE collation_name]

如果没有指定字符集 CHARACTER SET 或者排序规则 COLLATE,MySQL 默认使用 character_set_database 数据库字符集和排序规则创建或者修改表。

对于某个具体的表,可以使用 SHOW CREATE TABLE 语句或者通过 information_schema.table 视图查看默认的字符集和排序规则。例如:

SHOW CREATE TABLE employee;Table |Create Table |--------|---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|employee|CREATE TABLE `employee` (`emp_id` int NOT NULL,`emp_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,`sex` varchar(10) CHARACTER SET utf8 NOT NULL,`dept_id` int NOT NULL,`manager` int DEFAULT NULL,`hire_date` date NOT NULL,`job_id` int NOT NULL,`salary` decimal(8,2) NOT NULL,`bonus` decimal(8,2) DEFAULT NULL,`email` varchar(100) CHARACTER SET utf8 NOT NULL,PRIMARY KEY (`emp_id`),UNIQUE KEY `uk_emp_email` (`email`),KEY `idx_emp_name` (`emp_name`),KEY `idx_emp_job` (`job_id`),KEY `idx_emp_manager` (`manager`),KEY `idx_emp_dept` (`dept_id`),CONSTRAINT `fk_emp_dept` FOREIGN KEY (`dept_id`) REFERENCES `department` (`dept_id`),CONSTRAINT `fk_emp_job` FOREIGN KEY (`job_id`) REFERENCES `job` (`job_id`),CONSTRAINT `fk_emp_manager` FOREIGN KEY (`manager`) REFERENCES `employee` (`emp_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci|SELECT table_collationFROM information_schema.tablesWHERE table_name = 'employee';TABLE_COLLATION |------------------|utf8mb4_0900_ai_ci|

每个 CHAR、VARCHAR、TEXT、ENUM、SET 类型的字段也可以拥有自己的字符集和排序规则:

col_name {CHAR | VARCHAR | TEXT} (col_length)[CHARACTER SET charset_name][COLLATE collation_name]col_name {ENUM | SET} (val_list)[CHARACTER SET charset_name][COLLATE collation_name]

如果没有指定字符集 CHARACTER SET 或者排序规则 COLLATE,MySQL 默认使用表的字符集和排序规则创建或者修改字段。使用 SHOW CREATE TABLE 语句可以查看表中字段的字符集和排序规则。

📝MySQL 字符集和排序规则的优先级别为字段级别大于表级别,表级别大于数据库级别,数据库级别大于服务器级别。

另外,MySQL 中的字符串常量也有字符集和排序规则。例如,以下字符串常量使用相同的字符集:

SELECT '数据库';SELECT _utf8mb4'数据库';SELECT _utf8mb4'数据库' COLLATE utf8mb4_0900_ai_ci;

如果没有指定字符集或者排序规则 COLLATE,MySQL 使用默认的连接字符集 character_set_connection。

字符集和排序规则会对一些 MySQL 字符函数产生影响,例如:

SELECT length('abc'), char_length('abc'), length('数据库'), char_length('数据库');length('abc')|char_length('abc')|length('数据库')|char_length('数据库')|-------------|------------------|---------------|--------------------|3| 3|9| 3|

LENGTH 函数返回以字节为单位的字符串长度,多字节字符集中字符占用的字节数可能不同;CHAR_LENGTH 函数返回以字符为单位的字符串长度。

CONVERT(expr USING transcoding_name) 函数可以实现不同字符集之间的数据转换。例如:

SELECT CONVERT('MySQL' USING ascii);

📝专栏的第 15 篇介绍了MySQL 字符处理函数和运算符。

28.1.2 客户端字符集

MySQL 客户端应用和服务器连接之后,通过连接输入 SQL 语句,发送命令,接收服务器端的结果或者错误信息。在这个过程中,还会涉及到以下字符集设置:

客户端语句使用的字符集,MySQL 使用 character_set_client 的设置作为客户端字符集;服务器接收到语句之后需要将其转换为哪种字符集,MySQL 使用 character_set_connection 的值作为转换的目标字符集。同时它也是字符串常量默认的字符集;服务器在返回查询结果(包括字段名、字段值、错误信息等)之前需要将其转换为哪种字符集,MySQL 使用 character_set_results 作为这种转换的目标字符集。

客户端连接的字符集可以通过以下几种方法进行设置:

连接数据库之后,使用 SET NAMES ‘charset_name’ [COLLATE ‘collation_name’] 命令进行设置。例如:

SET NAMES 'utf8';

该语句相当于同时设置了 character_set_client、character_set_results 以及 character_set_connection 的值。

使用 SET CHARACTER SET ‘charset_name’ 命令进行设置。例如:

SET SET CHARACTER SET 'utf8';

该语句相当于同时设置了 character_set_client、character_set_results 的值,并且将 character_set_connection 的值设置为 character_set_database。

如果客户端应用支持 --default-character-set 选项,例如 mysql、mysqladmin 等,可以在配置文件中 [mysql] 部分的 default-character-set 配置项进行设置。

某些 MySQL 驱动程序允许在连接字符串中指定一个字符集编码,例如 JDBC 中的 characterEncoding。

为了避免不同字符集转换可能带来的问题,最好将客户端连接字符集和服务器的字符集设置为一致。

📝系统变量 character_set_system 是服务器存储元数据(Metadata)使用的字符集,总是设置为 utf8。character_set_filesystem 用于设置文件系统的字符集,主要影响打开文件时对文件名的解析。character_sets_dir 表示字符集的存储目录。

28.1.3 国家字符集

SQL 标准定义了两种使用特定字符集的字符串类型:NCHAR(NATIONAL CHAR)和 NVARCHAR(NATIONAL VARCHAR)。MySQL 选择 utf8(不是 utf8mb4)作为这种类型的预定义字符集,以下三种形式的定义等价:

CHAR(10) CHARACTER SET utf8NATIONAL CHARACTER(10)NCHAR(10)

同样,以下语句等价:

VARCHAR(10) CHARACTER SET utf8NATIONAL VARCHAR(10)NVARCHAR(10)NCHAR VARCHAR(10)NATIONAL CHARACTER VARYING(10)NATIONAL CHAR VARYING(10)

对于国家字符集中的字符串常量,可以使用 N’literal’ 的形式进行表示。例如:

SELECT N'数据库';SELECT n'数据库';SELECT _utf8'数据库';

28.2 排序规则

每个 MySQL 字符集可以支持一个或者多个排序规则,用于定义每个字符的比较规则,包括是否区分大小写,是否区分重音等。MySQL 使用 SHOW COLLATION 命令查看各种字符集支持的排序规则:

Collation |Charset |Id |Default|Compiled|Sortlen|Pad_attribute|--------------------------|--------|---|-------|--------|-------|-------------|armscii8_bin|armscii8| 64| |Yes |1|PAD SPACE|armscii8_general_ci |armscii8| 32|Yes|Yes |1|PAD SPACE|ascii_bin |ascii | 65| |Yes |1|PAD SPACE|...utf8mb4_hu_0900_ai_ci |utf8mb4 |274| |Yes |0|NO PAD |utf8mb4_hu_0900_as_cs |utf8mb4 |297| |Yes |0|NO PAD |utf8mb4_icelandic_ci|utf8mb4 |225| |Yes |8|PAD SPACE|

MySQL 8.0 默认使用 utf8mb4 字符集,默认的排序规则为 utf8mb4_0900_ai_ci,表示不区分重音和大小写。例如:

SELECT 'a' = 'A';'a' = 'A'|---------|1|

如果换成 utf8mb4_zh_0900_as_cs 排序规则,a 和 A 比较的结果如下:

SELECT 'a' COLLATE utf8mb4_zh_0900_as_cs = 'A';'a' COLLATE utf8mb4_zh_0900_as_cs = 'A'|---------------------------------------|0|

28.2.1 设置排序规则

排序规则和字符集一样支持不同级别的设置;如果没有指定排序规则,MySQL 会基于字符集设置一个默认的排序规则。使用 SHOW 语句查看当前的设置:

SHOW variables LIKE '%collation%';Variable_name|Value |-----------------------------|------------------|collation_connection |utf8mb4_0900_ai_ci|collation_database |utf8mb4_0900_ai_ci|collation_server |utf8mb4_0900_ai_ci|default_collation_for_utf8mb4|utf8mb4_0900_ai_ci|

其中,collation_server 与 character_set_server 对应,表示 MySQL 服务器的全局默认排序规则,可以在服务器配置文件中 [mysqld] 部分的 collation-server 选项或者启动服务时通过命令行参数 --collation-server 进行设置,也可以在运行时动态修改。

collation_database 和 character_set_database 对应,表示当前默认数据库的排序规则。当我们使用 CREATE DATABASE 或者 ALTER DATABASE 时,可以指定一个排序规则,参考上文。

同样,对于 CREATE TABLE 和 ALTER TABLE 语句,也可以为表或者字符类型的字段指定一个排序规则,参考上文。

collation_connection 和 character_set_connection 对应,表示客户端连接使用的排序规则。

排序规则由字符集的名称、可选的本地语言代码和 Unicode 版本以及其他属性组成,例如 utf8mb4_zh_0900_as_cs 表示 9.0.0 版本 utf8mb4 字符集的中文排序规则,区分重音(Accent-sensitive)和大小写(Case-sensitive)。

📝由于排序规则中包含了字符集信息,设置排序规则通常意味着同时设置了字符集。例如 CREATE TABLE … COLLATE utf8mb4_0900_ai_ci 表示使用 utf8mb4 字符集和 utf8mb4_0900_ai_ci 排序规则创建表。

28.2.2 中文排序规则

对于中文而言,排序方式与英文有所不同;中文通常需要按照拼音、偏旁部首或者笔画进行排序。

MySQL 8.0 默认使用的排序规则 utf8mb4_0900_ai_ci 对于中文按照偏旁部首进行排序。以下语句按照员工的姓名进行排序:

SELECT emp_nameFROM employeeORDER BY emp_name;emp_name|----------|关兴|关平|关羽|刘备|周仓|孙丫鬟|孙乾|孙尚香|庞统|廖化|...

对于 utf8mb4 字符集,utf8mb4_zh_0900_as_cs 排序规则按照中文拼音进行排序。例如:

SELECT emp_nameFROM employeeORDER BY emp_name collate 'utf8mb4_zh_0900_as_cs';emp_name |----------|邓芝|法正|关平|关兴|关羽|黄权|黄忠|简雍|蒋琬|廖化|...

也可以将数据转换为其他支持特定排序规则的字符集,例如 gbk 字符集默认的 gbk_chinese_ci 排序规则就是按照拼音进行排序:

SELECT emp_nameFROM employeeORDER BY convert(emp_name using gbk);

该语句和上一个示例返回的结果相同。

不剪发的Tony老师CSDN认证博客专家数据库架构师毕业于北京航空航天大学,十多年数据库管理与开发经验,拥有OCP以及RHCE证书。目前在一家全球性的游戏公司从事数据库架构设计和开发,CSDN学院签约讲师以及GitChat专栏作者。

郑重声明:本文版权归原作者所有,转载文章仅为传播更多信息之目的,如作者信息标记有误,请第一时间联系我们修改或删除,多谢。