Ошибка 1264 mysql - Не ошибается лишь тот, кто ничего не делает!

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.7k11 gold badges72 silver badges106 bronze badges

asked Jan 11, 2013 at 18:33

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

260k82 gold badges429 silver badges521 bronze badges

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.7k11 gold badges71 silver badges101 bronze badges

answered Nov 23, 2015 at 15:11

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.6k8 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

29.1k22 gold badges130 silver badges163 bronze badges

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

Источник

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.

Advertisements

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:-

figure 1: student_enroll_data

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:-

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.

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:-

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.

#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:-

figure 4

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!!!

Источник

When I try to insert the below into my MySQL

INSERT INTO `rooms` (`id`, `ip`) VALUES ('131213', '-259857341');

I fails with the follow error:

Warning: #1264 Out of range value for column 'ip' at row 1

I am looking around but haven’t found how to fix or work it out…

My field is unsigned int which should work just fine for that entry.

What is the problem and how do I solve ?

I am using unsigned int because I wanted to store ips using inet_ntoa/aton.

EDIT:

I am using unsigned INT as recommend in MySQL website:

To store values generated by
INET_ATON(), use an INT UNSIGNED
column rather than INT, which is
signed. If you use a signed column,
values corresponding to IP addresses
for which the first octet is greater
than 127 cannot be stored correctly.
See Section 10.6, “Out-of-Range and
Overflow Handling”.

http://dev.mysql.com/doc/refman/5.0/en/miscellaneous-functions.html

Источник

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

Источник

Русскоязычный форум CS-Cart

Загрузка…

Источник