MySQL Error 1406: Data Too Long for Column

MySQL Error 1406 (SQLSTATE 22001, ER_DATA_TOO_LONG) means a value exceeds what its target column can store. This guide focuses on CHAR and VARCHAR values, where the declared length is measured in characters but storage uses bytes according to the character set. See the MySQL error reference.

Check the column and SQL mode

Inspect the column’s declared type, length, character set, and other attributes:

SHOW CREATE TABLE labels;
SELECT @@SESSION.sql_mode;

For CHAR(M) and VARCHAR(M), M is a character limit. The effective size is also constrained by the table’s row-size limit and character set. MySQL 8.4 enables strict SQL mode by default, but the session mode can be changed. With strict mode, overlength non-space text is rejected; without it, MySQL can truncate the value and issue a warning. Trailing-space handling has additional CHAR/VARCHAR rules.

Measure characters and bytes

SELECT
  CHAR_LENGTH('海豚') AS character_count,
  LENGTH('海豚') AS byte_count;

CHAR_LENGTH() counts characters; LENGTH() counts bytes in the string’s character set. Use the character count when checking a CHAR(M) or VARCHAR(M) limit. For a staged import, find rows over a 20-character limit with:

SELECT row_id, CHAR_LENGTH(label) AS character_count
FROM import_staging
WHERE CHAR_LENGTH(label) > 20;

Choose a safe fix

• Correct or validate an accidentally overlong input value before inserting it.
• Increase the column length if the application’s data model needs longer values. Preserve the column’s existing type, character set, collation, nullability, default, and other attributes when altering it.
• Use a text column for genuinely long free-form content when that fits the schema and query requirements.

Do not turn off strict SQL mode just to silence the error: truncation can lose input data. For type-specific length and trailing-space rules, see MySQL CHAR and MySQL VARCHAR. For insert syntax and related insert errors, see MySQL INSERT.