CODEWITHSUNDEEP
phpmysqldatabase
Rakesh Sharma
Rakesh Sharma

Reputation: 13728

Integer overflow what will be next

I am using int(22) for now as my table field. but for now it's overflow with value 2147483647 and stop so my 3000 field gone the same id. I am really stuck with that

For now i changed it with bigint(20) unsigned but may be i will come same condition in future. Please advice me what will be better use for this varchar or bigint or any

Also would be great if i get some explanation.

Upvotes: 0

Views: 135

Answers (2)

Jason Heo
Jason Heo

Reputation: 10246

NUMERIC can store 65 digits which is larger than BIGINT UNSINGED

mysql> create table integral (a bigint, b numeric(65,0));
Query OK, 0 rows affected (0.06 sec)

mysql> insert into integral (a) values(123456789012345678901234567890);
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> show warnings;
+---------+------+--------------------------------------------+
| Level   | Code | Message                                    |
+---------+------+--------------------------------------------+
| Warning | 1264 | Out of range value for column 'a' at row 1 |
+---------+------+--------------------------------------------+
1 row in set (0.00 sec)

mysql> insert into integral (b) values(123456789012345678901234567890);
Query OK, 1 row affected (0.00 sec)

Upvotes: 1

Bill Karwin
Bill Karwin

Reputation: 562651

Think of it this way: how long did it take you to fill up an INT? Perhaps six months?

Now multiply that time by roughly 4 billion.

That's how long it will take to fill up a BIGINT, if you insert data at the same rate. So if it took you half a year to fill an INT, the BIGINT will last 2 billion years.

That gives you some scope of how much larger the range of a BIGINT is than an INT. A BIGINT supports up to 264 values, which is 232 times larger than the number of values in an INT.

Upvotes: 7

Related Questions