When you try to insert a new record into your MySQL database table, you may encounter an error saying
Incorrect string value along with some UTF-8 hex code for the description.
For example, suppose you create a
Test table with only one column as follows:
CREATE TABLE `Test` ( `names` varchar(255) )
Next, let’s insert the following Egyptian hieroglyph character into the table:
INSERT INTO Test VALUES('𓀀');
Your MySQL server may respond with the following error:
ERROR 1366 (HY000): Incorrect string value: '\xF0\x93\x80\x80' for column 'names' at row 1
The error above is because the character
𓀀 requires 4-bytes to be represented in UTF-8 encoding.
By default, MySQL databases and tables are created using a UTF-8 with 3-bytes encoding. You can see the encoding used for your table by using the
SHOW CREATE TABLE statement as follows:
SHOW CREATE TABLE Test \G
Here’s the result from my computer:
*************************** 1. row *************************** Table: Test Create Table: CREATE TABLE `Test` ( `names` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3
As you can see, the table uses the
DEFAULT CHARSET=utf8mb3 and the
names column uses
CHARACTER SET utf8.
utf8mb3 can’t store string values that contain a UTF-8 4-bytes character.
To store the values, you need to use the
utf8mb4 character set.
Here’s the query to alter your database, table, or column to
utf8mb4 character set:
-- Change a database ALTER DATABASE [database_name] CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci; -- Change a table ALTER TABLE [table_name] CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; -- Change a column ALTER TABLE [table_name] CHANGE [column_name] [column_name] VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
When you change the character set on the database level, then any new table you create for that database in the future will use that character set as the default encoding.
Returning to the
Test table, you can alter just the
names column to make the
INSERT statement works:
ALTER TABLE `Test` CHANGE `names` `names` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
Now you should be able to insert the character
𓁴 into the table:
INSERT INTO Test VALUES('𓁴'); -- Query OK, 1 row affected (0.00 sec)
By default, MySQL version 8 should use
utf8mb4 encoding and collation for your databases. If you see
utf8mb3, then you might be using MySQL version below 8 (MySQL version 5 may default to
When you encounter this error, pay attention to the characters that you want to insert into the database.
They may look like normal characters, but if you copy and paste them from some source, then they may have a strange encoding attached to them.
For example, the GOTHIC LETTER SAUIL
𐍃 looks like a normal capital
S but actually a
INSERT INTO Test VALUES('𐍃'); ERROR 1366 (HY000): Incorrect string value: '\xF0\x90\x8D\x83' for column 'names' at row 1
Alternatively, you can also pass the hex code (
\xF0\x90\x8D\x83 in the example above) into Google to look for the exact character that causes the error.
To conclude, the
ERROR 1366: Incorrect string value happens when MySQL can’t insert the value you specified into the table because of incompatible encoding.
You need to modify or remove characters that have
4-bytes UTF-8 encoding, or you can change the encoding and collation used by MySQL.
utf8 in MySQL always refers to
To use the
4-bytes UTF-8 encoding, it needs to be specified as
With this information, you should now be able to resolve this error. Feel free to use the provided
ALTER statements above if you need it 👍