MySQL Error 1366

Error number

1366

SQLSTATE

HY000

Symbol

ER_TRUNCATED_WRONG_VALUE_FOR_FIELD

Product

MySQL Server

Versions

MySQL 5.7, 8.0 and 8.4 (an error under strict sql_mode, the default since 5.7; a warning without it)

Category

Data Values & Strict Mode

Last reviewed

24 October 2026

What it means

Error 1366 means a value could not be converted to the column's type or character set, such as text in a number column or an emoji in a utf8mb3 column, so strict mode rejected the statement.

MySQL converts every value to the type of the column it is written to. When a string has no usable number in it ('abc', 'n/a', an empty string '', or a formatted amount such as '1,234.50'), or when a string contains characters the column's character set cannot hold, the conversion fails. Under strict sql_mode, which MySQL 8.0 enables by default (STRICT_TRANS_TABLES), the statement is stopped with 1366 and no row is changed. The message names the target type, the rejected value, the column and the row number within the statement.

Incorrect string value is the character set form. The value is shown as bytes, for example '\xF0\x9F\x98\x80', the UTF-8 encoding of an emoji. Characters outside the Basic Multilingual Plane, such as emoji, need four bytes; a column in utf8mb3 (also called utf8) stores at most three bytes per character and a latin1 column holds far fewer characters, so they cannot be stored. The fix is a utf8mb4 column, the MySQL 8.0 default.

Close relatives: a string with a number at the start and junk after it, such as '12abc', raises ERROR 1265 (01000): Data truncated for column instead, and a bad date raises 1292. A number that is too big for the column is 1264, and a string that is too long is 1406.

Common causes

• A form or API sends text, a placeholder such as 'n/a', or an empty string '' for a number field instead of a number or NULL.
• Numbers formatted for people, with thousands separators, currency symbols or a decimal comma ('1,234.50', 'GBP 10', '10,5'), are written to INT or DECIMAL columns.
• An import or INSERT ... SELECT copies a text column from a staging table into a numeric column, and some rows are not numbers.
• ALTER TABLE changes a VARCHAR column to INT or DECIMAL while some existing rows hold non-numeric text.
• Emoji or other 4-byte characters are written to a column, table or database created with utf8mb3 (utf8), or text in another script is written to a latin1 column.

Diagnose it

Read-only These statements only read metadata (information_schema, performance_schema and SHOW statements) or your own data. They do not change data, privileges or configuration, so they are safe to run first.

SELECT column_name, column_type, character_set_name, collation_name
FROM information_schema.columns
WHERE table_schema = DATABASE()
  AND table_name = 'reviews'
ORDER BY ordinal_position;

SELECT id, qty_text
FROM staging_items
WHERE qty_text NOT REGEXP '^[[:space:]]*-?[0-9]+[[:space:]]*$'
   OR qty_text IS NULL;

SELECT id, body
FROM staging_reviews
WHERE body REGEXP '[^\\x{0000}-\\x{FFFF}]';

SHOW SESSION VARIABLES LIKE 'character_set%';
SELECT @@SESSION.sql_mode;

How to fix it

Each fix is labelled with what it changes. Try read-only checks and the least invasive fix first, and test changes outside production.

INSERT INTO order_items (id, qty, price) VALUES (1, 3, 1234.50);
INSERT INTO order_items (id, qty, price) VALUES (2, NULL, NULL);  -- unknown, not ''

INSERT INTO items (id, qty)
SELECT id, NULLIF(TRIM(qty_text), '')
FROM staging_items
WHERE qty_text REGEXP '^[[:space:]]*-?[0-9]+[[:space:]]*$'
   OR TRIM(qty_text) = '';

UPDATE products
SET stock = NULL
WHERE stock NOT REGEXP '^[0-9]+$';

ALTER TABLE products MODIFY stock INT;

ALTER TABLE reviews
    CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

Examples

Text in an integer column

CREATE TABLE order_items (
    id  INT PRIMARY KEY,
    qty INT
);

INSERT INTO order_items (id, qty) VALUES (1, 'abc');

ERROR 1366 (HY000): Incorrect integer value: 'abc' for column 'qty' at row 1

A formatted amount in a DECIMAL column

CREATE TABLE order_items (
    id    INT PRIMARY KEY,
    price DECIMAL(10,2)
);

INSERT INTO order_items (id, price) VALUES (1, '1,234.50');  -- thousands separator

ERROR 1366 (HY000): Incorrect decimal value: '1,234.50' for column 'price' at row 1

An emoji in a utf8mb3 column

CREATE TABLE also returns Warning 1287 that utf8mb3 is deprecated. Typing the emoji itself in the statement gives the same error.

CREATE TABLE reviews (
    id   INT PRIMARY KEY,
    body VARCHAR(200)
) CHARACTER SET utf8mb3;

-- _utf8mb4 X'F09F9880' is the emoji U+1F600, written as bytes so the script stays plain text
INSERT INTO reviews VALUES (1, CONCAT('Great product ', _utf8mb4 X'F09F9880'));

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

Changing a text column to INT when one row is not a number

CREATE TABLE products (
    id    INT PRIMARY KEY,
    stock VARCHAR(20)
);
INSERT INTO products VALUES (1, '12'), (2, '5'), (3, 'unknown');

ALTER TABLE products MODIFY stock INT;

ERROR 1366 (HY000): Incorrect integer value: 'unknown' for column 'stock' at row 3

How to prevent it

Validate types at the edge of the application: convert form input to numbers or NULL before it reaches SQL, bind parameters with the right type, and load imports into a text staging table first so you can find the bad rows with a query rather than an error. Create new tables, databases and connections in utf8mb4, and convert old utf8mb3 or latin1 tables before users start sending emoji.

Keep strict mode on. With sql_mode empty, the same INSERT succeeds with a warning and stores 0 for 'abc', and an emoji becomes ?: the data is silently wrong and nobody is told. If you test non-strict behaviour, use SET SESSION sql_mode in a test session only.

Common questions

Learn more on CodeWithSQL

• SQL data types explained
• CAST function

Official documentation

• MySQL 8.0 server error message reference
• Server SQL modes (strict mode)
• The utf8mb4 character set
• Converting between 3-byte and 4-byte Unicode character sets

Last technically reviewed 24 October 2026; examples and diagnostics run on MySQL 8.0.46. Message templates checked against the server's error catalogue (perror) for that version.