Choosing ID instead og auto inchrement

[arnrog]

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?

[-TheDarkEye-]

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.

[arnrog]

I´m using mysql

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

Thank´s for very good answear

[Defender1]

if you set the default at like, 1000 would it start incrementing there?
try it and see.

[mitsubishi]

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.

[seanf]

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

[mitsubishi]

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