Note to self: To get the next auto_increment value from a table run this query:
SELECT AUTO_INCREMENT
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = "databaseName"
AND TABLE_NAME = "tableName"Don’t forget it (again).
Did this help you out? Like what you see?
Consider donating.
Consider donating.
I don’t run ads on my blog nor do I do this for profit. A donation however would always put a smile on my face though. Thanks!
MAN! THANKS FOR THIS!!!
i am searching this from years!!!!!!!
And for the record, if you want to change the auto_increment, issue the statement:
alter table tablename auto_increment = newvalue
Hahahahahahahaha!!! I really laughed hard when I read that! “Don’t forget it (again).” Hehehe.
But seriously, thanks a million, i’ve been using count() + 1 for years!! shame on me 🙂
thanks lot , i searched hundred of sites
excellent 🙂
i was doing +1 method 🙂
but this really rocks
super cool
nice!!!!
I should know this method.
let me bookmark this, and repost to my blog with backlink of course.
Thanks!
Handy stuff, thanks for sharing :), and google for showing me your page. Some very cool projects you have here.
SHOW TABLE STATUS LIKE ‘tablename’
Thanks, I WILL NOT FORGET IT (AGAIN)!!!!!!!!
Hello thks for the script.
is it more effeciente than :
SELECT MAX(id) FROM table
where id is the auto-incremented primary key
rgs
This does work, but seems to be drastically slower than “select max(id) from table”. On my table (innodb) with 2M records, the method shown here takes 1.4 seconds. The plain old select max takes 0.0001 seconds.
Thanks a lot man!!
but max(id) doesn’t show auto_increment if someone deleted your last record
the problem with using count or max +1 is that if you occasionally delete, you get out of line.
that is why the method proposed here is superior.
worrying though if it is slow. i do not see why it should be!
Seems like a smart hack. Not sure it’s usable in a multi-user environment.
Better to stick with ex. mysql_insert_id()
excellent, thank you …
thanks it’s work for me
Hi,
That’s great! I got such headache to find the solution.. Thanks a lot.
Not all database users may have access to the mysql database. This would effectively work as an alternative:
SELECT `aa_field`+1 FROM `table` ORDER BY `aa_field` DESC;
your solution only works if no record are deleted in your table
if you delete the last record then, auto_increment != max(id)+1 but auto_increment==max(id)+2
id +1 or count or max method are wrong, it looks like it works until you delete the last record, that ways only work if you never delete record !!!!
if you delete the last record then, auto_increment != max(id)+1 but auto_increment==max(id)+2
Running this in phpmyadmin needs just 0.0004 seconds if you include the databasename and the tablename correctly.
In case you have various databases with equal tablenames and you omit the databasename it really needs a lot of time, like 2.4 seconds for searching in 5 databases (f.e. all wordpress installations and all containing the table wp_posts).