家居小楠人≈

您现在的位置是:首页 > 慢生活 > 程序人生网站首页慢生活

如何在MySQL数据库中支持完整的Unicode,你MySql中还在使用charset=utf8么?

家居小楠人≈ 2019-05-17 298人围观
简介 自己收集的文章,学习之用!!!

你的MySql数据库 字符集 还在用utf8 么? 在这篇文章中,我将解释为什么你应该切换到utf8mb4,以及如何做到这一点。

UTF-8

UTF-8编码可以表示Unicode字符集中的每个符号,范围从U + 000000到U + 10FFFF。这是1,114,112个可能的符号。(并非所有这些Unicode代码点都已分配了字符,但这并不能阻止UTF-8对它们进行编码。)

UTF-8是可变宽度编码; 它使用一到四个8位字节对每个符号进行编码。具有较低数字代码点值的符号使用较少的字节进行编码。这样,UTF-8针对常见情况进行了优化,其中使用了ASCII字符和其他BMP符号(其代码点范围从U + 000000到U + 00FFFF) - 同时仍然允许星体符号(其代码点范围从U + 010000)到U + 10FFFF)存储。

MySQL的 utf8

很长一段时间,我使用MySQL的utf8数据库,表和列的字符集,假设它映射到上面描述的UTF-8编码。通过使用utf8,我将能够在我的数据库中存储我想要的任何符号 - 或者我想。

在写关于JavaScript的内部字符编码时,我注意到没有办法将U + 1D306 TETRAGRAM FOR CENTER(𝌆)符号插入到此站点后面的MySQL数据库中。我试图更新的列有utf8_unicode_ci排序规则,连接字符集设置为utf8

mysql> SET NAMES utf8; # just to emphasize that the connection charset is set to `utf8`
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE database_name.table_name SET column_name = 'foo𝌆bar' WHERE id = 9001;
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 1

mysql> SELECT column_name FROM database_name.table_name WHERE id = 9001;
+-------------+
| column_name |
+-------------+
| foo |
+-------------+
1 row in set (0.00 sec)

在这种情况下,内容在第一个星形Unicode符号处被截断𝌆- 因此,尝试插入foo𝌆bar实际插入foo,导致数据丢失(并可能引入安全问题;请参阅下文)。MySQL也发出了一条警告信息:

mysql> SHOW WARNINGS;
+---------+------+------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------------------------------------------+
| Warning | 1366 | Incorrect string value: '\xF0\x9D\x8C\x86' for column 'column_name' at row 1 |
+---------+------+------------------------------------------------------------------------------+
1 row in set (0.00 sec)

原来MySQL的utf8charset只部分实现了正确的UTF-8编码。它只能存储由一到三个字节组成的UTF-8编码符号; 不支持占用四个字节的编码符号。

由于星号(其代码点范围从U + 010000到U + 10FFFF)每个由UTF-8中的四个字节组成,因此您无法使用MySQL的utf8实现来存储它们。

这不仅影响𝌆角色,而且更重要的符号如U + 01F4A9 PILE OF POO(💩)。总共,这是您无法使用的1,048,575个可能的代码点。事实上,MySQL utf8只允许您存储(0x00FFFF + 1) / (0x10FFFF + 1)所有可能的Unicode代码点的5.88%()。适当的UTF-8可以编码100%的所有Unicode代码点。

如上所示,此行为可能导致数据丢失,但会变得更糟 - 它可能导致安全漏洞。以下是一些示例,所有这些都是在发布此文章后发现的:

TL; DR MySQL的utf8编码名称笨拙,因为它与正确的UTF-8编码不同。它不提供完整的Unicode支持,这可能导致数据丢失或安全漏洞。

MySQL的 utf8mb4

幸运的是,MySQL 5.5.3(2010年初发布)引入了一种新的编码,称为utf8mb4映射到正确的UTF-8,因此完全支持Unicode,包括星体符号。

从MySQL切换utf8utf8mb4

第1步:创建备份

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

第2步:升级MySQL服务器

将MySQL服务器升级到v5.5.3 +,或要求服务器管理员为您执行此操作。

第3步:修改数据库,表和列

更改要使用的数据库,表和列的字符集和排序规则属性,utf8mb4而不是utf8

# 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;
# (Don’t blindly copy-paste this! The exact statement depends on the column type, maximum length, and other properties. The above line is just an example for a `VARCHAR` column.)

由于utf8mb4完全向后兼容utf8,因此不应发生mojibake或其他形式的数据丢失。(但你有备份,对吧?)

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

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

当从转换utf8utf8mb4,列或索引关键字的最大长度是在以下方面不变字节。因此,它在字符方面较小,因为字符的最大长度现在是四个字节而不是三个字节。

例如,一TINYTEXT列最多可容纳255个字节,这与85个三字节或63个四字节字符相关。假设您有一个TINYTEXT使用utf8但必须能够包含超过63个字符的列。鉴于此要求,您无法将此列转换为utf8mb4除非您还将数据类型更改为更长的类型,例如TEXT- 因为如果您尝试使用四字节字符填充它,则只能输入63字符,但不是更多。

索引键也是如此。的InnoDB存储引擎具有767个字节的最大索引长度,所以对于utf8utf8mb4列,分别可以索引的最大值的255个或191个字符。如果您当前具有utf8索引长度超过191个字符的列,则在使用时需要索引较少数量的字符utf8mb4。(因此,我不得不将一些索引VARCHAR(255)列更改为VARCHAR(191)。)

“MySQL 5.5参考手册”的10.1.11节提供了更多相关信息。

步骤5:修改连接,客户端和服务器字符集

在应用程序代码中,将连接字符集设置为utf8mb4。这可以通过简单地替换SET NAMES utf8with的任何变体来完成SET NAMES utf8mb4。如果您的旧SET NAMES语句指定了排序规则,请确保也改变它,例如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

您可以轻松确认这些设置是否正常工作:

mysql> SHOW VARIABLES WHERE Variable_name LIKE'character \ _set \ _%'或Variable_name LIKE'collation%'; 
+ -------------------------- + -------------------- +
| Variable_name | 价值|
+ -------------------------- + -------------------- +
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8mb4 |
| character_set_filesystem | 二进制|
| 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集合中的行(0.00秒)

正如你所看到的,所有相关的选项都设置为utf8mb4,除了character_set_filesystem它应该是binary,除非你是一个支持在文件名中的多字节UTF-8编码字符的文件系统上,而character_set_system这始终是utf8不能被覆盖

注意:默认字符集和排序规则也可以在其他一些级别配置。

第6步:修复和优化所有表

升级MySQL服务器并进行上述必要的更改后,请确保修复和优化所有数据库和表。升级后我没有立即这样做(我认为没有必要,因为乍一看似乎一切正常),并遇到一些奇怪的错误,其中UPDATE语句没有任何效果,即使没有错误被扔了。

您可以为要修复和优化的每个表运行以下MySQL查询:

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

幸运的是,使用命令行mysqlcheck实用程序可以轻松地一次完成:

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

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

摘要

永远不要utf8在MySQL中使用 - 总是使用utf8mb4。更新数据库和代码可能需要一些时间,但绝对值得付出努力。为什么要随意限制可以在数据库中使用的符号集?每当用户输入星号符号作为评论或消息的一部分或您存储在数据库中的任何内容时,为什么会丢失数据?没有理由不在任何地方争取全面的Unicode支持。做正确的事,并使用utf8mb4。🍻


惭愧的是 本人在转载这篇文章时  虽然库和表 默认都是utf8mb4 格式了  但是 由于TP 框架 默认数据库配置 还是utf8  格式  导致发布这篇文章时总是添加失败.......😂😂😂

 


这才是正确的 ,动手实践 最重要👏👏👏👏👏👏

转载自:https://mathiasbynens.be/notes/mysql-utf8mb4#utf8-to-utf8mb4


文章评论