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

Powered by vBulletin® Version 4.2.2 Copyright © 2017 vBulletin Solutions, Inc. All rights reserved.