I've recently noticed that, when ever I start a new WordPress project, my tables' collation automatically changes from utf8_unicode_ci (which I select when I create a new DB from PhpMyAdmin) to utf8mb4_unicode_520_ci.
我最近注意到,当我开始一个新的WordPress项目时,我的表的排序自动从utf8_unicode_ci(我在从PhpMyAdmin创建新数据库时选择)变为utf8mb4_unicode_520_ci。
Also, I've noticed in PhpMyAdmin under General Settings that Server connection Collation defaults to utf8mb4_unicode_520_ci.
此外,我在PhpMyAdmin的常规设置下注意到,服务器连接排序规则默认为utf8mb4_unicode_520_ci。
I'm running MySQL Server 5.7.17 and PhpMyAdmin 4.6.6 on Ubuntu 17.04.
我在Ubuntu 17.04上运行MySQL Server 5.7.17和PhpMyAdmin 4.6.6。
My questions are following:
我的问题如下:
- Why is this happening?
- If possible, how do I prevent this? Because of utf8mb4 I've experienced problems when migrating WP sites to an older MySQL server which does not support it.
- Is point 2. advisable? Are there any benefits in using charset utf8mb4 over utf8, and collation utf8mb4_unicode_520_ci over utf8_unicode_ci?
为什么会这样?
如果可能,我该如何防止这种情况?由于utf8mb4,我在将WP站点迁移到不支持它的旧MySQL服务器时遇到了问题。
第2点是可取的吗?使用charset utf8mb4优于utf8,以及整理utf8mb4_unicode_520_ci超过utf8_unicode_ci有什么好处吗?
1 个解决方案
#1
17
In the past, there was only utf8; in the future, utf8mb4 will be the default character set.
在过去,只有utf8;将来,utf8mb4将成为默认字符集。
In the past, _general_ci was the default collation; then _unicode_ci (Unicode 4.0) was better, then _unicode_520_ci (Unicode 5.20). In the future (MySQL 8.0), the default will be _0900_ci_ai (Unicode 9.0).
在过去,_general_ci是默认排序规则;然后_unicode_ci(Unicode 4.0)更好,然后_unicode_520_ci(Unicode 5.20)。将来(MySQL 8.0),默认为_0900_ci_ai(Unicode 9.0)。
Meanwhile, the road is full of potholes generated by MySQL's past mistakes. And WP designers are driving in a big tank that does not notice the potholes.
与此同时,道路上充斥着MySQL过去的错误所产生的坑洼。 WP设计师驾驶着一辆没有注意到坑洼的大坦克。
MySQL 5.6 was a big pothole that swallowed up many a WP user because of a 767 limit on indexes together with WP indexes on the overly-long VARCHAR(255) and the possibility of using utf8mb4. You are well past it by having 5.7.17. (Your future move to 8.0 will be less bumpy.)
MySQL 5.6是一个巨大的坑洞,吞噬了许多WP用户,因为索引上的767限制以及过长的VARCHAR(255)上的WP索引以及使用utf8mb4的可能性。拥有5.7.17你已经远远超过了它。 (你将来的8.0会变得不那么坎坷。)
That is, newly created databases/tables/columns on 5.7.7+ should not experience the 767 problem, but things migrated from older versions (5.5.3+) may have issues, especially if something causes you to change to utf8mb4.
也就是说,5.7.7+上新创建的数据库/表/列不应该遇到767问题,但是从旧版本(5.5.3+)迁移的东西可能会有问题,特别是如果某些东西导致您更改为utf8mb4。
What to do? I'll probably run out of space trying to spell out all the options. So provide the history of the data, the upgrade path (if any), the current settings, the ROW_FORMAT of the tables, the CHARACTER SET and COLLATION of the columns, the output of SHOW VARIABLES LIKE 'char%';
该怎么办?我可能会用尽空间试图拼出所有选项。因此,提供数据的历史记录,升级路径(如果有),当前设置,表格的ROW_FORMAT,列的CHARACTER SET和COLLATION,SHOW VARIABLES LIKE'char%'的输出;
Where should you be? For 5.7.7+, utf8mb4 and utf8mb4_unicode_520_ci wherever practical. That charset gives you Emoji and all of Chinese (utf8 does not). That collation is the best available, although you might be hard pressed to notice where it matters.
你应该在哪里?适用于5.7.7 +,utf8mb4和utf8mb4_unicode_520_ci。那个charset给你表情符号和所有中文(utf8没有)。尽管您可能很难注意到它的重要性,但这种整理是最好的。
Note: the first part of the collation name is the only character set that it works with. That is utf8_unicode_ci does not work with utf8mb4.
注意:排序规则名称的第一部分是它使用的唯一字符集。这是utf8_unicode_ci不适用于utf8mb4。
#1
17
In the past, there was only utf8; in the future, utf8mb4 will be the default character set.
在过去,只有utf8;将来,utf8mb4将成为默认字符集。
In the past, _general_ci was the default collation; then _unicode_ci (Unicode 4.0) was better, then _unicode_520_ci (Unicode 5.20). In the future (MySQL 8.0), the default will be _0900_ci_ai (Unicode 9.0).
在过去,_general_ci是默认排序规则;然后_unicode_ci(Unicode 4.0)更好,然后_unicode_520_ci(Unicode 5.20)。将来(MySQL 8.0),默认为_0900_ci_ai(Unicode 9.0)。
Meanwhile, the road is full of potholes generated by MySQL's past mistakes. And WP designers are driving in a big tank that does not notice the potholes.
与此同时,道路上充斥着MySQL过去的错误所产生的坑洼。 WP设计师驾驶着一辆没有注意到坑洼的大坦克。
MySQL 5.6 was a big pothole that swallowed up many a WP user because of a 767 limit on indexes together with WP indexes on the overly-long VARCHAR(255) and the possibility of using utf8mb4. You are well past it by having 5.7.17. (Your future move to 8.0 will be less bumpy.)
MySQL 5.6是一个巨大的坑洞,吞噬了许多WP用户,因为索引上的767限制以及过长的VARCHAR(255)上的WP索引以及使用utf8mb4的可能性。拥有5.7.17你已经远远超过了它。 (你将来的8.0会变得不那么坎坷。)
That is, newly created databases/tables/columns on 5.7.7+ should not experience the 767 problem, but things migrated from older versions (5.5.3+) may have issues, especially if something causes you to change to utf8mb4.
也就是说,5.7.7+上新创建的数据库/表/列不应该遇到767问题,但是从旧版本(5.5.3+)迁移的东西可能会有问题,特别是如果某些东西导致您更改为utf8mb4。
What to do? I'll probably run out of space trying to spell out all the options. So provide the history of the data, the upgrade path (if any), the current settings, the ROW_FORMAT of the tables, the CHARACTER SET and COLLATION of the columns, the output of SHOW VARIABLES LIKE 'char%';
该怎么办?我可能会用尽空间试图拼出所有选项。因此,提供数据的历史记录,升级路径(如果有),当前设置,表格的ROW_FORMAT,列的CHARACTER SET和COLLATION,SHOW VARIABLES LIKE'char%'的输出;
Where should you be? For 5.7.7+, utf8mb4 and utf8mb4_unicode_520_ci wherever practical. That charset gives you Emoji and all of Chinese (utf8 does not). That collation is the best available, although you might be hard pressed to notice where it matters.
你应该在哪里?适用于5.7.7 +,utf8mb4和utf8mb4_unicode_520_ci。那个charset给你表情符号和所有中文(utf8没有)。尽管您可能很难注意到它的重要性,但这种整理是最好的。
Note: the first part of the collation name is the only character set that it works with. That is utf8_unicode_ci does not work with utf8mb4.
注意:排序规则名称的第一部分是它使用的唯一字符集。这是utf8_unicode_ci不适用于utf8mb4。