MySQL 如何在mysql中插入utf-8 mb4字符（ios5中的表情符号）？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7814293/
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
How to insert utf-8 mb4 character(emoji in ios5) in mysql?
提问by plenty
I am using mysql 5.5.10, and its character_sets are
我使用的是 mysql 5.5.10，它的字符集是
| 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 |
| character_sets_dir | /usr/share/mysql/charsets/ |
| collation_connection | utf8mb4_general_ci |
| collation_database | utf8mb4_general_ci |
| collation_server | utf8mb4_general_ci |
I changed utf8mb4 from utf8 for iOS5's emoji. They are represented by 4byte code.
我将 utf8mb4 从 utf8 更改为 iOS5 的表情符号。它们由 4 字节代码表示。
But when I inserted 3 smiley emojis, '???' is in mysql.
但是当我插入 3 个笑脸表情符号时，“？？？” 在mysql中。
They are 3F 3F 3F (Hex).
它们是 3F 3F 3F（十六进制）。
I can store iOS4's emojis well, but not iOS5's.
我可以很好地存储 iOS4 的表情符号，但不能很好地存储 iOS5 的。
How could I store iOS5's emojis?
我如何存储 iOS5 的表情符号？
Please help me.
请帮我。
回答by Nick Baicoianu
4 byte Unicode characters aren't yet widely used, so not every application out there fully supports them. MySQL 5.5 works fine with 4 byte characters when properly configured – check if your other components can work with them as well.
4 字节 Unicode 字符尚未广泛使用，因此并非所有应用程序都完全支持它们。正确配置后，MySQL 5.5 可以正常使用 4 字节字符 - 检查您的其他组件是否也可以使用它们。
Here's a few other things to check out:
这里有一些其他的事情要检查：
Make sure all your tables' default character sets and text fields are converted to utf8mb4, in addition to setting the client & server character sets, e.g.
ALTER TABLE mytable charset=utf8mb4, MODIFY COLUMN textfield1 VARCHAR(255) CHARACTER SET utf8mb4,MODIFY COLUMN textfield2 VARCHAR(255) CHARACTER SET utf8mb4;and so on.If your data is already in the utf8 character set, it should convert to utf8mb4 in place without any problems. As always, back up your data before trying!
Also make sure your app layer sets its database connections' character set to utf8mb4. Double-check this is actually happening?–?if you're running an older version of your chosen framework's mysql client library, it may not have been compiled with utf8mb4 support and it won't set the charset properly. If not, you may have to update it or compile it yourself.
When viewing your data through the mysql client, make sure you're on a machine that can display emoji, and run a
SET NAMES utf8mb4before running any queries.
确保所有表格的默认字符集和文本字段都转换为 utf8mb4，除了设置客户端和服务器字符集，例如
ALTER TABLE mytable charset=utf8mb4, MODIFY COLUMN textfield1 VARCHAR(255) CHARACTER SET utf8mb4,MODIFY COLUMN textfield2 VARCHAR(255) CHARACTER SET utf8mb4;等等。如果您的数据已经在 utf8 字符集中，它应该会转换为 utf8mb4 没有任何问题。与往常一样，在尝试之前备份您的数据！
还要确保您的应用层将其数据库连接的字符集设置为 utf8mb4。仔细检查这是否真的发生了？–？如果你运行的是你选择的框架的 mysql 客户端库的旧版本，它可能没有用 utf8mb4 支持编译，它不会正确设置字符集。如果没有，您可能需要更新它或自己编译它。
通过 mysql 客户端查看您的数据时，请确保您在一台可以显示表情符号的机器上，并
SET NAMES utf8mb4在运行任何查询之前运行a 。
Once every level of your application can support the new characters, you should be able to use them without any corruption.
一旦您的应用程序的每个级别都可以支持新字符，您应该能够使用它们而不会出现任何损坏。
回答by Mathias Bynens
I've recently written a detailed guide on how to switch from MySQL's utf8to utf8mb4. If you follow the steps there, everything should work correctly. Here are direct links to each individual step in the process:
我最近写了一篇关于如何从 MySQL 切换utf8到utf8mb4. 如果您按照那里的步骤操作，一切都应该正常工作。以下是流程中每个步骤的直接链接：
- Step 1: Create a backup
- Step 2: Upgrade the MySQL server
- Step 3: Modify databases, tables, and columns
- Step 4: Check the maximum length of columns and index keys
- Step 5: Modify connection, client, and server character sets
- Step 6: Repair and optimize all tables
Hope this helps.
希望这可以帮助。
