Mysql won't store number larger than 9223372036854775808 with bigint

Question

I'm hoping that there is a simple fix for this. I have a database column in which I store a number. I knew that the numbers would get pretty big, so I set the field to 'bigint'. However it will not store a number larger than 9223372036854775808.

Why is this?

-1: You should get into the habit of using the reference material. — Lightness Races in Orbit, Feb 20 '14 at 23:18
Related: http://stackoverflow.com/questions/7142604/what-to-do-when-you-need-integers-larger-than-20-digits-on-mysql — Joren, Feb 20 '14 at 23:19
Simple fix: use a string. And given MySQL weird numeric types, it's really more convenient. — Joker_vD, Feb 20 '14 at 23:19

score 5 · Answer 1 · answered Feb 20 '14 at 23:17

5

Quoting from the manual:

BIGINT[(M)] [UNSIGNED] [ZEROFILL]
A large integer. The signed range is -9223372036854775808 to 9223372036854775807. The unsigned range is 0 to 18446744073709551615.

answered Feb 20 '14 at 23:17

TimWolla

31,849
8
63
96

score 3 · Answer 2 · answered Feb 20 '14 at 23:20

You've hit the bigint maximum size. This is a limitation due to the way the number is stored on the computer. It's the maximum size number you can represent with 8 bytes.

If you need to store a bigger number, consider using another method. You could use varchars but you will need to convert if you're doing math operations on it.

score 1 · Answer 3 · answered Feb 20 '14 at 23:21

1

I think you're using a signed BIGINIT:

Range of a signed bigint: -9223372036854775808 - 9223372036854775807 Range of an unsigned bigint: 0- 18446744073709551615

Use ALTER TABLE to modify the column:

ALTER TABLE t1 MODIFY col1 BIGINT UNSIGNED;

answered Feb 20 '14 at 23:21

ediardo

147
5

Mysql won't store number larger than 9223372036854775808 with bigint

3 Answers3