Preface
最近开发的时候,因为数据库相关字段编码设置的问题,出现了bug。本文是对MySQL字符编码相关设置的记录。
本文较多参考了鸟哥的 深入Mysql字符集设置 一文,更多参考链接均在文末。
基本概念
• 字符(Character) 是指人类语言中最小的表义符号。例如’A’、’B’等;
• 给定一系列字符,对每个字符赋予一个数值,用数值来代表对应的字符,这一数值就是 字符的编码(Encoding) 。例如,我们给字符’A’赋予数值0,给字符’B’赋予数值1,则0就是字符’A’的编码;
• 给定一系列字符并赋予对应的编码后,所有这些字符和编码对组成的集合就是 字符集(Character Set) 。例如,给定字符列表为{‘A’,’B’}时,{‘A’=>0, ‘B’=>1}就是一个字符集;
• 字符序(Collation) 是指在同一字符集内字符之间的比较规则;
• 确定字符序后,才能在一个字符集上定义什么是等价的字符,以及字符之间的大小关系;
• 每个字符序唯一对应一种字符集,但一个字符集可以对应多种字符序,其中有一个是默认字符序(Default Collation);
• MySQL中的字符序名称遵从命名惯例:以字符序对应的字符集名称开头;以_ci(表示大小写不敏感)、_cs(表示大小写敏感)或_bin(表示按编码值比较)结尾。例如:在字符序“utf8_general_ci”下,字符“a”和“A”是等价的;
查看编码
查看数据库编码
1 | > show variables like '%char%'; | show variables like 'character%'; |
系统变量介绍
1 | character_set_client 客户端来源数据使用的字符集 |
MySQL中的字符集转换过程
A. MySQL Server收到请求时将请求数据从character_set_client
转换为character_set_connection
;
B. 进行内部操作前将请求数据从character_set_connection
转换为内部操作字符集,其确定方法如下:
• 使用每个数据字段的CHARACTER SET
设定值;
• 若上述值不存在,则使用对应数据表的DEFAULT CHARACTER SET
设定值(MySQL扩展,非SQL标准);
• 若上述值不存在,则使用对应数据库的DEFAULT CHARACTER SET
设定值;
• 若上述值不存在,则使用character_set_server
设定值。
C. 将操作结果从内部操作字符集转换为character_set_results
。
查看所有数据库的编码
1 | > SELECT SCHEMA_NAME 'database', default_character_set_name 'charset', DEFAULT_COLLATION_NAME 'collation' FROM information_schema.SCHEMATA; |
查看单个数据库的编码
1 | > USE my_database; |
查看表编码
1 | show create table <表名>; |
查看数据表中字符集设置
1 | > show full columns from <表名>; |
这里要特别说明,网上的很多文章都是将 show full columns from <表名>
这个命令写为查看字段的编码。这个确切的说来是错误的。
从上面的查询结果可以看到实际查询出来的是Collation(字符序),而不是字段的编码,但是我们可以通过Collation(字符序)的前缀得知字段的编码。
在stackoverflow上找到这样一段命令
查询当前数据库所有表的所有字段的字符编码
1 | SELECT TABLE_SCHEMA, |
这里推荐阅读MySQL中CREATE DATABASE和CREATE SCHEMA区别
查询所有表的信息
1 | > SHOW TABLE STATUS |
查询单张数据表的信息
1 | > SHOW TABLE STATUS where name like 'table_123'; | |
修改编码
修改数据库编码格式
1 | alter table <表名> character set utf8; |
修改一张表的所有字段的编码格式
1 | alter table `tablename` convert to character set utf8; |
修改数据表编码格式
1 | alter table <表名> character set utf8; |
修改字段编码格式
1 | alter table <表名> change <字段名> <字段名> <类型> character set utf8; |
注意字段名要输入两次
存emoji表情
mysql的utf8
编码的一个字符最多3个字节,但是一个emoji表情为4个字节,所以utf8不支持存储emoji表情。但是utf8
的超集utf8mb4
一个字符最多能有4字节,所以能支持emoji表情的存储。
参考
整理 : 查看和修改 mysql库、表、字段编码
深入Mysql字符集设置
mysql字符集小结
How do I see what character set a MySQL database / table / column is?
数据库 schema含义
MySQL中CREATE DATABASE和CREATE SCHEMA区别
mysql修改数据库表和表中的字段的编码格式的修改
浅谈MySQL中utf8和utf8mb4的区别