MySQL: Error 1264 Out of range value for a column [Solved]

In this article, we will discuss why Error 1264 occurs and how to resolve it. 

Table of Contents

• Introduction
• Error code 1264. out of range value for column at row 1
• Error code 1264. out of range value for column decimal

Introduction

MySQL server throws the error 1264 if the MySQL query or statement stores a value in a numeric column outside the permissible range of the column data type. This error will occur if strict SQL mode is enabled; otherwise, the MySQL server will clip the value to an appropriate endpoint value of the column data type range.

Let us look into a few examples to have some more clarity. We will get started by making a sample table student_enroll_data.

#create the table
CREATE TABLE student_enroll_data (
student_id INT,
student_name VARCHAR(50),
enroll_date DATE,
student_ssn_no INT,
fee_submitted DECIMAL(10,2)
);
SELECT * FROM student_enroll_data;

Action Output Message Response:-

0 row(s) returned.

Output:-

Error code 1264. out of range value for column at row 1

The table student_enroll_data is empty at the moment. Let us try to insert some rows.

INSERT INTO student_enroll_data(student_id,student_name,enroll_date,student_ssn_no,fee_submitted) 
VALUES(1,"Daniel",'2021-12-12',3147483647,12378.90),
(2,"Sandy",'2021-10-12',1147483788,14578.90),
(3,"Ryma",'2021-11-22',1147483789,22378.90),
(4,"Jennifer",'2021-12-02',1147483790,12378.90),
(5,"Austin",'2021-11-12',1147483791,12378.90),
(6,"George",'2021-10-10',1147483792,12788.90),
(7,"Veronica",'2021-02-13',1147483793,12378.90);

Action Output Message Response:-

Error Code: 1264. Out of range value for column ‘student_ssn_no’ at row 1.

We got the error 1264 because we are trying to save the value 3147483647, which is out of range for INT in the first row.

If we notice the datatype of the student_ssn_no is INT, and the error occurred because the value of this column in row 1 is more than the maximum value of INT. Here we are trying to save value 3147483647, which is more than the maximum INT value: 2147483647.

To fix the issue, we will have to store a less than 2147483647 or change the data type of student_ssn_no to BIGINT. Let us modify the column to BIGINT and try inserting the data again.

#Alter the table, modify the column student_ssn_no
ALTER TABLE student_enroll_data MODIFY student_ssn_no BIGINT ;
#insert rows into the table
INSERT INTO student_enroll_data(student_id,student_name,enroll_date,student_ssn_no,fee_submitted) 
VALUES(1,"Daniel",'2021-12-12',3147483647,12378.90),
(2,"Sandy",'2021-10-12',1147483788,14578.90),
(3,"Ryma",'2021-11-22',1147483789,22378.90),
(4,"Jennifer",'2021-12-02',1147483790,12378.90),
(5,"Austin",'2021-11-12',1147483791,12378.90),
(6,"George",'2021-10-10',1147483792,12788.90),
(7,"Veronica",'2021-02-13',1147483793,12378.90);

Let us confirm what got inserted by executing:

SELECT * FROM student_enroll_data;

Output:-

As we can see in the above image, student_ssn_no for row 1 got inserted without any error now.

Error code 1264. out of range value for column decimal

This section will see why the error code 1264 occurs for the columns with the decimal data type and the solution.

Again let us try to insert one more row into the table student_enroll_data.

INSERT INTO student_enroll_data(student_id,student_name,enroll_date,student_ssn_no,fee_submitted) 
VALUES(8,"Lorem ipsum",'2021-08-12',1147483794, 111111111.12);

Action Output Message Response:-

Error Code: 1264. Out of range value for column ‘fee_submitted’ at row 1

Instead of the row getting inserted, we got the error 1264 for the value 111111111.12 going out of range for the column fee_submitted. If we notice the datatype of the fee_submitted is DECIMAL(10,2).

DECIMAL(10,2) means that 10 digits can be allowed, including the two decimal places. Here, we are trying to insert a value that has 11 digits. Therefore only the values with a maximum of 8 digits before the decimal will be allowed. Let us change the value of the column fee_submitted to 99999999.12 and observe the difference.

INSERT INTO student_enroll_data(student_id,student_name,enroll_date,student_ssn_no,fee_submitted) 
VALUES(8,"Lorem ipsum",'2021-08-12',1147483794, 99999999.12);

Action Output Message Response:-

1 row(s) affected.

SELECT * FROM student_enroll_data;

Output:-

As we can see in figure 3, the row is inserted successfully.

Another solution is to modify the data type of the column fee_submitted to DECIMAL(11,2) which will allow 11 digits. We will change the datatype of fee_submitted and try inserting the row again.

#modify the datatype of column fee_submitted
ALTER TABLE student_enroll_data MODIFY fee_submitted DECIMAL(11,2) ;
#Insert the row
INSERT INTO student_enroll_data(student_id,student_name,enroll_date,student_ssn_no,fee_submitted) 
VALUES(9,"Lorem ipsum",'2021-08-12',1147483794, 123456789.12);

Action Output Message Response:-

1 row(s) affected.

SELECT * FROM student_enroll_data WHERE student_id = 9;

Output:-

As shown in figure 4, the value 123456789.12 is inserted successfully.

We hope this article helped you understand and resolve Error 1264 out of range value for a column. Good Luck!!!