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

目录
    • 修改mysql/mariadb的配置文件,将utf8编码改为utf8mb4
    • 重启mysql/mariadb

      永远不要在 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。

      • 应该是二进制的,除非你使用的文件系统的文件名中支持多字节 utf-8 编码字符。
      • 始终是 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内容请搜索www.887551.com以前的文章或继续浏览下面的相关文章希望大家以后多多支持www.887551.com!

      (0)
      上一篇 2022年3月21日
      下一篇 2022年3月21日

      相关推荐