[聚合文章] mariaDB utf-8改编码到utf8mb

背景：

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

如何修改：

先查看下编码：

mysql> 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)

先备份数据库：

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

开启log-bin：

[server]
# 开启 binlog
log-bin=mariadb-bin

修改/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 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;