在 MySQL 中使用 utf8mb4
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28540772/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using utf8mb4 in MySQL
提问by Tiny
In order to use 4-byte utf8mb4in MySQL (5.6.11), I have set the following variables in the my.inifile (my.cnfis not found). This file is located in a hidden folder named Application Data(C:\Documents and Settings\All Users\Application Data\MySQL\MySQL Server 5.6) on Windows XP. It is not available under the installation directory.
为了utf8mb4在 MySQL (5.6.11) 中使用 4 字节，我在my.ini文件中设置了以下变量（my.cnf未找到）。此文件位于Windows XP 上名为Application Data( C:\Documents and Settings\All Users\Application Data\MySQL\MySQL Server 5.6)的隐藏文件夹中。安装目录下没有。
[client]
port=3306
default-character-set=utf8mb4
[mysql]
default-character-set=utf8mb4
[mysqld]
init-connect='SET NAMES utf8mb4'
collation_server=utf8mb4_unicode_ci
character_set_server=utf8mb4
And then issuing the following command,
然后发出以下命令，
SHOW VARIABLES
WHERE Variable_name
LIKE 'character\_set\_%'
OR Variable_name LIKE 'collation%';
still displays the following list.
仍然显示以下列表。
From the picture itself, it is clear that several variables are still using 3-byte utf8.
从图片本身可以看出，有几个变量仍然使用 3-byte utf8。
Before doing this, the following command had already been issued to make corresponding changes to the database itself.
在执行此操作之前，已发出以下命令对数据库本身进行相应更改。
ALTER DATABASE database_name
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;
And the following command had also been issued on each and every table in the said database.
并且还对所述数据库中的每个表发出了以下命令。
ALTER TABLE table_name
CONVERT TO CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;
Nevertheless, what is the reason why some variables have not yet been set to the said character set as well as the collation? What is missing?
尽管如此，为什么某些变量尚未设置为所述字符集以及排序规则？有什么不见了？
The system (operating system) itself was restarted after every single task specified above had been carried out.
系统（操作系统）本身在上面指定的每一个任务都执行后重新启动。
采纳答案by deceze
The clientusually sets these values when connecting. The settings in my.ini are merely defaults which apply when the client does not explicitly specify a connection encoding. Since they're unreliable, every client shouldspecify a connection encoding. Since you've got some fancy screenshot there I'll guess that you're connecting with some GUI utility which probably explicitly doesset some connection encodings.
该客户端通常连接时设置这些值。my.ini 中的设置只是在客户端未明确指定连接编码时应用的默认值。由于它们不可靠，因此每个客户端都应指定连接编码。既然你已经有了一些花哨的屏幕截图时，我会想，你有这可能明确一些GUI工具连接并设置一些连接编码。
PHP example of setting a connection charset:
设置连接字符集的 PHP 示例：
new PDO('mysql:host=localhost;charset=utf8mb4')
回答by smndiaye
If you show your global variables, you might see that all your settings are correct actually.
如果您显示全局变量，您可能会看到所有设置实际上都是正确的。
SHOW GLOBAL VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
SHOW GLOBAL VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
This might be related to this bugI also faced the same problem in the past. I changed a DB charset from utf8 to utf8mb4. Executing queries directly from mysql command line was ok but I had problem inserting emojis with Workbench. I ended up setting it manually by running
这可能与这个错误有关， 我过去也遇到过同样的问题。我将数据库字符集从 utf8 更改为 utf8mb4。直接从 mysql 命令行执行查询没问题，但我在使用 Workbench 插入表情符号时遇到问题。我最终通过运行手动设置它
SET NAMES 'utf8mb4'
SET NAMES 'utf8mb4'
every time I open a connection to my DB with Workbench.
每次我使用 Workbench 打开到我的数据库的连接时。
Using Sequel Proas alternative was fine as well.
使用Sequel Pro作为替代也很好。
回答by Rick James
I think you are connecting as root, hence the init-connect='SET NAMES utf8mb4'is not executed.
我认为您以 root 身份连接，因此init-connect='SET NAMES utf8mb4'未执行。
It is unwise to use root (or SUPER) for any application code; just for administrative actions.
对任何应用程序代码使用 root（或 SUPER）是不明智的；仅用于行政行为。
回答by idaren
On win7
在win7上
use "win + R" and type "services.msc"
find service of mysql
check the file path. It will tell you where the my.ini
open and add some properties:
[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
restart the mysql service
使用“win + R”并输入“services.msc”
找到mysql的服务
检查文件路径。它会告诉你 my.ini 在哪里
打开并添加一些属性：
[客户端] 默认字符集 = utf8mb4
[mysql] 默认字符集 = utf8mb4
[mysqld] 字符集客户端握手 = FALSE 字符集服务器 = utf8mb4 校对服务器 = utf8mb4_unicode_ci
重启mysql服务
