jopa

MariaDB utf-8改编码到utf8mb
背景:公司之前使用的都是utf8的字符编码,用户在发表文字内容中带有Emoji表情,导致无法插入数据库,原因是Em...
扫描右侧二维码阅读全文
09
2018/07

MariaDB utf-8改编码到utf8mb

背景:
公司之前使用的都是utf8的字符编码,用户在发表文字内容中带有Emoji表情,导致无法插入数据库,原因是Emoji是4个字节,而utf-8是3个字节。

如何修改:
先查看下编码:

MariaDB [(none)]>  SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
+--------------------------+-------------------+
| Variable_name            | Value             |
+--------------------------+-------------------+
| character_set_client     | utf8              |
| character_set_connection | utf8              |
| character_set_database   | latin1            |
| character_set_filesystem | binary            |
| character_set_results    | utf8              |
| character_set_server     | latin1            |
| character_set_system     | utf8              |
| collation_connection     | utf8_general_ci   |
| collation_database       | latin1_swedish_ci |
| collation_server         | latin1_swedish_ci |
+--------------------------+-------------------+
10 rows in set (0.00 sec)

修改/etc/my.cnf.d/server.cnf
[mysqld]

[mysqld]
character_set_server=utf8mb4
collation-server=utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'
skip-character-set-client-handshake=true

重启DB:

systemctl restart mariadb

再次查看编码:

MariaDB [(none)]> 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_unicode_ci |
| collation_server         | utf8mb4_unicode_ci |
+--------------------------+--------------------+
10 rows in set (0.00 sec)

修改DB的默认编码:

ALTER DATABASE XX_db CHARACTER SET utf8mb4;

修改表中字段编码

ALTER TABLE 表名 MODIFY `字段` TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
最后修改:2018 年 07 月 12 日 11 : 24 AM
如果觉得我的文章对你有用,请随意赞赏

发表评论