...

View Full Version : MySQL: Value does not increment



phantom007
03-27-2013, 05:40 AM
Hi All

I have the value 9223372036854775807 in a bigint datatype field and am trying to increment the value but it does not increment greater than the above value.

So can someone please suggest me the solution or tell me which datatype should i use that will support me a value greater than 9223372036854775807?



/*DDL Information*/
-------------------

CREATE TABLE `log_counter` (
`log_counter` BIGINT(20) DEFAULT '0'
) ENGINE=MYISAM DEFAULT CHARSET=latin1




Thanks

djm0219
03-27-2013, 08:58 AM
If you don't need negative values change it to an unsigned column and the maximum number that may be stored increases to 18446744073709551615.

phantom007
03-27-2013, 09:05 AM
If you don't need negative values change it to an unsigned column and the maximum number that may be stored increases to 18446744073709551615.

Negative values? But i dont have any negative values (-) in that field. What exactly do u mean by negative value?

Thanks

felgall
03-27-2013, 07:46 PM
A BIGINT can hold numbers between -9223372036854775808 and 9223372036854775807

An UNSIGNED BIGINT can hold numbers between 0 and 18446744073709551615

Whether you actually use all the numbers in either range is irrelevant.

By changing the field to UNSIGNED you recover the sign bit to use to doulbe the size of the biggest positive number that can be held.

Old Pedant
03-27-2013, 08:16 PM
It's probably easier to see this if you look at a TINYINT, a one-byte value.

One byte is 8 bits. One of those bits has to be used to hold the SIGN of the number. Traditionally (and in all modern hardware) it is bit 0.

So some of the possible bit patterns:
0000 0000 == 0 decimal
0000 0001 == 1 decimal
0111 1111 == 127 decimal
1000 0000 == -128 decimal
1000 0001 == -127 decimal
1111 1111 == -1 decimal

Now, look what happens if (ignoring the sign) you increment 127 decimal (0111 1111) by 1: The value becomes 1000 0000 which is -128 decimal! So incrementing the maximum positive number by one produces the maximum negative number!!

As Felgall and DJM said, if you instead use an UNSIGNED number -- here we will use UNSIGNED TINYINT for illustration -- you double (plus 1) the range of positive values.
0000 0000 == 0 decimal
0000 0001 == 1 decimal
0111 1111 == 127 decimal
1000 0000 == 128 decimal
1000 0001 == 129 decimal
1111 1111 == 255 decimal

Same exact thing happens with BIGINT except the decimal numbers involved are much bigger.

phantom007
03-28-2013, 03:02 AM
Thanks for the detailed explanation everyone.


Cheers!!



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum