mariaDB utf-8改编码到utf8mb

来源：互联网发布：java new string 参数编辑：程序博客网时间：2024/05/30 02:25

背景：

设计的数据库默认使用utf-8编码，使用微信登录时，有用户使用Emoji表情，导致无法插入数据库，原因是Emoji是4个字节，而utf-8是3个字节。

如何修改：

先查看下编码：

mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';

先备份数据库：

mysqldump -B XX_db -u root -p --lock-all-tables --flush-logs  --master-data=2 >XX_db-.sql

开启log-bin：

[server]# 开启 binloglog-bin=mariadb-bin

修改/etc/my.cnf.d/server.cnf

[mysqld]

[mysqld]character_set_server=utf8mb4collation-server=utf8mb4_unicode_ciinit_connect='SET NAMES utf8mb4'skip-character-set-client-handshake=true

重启DB：

systemctl restart mysql

再次查看编码：

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               || collation_connection     | utf8mb4_unicode_ci || collation_database       | utf8mb4_general_ci || collation_server         | utf8mb4_unicode_ci |+--------------------------+--------------------+10 rows in set (0.01 sec)

修改DB的默认编码：

ALTER DATABASE XX_db CHARACTER SET utf8mb4;

阅读全文

0 0