SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Enthusiast
    Join Date
    Jun 2001
    Posts
    36
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Choosing ID instead og auto inchrement

    Hello, iīve noticed on several sites that their idīs on the news are like 5001 and 5235... and itīs not possible that a 3 month old site could have puplished that alot of news and i was wondering about when a create a table that i could choose where she start to select ID for the news that is saved in the database?


    Or is it some other way?

  2. #2
    SitePoint Addict -TheDarkEye-'s Avatar
    Join Date
    Mar 2001
    Location
    canada
    Posts
    286
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    well it depends on the db youre using.

    i know some of them you can choose what value to start an auto incriment field with. for example, in oracle you would just start the sequence youre using for the id field at some number besides 1.

    if you cant figure out how to get youre db to do this you could always just code a news update script that does it for you. something like this:

    UPDATE newstable (newsid, news, date) VALUES ((SELECT max(newsid) FROM newstable)+1, whatevernewsgoeshere, now());

    something like that would work. of course youd need to fill in the spaces with whatever scripting language you happen to be using... php i assume.

  3. #3
    SitePoint Enthusiast
    Join Date
    Jun 2001
    Posts
    36
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Iīm using mysql

    Is there a option to do that kind of thing like in oracle?

    Thankīs for very good answear

  4. #4
    SitePoint Wizard Defender1's Avatar
    Join Date
    Apr 2001
    Location
    My Computer
    Posts
    2,808
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    if you set the default at like, 1000 would it start incrementing there?
    try it and see.
    Defender's Designs
    I'm Getting Married!

    Not-so-patiently awaiting Harry Potter Book 7 *sigh*

  5. #5
    Probably eating pie mitsubishi's Avatar
    Join Date
    Sep 2001
    Location
    England, UK
    Posts
    405
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    In mySQL the auto-increment is 1 more than the previous highest number, it doesn't fill gaps. This is why if one of the rows has a negative number it screws up because auto-increment see's this as a very, very high number (2^32-2 I think?). There's a reason for that, but I can't remember it, might be changed in v4 though.

  6. #6
    Mlle. Ledoyen silver trophy seanf's Avatar
    Join Date
    Jan 2001
    Location
    UK
    Posts
    7,168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by Defender1
    if you set the default at like, 1000 would it start incrementing there?
    Yes, just insert a row and manually put the ID in and MySQL will count from there

    mitsubishi if you make the ID unsigned you can't use negative numbers

    Sean
    Harry Potter

    -- You lived inside my world so softly
    -- Protected only by the kindness of your nature

  7. #7
    Probably eating pie mitsubishi's Avatar
    Join Date
    Sep 2001
    Location
    England, UK
    Posts
    405
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You can't use negative numbers in an auto-increment column anyway. I looked it up the manual to find why, I knew there was a reason, anyway here's the passage, I'll put it all for completeness.
    An integer column may have the additional attribute AUTO_INCREMENT. When you insert a value of NULL (recommended) or 0 into an AUTO_INCREMENT column, the column is set to value+1, where value is the largest value for the column currently in the table. AUTO_INCREMENT sequences begin with 1. See section 8.4.3.126 mysql_insert_id(). If you delete the row containing the maximum value for an AUTO_INCREMENT column, the value will be reused with an ISAM, or BDB table but not with a MyISAM or InnoDB table. If you delete all rows in the table with DELETE FROM table_name (without a WHERE) in AUTOCOMMIT mode, the sequence starts over for all table types. NOTE: There can be only one AUTO_INCREMENT column per table, and it must be indexed. MySQL Version 3.23 will also only work properly if the auto_increment column only has positive values. Inserting a negative number is regarded as inserting a very large positive number. This is done to avoid precision problems when numbers 'wrap' over from positive to negative and also to ensure that one doesn't accidentally get an auto_increment column that contains 0. To make MySQL compatible with some ODBC applications, you can find the last inserted row with the following query:
    SELECT * FROM tbl_name WHERE auto_col IS NULL
    Cheers for bringing up unsigned/signed because it forced me to find out what it means


Bookmarks

Posting Permissions

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