MySQL error 1366: Incorrect value for column
A value can't be stored in the column's type or character set: text in a numeric column, an invalid date, or characters such as emoji that utf8mb3 can't hold.
- Error number
1366- Name
ER_TRUNCATED_WRONG_VALUE_FOR_FIELD- SQLSTATE
HY000- Category
- Data values
What it means
MySQL couldn't convert the value to the column's type. The message gives the type, the value, the column and the row number. With string columns the type shown is string and the value is shown as bytes like \xF0\x9F\x98\x80, which means the characters don't fit the column's character set.
In strict mode this is an error; in non-strict mode MySQL stores a converted or truncated value with a warning.
Common causes
- Emoji or other 4-byte characters stored in a
utf8(that is,utf8mb3) orlatin1column. - An empty string
''inserted into anINT,DECIMALorDATEcolumn. - Text such as
N/Aor1,234in a numeric column, often from a CSV import. - The connection's character set doesn't match the data sent.
How to fix it
-
For character errors, convert the table and connection to
utf8mb4.ALTER TABLE comments CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; SET NAMES utf8mb4;
-
For numbers and dates, send
NULLinstead of''and clean values before inserting.INSERT INTO products (name, price) VALUES ('Widget', NULLIF(@price, '')); -
When loading files, convert values in
LOAD DATAwithSET.LOAD DATA INFILE '/tmp/products.csv' INTO TABLE products FIELDS TERMINATED BY ',' (name, @price) SET price = NULLIF(REPLACE(@price, ',', ''), '');
Related errors
1292Truncated incorrect value1406Data too long for column1265Data truncated for column1264Out of range value for column245SQL Server: Conversion failed converting a value22P02PostgreSQL: invalid input syntax for typeORA-01722Oracle: invalid number
More data values errors
The code and message text are from the MySQL 8.4 Error Message Reference. Browse the full MySQL error list or search every database error.
Work on MySQL in SQLly
A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for MySQL and the other databases you use.