MySQL/MariaDB中如何支持全部的Unicode

目录

• utf8mb4介绍
• utf8字节数超出的报错
• utf8mb4支持
• 将默认字符编码设置为utf8mb4，及对应排序规则。
• 查看当前编码
  
• 修改MySQL/Mariadb的配置文件，将utf8编码改为utf8mb4
• 重启MySQL/MariaDB
  
• 再次查看字符集和排序规则
  
• character_set_filesystem和character_set_system的说明
  
• 关于字符集设置的其他参考
  
• 现有数据库切换字符集到utf8mb4的完整过程
• 参考

永远不要在 MySQL 中使用 utf8，并且始终使用 utf8mb4 。

utf8mb4介绍

MySQL/MariaDB中，utf8字符集并不是对Unicode的真正实现，即不是真正的UTF-8编码，因为它支持最多3个字节的字符。
作为可变长字符编码，UTF-8最多支持4个字节，对于emoji、中日韩繁体等部分字符，需要使用4个字节才能完整存储。也就是，如果想要支持全部的Unicode编码，MySQL/MariaDB中的utf8是不够的。

UTF-8 编码可以表示 Unicode 字符集中的每个符号，范围从 U+000000 到 U+10FFFF。那是 1,114,112 个可能的符号（并非所有这些 Unicode 代码点都被分配了字符，但这并不影响UTF-8对其进行编码。Unicode实际映射了字符的共有1,112,064个码位）。UTF-8 是一种可变宽度编码；它使用一到四个(8位bit)字节对每个符号进行编码。这样既可以节省存储低代码值的空间，

又能充分表示所有的字符。

可以说UTF-8是最受欢迎的Unicode编码实现。

而MySQL/MariaDB真正实现UTF-8编码的是utf8mb4，最多支持4个字节的存储。

总共有 1,048,576 个无法使用的可能代码点。MySQL 的 utf8 只允许存储所有可能的 Unicode 代码点的 5.88% ((0x00FFFF + 1) / (0x10FFFF + 1))。正确的 UTF-8 可以编码 100% 的所有 Unicode 代码点。

utf8字节数超出的报错

如果使用utf8，存储emoji等4个字节的字符时，通常会报错 Error: 1366 错误：

[Err] 1366 - Incorrect string value: '\xF0\x9F\x98\x93' for column 'xxx' at row xx

[Error] 1366 - Incorrect string value: '\x...' for column 'xxx' at row xx

utf8mb4支持

MySQL/MariaDB 中的utf8mb4是对原先utf8只能存储3个字节大小的字符的一种补充，是一种真正的UTF-8编码。

MySQL 5.5.3+ 版本之后开始支持。

从 MySQL 8.0 开始默认的字符集已经变为 utf8mb4。也就是使用MySQL 8.0及以上版本，就不用担心字节长度问题。
MariaDB 10.x 版本默认的字符集仍是 latin1。因此通常情况下，应该修改为utf8mb4。

utf8mb4对应常用的排序规则为：utf8mb4_general_ci 和 utf8mb4_unicode_ci。

将默认字符编码设置为utf8mb4，及对应排序规则。

查看当前编码

登陆MySQL/MariaDB之后，使用SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';查看字符集和排序规则。

查看字符集：

MariaDB [(none)]> show variables like 'character%';
+--------------------------+------------------------------------------------+
| Variable_name            | Value                                          |
+--------------------------+------------------------------------------------+
| character_set_client     | gbk                                            |
| character_set_connection | gbk                                            |
| character_set_database   | utf8                                           |
| character_set_filesystem | binary                                         |
| character_set_results    | gbk                                            |
| character_set_server     | utf8                                           |
| character_set_system     | utf8                                           |
| character_sets_dir       | C:\Program Files\MariaDB 10.3n\share\charsets\ |
+--------------------------+------------------------------------------------+
8 rows in set (0.043 sec)

由于是在中文Windows下安装的，所以含有gbk。

修改MySQL/Mariadb的配置文件，将utf8编码改为utf8mb4

• Linux系统下，修改 /etc/my.cnf 或 /etc/my.cnf.d/server.cnf 文件，在对应[mysql]、[mysqld]等下面添加内容如下：
• Windows系统下，修改MySQL/Mariadb安装目录下data/my.ini文件，同样在对应[mysql]、[mysqld]等下面添加内容。

Linux下my.cnf ：

> vim /etc/my.cnf
#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]

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

[client]
default-character-set=utf8mb4

[mysql]
default-character-set=utf8mb4
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

Windows下my.ini ：

[mysqld]
datadir=C:/Program Files/MariaDB 10.3n/data
port=3306
character_set_server=utf8mb4
# Using unique option prefix 'character_set_client' is error-prone and can break in the future. Please use the full name 'character-set-client-handshake' instead.
character-set-client-handshake=utf8mb4  
# character_set_client=utf8mb4
collation-server=utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'
skip-character-set-client-handshake=false
character_set_filesystem = binary
lower_case_table_names=2 

[mysqldump]
loose_character_set_client=utf8mb4

[mysql]
default-character-set=utf8mb4

[client]
port=3306
plugin-dir=C:/Program Files/MariaDB 10.3n/lib/plugin
default-character-set=utf8mb4

几个设置项说明：

• character-set-client-handshake=FALSE 可以影响collation_connection的结果为utf8mb4_unicode_ci，而不是utf8mb4_general_ci。当然，使用SET collation_connection = utf8mb4_unicode_ci或collation_connection = utf8mb4_unicode_ci也可以。
• SET NAMES 指示客户端连接使用的字符集，即向服务器发送 SQL 语句的字符集。 character-set-server 设置服务器字符集。要正确使用 utf8mb4，需要确保客户端、服务器和连接都设置为 utf8mb4。
• init_connect等所有其他有关字符集的默认设置都会继承自character-set-server，也即单独指定init-connect、character_set_client, character_set_results,character_set_connection等都是不必须的。因此，上面设置中的init_connect可以省略。
• 连接的编码值，可以使 MySQL 正确解码输入并对结果进行编码。 否则会在内部重新编码处理。

纯净的精简的关于utf8mb4字符集和排序规则的设置，如下，只需要5项设置即可：

[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/MariaDB

Linux下使用systemctl restart mariadb或systemctl restart mysqld。必须使用restart重启(非reload)。

低版本MariaDB启动或重启的服务名为mysqld(mysqld.service)。systemctl restart mysqld

Windows下打开“服务”，在服务中找到MariaDB或MySQL对应的服务，右键重启即可。

再次查看字符集和排序规则

上面的修改重启生效后，再次查看，如下，已经变成为utf8mb4和utf8mb4_unicode_ci。

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.008 sec)

character_set_filesystem和character_set_system的说明

上面显示的字符集和排序规则可以看到，有两项并不是utf8mb4。

• character_set_filesystem 应该是二进制的，除非你使用的文件系统的文件名中支持多字节 UTF-8 编码字符。
• character_set_system 始终是 utf8 并且不能被覆盖。

关于字符集设置的其他参考

上面的设置已经是正确的修改utf8mb4的设置。下面将可能的几个相关设置项列出来，供可能的参考(虽然基本用不到)：

[client]
default-character-set                     = utf8mb4

[mysql]
default-character-set                     = utf8mb4

[mysqld]
explicit_defaults_for_timestamp           = 1 # Posting it here as a tip to disable the Timestamp message, maybe it can help someone :)
character-set-client-handshake            = 0 # FALSE
init_connect                              = 'SET character_set_system = utf8mb4'
init_connect                              = 'SET character_set_connection = utf8mb4'
init_connect                              = 'SET character_set_database = utf8mb4'
init_connect                              = 'SET character_set_results = utf8mb4'
init_connect                              = 'SET collation_database = utf8mb4_unicode_ci'
init_connect                              = 'SET collation_connection = utf8mb4_unicode_ci'
init_connect                              = 'SET NAMES utf8mb4'
character-set-server                      = utf8mb4
#character_set_client                     = utf8mb4
collation-server                          = utf8mb4_unicode_ci
collation_connection                      = utf8mb4_unicode_ci
collation_database                        = utf8mb4_unicode_ci

多个init_connect也可以这样设置：init_connect  = 'SET collation_connection = utf8mb4_unicode_ci,NAMES utf8mb4'。未验证

现有数据库切换字符集到utf8mb4的完整过程

如果数据库创建之初就是utf8mb4，则就没有这些烦心事了！！！

第 1 步：创建备份
创建要升级的服务器上所有数据库的备份。安全第一！

第 2 步：升级MySQL服务器
将 MySQL 服务器升级到 v5.5.3+。MySQL升级到8.0就不用第5步MySQL服务器字符集的修改了，默认就是utf8mb4。MariaDB还需要修改字符集。

第 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;
# （不要盲目复制粘贴！具体的语句取决于列类型、最大长度和其他属性。上面这行只是一个`VARCHAR`列的例子。）
# 或者modefy语句
ALTER TABLE table_name MODIFY column_name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

第 4 步：检查列和索引键的最大长度

这可能是整个升级过程中最乏味的部分。

从 utf8 转换为 utf8mb4 时，列或索引键的最大长度以字节为单位不变。因此，它在字符方面更小，因为字符的最大长度现在是四个字节而不是三个字节。

例如，TINYTEXT 列最多可容纳 255 个字节，这与 85 个三字节字符或 63 个四字节字符相关。假设你有一个使用 utf8 的 TINYTEXT 列，但必须能够包含 63 个以上的字符。鉴于此要求，无法将此列转换为 utf8mb4，除非还将数据类型更改为更长的类型，例如 TEXT — 因为如果你尝试用四字节字符填充它，将只能输入 63 个字符，但不能更多。
索引键也是如此。 InnoDB 存储引擎的最大索引长度为 767 字节，因此对于 utf8 或 utf8mb4 列，您最多可以分别索引 255 或 191 个字符。如果您当前有索引长度超过 191 个字符的 utf8 列，则在使用 utf8mb4 时需要索引较少数量的字符。 （因此，我不得不将一些索引的 VARCHAR(255) 列更改为 VARCHAR(191)。）

第 5 步：修改连接、客户端和服务器字符集

在应用程序代码中，将连接字符集设置为 utf8mb4。这可以通过简单地用 SET NAMES utf8mb4 替换 SET NAMES utf8 来完成。同时排序规则也要对应修改，例如 SET NAMES utf8 COLLATE utf8_unicode_ci 变为 SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci。

确保同时设置客户端和服务器字符集。

MySQL 配置文件 (/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

第 6 步：修复优化所有表（ Repair and optimize ）
升级 MySQL 服务器并进行上述必要更改后，请确保修复和优化所有数据库和表。否则可能会遇到奇怪的错误，即使没有错误被抛出。
可以为要修复和优化的每个表运行以下 MySQL 查询：

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

该工作，可以使用命令行 mysqlcheck 实用程序一次性轻松完成：

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

这将提示输入root用户的密码，之后将修复和优化所有数据库中的所有表。

参考

主要参考翻译自：How to support full Unicode in MySQL databases，欢迎阅读原文。

到此这篇关于MySQL/MariaDB中如何支持全部的Unicode的文章就介绍到这了,更多相关MySQL/MariaDB支持Unicode内容请搜索自学编程网以前的文章或继续浏览下面的相关文章希望大家以后多多支持自学编程网！