mySQL: auto_increment

Hello,

i need a real unique counter for every entry in my mySQL database.
auto_increment works fine so far. The only problem is, that the counter is reset with every DELETE FROM 'tablename' statement.

Is there any way to suppress that behaviour?
How can i get a counter, that is NEVER reset?

Thanks,
Claus

I don't know what you mean by reset. It sounds like it should work. For example say you have:

1
2
3
4
5
In your table, if you delete 5 the next numbers is still 6, so.....

I agree with Silver.
As long as you dont touch it the auto_increment field will increment by 1 each record you add. You can get into trouble if you start messing with it but as long as you leave it well alone you will be ok. If you need to know what number will be allocated next you can do this by "SHOW TABLE STATUS" as an MYSQL query and you get a list of all tables in your db. There is one column in this display that will tell you the next auto_increment number.
As a side note of interest:
I use an auto_increment field in every table in a database I am designing at the moment. It is the first field and is always named xxRECID where xx is the table name idetifier (E.G. CM for Customer Master etc). This field is a mediumint unsigned and is auto_increment. I have these RECID fields alot in helping me to maintain the database and its structure. (They are not always utilized but always there just in case). For "master" files I use the RECID to identify that record (E.G. 00001 = John Smith High Street Anytown). For transitory/transaction type files I use the RECID field to help maintain the record (E.G. 001234 Invoice A123 GBP00123.45) etc etc.

As another idea you could use timestamp or datetime fields to help you identify your records but beware if you create and more than 1 record a second as the stamp ist yyyymmddhhmmss.

Hope this all helps - sorry I have gone on a bit

Pryce

Hello,

thanks for your postings.

It seems that i didn't describe my problem exact enough. The auto_increment works fine as long as i do not delete ALL the records of the table.
But when i enter the command

DELETE FROM 'tablename'

all records are deleted and the auto_increment counter is reset to 1.

Is there any way to prevent mySQL from resetting the counter in this case?

Claus

Hello Claus,

If you run "show table status" you can get the next auto_increment that will be used when the next record is added. Keep this number before you do a "delete all ...".
Then, when you add the first record after you have deleted all the records, use the kept "next_number" from the "show table status".
I have just tested this and it works.
If you want to have a look at an example of the results from a "show table status" look at this page on my site and scroll down to the "meta_tables_status_display FUNCTION".
http://www.prycejones.com/caravan/meta_functions_test.php

Hope this idea helps.

If all this is too complicated then another idea is to keep the "last record used" number in a small file/table somewhere, and just use this when ever you are writing a record.

Good luck

Pryce

mailto: pryce AT prycejones DOT com