MySQL error 1264: Out of range value for column
A number is too large or too small for the column's type, such as 300 into TINYINT or a negative value into an UNSIGNED column.
- Error number
1264- Name
ER_WARN_DATA_OUT_OF_RANGE- SQLSTATE
22003- Category
- Data values
What it means
Every numeric type has a range: TINYINT is -128 to 127 (0 to 255 unsigned), INT about ±2.1 billion, and DECIMAL(5,2) up to 999.99. A value outside it fails with 1264 in strict mode, or is clipped to the nearest limit with a warning otherwise.
Common causes
- An
INTid or counter that outgrew its type. - Negative values in
UNSIGNEDcolumns, sometimes from subtraction in anUPDATE. - A
DECIMALwith too few digits before the decimal point.
How to fix it
-
Widen the column.
ALTER TABLE orders MODIFY id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT; ALTER TABLE products MODIFY price DECIMAL(10,2) NOT NULL;
-
Guard subtractions on unsigned columns.
UPDATE stock SET qty = GREATEST(CAST(qty AS SIGNED) - 5, 0) WHERE product_id = 7;
Reproduce it safely
CREATE TEMPORARY TABLE t (n TINYINT); INSERT INTO t VALUES (300); -- ERROR 1264 (22003)
Related errors
1366Incorrect value for column1406Data too long for column8115SQL Server: Arithmetic overflow error converting22003PostgreSQL: numeric value out of rangeORA-01438Oracle: value larger than specified precision
More data values errors
1364Field doesn't have a default value1292Truncated incorrect value1265Data truncated for column1067Invalid default value
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.