ANBOB™

前几天有套MySQL业务上线，开发提供的那表脚本中默认表级字符集为utf8bm3,  印像是utf8mb4在MySQL8为默认首选字符集，所以针对这块做个小功课，utf8bm3和utfbm4在MySQL8中我们应该选哪个字符集？会有什么影响？

看一下建表语句

DROP TABLE IF EXISTS `XXXXX`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `XXXXX` (
  `OBJECTID` varchar(128) NOT NULL,
  `MEOBJECTID` varchar(128) DEFAULT NULL,
...
  `OBJECTKEY` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

UTF-8

UTF-8是Unicode转换Format-8-bit.的缩写。它是一种可变宽度的字符编码，字符范围从U+0000到U+10FFFF，使用1到4个字节的序列。下表显示了不同八位元类型的编码格式。字母x表示可用于编码位的位。

UTF8 in MySQL

但是，根据MySQL的官方手册，MySQL的utf8字符集只实现了整个UTF-8编码的一部分，它只使用1到3个字节进行编码。因此，当您尝试使用一些占用4个字节的字符时如emoji 表情符号U+1F601，这些字符无法存储，并且会出现一些警告，表情符号作为字符输入的流行也推动了utf8mb4而不是utf8mb3和latin1的采用。在MySQL中，utf8是utf8mb3的别名。

如果你想使用更多的UTF-8编码字符，你可以使用MySQL的utf8mb4。对于基本多语言平面(BMP)字符，utf8mb4和utf8mb3具有相同的存储特征:相同的代码值、相同的编码、相同的长度。对于一个补充字符，utf8mb4使用4个字节来存储它，当utf8mb3根本不能存储它时，它可以存储更多。您还应该意识到MySQL8中utf8mb3字符集已弃用，您应该期望在未来的MySQL发行版中删除它。请改用utf8mb4。新的基于utf8mb4的排序比任何旧的基于utf8mb3的排序都快得多，下面是官方提供的基准测试，Utf8mb4用红色表示。每秒事务数的结果;越高越好

长期使用MySQL的用户会发现MySQL中有两种utf8支持;Utf8mb3和utf8mb4。看一下历史

·MySQL 4.1(2004)是第一个支持字符集和排序规则的版本。默认字符集是latin1，但是utf8[mb3]是可用的选项。我们选择了一个优化来将utf8限制在3个字节，这足以处理几乎所有的现代语言。
·MySQL 5.5(2010)使用新的utf8mb4字符集增加了对最多4字节utf8的支持
·MySQL 5.7(2015)增加了一些优化，比如可变长度排序缓冲区，并将InnoDB的默认行格式更改为DYNAMIC。这允许使用utf8mb4在VARCHAR(255)上建立索引;这使得之前的迁移更加困难。
·MySQL 8.0(2018-)极大地提高了utf8mb4的性能，并添加了一些新的排序规则。它现在是MySQL的默认字符集。

The utf8mb3 character set has these characteristics:

建议

如果您使用的是MySQL（或MariaDB或Percona Server），请确保您知道自己的编码。我会建议任何人将MySQL编码设置为utf8mb4。永远不要在MySQL中使用utf8，没有充分的理由这样做（除非你喜欢跟踪与编码相关的错误）。

转换utf8mb3 到utf8mb4

用 utf8mb4 并删除旧的 utf8（utf8mb3）

1,创建备份。一定要记住这一点——创建备份,修改数据库、表和列

# Run this once on each schema you have (Replace database_name with your schema name)
ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
 
# Run this once for each table you have (replace table_name with the table name)
ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 
# Run this for each column (replace table name, column_name, the column type, maximum length, etc.)
ALTER TABLE table_name CHANGE column_name column_name VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

2, 检查列和索引键的最大长度。这是因为utf8mb4最多允许4个字节而不是3个字节，这将导致列或索引键存储的字符比以前更少。
3, 修改连接、客户端和服务器字符集。在应用程序中，使用SET NAMES utf8mb4或SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci。在服务器端，修改配置文件my.cnf，更新客户端、mysql和mysqld的“default-character-set=utf8mb4”。
4, Repair and optimize all tables using the commands:

REPAIR TABLE [table_name];
OPTIMIZE TABLE [table_name]

扩展知识

您必须考虑将列大小从每个字符 3 个字节增加到 4 个字节的后果。例如，MySQL 索引限制为 768 字节。这意味着，如果将 VARCHAR（255） 从每个字符 3 个字节增加到每个字符 4 个字节，您将不再满足该限制。

# mysql index key max length
The index key prefix length limit is 3072 bytes for InnoDB tables that use DYNAMIC or COMPRESSED row format.

The index key prefix length limit is 767 bytes for InnoDB tables that use the REDUNDANT or COMPACT row format. For example, you might hit this limit with a column prefix index of more than 191 characters on a TEXT or VARCHAR column, assuming a utf8mb4 character set and the maximum of 4 bytes for each character.

Attempting to use an index key prefix length that exceeds the limit returns an error.

If you reduce the InnoDB page size to 8KB or 4KB by specifying the innodb_page_size option when creating the MySQL instance, the maximum length of the index key is lowered proportionally, based on the limit of 3072 bytes for a 16KB page size. That is, the maximum index key length is 1536 bytes when the page size is 8KB, and 768 bytes when the page size is 4KB.

The limits that apply to index key prefixes also apply to full-column index keys.

# postgresql index key max length
The maximum length for a value in a B-tree index, which includes primary keys, is one third of the size of a buffer page, by default floor(8192/3) = 2730 bytes.

# oracle index key max length
ora-1450 max key length 80% block size, e.g. 6389 bytes for 8K block .

note:如果必须使用长值作为键，那么说明您的设计就很糟糕 ~_~!

Reference
https://dev.mysql.com/blog-archive/mysql-8-0-when-to-use-utf8mb3-over-utf8mb4/

https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-utf8mb3.html

打赏

微信扫一扫，打赏作者吧～