Mysql5.7中的字符编码utf8转utf8mb4

分类：Mysql | 2023-07-31 12:47:53

字符类型为utf8是不能存放emoji的, MySQL的utf8是3个字节，存放一个emoji是需要4个字节, 所以需要utf8mb4, mysql8.x已默认 utf8mb4

对于mysql5.7 修改utf8mb4：

1. 修改my.ini

[mysqld] character-set-server=utf8mb4

2. 在应用的的连接参数中，不要加charset参数。不加这个参数时，默认值就时autodetect。

3. 将已经建好的表也转换成utf8mb4

命令：ALTER TABLE `TABLE_NAME` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; （将TABLE_NAME替换成你的表名）

4. 将需要使用emoji的字段设置类型为：

命令：ALTER TABLE `TABLE_NAME`MODIFY COLUMN `COLUMN_NAME` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

MySQL字符集:1.字符集和排序规则

字符集(character set)：定义了字符以及字符的编码。
排序规则(collation)：定义了字符的比较规则。

一个字符集对应至少一种排序规则(一般是1对多)
两个不同的字符集不能有相同的排序规则
每个字符集都有默认的排序规则

2.MySQL字符集相关参数

MySQL 8.0开始，这些都是默认utf8mb4了(character_set_system除外)。

character_set_client      客户端来源数据使用的字符集，默认值:utf8mb4
character_set_connection  连接层字符集，默认值:utf8mb4
character_set_database    当前选中数据库的默认字符集，默认值:utf8mb4
character_set_results     查询结果字符集，默认值:utf8mb4
character_set_server      默认的内部操作字符集，默认值:utf8mb4
character_set_system      系统元数据(字段名等)字符集，默认值:utf8

3.MySQL支持的字符集、排序规则

MySQl支持的字符集

show character set;

MySQL utf8mb4支持的排序规则:

show collation where charset = 'utf8mb4';

一.常用的字符集1.utf8和utf8mb4

MySQL8.0开始，默认字符集修改为了utf8mb4。

MySQL在5.5.3之后增加了utf8mb4的编码，mb4即4-Byte UTF-8 Unicode Encoding，专门用来兼容四字节的unicode。utf8mb4为utf8的超集并兼容utf8，比utf8能表示更多的字符。

如果需要存储4 字节的字符，utf8字符集就会出现问题，例如emoji表情，所以建议选择utf8mb4而非utf8。

当然utf8替换为utf8mb4占的空间更多，如果utf8绝对够用，也可以依旧使用utf8。

2.gbk/gb2312/gb18030

GB2312 :中国国家标准简体中文字符集,共收录 6763 个汉字，其中一级汉字 3755 个，二级汉字 3008 个；同时收录了包括拉丁字母、希腊字母、日文平假名及片假名字母、俄语西里尔字母在内的 682 个字符。

GBK :共收入21886个汉字和图形符号,GBK 向下与 GB 2312 完全兼容，向上支持 ISO 10646 国际标准，在前者向后者过渡过程中起到的承上启下的作用。

GB18030: 共收录汉字70244个,GB18030 与 GB 312 和 GBK 兼容

大多数情况下，GBK就够用了，如果生僻字比较多，可以改为GB18030

关于utf8和gbk字符集如何选择:
UTF-8：一个汉字 = 3个字节，英文是1个字节
GBK：一个汉字 = 2个字节，英文是1个字节

这样看，如果是中文的的系统，选择GBK可以比UTF8更节省磁盘空间，性能也就更好。

二.常用MySQL的排序规则介绍

utf8_general_ci 不区分大小写，这个你在注册用户名和邮箱的时候就要使用。

utf8_general_cs 区分大小写，如果用户名和邮箱用这个就会造成不良后果

utf8_bin:字符串每个字符串用二进制数据编译存储。区分大小写，而且可以存二进制的内容

utf8_general_ci校对速度快，但准确度稍差。

utf8_unicode_ci准确度高，但校对速度稍慢。

如果字符集选择utf8，则排序规则选择默认的utf8_general_ci
如果字符集选择utf8mb4，则排序规则选择默认的utf8mb4_0900_ai_ci
如果字符集选择gbk，则排序规则选择默认的gbk_chinese_ci

三.MySQL字符集相关参数3.1 字符集参数介绍

character_set_client      客户端来源数据使用的字符集，默认值:utf8mb4
character_set_connection  连接层字符集，默认值:utf8mb4
character_set_database    当前选中数据库的默认字符集，默认值:utf8mb4
character_set_results     查询结果字符集，默认值:utf8mb4
character_set_server      默认的内部操作字符集，默认值:utf8mb4
character_set_system      系统元数据(字段名等)字符集，默认值:utf8

因为服务器端和客户端，字符集会存在一定的差异。

服务器端总是假设客户端是按照 character_set_client设置的字符来传输数据和SQL语句的。

当服务器收到客户端的SQL语句时，它先将其转换为字符集character_set_connection。它还是用这个设置来决定如何将数据转换成字符串。

当服务器端返回数据或错误信息给客户端时，它会将其转换成character_set_result。

这样就保证了，服务器端和客户端在中转数据的时候，不会出现乱码。

至于character_set_server和character_set_database这两个，一个是系统默认的字符集，一个是创建数据库默认的字符集。

3.2 字符集参数修改

备注:
character_set_system参数是只读参数，不能修改，其余参数可以通过set命令进行修改

上述5个参数需要保持一致，这样才能避免出现乱码，如果需要修改，可以参考如下:

set character_set_client = utf8;
set character_set_connection = utf8;
set character_set_database = utf8;
set character_set_results = utf8;
set character_set_server = utf8;

show variables like 'character_set%';
+--------------------------+----------------------------------------------+
| Variable_name            | Value                                        |
+--------------------------+----------------------------------------------+
| character_set_client     | utf8                                         |
| character_set_connection | utf8                                         |
| character_set_database   | utf8                                         |
| character_set_filesystem | binary                                       |
| character_set_results    | utf8                                         |
| character_set_server     | utf8                                         |
| character_set_system     | utf8                                         |
| character_sets_dir       | E:\mysql\mysql-8.0.19-winx64\share\charsets\

set character_set_system = utf8;
ERROR 1238 (HY000): Variable 'character_set_system' is a read only variable

四.数据库、表、列的字符集

创建数据库时，如不指定字符集，将根据服务器上的character_set_server设置来设定该数据库的默认字符集。

创建表时，如不指定字符集，将根据数据库的字符集设置指定这个表的字符集。

创建列时，如不指定字符集，将根据表的设置指定列的字符集。

最好是先为服务器(数据库)选择一个合理的字符集，然后表和列都遵循这个字符集。

当有特殊需求的时候，可以显式的指定表或列的字符集，让表和列选择自己合适的字符集。

以下转自: https://blog.csdn.net/mingli_a/article/details/115351986

作为Mysql的一个连接参数，init_connect本身并不十分抢眼，官方手册中对其介绍只有几行，只简单指出了init_connect的一些基本规则：

只有在普通用户的客户端连接时才能执行这个参数，超级用户或有连接管理权限的用户会跳过这个设置；

一个init_connect可以带一个或多个sql语句；

init_connect的sql语句中若包含错误，则会导致连接失败。

用于审计

从具体的使用角度讲，在网上能查到的init_connect使用率最高的，就是结合mysql自身的日志进行的审计功能，而init_connect则是被用来记录普通用户的登录信息，追踪业务操作时，可以根据记录的用户线程编号，用户名等基本信息，跟踪日志中该用户的所有操作。下面我们通过mysql单机版本，简述该审计功能的实现过程和最终效果。

我们选择的mysql是8.0.23，mysql的编译、安装、初始化等操作这里不做过多描述，相关内容请读者自行搜索学习。

网上很多资料写的是通过general-log的内容进行审计跟踪，但由于general-log会记录用户登录后的所有内容，因此存在IO占用和资源消耗的问题，所以还通过binlog的进行，改变数据库的SQL语句执行结束时,将在binlog的末尾写入一条记录,同时通知语句解析器,语句执行完毕，我们可以根据这个信息，对binlog能够记录的内容进行审计跟踪。

在配置文件中配置binlog的位置，系统根据执行的持续，自动生成.000001及以后的文件。

接下来我们来看init_connect的设置：

首先我们需要在cnf文件中增加init_connect参数，value是向一个记录用户登录信息的表中写入该登录用户的线程编号，登录时间等基本内容，具体语句为：

init_connect='insert into auditdb.access(thread_id,login_time,localname,matchname) values (connection_id(),now(),user(),current_user());'

由语句显示，每一个登录服务器的普通用户，都会自动把自己的thread_id，当前时间，用户名，匹配规则等内容，记录到auditdb库的access表中。如果需要记录其他更多的内容，可根据业务需要和系统函数所能提供的功能，调整这个表以及字段。

create database auditdb;

create table auditdb.access(

id int not null auto_increment,

thread_id int not null,

login_time timestamp,

localname varchar(50) default null,

matchname varchar(50) default null,

primary key (id)

) comment '审计用户登录信息';

修改了cnf文件，根据情况需要重启MySQL服务，使参数在本次启动中生效。

数据库管理员登录，创建实际存储普通用户登录记录的库和表。

根据实际使用需要，创建存储业务数据的库。

create database testDB;

创建一个普通用户，要求这个用户的所有操作被审计。

CREATE USER 'user1'@'%' IDENTIFIED BY '123456'; --创建用户user1

GRANT ALL ON testDB.* TO 'user1'@'%'; --首先授权user1可以通过任何位置访问testDB库的所有操作

GRANT insert on auditdb.access to 'user1'@'%'; --此步最关键，授权user1对访问记录表有写入权限，因为在init_connect中设置了普通用户要对这个表进行写入，因此如果不设定这个权限，user1永远不能访问任何一个库。

flush privileges; --刷新所有用户权限生效

检验审计效果：

使用刚刚创建的普通用户user1登录。

登录成功，说明init_connect中，写入记录表的操作成功了。

有了thread_id，该用户在数据库里的所有被记录在binlog中的操作就都能被跟踪了。

查看binlog有两种方法，一个是在登录mysql的状态下，通过show命令，可以有格式的查看记录：

使用 show binlog events in ‘binlogfile’;

其中Pos和End_log_pos成对出现，可以理解为一个事务的开始和结束，下面的截图中可以看到，从10292到10323，完成一个提交，10402到10479是一个事务开始，10479到10531查询的是testDB.tb3这个表，10531到10575对这个表（id都是128）执行了写入，最后从10575到10606做提交。

那么如何知道这些操作是谁完成的能，则需要通过mysqlbinlog查看具体信息：

mysqlbinlog binlog.000002

找到刚才10402到10479开始事务的位置，看到有一个thread_id=33，这个就是执行接下来操作的用户ID，登录状态下，可以直接执行select connection_id();查看当前用户的这个值。而init_connect要做的，就是把每次用户登录时他的thread_id记录下来。

具体能审计的内容受binlog中的内容限制，对于二进制文件可以通过mysqlbinlog打开然后重定向到文件，再进行文本过滤。

对于init_connect的扩展使用：

如前面所讲，init_connect的功能其实本身非常简单，无非就是让普通用户在连接服务器的时候能够自动执行SQL语句，那么除了做审计记录登记的功能，可能还有的用途就是给普通用户做某些功能的初始化或清理某些数据，当然比较复杂的功能我们完全可以写成存储过程或函数，init_connect执行一个call，自动执行一大批任务。下面我们来简单尝试一下：

无论是初始化数据还是清理数据，我们都可以看作是一系列操作，具体如何操作其实我们并不十分关心，我们考虑把一系列操作或多条SQL语句放在一个存储过程中，而使用init_connect调用存储过程来简化执行的步骤。

假设当前mysql系统中包含一个testDB库，其中包含一个tb1(id int primary key auto_increment,time timestamp, comment text)表。

我们编写一个存储过程，自动向这个表里写一些内容。

DELIMITER //

CREATE PROCEDURE init_insert()

begin

insert into testDB.tb1 (time,comment) values (now(),’insert by procedure’);

end //

DELIMITER ;

由于这个存储过程是在testDB这个库中创建的，因此在登录状态下，尝试了使用

use testDB;

call init_insert();

和登录后直接

call testDB.init_insert();

的方式都是可以正常执行的，考虑init_connect的时候没有use库，所以我们按call testDB.init_insert()的方式写。

普通用户登录，查询testDB.tb1，

我们进一步看，一个init_connect中顺序执行多条SQL语句的用法。

还用刚才的testDB库，再创建一个tb2(a int,b int);，同样的思路，继续给用户user1在tb2上的写入权限。

在cnf的init_connect中写为两个SQL的形式

尝试普通用户连接并查看相关表记录。

综上所述，init_connect的功能相对简单，只是让普通用户在连接时自动执行一些语句，甚至DBA都不关心当时执行返回了哪些结果，而直接的效果就是成功执行则可以use下去，不成功执行则会报类似

的错误。

使用注意

init_connect的功能虽然简单，官方文档介绍的篇幅也不大，但是在验证上面的功能时，发现了一个比较麻烦的问题，就是init_connect虽然是控制客户端连接时执行的内容，但是这个设定需要在初始化data目录的时候就设置好，单纯的重启已有的库，对于参数修改是没有效果的，也可能因为这个小功能上，mysql本身没有给予太多的关注，所以在使用上多少有点儿限制，但总之init_connect功能比较简单，可做的事情也非常有限，我们利用好他提供的用户登录信息记录，做好审计相关的内容就足够了，这也是目前在网上关于这个参数讨论最多的用法。

// SELECT * FROM information_schema.TABLES where TABLE_TYPE='BASE TABLE' and table_schema='数据名';
// dbObj() 即链接数据库的对象, 请根据实际情况调整...
$list = dbObj()->tGetList('information_schema.TABLES', 'table_schema,table_name,table_collation', [
    'TABLE_TYPE'   => 'BASE TABLE',
    'table_schema' => ['数据名1', '数据名2', '数据名3']
]);
foreach ($list as $v) {
    if ($v['table_collation'] != 'utf8mb4_general_ci') {
        $alterSql = "ALTER TABLE `{$v['table_schema']}`.`{$v['table_name']}` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci";
        dbObj()->execute($alterSql);
        echo $alterSql . "\n";
    }
}