php 错误 1366 (HY000)：字符串值不正确：第 1 行的“评论”列的“\xF0\x9F\x98\x9C”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/34165523/
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
ERROR 1366 (HY000): Incorrect string value: '\xF0\x9F\x98\x9C' for column 'comment' at row 1
提问by jkushner
Here's my sql:
这是我的sql：
INSERT INTO comments (createdate,userid,profileid,comment,status)
VALUES (1449503167,65704,65704,'@Mr_S66 Wish I was There For The Xmas Party I Miss My Studio 66 Family e???',15)
Here's my comments schema:
这是我的评论模式：
+------------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+----------------+
| commentid | int(11) | NO | PRI | NULL | auto_increment |
| parentid | int(11) | YES | | 0 | |
| refno | int(11) | YES | | 0 | |
| createdate | int(11) | YES | | 0 | |
| remoteip | varchar(80) | YES | | | |
| locid | int(11) | YES | MUL | 0 | |
| clubid | int(11) | YES | | 0 | |
| profileid | int(11) | YES | MUL | 0 | |
| userid | int(11) | YES | MUL | 0 | |
| legacyuser | int(11) | YES | MUL | 0 | |
| mediaid | int(11) | YES | | 0 | |
| status | int(11) | YES | | 1 | |
| comment | varchar(4000) | YES | | | |
| likes | int(11) | YES | | 0 | |
| dislikes | int(11) | YES | | 0 | |
| import | int(11) | YES | | 0 | |
| author | varchar(50) | YES | | | |
+------------+---------------+------+-----+---------+----------------+
Heres my output of the sql query:
这是我的输出sql query：
ERROR 1366 (HY000): Incorrect string value: '\xF0\x9F\x98\x9C' for column 'comment' at row 1
错误 1366 (HY000)：字符串值不正确：第 1 行的“评论”列的“\xF0\x9F\x98\x9C”
Not quite sure how to solve this yet. Possibly filter the comment text using phpto accommodate the string value.
还不太确定如何解决这个问题。可能过滤注释文本using php以适应字符串值。
回答by duskwuff -inactive-
Something in your environment is not set up to correctly process Unicode text.
您环境中的某些内容未设置为正确处理 Unicode 文本。
The byte sequence F0 9F 98 9C, represented incorrectly as "e???" in your query, is the UTF8 encoding of the Unicode character "", FACE WITH STUCK-OUT TONGUE AND WINKING EYE. (That is, it's an emoji character.)
字节序列F0 9F 98 9C，错误地表示为“e???” 在您的查询中，是 Unicode 字符“”的 UTF8 编码，FACE WITH STUCK-OUT TONGUE AND WINKING EYE。（也就是说，它是一个表情符号。）
To store this character correctly, you will need to make sure that:
要正确存储此字符，您需要确保：
- You are enabling UTF8 on your MySQL connection (i.e,
SET NAMES utf8mb4, or use an option when connecting that similarly enables it). - You are running MySQL 5.5 or later.
- Your table's character set is
utf8mb4.
- 您正在 MySQL 连接上启用 UTF8（即
SET NAMES utf8mb4，或在连接时使用类似启用它的选项）。 - 您正在运行 MySQL 5.5 或更高版本。
- 您的表的字符集是
utf8mb4.
回答by thelightings
Change connection to mysql from SET NAMES utf8 to SET NAMES utf8mb4
将到 mysql 的连接从 SET NAMES utf8 更改为 SET NAMES utf8mb4
回答by Sujay Kumar
Change connection to mysql from "SET NAMES utf8" to "SET NAMES utf8mb4"
将与 mysql 的连接从“SET NAMES utf8”更改为“SET NAMES utf8mb4”
In PHP, use mysqli_set_charset to add charset, https://www.w3schools.com/php/func_mysqli_set_charset.asp
PHP中使用mysqli_set_charset添加字符集，https://www.w3schools.com/php/func_mysqli_set_charset.asp
$conn = mysqli_connect("localhost","my_user","my_password","my_db");
if (mysqli_connect_errno()) {
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
// Change character set to utf8
mysqli_set_charset($conn, ”utf8mb4”);
Or if you are in NodeJS, (This is extra information, just in case)
或者如果你在 NodeJS 中，（这是额外的信息，以防万一）
db_config = {
host: "localhost",
user: "user",
password: "password",
database: "mydb",
charset: "utf8mb4_unicode_ci"
}
var conn = mysql.createConnection(db_config)
Also, make sure the column of the table and the Table itself is of same uf8mb4 encoding.
另外，请确保表的列和表本身具有相同的 uf8mb4 编码。
ALTER TABLE my_table CONVERT TO CHARACTER SET utf8mb4;
ALTER TABLE my_table
CHANGE COLUMN my_column my_column TEXT
CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
回答by ytdm
to save emoji characters you have to set your data field to utf8m4b because mysql utf8 only uses 3 bytes to store character. Check my solution at https://stackoverflow.com/a/36274546/3792270
要保存表情符号字符，您必须将数据字段设置为 utf8m4b，因为 mysql utf8 仅使用 3 个字节来存储字符。在https://stackoverflow.com/a/36274546/3792270查看我的解决方案
回答by Emanuel
If nothing works in mysql shell, try to change some settings in mysqld.cnf
如果在 mysql shell 中没有任何效果，请尝试更改 mysqld.cnf 中的一些设置
[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
Original answer MySQL utf8mb4, Errors when saving EmojisCredits to: user3624198
原始答案MySQL utf8mb4, Errors when Saving EmojisCredits to: user3624198
