How to Fix Incorrect String Value Error in MySQL

The Incorrect string value error in MySQL happens when you try to insert characters that don't match the column's character set. To fix it, change the column or database character set to utf8mb4 which supports all Unicode characters, and ensure your connection uses the same encoding.

Why This Happens

This error occurs because MySQL cannot store certain characters in a column that uses a limited character set like latin1 or utf8 (which is not full Unicode). For example, emojis or some special symbols require utf8mb4 encoding. When you try to insert such characters, MySQL rejects them and shows the error.

sql

CREATE TABLE messages (
  id INT PRIMARY KEY AUTO_INCREMENT,
  text VARCHAR(100) CHARACTER SET latin1
);

INSERT INTO messages (text) VALUES ('Hello 😊');

Output

ERROR 1366 (HY000): Incorrect string value: '\xF0\x9F\x98\x8A' for column 'text' at row 1

The Fix

Change the column's character set to utf8mb4 which supports all Unicode characters including emojis. Also, set the connection character set to utf8mb4 to avoid mismatches.

sql

ALTER TABLE messages MODIFY text VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Then insert the emoji again
INSERT INTO messages (text) VALUES ('Hello 😊');

-- To ensure connection uses utf8mb4
SET NAMES utf8mb4;

Output

Query OK, 0 rows affected (0.01 sec)

Query OK, 1 row affected (0.00 sec)

Prevention

Always use utf8mb4 character set for your database, tables, and columns if you expect to store any Unicode characters beyond basic multilingual plane (like emojis). Also, configure your client and connection to use utf8mb4 to avoid encoding mismatches. This prevents the error from happening in the first place.

Related Errors

Similar errors include Truncated incorrect DOUBLE value when inserting wrong data types, or Data too long for column when string length exceeds column size. Fixes usually involve adjusting data types, sizes, or character sets.

Key Takeaways

Use utf8mb4 character set for full Unicode support in MySQL.

Set your connection encoding to utf8mb4 to match the database.

Change existing columns from latin1 or utf8 to utf8mb4 to fix insertion errors.

Always plan for Unicode if your app handles emojis or special characters.

Check and align client, connection, and server character sets to avoid mismatches.