ERROR 1366 (HY000): Incorrect string value: '\xF0\x9F\x98\x9C' for column 'comment' at row 1
Something in your environment is not set up to correctly process Unicode text.
The byte sequence F0 9F 98 9C
, represented incorrectly as "😜" 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.)
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
.
Change connection to mysql from SET NAMES utf8 to SET NAMES utf8mb4
Change connection to mysql from "SET NAMES utf8" to "SET NAMES utf8mb4"
In PHP, use mysqli_set_charset to add 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)
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.
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;