// MySQL error reference

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
Error message
Incorrect %s value: '%s' for column '%s' at row %ld

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) or latin1 column.
  • An empty string '' inserted into an INT, DECIMAL or DATE column.
  • Text such as N/A or 1,234 in a numeric column, often from a CSV import.
  • The connection's character set doesn't match the data sent.

How to fix it

  1. 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;
  2. For numbers and dates, send NULL instead of '' and clean values before inserting.

    INSERT INTO products (name, price) VALUES ('Widget', NULLIF(@price, ''));
  3. When loading files, convert values in LOAD DATA with SET.

    LOAD DATA INFILE '/tmp/products.csv' INTO TABLE products
    FIELDS TERMINATED BY ','
    (name, @price)
    SET price = NULLIF(REPLACE(@price, ',', ''), '');

Related errors

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.

// fewer errors, faster fixes

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.