阅读中文版本

MySQL ERROR 1406 — Data Too Long for Column

Fix MySQL 1406 by widening the column, validating input length, and making column characters match the UTF-8 byte budget.

mysql -e "SHOW CREATE TABLE posts\G" | grep -i title

mysql -e "SELECT CHAR_LENGTH('...'), LENGTH('...')"

Symptoms

This guide profiles the failure behind “MySQL ERROR 1406 — Data Too Long for Column”. It walks through the observable symptoms, a reproducible diagnosis path, the underlying root cause, the concrete fix, and how to verify the change and prevent a regression.

Observed symptoms include:

• ERROR 1406 (22001): Data too long for column ‘title’ at row 1
• Insert works for short strings and fails for long ones
• The value fits visually but is rejected under UTF-8

Reproduce and Diagnose

Reproduce the failure in isolation before changing any code. Work through the checks below in order; each one narrows the search space.

Step 1: Read the column and length

mysql -e "SHOW CREATE TABLE posts\G" | grep -i title

The declared length tells you the exact limit being exceeded.

Step 2: Measure the value length

mysql -e "SELECT CHAR_LENGTH('...'), LENGTH('...')"

CHAR_LENGTH counts characters while LENGTH counts bytes, revealing UTF-8 overhead.

Likely contributors, in order of frequency:

• The string exceeds the column VARCHAR length in characters
• A multi-byte character set makes the byte count exceed an index or column limit
• Strict SQL mode rejects truncation instead of warning

Root Cause

In strict mode MySQL rejects a value longer than the column definition rather than truncating it. VARCHAR length is in characters, but the storage and index budget are in bytes, so multi-byte content can still exceed limits.

The Fix

Widen the column (VARCHAR/TEXT), validate input length before insert, and account for character-set bytes when sizing indexed columns.

Watch out: Silently truncating in non-strict mode loses data without error; keep strict mode on and handle length at the application boundary.

ALTER TABLE posts MODIFY title VARCHAR(500) NOT NULL;

-- validate before insert
INSERT INTO posts (title) SELECT ? WHERE CHAR_LENGTH(?) <= 500;

-- prefer TEXT for arbitrary length
-- ALTER TABLE posts MODIFY title TEXT;

Verify and Prevent

Insert the previously failing value and confirm success; verify the stored value equals the input (no truncation).

Once verified, add a regression test that reproduces the original symptom so the failure cannot silently return.

This class of failure is usually environmental rather than pure logic, so capture the exact command, the environment variables, and the working directory in your bug report. Reproduce it in the smallest possible script, then widen the scope only once the minimal case is understood. A short written checklist for this failure mode saves hours the next time it appears.