Get the next AUTO_INCREMENT ID from MySQL

get_next_ID.md

Get the Next AUTO_INCREMENT ID from MySQL

There are two methods for getting the next auto_increment ID from MySQL. Both of these examples are using Laravel. Please note that this method, non-blocking, opens the door for a "race condition" in the sense that one person could get the next ID, pause for a moment, and then another request gets the same ID (since it hasn't been incremented yet) leading to a situation in which two requests both have the same ID reserved.

Method 1

\DB::select('SELECT AUTO_INCREMENT as "id"
FROM information_schema.tables
WHERE table_name = `my_happy_table`
AND table_schema = DATABASE( )')[0]->id;

Method 2

$t = DB::select(DB::raw('SHOW CREATE TABLE my_happy_table'));
preg_match('@AUTO_INCREMENT=([0-9]+?) @', $t[0]->{'Create Table'}, $pieces );
return (int)$pieces[1];

Methods that don't work include variations on reading the highest ID value in the table, e.g. DB::table('my_happy_table')->max('id') + 1; These methods fail because if the highest ID is deleted for any reason, then the example function will return less than the next actual auto_increment ID.