MySQL字符集与校对集规则说明

2024-02-02 04:59| 来源: 网络整理| 查看: 265

一：字符集　　字符集（Character set）是多个字符(英文字符，汉字字符，或者其他国家语言字符)的集合，字符集种类较多，每个字符集包含的字符个数不同。特点： ①：字符编码方式是用一个或多个字节表示字符集中的一个字符 ②：每种字符集都有自己特有的编码方式，因此同一个字符，在不同字符集的编码方式下，会产生不同的二进制常见字符集： ASCII字符集：基于罗马字母表的一套字符集，它采用1个字节的低7位表示字符，高位始终为0 LATIN1字符集：拉丁字符集相对于ASCII字符集做了扩展，使用一个字节表示字符，但启用了高位，扩展了字符集的表示范围 GBK字符集：支持中文，字符有一字节编码和两字节编码方式 UTF8字符集：Unicode字符集的一种，是计算机科学领域里的一项业界标准，支持了所有国家的文字字符，utf8采用1-4个字节表示字符

注：计算机中只要涉及到文字的地方，就会存在字符集和编码

二：MySQL中字符集 1：MySQL字符集说明　　说明：在MySQL 8.0版本之前，默认字符集为latin1，utf8字符集指向的是utf8mb3。网站开发人员在数据库设计的时候往往会将编码修改为utf8字符集。如果遗忘修改默认的编码，就会出现乱码的问题。从MySQL8.0开始，数据库的默认编码将改为utf8mb4，从而避免上述乱码的问题。总结就是在MySQL8.0前字符集默认为latin1，而MySQL8.0后默认字符集为utf8mb3。 utf8、utf8mb3、utf8mb4三者关系：官方中utf8字符集表示一个字符需要使用1～4个字节，但是我们常用的一些字符使用1～3个字节就可以表示了。而字符集表示一个字符所用的最大字节长度，在某些方面会影响系统的存储和性能，所以在设计MySQL的设计者偷偷的定义了两个概念：　　　　utf8：在MySQL8.0还是指代的utf8mb3，未来的会变为uft8mb4 　　　　utf8mb3：阉割过的utf8字符集，只使用1～3个字节表示字符。　　　　utf8mb4：正宗的utf8字符集，使用1～4个字节表示字符（可以存放emoji小表情）。　　　　-- 注：在MySQL中，实际只有utf8mb4和utf8mb3 【国际上的UTF-8，在MySQL中对标的是uft8mb4】查询MySQL字符集语句：SHOW VARIABLES LIKE 'character%'；查询MySQL支持的字符集：SHOW CHARACTER SET; +--------------+-------------------------+----------------------------+------------------+ |Charset(字符集)|Description(字符集说明) |Default collation(默认校对集) |Maxlen(占用最大字节)| +--------------+-------------------------+----------------------------+------------------+ |ascii |US ASCII | ascii_general_ci | 1 | |binary |Binary pseudo charset | binary | 1 | |gb2312 |GB2312 Simplified Chinese| gb2312_chinese_ci | 2 | |gbk |GBK Simplified Chinese | gbk_chinese_ci | 2 | |latin1 |cp1252 West European | latin1_swedish_ci | 1 | |utf8mb3 |UTF-8 Unicode | utf8mb3_general_ci | 3 | |utf8mb4 |UTF-8 Unicode | utf8mb4_0900_ai_ci | 4 | ....... 省略；一共41个字符集 +--------------+-------------------------+----------------------------+------------------+ 2：MySQL5.7字符集信息 -- 查询数据库版本 mysql> SELECT VERSION(); +-----------+ | VERSION() | +-----------+ | 5.7.33 | +-----------+ -- 查询当前数据库字符集 mysql> SHOW VARIABLES LIKE 'character%'; +--------------------------+----------------------------+ | Variable_name | Value | +--------------------------+----------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | latin1 | -- 数据库使用latin1字符集 | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | latin1 | -- MySQL服务使用latin1字符集 | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | +--------------------------+----------------------------+ -- 创建表和表 CREATE DATABASE demo_char; CREATE TABLE demo_char.demo_student(sid INT,sname VARCHAR(10)); -- 查询数据库和表结构 mysql> SHOW CREATE DATABASE demo_char; +-----------+----------------------------------------------------------------------+ | Database | Create Database | +-----------+----------------------------------------------------------------------+ | demo_char | CREATE DATABASE `demo_char` /*!40100 DEFAULT CHARACTER SET latin1 */ | +-----------+----------------------------------------------------------------------+ mysql> SHOW CREATE TABLE demo_char.demo_student\G *************************** 1. row *************************** Table: demo_student Create Table: CREATE TABLE `demo_student` ( `sid` int(11) DEFAULT NULL, `sname` varchar(10) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) -- 说明：我们在创建数据库或者数据库表时不指定字符集时；创建数据库则会取character_set_database配置； -- 创建数据表则会取上一级的数据库字符集 -- 插入中文数据则报错（因为latin1字符集无法存储中文） INSERT INTO demo_char.demo_student VALUES(1,'张三'); ERROR 1366 (HY000): Incorrect string value: '\xE5\xBC\xA0\xE4\xB8\x89' for column 'sname' at row 1 -- 解决方式只有修改字符集 3：MySQL8.0字符集信息 -- 查询数据库版本 mysql> SELECT VERSION(); +-----------+ | VERSION() | +-----------+ | 8.0.31 | +-----------+ -- 查询当前数据库字符集 mysql> 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 | utf8mb4 | | character_set_server | utf8mb4 | | character_set_system | utf8mb3 | | character_sets_dir | /usr/share/mysql-8.0/charsets/ | +--------------------------+--------------------------------+ -- 创建表和表 CREATE DATABASE demo_char; CREATE TABLE demo_char.demo_student(sid INT,sname VARCHAR(10)); -- 查询数据库和表结构 mysql> SHOW CREATE DATABASE demo_char\G *************************** 1. row *************************** Database: demo_char Create Database: CREATE DATABASE `demo_char` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ mysql> SHOW CREATE TABLE demo_char.demo_student\G *************************** 1. row *************************** Table: demo_student Create Table: CREATE TABLE `demo_student` ( `sid` int DEFAULT NULL, `sname` varchar(10) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci -- 插入中文数据测试（utf8mb4为标准的utf8则可以插入） INSERT INTO demo_char.demo_student VALUES(1,'张三'); mysql> SELECT * FROM demo_char.demo_student; +------+--------+ | sid | sname | +------+--------+ | 1 | 张三 | +------+--------+ 4：修改MySQL字符集

　　在Linux里修改字符集只需要在配置文件加入配置即可

-- 退出当前MySQL登录 exit -- 修改MySQL配置文件 vim /etc/my.cnf 在MySQL5.7或之前的版本中，在文件最后加上中文字符集配置：（[mysqld]后面加） character_set_server=utf8 -- 重启MySQL服务器 systemctl restart mysqld.service 注：我们之前创建的数据库和数据表的字符集不会发生改变；参数修改只对新建的数据库生效。 5：对已存在的库和表修改字符集

　　我们在MySQL5.7版本中，以前创建的库没指定字符集，默认会取 character_set_database 配置的字符集；所以默认latin1字符集。

-- 修改已创建数据库的字符集 ALTER DATABASE demo_char CHARACTER SET 'utf8'; SHOW CREATE DATABASE demo_char; +-----------+--------------------------------------------------------------------+ | Database | Create Database | +-----------+--------------------------------------------------------------------+ | demo_char | CREATE DATABASE `demo_char` /*!40100 DEFAULT CHARACTER SET utf8 */ | +-----------+--------------------------------------------------------------------+ -- 修改已创建数据表的字符集 ALTER TABLE demo_char.demo_student CONVERT TO CHARACTER SET 'utf8'; SHOW CREATE TABLE demo_char.demo_student\G *************************** 1. row *************************** Table: demo_student Create Table: CREATE TABLE `demo_student` ( `sid` int(11) DEFAULT NULL, `sname` varchar(10) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) -- 这时候就可以插入中文数据了 -- 总结说明：修改字符集的话，若是从latin1字符集转换为utf8是可以兼容了，因为latin1数据可以存放到utf8字符集里 -- 但是原表为utf8字符集，并且存在数据，这时把字符集修改为latin1就会存在无法修改成功；因为无法把数据转换 6：MySQL8个系统变量值及作用

　　查询MySQL系统字符集变量值：SHOW VARIABLES LIKE 'character%';

Ⅰ：MySQL8.0下查询字符集 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 | utf8mb4 | | character_set_server | utf8mb4 | | character_set_system | utf8mb3 | | character_sets_dir | /usr/share/mysql-8.0/charsets/ | +--------------------------+--------------------------------+ Ⅱ：介绍8大变量 ①：character_set_client 服务器端默认的，认为客户端传来数据的字符集格式，[就是设置客户端使用的是什么字符集，方便传来的数据服务器可以方便解析] ②：character_set_connection 主要用来设置连接数据库时的字符集，如果程序中没有指明连接数据库使用的字符集类型则按照这个字符集设置 ③：character_set_database 当前所在数据库的字符集，注意的是如果是刚进数据库系统，还没进入指定数据库（use ***），则此时的字符集和character_set_server的字符集是一致的，如进入字符集指定为gbk的数据库中，这时候查询就显示gbk ④：character_set_filesystem 文件字符集都是二进制binary ⑤：character_set_results 服务器默认发给客户端/外部的数据字符集格式，如果不指明默认是使用服务器默认编码 ⑥：character_set_server 服务器安装时指定的默认编码格式 ⑦：character_set_system 数据库系统使用的编码格式，这个值一直是utf8，不需要设置，他是为存储系统元数据的编码格式 ⑧：character_sets_dir 字符集安装的目录 Ⅲ：上面8种参数如何起作用（1）：服务器字符集由来 character_set_server：服务器级别的字符集我们可以在启动服务器程序时通过启动选项或者在服务器程序运行过程中使用SET语句修改这两个变量的值临时修改字符集（端口连接服务字符集就恢复默认，只争夺某个连接）： SET character_set_xxx='字符集'; 永久修改字符集（使用vim修改，修改后重启MySQL）： [server] character_set_server=gbk # 默认字符集 collation_server=gbk_chinese_ci #对应的默认的比较规则当服务器启动的时候读取这个配置文件后这两个系统变量的值便修改了。（2）：数据库、数据表、数据列字符集由来 character_set_database：当前数据库的字符集 ①建库时，若未明确指定字符集，则采用character_set_server指定的字符集。 ②建表时，若未明确指定字符集，则采用当前库所采用的字符集。 ③新增时，修改表字段时，若未明确指定字符集，则采用当前表所采用的字符集。数据库字符集：我们在创建和修改数据库的时候可以指定该数据库的字符集和比较规则 CREATE DATABASE 数据库名 [[DEFAULT] CHARACTER SET 字符集名称] [[DEFAULT] COLLATE 比较规则名称]; ALTER DATABASE 数据库名 [[DEFAULT] CHARACTER SET 字符集名称] [[DEFAULT] COLLATE 比较规则名称]; 数据表字符集：我们也可以在创建和修改表的时候指定表的字符集和比较规则 CREATE TABLE 表名 (列的信息) [[DEFAULT] CHARACTER SET 字符集名称] [COLLATE 比较规则名称]]; ALTER TABLE 表名 [[DEFAULT] CHARACTER SET 字符集名称] [COLLATE 比较规则名称]; 注：如果创建和修改表的语句中没有指明字符集和比较规则，将使用该表所在数据库的字符集和比较规则作为该表的字符集和比较规则。数据列字符集：对于存储字符串的列，同一个表中的不同的列也可以有不同的字符集和比较规则。我们在创建和修改列定义的时候可以指定该列的字符集和比较规则，语法如下： CREATE TABLE 表名( 列名字符串类型 [CHARACTER SET 字符集名称] [COLLATE 比较规则名称], 其他列... ); ALTER TABLE 表名 MODIFY 列名字符串类型 [CHARACTER SET 字符集名称] [COLLATE 比较规则名称]; 注：对于某个列来说，如果在创建和修改的语句中没有指明字符集和比较规则，将使用该列所在表的字符集和比较规则作为该列的字符集和比较规则。注：在转换列的字符集时需要注意，如果转换前列中存储的数据不能用转换后的字符集进行表示会发生错误。比方说原先列使用的字符集是utf8，列中存储了一些汉字，现在把列的字符集转换为ascii的话就会出错，因为ascii字符集并不能表示汉字字符（3）：更新、查询涉及到的字符集变量更新流程字符集转换过程：character_set_client ==> character_set_connection ==> 表字符集。查询流程字符集转换过程：表字符集 ==> character_set_result Ⅳ：字符集变量其它说明在启动mysql后，我们只关注下列变量是否符合我们的要求 character_set_client 、 character_set_connection 、 character_set_database 、 character_set_results 、 character_set_server 下列三个系统变量我们不需要关心，不会影响乱码等问题 character_set_filesystem 、 character_set_system 、 character_sets_dir 更改字符集的方法 ①：修改指定一个字符集 SET character_set_xxx='字符集'; ②：修改3处字符集 SET NAME '字符集'; 临时修改这三处字符集：character_set_client 、 character_set_connection 、 character_set_results 三：MySQL请求到响应过程中字符集变化

　　这里我就以MySQL 8.0来做基本的说明，在正常的操作下是没有问题的，都是以utf8方式进行一个交互，所以我要进行一个基本的修改。

2：问题说明

　　我们在之前插入一条带中文的数据 “蚂蚁”；但是后期查询时却出现乱码 “铓傝殎”；这是以为我们转换时出现了问题

"蚂蚁" 在不同的字符集下编译情况 UTF8字符集下：\xE8 \x9A \x82 \xE8 \x9A \x81 GBK字符集下： \xC2 \xEC \xD2 \xCF "铓傝殎" 转换为GBK为： "\xE8\x9A" = "铓" "\x82\xE8" = "傝" "\x9A\x81" = "殎" 现在看一下在请求从发送到结果返回过程中字符集的变化：客户端发送请求所使用的字符集一般情况下客户端所使用的字符集和当前操作系统一致，不同操作系统使用的字符集可能不一样，如下： Unix/Linux 系统使用的是 utf8 Windows 使用的是 gbk ①：当我们发送 insert into demo_student values(4,'蚂蚁'); SQL语句时；我们当前的客户端（CMD或者Navicat等等）会把 ”蚂蚁“ 转换为UTF8的URL编码（因为在UTF8的客户端下）；这时候就转换为：”\xE8 \x9A \x82 \xE8 \x9A \x81“ 把这个转换后的URL编码发送到MySQL服务，让其MySQL进行保存操作。 ②：MySQL接收到我们之前插入的”\xE8 \x9A \x82 \xE8 \x9A \x81“URL编码；这时候MySQL要对其解析；但是MySQL并不知道客户端传来的数据是什么格式的字符集；所以MySQL就取系统 character_set_client 变量，此时为 gbk；这时候就有意思了，因为MySQL认为”\xE8 \x9A \x82 \xE8 \x9A \x81“为GBK格式的字符集；所以GBK是2个字节为一个字符；所以对其一拆分，转换过来就为："\xE8\x9A" = "铓"、"\x82\xE8" = "傝"、"\x9A\x81" = "殎" ③：这时候MySQL已经解析完从客户端来的数据了；解析完的数据”铓傝殎“；这时候MySQL就去请求连接具体的MySQL服务；这一步时需要把从客户端获取解析的数据”铓傝殎“解析成 character_set_connection = utf8mb4；这时候就被解析为：“\xE9\x93\x93\xE5\x82\x9D\xE6\xAE\x8E”；把这个交给具体的MySQL服务执行； ④：其实MySQL服务也不认识 character_set_connection 传来的是什么字符集；所以会通过 character_set_server 解析使用 ⑤：其实我们执行查询语句时也会走完前面的1~4步；因为也得解析SQL和里面的字符集转换；当MySQL服务查询到数据后再交由 character_set_connection；character_set_connection再把数据转换为 character_set_results 发送给客户端可以使用JAVA查看： String encode = URLEncoder.encode("蚂蚁", "utf8"); // URL编码 String decode = URLDecoder.decode(encode, "gbk"); // URL解码

发现是我们的Client字符集不对；解决方案设置字符集：set character_set_client = 'utf8'; 四：字符集之校对集规则collation

　　字符集是一套符号对应的编号；而校对集是在字符集内用于字符比较和排序的一套规则，比如有的规则区分大小写，有的则无视。

Ⅰ：校对集查询方式　　我们知道查询所有字符集SQL是：SHOW CHARACTER SET; 　　查询数据库支持的所有校对集规则：SHOW COLLATION; 　　+-----------------------------+----------+-----+---------+----------+---------+---------------+ 　　| Collation | Charset | Id | Default | Compiled | Sortlen | Pad_attribute | 　　+-----------------------------+----------+-----+---------+----------+---------+---------------+ 　　| gbk_bin | gbk | 87 | | Yes | 1 | PAD SPACE | 　　| gbk_chinese_ci | gbk | 28 | Yes | Yes | 1 | PAD SPACE | 　　| utf8mb3_bin | utf8mb3 | 83 | | Yes | 1 | PAD SPACE | 　　| utf8mb3_general_ci | utf8mb3 | 33 | Yes | Yes | 1 | PAD SPACE | 　　| utf8mb3_spanish_ci | utf8mb3 | 199 | | Yes | 8 | PAD SPACE | 　　| utf8mb4_vi_0900_as_cs | utf8mb4 | 300 | | Yes | 0 | NO PAD | 　　| utf8mb4_zh_0900_as_cs | utf8mb4 | 308 | | Yes | 0 | NO PAD | 　　.......省略；在MySQL8.0共286个校对集；在MySQL5.7共222个校对集　　 +-----------------------------+----------+-----+---------+----------+---------+---------------+ 　　查询当前字符集和校对集规则设置： 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 | 　　+----------------------+--------------------+ 说明：MySQL版本一共支持41种字符集（使用SHOW CHARACTER SET查看），其中的Default collation列表示这种字符集中一种默认的比较规则，里面包含着该比较规则主要作用于哪种语言，utf8mb3_spanish_ci是以西班牙语的规则比较， utf8_general_ci 是一种通用的比较规则。后缀表示该比较规则是否区分语言中的重音、大小写。具体如下： _ai（accent insensitive）：不区分重音 _as（accent sensitive）：区分重音 _ci（case insensitive）：不区分大小写 _cs（case sensitive）：区分大小写 _bin（binary）：以二进制方式比较 Ⅱ：校对集规则特征 ①：两个不同的字符集不能有相同的校对规则；如：gbk有gbk_chinese_ci校对集，而utf8mb3有utf8mb3_general_ci；不能说gbk有utf8mb3_general_ci校对集 ②：每个字符集有一个默认校对规则；如：可以通过SHOW CHARACTER SET;查看字符集中Default collation列 ③：存在校对规则命名约定：以其相关的字符集名开始，中间包括一个语言名，并且以_ci、_cs、_bin、_ai、_as或者其它们后缀的组合。 Ⅲ：基本实操 -- 使用utf8mb3_bin创建库和表，并查看创建结果 CREATE DATABASE demo_db1 CHARACTER SET utf8mb3 COLLATE utf8mb3_bin; USE demo_db1; CREATE TABLE demo_t1(sid int,sname varchar(10))CHARACTER SET utf8mb3 COLLATE utf8mb3_bin; SHOW CREATE TABLE demo_t1\G *************************** 1. row *************************** Table: demo_t1 Create Table: CREATE TABLE `demo_t1` ( `sid` int DEFAULT NULL, `sname` varchar(10) COLLATE utf8mb3_bin DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_bin -- 说明：utf8mb3_bin校对集代表二进制比较 -- 插入数据并查看 INSERT INTO demo_t1 VALUES(1,'A'),(2,'a'),(3,'B'),(4,'b'); SELECT * FROM demo_t1 ORDER BY sname asc; +------+-------+ | sid | sname | +------+-------+ | 1 | A | | 3 | B | | 2 | a | | 4 | b | +------+-------+ -- 可以看出使用utf8mb3_bin校对集就可以把大小写也排序了（因为按照二进制） -- 使用默认的utf8mb3_general_ci测试 CREATE DATABASE demo_db2 CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci; USE demo_db2; CREATE TABLE demo_t2(sid int,sname varchar(10))CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci; SHOW CREATE TABLE demo_t2\G *************************** 1. row *************************** Table: demo_t2 Create Table: CREATE TABLE `demo_t2` ( `sid` int DEFAULT NULL, `sname` varchar(10) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 -- 因为是默认的校对集；就没显示了 -- 插入数据并查看 INSERT INTO demo_t2 VALUES(1,'A'),(2,'a'),(3,'B'),(4,'b'); SELECT * FROM demo_t2 ORDER BY sname asc; +------+-------+ | sid | sname | +------+-------+ | 1 | A | | 2 | a | | 3 | B | | 4 | b | +------+-------+一定注意：表一旦创建后，里面添加数据以后，再修改校对集就无效！无效操作；虽然校对集变了，但是数据查询还和以前一样的顺序即使再后面追加数据；查询依然和之前的校对集排序一样

常用操作：

-- 查看GBK字符集的比较规则 SHOW COLLATION LIKE 'gbk%'; -- 查看UTF-8字符集的比较规则 SHOW COLLATION LIKE 'utf8mb3%'; -- 查看服务器的字符集和比较规则 SHOW VARIABLES LIKE '%_server'; -- 查看数据库的字符集和比较规则 SHOW VARIABLES LIKE '%_database'; -- 查看具体数据库的字符集 SHOW CREATE DATABASE demo_db1; -- 修改具体数据库的字符集 ALTER DATABASE demo_db1 DEFAULT CHARACTER SET 'utf8mb3' COLLATE 'utf8mb3_general_ci'; -- 查看表的字符集 SHOW CREATE TABLE demo_t2\G -- 修改表的字符集和比较规则 ALTER TABLE demo_t2 DEFAULT CHARACTER SET 'utf8mb3' COLLATE 'utf8mb3_bin';

【本文地址】

公司简介

联系我们