烦恼一般都是想太多了。

0%

MySQL统一编码为utf8mb4

据说 MySQL 的 utf8 编码并不是真正的 utf8,而是只有 3 个字节,所以很多内容可能是存储不了的。比如 emoji 表情,就会出错,在 MySQL 5.5.3 (released in early 2010)引入了一个新的字符编码 utf8mb4 ,可以完全的支持 Unicode 字符集。我们就来看看如何使用这个编码。

Step 1: 创建备份

这是必须的,把我们的数据库备份一份。

参考 MySQL-backup 一文。

Step 2: 升级服务器

将 MyySQL server 升级到 v5.5.3+ 以上。

Step 3: 修改数据库、表、列

改变数据库,表,和列的字符集及排序属性来支持 utf8mb4。

-- For each database:
ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
-- For each table:
ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- For each column:
ALTER TABLE table_name CHANGE column_name column_name VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

不要直接复制上面的代码,要根据你具体的库,表,列信息来进行变动。

Step 4: 检查列的最大长度和索引长度

这可能最乏味的一个事情了在整个升级过程中。

当从 utf8 转换到 utfb8mb4 的时候,列或索引的最大长度的字节数是没有变化的。因此,从字符数量来看,其就更小了,因为现在每个字符的最大长度是 4字节 而不是 3字节了

例如,一个 TINYTEXT 列可以容纳 255 字节,也就是 85 个 3 字节的字符或者是 63 个 4 字节的字符。现在,我们有一个 TINYTEXT 列使用 utf8,其不许能容纳超过 63 个字符。在这种的要求下,我们不能直接将此列转换为 utf8mb4,除非我们同样改变此列的数据类型为 TEXT——因为我们将会使用 4 字节的字符进行填充,不然我们就只能输入 63 个字符了。

这对与索引键是同样的。InnoDB 存储引擎对于索引有个最大长度的限制 767字节,所以对于 utf8 或者 utf8mb4 列,我们的索引的最大分别是 255191 个字符。如果我们当前的 utf8 列的索引比 191 个字符要长,那么只能缩小这个索引。比如 VARCHAR(255) 到 VARCHAR(191)。

Section 10.1.11 of the MySQL 5.5 Reference Manual has some more information on this.

Step 5: 改变 connection, client, and server 的字符集

在我们的应用程序代码中,让 connection 使用 utf8mb4 字符集。这可以简单的通过替换所有的 SET NAMES utf8SET NAMES utf8mb4。如果我们指定了排序属性,那么同样也要进行变更。如从 SET NAMES utf8 COLLATE utf8_unicode_ciSET NAMES utf8mb4 COLLATE utf8mb4_unicode_c

我们可以这样在配置文件中进行设置。(/etc/my.cnf):

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

然后重新启动后进行验证:

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

我们可以看到,所有相关的选项都可以设置为 utf8mb4 了,除了 character_set_filesystem ,这个应该是 binary 除非我们在一个支持多字节 UTF-8 编码字符文件名的文件系统上,character_set_system 是不能进行修改的,总是 utf8.

Note: 默认字符集也可以在其他地方进行设置。

Step 6: 修复和优化所有的表

在升级了 MySQL 服务器和做了以上说的这些事情后,确认进行了修复和优化所有的数据库和表。
could run the following MySQL queries for each table you want to repair and optimize:

# For each table
REPAIR TABLE table_name;
OPTIMIZE TABLE table_name;

不过,我们我们也可以用命令行来把这两个事情一起做了。

mysqlcheck -u root -p --auto-repair --optimize --all-databases

Docker 如何设置呢?

自己使用 Docker File 来建立一个,带有配置文件的镜像:

# mysql_charset.cnf


[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
FROM mysql:5.7
COPY ./mysqld_charset.cnf /etc/mysql/conf.d/mysqld_charset.cnf

然后执行:

docker build  -t mysql-utf8-mb4:5.7 .
docker run --name=mysql -p 3306:3306 -v /opt/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD='wouinibaba' -d mysql-utf8-mb4:5.7

即OK了

Docker 加速

sudo mkdir -p /etc/docker
sudo tee /etc/docker/daemon.json <<-'EOF'
{
"registry-mirrors": ["https://个人专用前缀.mirror.aliyuncs.com"]
}
EOF
sudo systemctl daemon-reload
sudo systemctl restart docker