mysql设置utf8mb4字符编码

发布于 作者: 2 次浏览

1.查看编码

#显示所有编码和字符校对的参数
SHOW VARIABLES WHERE Variable_name LIKE 'character_set_%' OR Variable_name LIKE 'collation%';
+--------------------------+----------------------------+
| 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     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
| collation_connection     | utf8mb4_general_ci         |
| collation_database       | utf8mb4_general_ci         |
| collation_server         | utf8mb4_general_ci         |
+--------------------------+----------------------------+
关注下列变量是否符合我们的要求
character_set_client
character_set_connection
character_set_database
character_set_results
character_set_server
下列三个系统变量我们不需要关心，不会影响乱码等问题
character_set_filesystem
character_set_system
character_sets_dir

2、修改编码为utf8mb4

修改my.cnf文件。加入以下内容，然后重启数据库：systemctl restart mysqld

[mysqld]
character-set-server=utf8mb4

[mysql]
default-character-set=utf8mb4

[client]
default-character-set=utf8mb4

3.已有的库和表更改为utf8mb4

SELECT
CONCAT(
'ALTER TABLE `',
TABLE_NAME,
'` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;'
) AS mySQL
FROM
INFORMATION_SCHEMA. TABLES
WHERE
TABLE_SCHEMA = '你的库名'；

生成可以批量执行的sql语句，导出批量跑一下，ok了