Enjoy an ad free experience by logging in. Not a member yet? Register.

Results 1 to 6 of 6
Thread: MySQL: Value does not increment

03272013, 06:40 AM #1
 Join Date
 Jun 2006
 Location
 UK
 Posts
 923
 Thanks
 302
 Thanked 3 Times in 3 Posts
MySQL: Value does not increment
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?
Code:/*DDL Information*/  CREATE TABLE `log_counter` ( `log_counter` BIGINT(20) DEFAULT '0' ) ENGINE=MYISAM DEFAULT CHARSET=latin1
Thanks
03272013, 09:58 AM
#2
 Join Date
 Aug 2003
 Location
 Wake Forest, North Carolina
 Posts
 1,317
 Thanks
 4
 Thanked 208 Times in 205 Posts
If you don't need negative values change it to an unsigned column and the maximum number that may be stored increases to 18446744073709551615.
Dave .... HostMonster for all of your hosting needs
Users who have thanked djm0219 for this post:
phantom007 (03282013)
03272013, 10:05 AM
#3
 Join Date
 Jun 2006
 Location
 UK
 Posts
 923
 Thanks
 302
 Thanked 3 Times in 3 Posts
03272013, 08:46 PM
#4
 Join Date
 Sep 2005
 Location
 Sydney, Australia
 Posts
 6,727
 Thanks
 0
 Thanked 665 Times in 654 Posts
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.
Stephen
Learn Modern JavaScript  http://javascriptexample.net/
Helping others to solve their computer problem at http://www.felgall.com/
Don't forget to start your JavaScript code with"use strict";
which makes it easier to find errors in your code.
Users who have thanked felgall for this post:
phantom007 (03282013)
03272013, 09:16 PM
#5
It's probably easier to see this if you look at a TINYINT, a onebyte 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.
An optimist sees the glass as half full.
A pessimist sees the glass as half empty.
A realist drinks it no matter how much there is.
Users who have thanked Old Pedant for this post:
phantom007 (03282013)
03282013, 04:02 AM
#6
 Join Date
 Jun 2006
 Location
 UK
 Posts
 923
 Thanks
 302
 Thanked 3 Times in 3 Posts
Thanks for the detailed explanation everyone.
Cheers!!