部署mysql db时出现问题（utf8mb4_unicode_520_ci - > utf8mb4_unicode_ci） [英] Issue when deploying mysql db (utf8mb4_unicode_520_ci -> utf8mb4_unicode_ci)
问题描述
我的问题是，在我的托管（mysql 5.5）utf8mb4_unicode_520_ci是无法识别为有效的编码。所以我正在尝试定位utf8mb4_unicode_ci编码，因为我的主机知道这一个，如果我理解正确，这将是 - 反对去utf8 - 允许我保持4个字节。
我尝试了几个不同的编码和排序规则，为db设置，但没有任何成功（从这里如何将整个MySQL数据库字符集和排序规则转换为UTF-8？）。
我在wp-config中尝试了几种编码和归类的组合，但没有任何内容。
数据库中的所有内容（如帖子标题并且发布内容显示所有患者的严重编码的字符，其他任何内容都会适当显示）
菜单标签从数据库显示不正确，其中hardcoded / trans标签显示正确
我想我需要转换数据库的实际内容，更改字符集和排序规则似乎不够。 >
我发现这个，但是它并没有直接解决我的问题，或者我没有想到它。
任何帮助将不胜感激
--------------------------------
更新：
这是我经历的确切程序：
初始情况：
我在本地安装了一个wordpress（4.6.1）（在我的dev机器上，mysql 5.6.28）。
我在本地工作的主题和插件
（在这一刻，我在本地，一个数据库是 utf8_general_ci 和表$ utf8mb4_unicode_520_ci
问题：
$ b $我想在我的主机上部署我的wordpress（mysql：5.5 - db collation似乎是
utf8mb4_unicode_ci ）我本地mysqldump db，尝试将其导入我的主机'phpmyadmin
这给错误：
未知排序规则：'utf8mb4_unicode_520_ci'
解决方案1将表charset更改为utf8mb4_unicode_ci：
在我的主机sql服务器上，utf8mb4_unicode_520_ci不是可用，我无法获得更新版本的mysql。
utf8mb4_unicode_ci似乎是最接近的，在我的主机sql服务器上可用。
从各种这样的问题，我调整一个bash脚本来改变我的表的字符集和整理
'pre>
在wp_sij2017_commentmeta wp_sij2017_comments wp_sij2017_cwa wp_sij2017_links wp_sij2017_options wp_sij2017_postmeta wp_sij2017_posts wp_sij2017_term_relationships wp_sij2017_term_taxonomy wp_sij2017_termmeta wp_sij2017_terms wp_sij2017_usermeta wp_sij2017_users wp_sij2017_woocommerce_api_keys wp_sij2017_woocommerce_attribute_taxonomies wp_sij2017_woocommerce_downloadable_product_permissions wp_sij2017_woocommerce_order_itemmeta wp_sij2017_woocommerce_order_items wp_sij2017_woocommerce_payment_tokenmeta wp_sij2017_woocommerce_payment_tokens wp_sij2017_woocommerce_sessions wp_sij2017_woocommerce_shipping_zone_locations wp_sij2017_woocommerce_shipping_zone_methods wp_sij2017_woocommerce_shipping_zones wp_sij2017_woocommerce_tax_rate_locations wp_sij2017_woocommerce_tax_rates TBL; do
mysql --execute =ALTER TABLE wp_sij_2017_original_copy。$ {tbl} CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
done
我在本地db上运行此脚本
我现在拥有我的表设置为排序规则 utf8mb4_unicode_ci
我的数据库排序规则仍然是 utf8
我mysqldump的数据库，然后导入到我的主机和...
导入是成功。
我在db中搜索并替换了siteurl。
然后我访问了在线网站，我得到了一些表现为问号的小数字
来自数据库的任何文本都具有解码问题在某些点
源/ html标记也有这些问号char
我不知道去哪里或下一步做什么
澄清： CHARACTER SETs utf8 和 utf8mb4 指定字符如何编码为字节。 COLLATIONs * _ unicode _ * 等，指定这些字符的比较。
utf8mb4_unicode_ci 和 utf8mb4_unicode_520_ci 的编码是相同的，因为它们被编码在字符集utf8mb4 中。
utf8_general_ci的数据库和utf8mb4_unicode_520_ci的表 - 这可能意味着 / em>表中，除非另有明确规定，否则将为 CHARACTER SET utf8 COLLATION utf8_general_ci 。那就是数据库设置只是 CREATE TABLE 的默认值。由于您的表已经是 CHARACTER SET utf8mb4 COLLATION utf8mb4_unicode_520_ci ，数据库默认与他们无关。
因为 CHARACTER SET 保持 utf8mb4 ，没有表情符号，中文等将丢失或以其他方式被破坏。
不要使用 mysql40 ;它不知道任何 CHARACTER SETs 。不要使用 CONVERT 或 CAST 。等等
我假定 520 来自 mysqldump ？你有编辑器可以处理大的文件吗？如果是这样，只需编辑它，将 utf8mb4_unicode_520_ci 更改为 utf8mb4_unicode_ci 。然后加载转储。问题解决了吗？
您的修正
您做过 ALTER ... CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci 在您的本地机器上。这可能是一个更好的方法 - 因为它将使您的开发和生产机器彼此一致。这应该是有效的。不要担心数据库的声明。
I started working on a wordpress on my dev machine. mysql version is 5.6, and worpdress is 4.7 so its already using the utf8mb4_unicode_520_ci encoding if it detects its possible.
My problem is that on my hosting (mysql 5.5) utf8mb4_unicode_520_ci is not recognized as a valid encoding. So I'm trying to target utf8mb4_unicode_ci encoding as my hosting knows about this one, and if I understand correctly, this would - in opposition to going to utf8 - allow me to keep the 4 bytes.
I tried several different combinaison of encoding and collation set up for the db, but nothing successful (from here How to convert an entire MySQL database characterset and collation to UTF-8?).
I tried several combination of encoding and collation in the wp-config, but nothing.
Everything that is coming from the database (like post titles and post contents displays badly encoded char for all diatrics, anything else is displayed appropriately )
I think I need to convert the actual content of the database, changing charset and collation does not seems to be enough.
I found this but it does not address my problem directly, or if it does I missed it.
Any help would be appreciated
————————————————————————————————
UPDATE :
here is the precise procedure I went through:
Initial situation:
I installed a wordpress (4.6.1) locally (on my dev machine, mysql 5.6.28). I worked on the theme and plugin locally
(at this moment I have, locally, a database that is utf8_general_ci and tables that are utf8mb4_unicode_520_ci
Problem:
I want to deploy my wordpress on my hosting (mysql: 5.5 - db collation seems to be utf8mb4_unicode_ci).
I mysqldump the db locally, then try to import it on my hostings' phpmyadmin.
This gives error :
Unknown collation: 'utf8mb4_unicode_520_ci'
solution 1 change the tables charset to utf8mb4_unicode_ci:
On my hosting sql server, utf8mb4_unicode_520_ci is not available and I can't get a more recent version of mysql.
utf8mb4_unicode_ci seems like the closest and is available on my hosting sql server.
from various so question, I adapt a bash script to change charset and collation of my tables
for tbl in wp_sij2017_commentmeta wp_sij2017_comments wp_sij2017_cwa wp_sij2017_links wp_sij2017_options wp_sij2017_postmeta wp_sij2017_posts wp_sij2017_term_relationships wp_sij2017_term_taxonomy wp_sij2017_termmeta wp_sij2017_terms wp_sij2017_usermeta wp_sij2017_users wp_sij2017_woocommerce_api_keys wp_sij2017_woocommerce_attribute_taxonomies wp_sij2017_woocommerce_downloadable_product_permissions wp_sij2017_woocommerce_order_itemmeta wp_sij2017_woocommerce_order_items wp_sij2017_woocommerce_payment_tokenmeta wp_sij2017_woocommerce_payment_tokens wp_sij2017_woocommerce_sessions wp_sij2017_woocommerce_shipping_zone_locations wp_sij2017_woocommerce_shipping_zone_methods wp_sij2017_woocommerce_shipping_zones wp_sij2017_woocommerce_tax_rate_locations wp_sij2017_woocommerce_tax_rates; do
mysql --execute="ALTER TABLE wp_sij_2017_original_copy.${tbl} CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"
done
I run this script on the local db
I now have all my tables set to collation utf8mb4_unicode_ci
My db collation is still utf8
I mysqldump the db, then import it to my hosting and...
Import is successful.
I search and replace siteurl in the db. I then visit the online website, I got SOME diatrics that renders a "question mark char"
Any text coming from the db has decoding issue AT SOME POINT
The source/html markup also has those "question mark char"
I have no idea where to look or what to do next
Clarification: CHARACTER SETs utf8 and utf8mb4 specify how characters are encoded into bytes. COLLATIONs *_unicode_*, etc, specify how those character compare.
The encoding for utf8mb4_unicode_ci and utf8mb4_unicode_520_ci are the same because they are encoded in the character set utf8mb4.
"database that is utf8_general_ci and tables that are utf8mb4_unicode_520_ci" -- that probably means that new tables in that database, unless specifically stated, will be CHARACTER SET utf8 COLLATION utf8_general_ci. That is the database setting is just a default for CREATE TABLE. Since your tables are already CHARACTER SET utf8mb4 COLLATION utf8mb4_unicode_520_ci, the database default is not relevant to them.
As long as the CHARACTER SET stays utf8mb4, no Emoji, Chinese, etc will be lost or otherwise mangled.
Do not use mysql40; it did not know about any CHARACTER SETs. Do not use CONVERT or CAST. Etc.
I assume the 520 is coming from the output of mysqldump? Do you have an editor that can handle a file that big? If so, simply edit it to change utf8mb4_unicode_520_ci to utf8mb4_unicode_ci throughout. Then load the dump. Problem solved?
Your fix
You did ALTER ... CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci on your local machine. That is probably an even better way -- since it will put your dev and prod machine in line with each other. That should have worked. Don't worry about what the "database" claims.
这篇关于部署mysql db时出现问题（utf8mb4_unicode_520_ci - > utf8mb4_unicode_ci）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
