As I SET cust_fax in a table in MySQL like this:
cust_fax integer(10) NOT NULL,
and then I insert value like this:
INSERT INTO database values ('3172978990');
but then it say
`error 1264` out of value for column
And I want to know where the error is? My set? Or other?
Any answer will be appreciated!
yunzen
32.4k11 gold badges73 silver badges106 bronze badges
asked Jan 11, 2013 at 18:33
3
The value 3172978990 is greater than 2147483647 – the maximum value for INT – hence the error. MySQL integer types and their ranges are listed here.
Also note that the (10) in INT(10) does not define the «size» of an integer. It specifies the display width of the column. This information is advisory only.
To fix the error, change your datatype to VARCHAR. Phone and Fax numbers should be stored as strings. See this discussion.
answered Jan 11, 2013 at 18:37
Salman ASalman A
255k81 gold badges426 silver badges515 bronze badges
6
You can also change the data type to bigInt and it will solve your problem, it’s not a good practice to keep integers as strings unless needed. 
ALTER TABLE T_PERSON MODIFY mobile_no BIGINT;
Giacomo1968
25.4k11 gold badges70 silver badges100 bronze badges
answered Nov 23, 2015 at 15:11
4
You are exceeding the length of int datatype. You can use UNSIGNED attribute to support that value.
SIGNED INT can support till 2147483647 and with UNSIGNED INT allows double than this. After this you still want to save data than use CHAR or VARCHAR with length 10
answered Jan 11, 2013 at 18:39
Saharsh ShahSaharsh Shah
28.4k8 gold badges47 silver badges83 bronze badges
tl;dr
Make sure your AUTO_INCREMENT is not out of range. In that case, set a new value for it with:
ALTER TABLE table_name AUTO_INCREMENT=100 -- Change 100 to the desired number
Explanation
AUTO_INCREMENT can contain a number that is bigger than the maximum value allowed by the datatype. This can happen if you filled up a table that you emptied afterward but the AUTO_INCREMENT stayed the same, but there might be different reasons as well. In this case a new entry’s id would be out of range.
Solution
If this is the cause of your problem, you can fix it by setting AUTO_INCREMENT to one bigger than the latest row’s id. So if your latest row’s id is 100 then:
ALTER TABLE table_name AUTO_INCREMENT=101
If you would like to check AUTO_INCREMENT‘s current value, use this command:
SELECT `AUTO_INCREMENT`
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'DatabaseName'
AND TABLE_NAME = 'TableName';
answered Mar 17, 2019 at 0:37
totymedlitotymedli
28.3k22 gold badges131 silver badges160 bronze badges
1
Work with:
ALTER TABLE `table` CHANGE `cust_fax` `cust_fax` VARCHAR(60) NULL DEFAULT NULL;
answered Mar 5, 2020 at 2:54
Just table id column name delete and new id created with autoincrement then worked successfully.
answered Apr 10, 2022 at 5:34
1
As I SET cust_fax in a table in MySQL like this:
cust_fax integer(10) NOT NULL,
and then I insert value like this:
INSERT INTO database values ('3172978990');
but then it say
`error 1264` out of value for column
And I want to know where the error is? My set? Or other?
Any answer will be appreciated!
yunzen
32.4k11 gold badges73 silver badges106 bronze badges
asked Jan 11, 2013 at 18:33
3
The value 3172978990 is greater than 2147483647 – the maximum value for INT – hence the error. MySQL integer types and their ranges are listed here.
Also note that the (10) in INT(10) does not define the «size» of an integer. It specifies the display width of the column. This information is advisory only.
To fix the error, change your datatype to VARCHAR. Phone and Fax numbers should be stored as strings. See this discussion.
answered Jan 11, 2013 at 18:37
Salman ASalman A
255k81 gold badges426 silver badges515 bronze badges
6
You can also change the data type to bigInt and it will solve your problem, it’s not a good practice to keep integers as strings unless needed.
ALTER TABLE T_PERSON MODIFY mobile_no BIGINT;
Giacomo1968
25.4k11 gold badges70 silver badges100 bronze badges
answered Nov 23, 2015 at 15:11
4
You are exceeding the length of int datatype. You can use UNSIGNED attribute to support that value.
SIGNED INT can support till 2147483647 and with UNSIGNED INT allows double than this. After this you still want to save data than use CHAR or VARCHAR with length 10
answered Jan 11, 2013 at 18:39
Saharsh ShahSaharsh Shah
28.4k8 gold badges47 silver badges83 bronze badges
tl;dr
Make sure your AUTO_INCREMENT is not out of range. In that case, set a new value for it with:
ALTER TABLE table_name AUTO_INCREMENT=100 -- Change 100 to the desired number
Explanation
AUTO_INCREMENT can contain a number that is bigger than the maximum value allowed by the datatype. This can happen if you filled up a table that you emptied afterward but the AUTO_INCREMENT stayed the same, but there might be different reasons as well. In this case a new entry’s id would be out of range.
Solution
If this is the cause of your problem, you can fix it by setting AUTO_INCREMENT to one bigger than the latest row’s id. So if your latest row’s id is 100 then:
ALTER TABLE table_name AUTO_INCREMENT=101
If you would like to check AUTO_INCREMENT‘s current value, use this command:
SELECT `AUTO_INCREMENT`
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'DatabaseName'
AND TABLE_NAME = 'TableName';
answered Mar 17, 2019 at 0:37
totymedlitotymedli
28.3k22 gold badges131 silver badges160 bronze badges
1
Work with:
ALTER TABLE `table` CHANGE `cust_fax` `cust_fax` VARCHAR(60) NULL DEFAULT NULL;
answered Mar 5, 2020 at 2:54
Just table id column name delete and new id created with autoincrement then worked successfully.
answered Apr 10, 2022 at 5:34
1
As I SET cust_fax in a table in MySQL like this:
cust_fax integer(10) NOT NULL,
and then I insert value like this:
INSERT INTO database values ('3172978990');
but then it say
`error 1264` out of value for column
And I want to know where the error is? My set? Or other?
Any answer will be appreciated!
yunzen
32.4k11 gold badges73 silver badges106 bronze badges
asked Jan 11, 2013 at 18:33
3
The value 3172978990 is greater than 2147483647 – the maximum value for INT – hence the error. MySQL integer types and their ranges are listed here.
Also note that the (10) in INT(10) does not define the «size» of an integer. It specifies the display width of the column. This information is advisory only.
To fix the error, change your datatype to VARCHAR. Phone and Fax numbers should be stored as strings. See this discussion.
answered Jan 11, 2013 at 18:37
Salman ASalman A
255k81 gold badges426 silver badges515 bronze badges
6
You can also change the data type to bigInt and it will solve your problem, it’s not a good practice to keep integers as strings unless needed.
ALTER TABLE T_PERSON MODIFY mobile_no BIGINT;
Giacomo1968
25.4k11 gold badges70 silver badges100 bronze badges
answered Nov 23, 2015 at 15:11
4
You are exceeding the length of int datatype. You can use UNSIGNED attribute to support that value.
SIGNED INT can support till 2147483647 and with UNSIGNED INT allows double than this. After this you still want to save data than use CHAR or VARCHAR with length 10
answered Jan 11, 2013 at 18:39
Saharsh ShahSaharsh Shah
28.4k8 gold badges47 silver badges83 bronze badges
tl;dr
Make sure your AUTO_INCREMENT is not out of range. In that case, set a new value for it with:
ALTER TABLE table_name AUTO_INCREMENT=100 -- Change 100 to the desired number
Explanation
AUTO_INCREMENT can contain a number that is bigger than the maximum value allowed by the datatype. This can happen if you filled up a table that you emptied afterward but the AUTO_INCREMENT stayed the same, but there might be different reasons as well. In this case a new entry’s id would be out of range.
Solution
If this is the cause of your problem, you can fix it by setting AUTO_INCREMENT to one bigger than the latest row’s id. So if your latest row’s id is 100 then:
ALTER TABLE table_name AUTO_INCREMENT=101
If you would like to check AUTO_INCREMENT‘s current value, use this command:
SELECT `AUTO_INCREMENT`
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'DatabaseName'
AND TABLE_NAME = 'TableName';
answered Mar 17, 2019 at 0:37
totymedlitotymedli
28.3k22 gold badges131 silver badges160 bronze badges
1
Work with:
ALTER TABLE `table` CHANGE `cust_fax` `cust_fax` VARCHAR(60) NULL DEFAULT NULL;
answered Mar 5, 2020 at 2:54
Just table id column name delete and new id created with autoincrement then worked successfully.
answered Apr 10, 2022 at 5:34
1
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:-
Advertisements
0 row(s) returned.
Output:-
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!!!
Advertisements
Thanks for reading.
Содержание
- Error code 1264 mysql
- 11.1.7В Out-of-Range and Overflow Handling
- Out of range value for column – MySQL: Error 1264 Out of range value for a column
- Error code 1264. out of range value for column decimal :
- MySQL: Error 1264 Out of range value for a column [Solved]
- Introduction
- Error code 1264. out of range value for column at row 1
- Error code 1264. out of range value for column decimal
- Error code 1264 mysql
- Код ошибки MySQL: 1264. Значение вне допустимого диапазона для столбца ‘columnname’ в строке 1
- MySQL Workbench: создание подключения и устранение неполадок (для начинающих)
Error code 1264 mysql
MySQL 5.6 Reference Manual Including MySQL NDB Cluster 7.3-7.4 Reference Guide
11.1.7В Out-of-Range and Overflow Handling
When MySQL stores a value in a numeric column that is outside the permissible range of the column data type, the result depends on the SQL mode in effect at the time:
If strict SQL mode is enabled, MySQL rejects the out-of-range value with an error, and the insert fails, in accordance with the SQL standard.
If no restrictive modes are enabled, MySQL clips the value to the appropriate endpoint of the column data type range and stores the resulting value instead.
When an out-of-range value is assigned to an integer column, MySQL stores the value representing the corresponding endpoint of the column data type range.
When a floating-point or fixed-point column is assigned a value that exceeds the range implied by the specified (or default) precision and scale, MySQL stores the value representing the corresponding endpoint of that range.
Suppose that a table t1 has this definition:
With strict SQL mode enabled, an out of range error occurs:
With strict SQL mode not enabled, clipping with warnings occurs:
When strict SQL mode is not enabled, column-assignment conversions that occur due to clipping are reported as warnings for ALTER TABLE , LOAD DATA , UPDATE , and multiple-row INSERT statements. In strict mode, these statements fail, and some or all the values are not inserted or changed, depending on whether the table is a transactional table and other factors. For details, see Section 5.1.10, “Server SQL Modes”.
Overflow during numeric expression evaluation results in an error. For example, the largest signed BIGINT value is 9223372036854775807, so the following expression produces an error:
To enable the operation to succeed in this case, convert the value to unsigned;
Whether overflow occurs depends on the range of the operands, so another way to handle the preceding expression is to use exact-value arithmetic because DECIMAL values have a larger range than integers:
Subtraction between integer values, where one is of type UNSIGNED , produces an unsigned result by default. If the result would otherwise have been negative, an error results:
If the NO_UNSIGNED_SUBTRACTION SQL mode is enabled, the result is negative:
If the result of such an operation is used to update an UNSIGNED integer column, the result is clipped to the maximum value for the column type, or clipped to 0 if NO_UNSIGNED_SUBTRACTION is enabled. If strict SQL mode is enabled, an error occurs and the column remains unchanged.
Источник
Out of range value for column – MySQL: Error 1264 Out of range value for a column
Out of range value for column: In this article we will discuss why Error code 1264 in MySQl occurs and how we can resolve it.
Overview :
MYSQL out of range value for column: This error arises mainly when strict SQL is enabled and the MySQL query stores a value in numeric column outside permissible range of column data type. If strict SQL is not enabled then MySQL server will clip the value appropriate to column data type range.
Error code 1264. out of range value for column at row 1
As studentEnroll_data is empty let’s insert some values to it. If we will try to save a value larger than maximum value of 1264 then will get the error saying-
For example: Write this in code – #1264 – Out Of Range Value For Column
Likewise: Out Of Range Value For Column ‘Phone’ At Row 1.
We can remove the issue by storing a value less than maximum value of INT or change the data type of student_sub_no to BIGINT.
Error code 1264. out of range value for column decimal :
Let’s try to add one more row with value in fee_paid column as 125487495.50. But fee_paid column can only allow 10 digits including two decimal places. So we will get a error message as-
To avoid this we can try inserting one more row by modifing the data type of column fee_paid to DECIMAL(10,2). So now columns can accept total 12 digits.
You can also read for more perfection in this topic.
- Out Of Range Value For Column ‘Contact_No’ At Row 1
- Numeric Value Out Of Range: 1264 Out Of Range Value For Column
- Out Of Range Value For Column Decimal At Row 1
- Sql Error 1264
- Mysql Decimal Out Of Range
- 1264 Out Of Range Value For Column
- Error 1264 In Mysql
- Out of range value mysql
- Out Of Range Value For Column Sql
Источник
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
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.
Action Output Message Response:-
0 row(s) returned .
Output:-
figure 1: student_enroll_data
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.
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.
Let us confirm what got inserted by executing:
Output:-
figure 2: student_enroll_data- with rows inserted
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.
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.
Action Output Message Response:-
1 row(s) affected .
Output:-
figure 3
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.
Action Output Message Response:-
1 row(s) affected .
Output:-
figure 4
As shown in figure 4, the value 123456789.12 is inserted successfully.
Источник
Error code 1264 mysql
When MySQL stores a value in a numeric column that is outside the permissible range of the column data type, the result depends on the SQL mode in effect at the time:
If strict SQL mode is enabled, MySQL rejects the out-of-range value with an error, and the insert fails, in accordance with the SQL standard.
If no restrictive modes are enabled, MySQL clips the value to the appropriate endpoint of the column data type range and stores the resulting value instead.
When an out-of-range value is assigned to an integer column, MySQL stores the value representing the corresponding endpoint of the column data type range.
When a floating-point or fixed-point column is assigned a value that exceeds the range implied by the specified (or default) precision and scale, MySQL stores the value representing the corresponding endpoint of that range.
Suppose that a table t1 has this definition:
With strict SQL mode enabled, an out of range error occurs:
With strict SQL mode not enabled, clipping with warnings occurs:
When strict SQL mode is not enabled, column-assignment conversions that occur due to clipping are reported as warnings for ALTER TABLE , LOAD DATA , UPDATE , and multiple-row INSERT statements. In strict mode, these statements fail, and some or all the values are not inserted or changed, depending on whether the table is a transactional table and other factors. For details, see Section 5.1.11, “Server SQL Modes”.
Overflow during numeric expression evaluation results in an error. For example, the largest signed BIGINT value is 9223372036854775807, so the following expression produces an error:
To enable the operation to succeed in this case, convert the value to unsigned;
Whether overflow occurs depends on the range of the operands, so another way to handle the preceding expression is to use exact-value arithmetic because DECIMAL values have a larger range than integers:
Subtraction between integer values, where one is of type UNSIGNED , produces an unsigned result by default. If the result would otherwise have been negative, an error results:
If the NO_UNSIGNED_SUBTRACTION SQL mode is enabled, the result is negative:
If the result of such an operation is used to update an UNSIGNED integer column, the result is clipped to the maximum value for the column type, or clipped to 0 if NO_UNSIGNED_SUBTRACTION is enabled. If strict SQL mode is enabled, an error occurs and the column remains unchanged.
Источник
Код ошибки MySQL: 1264. Значение вне допустимого диапазона для столбца ‘columnname’ в строке 1
MySQL Workbench: создание подключения и устранение неполадок (для начинающих)
Я использую MySQL для хранения 20-значного идентификационного номера. Когда я запрашиваю базу данных с помощью следующего запроса, я получаю следующую ошибку.
Информация о таблице:
Двигатель: InnoDB Формат строки: динамический Сортировка таблиц: utf8mb4_general_ci
Информация о столбце:
Тип: BIGINT (255) Обнуляемый: Да Права: выбор, вставка, обновление, ссылки
Что я делаю не так? Что-то не так с моим запросом? Может с настройками таблицы или столбца? Большинство других, у кого есть эта ошибка, просто не используют тип столбца, такой как BIGINT, но я я. Ответ очень важен. Благодарность!
похоже, что вы храните большое значение для вашего БОЛЬШОГО ИНТ столбец (5.9E + 19> МАКС.9.2E + 18)!
Если вы посмотрите документацию MySQL:
У вас есть следующие МАКС / МИН ценности:
ПОДПИСАНО BIGINT МИН = -9223372036854775808 МАКС = 9223372036854775807
НЕПОДПИСАННЫЙ БОЛЬШОЙ МИН = 0 МАКС = 18446744073709551615
И последнее, но не менее важное: я бы порекомендовал прочитать следующую ссылку: MySQL Error Code 1264 определяется и объясняется с примерами:
- да ладно, я не знал . думаю, мне придется использовать VARCHAR и использовать parseInt (), когда мне нужно выполнить с ним математику, ха-ха
Вы достигли MAXIMUM VALUE используемого вами типа данных.
Тип Хранение Минимальное значение Максимальное значение
- да ладно, я не знал . думаю, мне придется использовать VARCHAR и использовать parseInt (), когда мне нужно выполнить с ним математику, ха-ха
Не имеет значения, что вы используете BIGINT (255). Аргумент является лишь подсказкой для ширины отображения, он не влияет на диапазон значений, которые вы можете сохранить в 64-битном целом числе со знаком.
- да ладно, я не знал . думаю, мне придется использовать VARCHAR и использовать parseInt (), когда мне нужно выполнить с ним математику, ха-ха
Источник
Out of range value for column: In this article we will discuss why Error code 1264 in MySQl occurs and how we can resolve it.
Overview :
MYSQL out of range value for column: This error arises mainly when strict SQL is enabled and the MySQL query stores a value in numeric column outside permissible range of column data type. If strict SQL is not enabled then MySQL server will clip the value appropriate to column data type range.
# to create the table CREATE TABLE studentEnroll_data ( student_roll INT, stud_name VARCHAR(50), date_of_adm DATE, student_sub_no INT, fee_paid DECIMAL(8,2) ); SELECT * FROM studentEnroll_data;
Output :
Error code 1264. out of range value for column at row 1
As studentEnroll_data is empty let’s insert some values to it. If we will try to save a value larger than maximum value of 1264 then will get the error saying-
For example: Write this in code – #1264 – Out Of Range Value For Column
Error Code: 1264. Out of range value for column ‘student_sub_no’ at row 1.
Likewise: Out Of Range Value For Column ‘Phone’ At Row 1.
We can remove the issue by storing a value less than maximum value of INT or change the data type of student_sub_no to BIGINT.
#Alter the table, modify the column student_sub_no ALTER TABLE studentEnroll_data MODIFY student_ssn_no BIGINT ; #insert rows into the table INSERT INTO studentEnroll_data(student_roll,stud_name,date_of_adm,student_sub_no,fee_paid) VALUES(1,"Rohit",'1999-06-22',321458795,12000.50), (2,"Sourav",'1999-11-02',1447480374,12500.50), (3,"Tarun",'1999-01-22',0197485789,32478.50), (4,"Soumya",'1999-03-25',1145484791,25874.50), (5,"Meghna",'1999-04-29',0144483594,12378.50), (6,"Aditya",'1999-12-25',0142443794,25000.50), (7,"Sahil",'1999-05-14',1447443791,32178.50); SELECT * FROM studentEnroll_data;
Output :
Error code 1264. out of range value for column decimal :
Let’s try to add one more row with value in fee_paid column as 125487495.50. But fee_paid column can only allow 10 digits including two decimal places. So we will get a error message as-
Error Code: 1264. Out of range value for column ‘fee_paid’ at row 1
To avoid this we can try inserting one more row by modifing the data type of column fee_paid to DECIMAL(10,2). So now columns can accept total 12 digits.
#change the datatype of fee_submitted column ALTER TABLE student_enroll_data MODIFY fee_paid DECIMAL(10,2) ; #Inserting a row INSERT INTO studentEnroll_data(student_roll,student_name,date_of_adm,student_sub_no,fee_paid) VALUES(8,"Suresh",'1999-09-12',1267483794, 1958295862.12); SELECT * FROM studentEnroll_data WHERE student_roll = 9;
Output
You can also read for more perfection in this topic.
- Out Of Range Value For Column ‘Contact_No’ At Row 1
- Numeric Value Out Of Range: 1264 Out Of Range Value For Column
- Out Of Range Value For Column Decimal At Row 1
- Sql Error 1264
- Mysql Decimal Out Of Range
- 1264 Out Of Range Value For Column
- Error 1264 In Mysql
- Out of range value mysql
- Out Of Range Value For Column Sql
I was working on migrating Magento from 2.1.9 to 2.2.5 and tried to run this query for migrating product price data
REPLACE INTO magento_225.catalog_product_entity_decimal SELECT * FROM magento_219.catalog_product_entity_decimal;
But this issue prevented me to have it done:
ERROR 1264 (22003): Out of range value for column 'value' at row 240
I checked table definition in the source database and it gave me this output
mysql> describe magento_219.catalog_product_entity_decimal; +--------------+----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+----------------------+------+-----+---------+----------------+ | value_id | int(11) | NO | PRI | NULL | auto_increment | | attribute_id | smallint(5) unsigned | NO | MUL | 0 | | | store_id | smallint(5) unsigned | NO | MUL | 0 | | | entity_id | int(10) unsigned | NO | MUL | 0 | | | value | decimal(16,4) | YES | | NULL | | +--------------+----------------------+------+-----+---------+----------------+ 5 rows in set (0.00 sec)
I also checked table definition in the destination database and I have
mysql> describe vmax.catalog_product_entity_decimal; +--------------+----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+----------------------+------+-----+---------+----------------+ | value_id | int(11) | NO | PRI | NULL | auto_increment | | attribute_id | smallint(5) unsigned | NO | MUL | 0 | | | store_id | smallint(5) unsigned | NO | MUL | 0 | | | entity_id | int(10) unsigned | NO | MUL | 0 | | | value | decimal(12,4) | YES | | NULL | | +--------------+----------------------+------+-----+---------+----------------+ 5 rows in set (0.00 sec)
Well, I had to update the value column value to decimal(16,4) to have the same data type in both databases
ALTER TABLE magento_225.catalog_product_entity_decimal CHANGE `value` `value` DECIMAL(16,4) NULL DEFAULT NULL COMMENT 'Value';
and finally, I ran the above query successfully and product price was migrated successfully.
I am getting the MySQL error #1264 — Out of range value adjusted for column ‘id’ at row 1 when I try to run my INSERT query. With me MySQL server version is 5.0.24a-community-max-nt-log and my table structure is as follow:
CREATE TABLE `tbl_oer` (
`id` int(11) NOT NULL auto_increment,
`programid` int(11) default NULL,
`workshop` varchar(250) default NULL,
`fname` varchar(250) default NULL,
`lname` varchar(250) default NULL,
`designation` varchar(250) default NULL,
`organization` varchar(250) default NULL,
`experience` varchar(250) default NULL,
`contactno` varchar(250) default NULL,
`mobile` varchar(255) default NULL,
`email` varchar(250) default NULL,
`nominatedby_section_b` varchar(250) default NULL,
`designation_section_b` varchar(250) default NULL,
`address_section_b` varchar(250) default NULL,
`telephone_section_b` varchar(250) default NULL,
`mobile_section_b` varchar(255) default NULL,
`fax_section_b` varchar(250) default NULL,
`email_section_b` varchar(250) default NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
My sample insert query, which gets error the error $1264 is below:
INSERT INTO tbl_oer values('', '18', '', 'testsaleem', 'test',
'', '', '', '', '',
'saleem@website-developer-service.com',
'', '', '', '', '', '', '');
Can you help to fix problem if exists in the above query?
Answer No: 97
The error 1264 Out of range value adjusted for column mostly occurs when INSERT query makes try to insert an empty value into a NOT NULL field. So ‘id‘ is a NOT NULL field in your table against which you are inserting empty (») value. Means, you must pass some value instead of empty value against NOT NULL type field. Further, because your id field is also AUTO_INCREMENT so you can change your query by passing NULL value against the id field. Change your query from:
INSERT INTO tbl_oer values('', '18', '', 'testsaleem', 'test',
'', '', '', '', '',
'saleem@website-developer-service.com',
'', '', '', '', '', '', '');
to
INSERT INTO tbl_oer values(NULL, '18', '', 'testsaleem', 'test',
'', '', '', '', '',
'saleem@website-developer-service.com',
'', '', '', '', '', '', '');
Why I see an error when I EXPLAIN for update query?