Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    Regular Coder
    Join Date
    Jun 2006
    Location
    UK
    Posts
    922
    Thanks
    302
    Thanked 3 Times in 3 Posts

    Question 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

  • #2
    Senior Coder djm0219's Avatar
    Join Date
    Aug 2003
    Location
    Wake Forest, North Carolina
    Posts
    1,300
    Thanks
    4
    Thanked 203 Times in 200 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 (03-28-2013)

  • #3
    Regular Coder
    Join Date
    Jun 2006
    Location
    UK
    Posts
    922
    Thanks
    302
    Thanked 3 Times in 3 Posts

    Thumbs up

    Quote Originally Posted by djm0219 View Post
    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

  • #4
    Master Coder felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, Australia
    Posts
    6,639
    Thanks
    0
    Thanked 649 Times in 639 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 (03-28-2013)

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,441
    Thanks
    76
    Thanked 4,372 Times in 4,337 Posts
    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.
    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 (03-28-2013)

  • #6
    Regular Coder
    Join Date
    Jun 2006
    Location
    UK
    Posts
    922
    Thanks
    302
    Thanked 3 Times in 3 Posts
    Thanks for the detailed explanation everyone.


    Cheers!!


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •