GEt current auto_increment value for a table?

[youradds]

Hi,

I'm trying to get the current ai value for a table. I've had a read up on the docs, and they suggest using

SELECT LAST_INSERT_ID();

I want to do this on a specific table though. I've tried just doing SEELCT MAX(ID) , but that doesn't always work (for example, if the last record in the table is ID 20, but the auto inciment value is set at 22 (due to deleting a record), it only return:

20 + 1 (which would give the new row number)

Any suggestions?

If I can't work out a way to do that - I guess I'll just have to rethink the code that does this

TIA

Andy

 

[youradds]

Never mind - sorted it (typical, and now I can't seem to remove the post )

For anyone else interested - just use:

SHOW TABLE STATUS LIKE 'TABLE_NAME'

...then for example if using perl, you would access it via:

$rows->{Auto_increment} + 1

..to get the new ID

CHeers

 

[youradds]

Actually, is there a way to ONLY grab the max auto increment value, and not all the other bits? Not sure how heavy that command will be on the DB, so trying to make it as nice as possible (its a very busy site)

TIA

Andy

 

[ingresman]

or you could look in the information_schema like this

  select it.table_schema,
        it.table_name,
        it.auto_increment
 from information_schema.tables it
 where it.table_name = "category"
 and   it.table_schema = "skill"
;

I've shown how to limit it to a particular database (schema) and table name.
The auto_increment is the next value to be assigned which looks consistent with show table status (which makes sense)

 

[youradds]

Hi,

Thanks - will give that a go

Cheers

Andy

 

[vacunita]

If you are using Perl why not use Perl's own Inserid() function.

http://www.tizag.com/perlT/perlmysqlquery.php

Its built and probably much faster than parsing through text.

----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

Behind the Web, Tips and Tricks for Web Development.

http://behindtheweb.blogspot.com/

 

[youradds]

Hi,

Yeah, the problem is that we use a hashed folder structure. For example, the ID is 150, we take the last digit and place the image in that folder.

So:

143 - goes in /3
554 - goes in /4
6754 - goes in /4

This helps to keep the table clean, as we have quite a few images

I was contemplating adding to the DB field, and then getting the insert_id, but the problem I found with that is that we need to edit file vars (to convert foreign charachter and other charachters we dont want included in the image in normal alpha chars)

If we find the other solution too slow, I guess I won't have an option but to change the methodolgy of it

Cheers

Andy

 

[vacunita]

huh? I don't follow. inserId will return the last inserted ID regardless of what it was.

Its specially handy when you are dealing with auto increment ID's. Which is what I thought you were doing. Perhaps I misunderstood the requirement though.





----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

Behind the Web, Tips and Tricks for Web Development.

http://behindtheweb.blogspot.com/