MySQL: Getting the Next ID Value (next_id, AUTO_INCREMENT)

A problem as old as the world - how to get the next ID value for a table in a MySQL database, given that the ID value is generated as AUTO_INCREMENT.

Such a need could arise in two cases:
a) After making a new record (INSERT), to be able to work with the new record's id value later;
b) To perform operations (form a code, or an SSID value tied to the ID) before the actual record has been made.

If the ID value is needed after making the record:

1. In virtually any language there is an equivalent to PHP's mysql_insert_id function. This function returns the ID value for the last record made. True, if there have been several, it still returns only the last one.

mysql_query("INSERT INTO mytable (product) values ('kossu')");
printf("Last inserted record has id %d
", mysql_insert_id());

2. Inside a MySQL trigger, such a value can be obtained as NEW.id, provided the trigger executes after the operation (after). True, after the operation the values to be written can no longer be changed. But this scenario works if an operation on another table needs to be performed.

3. If the data write intensity is not insanely high, both in MySQL triggers and in PHP you can execute the query: SELECT max(ID) AS curr_id FROM my_table.

If the ID value is needed before making the record:

1. A completely unreliable but theoretically usable scenario is to execute the query:

SELECT max(ID) + 1 AS next_id FROM my_table

Unreliable because the last record may have been deleted, meaning the next largest ID value will no longer be the next AUTO_INCREMENT value.

2. In PHP you can read the table status:

function get_menu_id($menu) {
   $result = mysql_query("SHOW TABLE STATUS LIKE 'menu'");
   $rows = mysql_fetch_assoc($result);
   return $rows['auto_increment'];
}

This principle could also be used in a table trigger, but I am not aware of a mechanism for reading a specific (AUTO_INCREMENT) field from a table's status using MySQL tools.

3. In MySQL, create a function that reads the AUTO_INCREMENT value of a specified table from the MySQL information schema. It looks like this:

CREATE FUNCTION next_id(_TABLE varchar(255)) RETURNS int(11)
BEGIN

  DECLARE _NEXT_ID INT(11) DEFAULT 0;

  SELECT AUTO_INCREMENT FROM information_schema.`tables`
  WHERE TABLE_SCHEMA = "my_db" AND TABLE_NAME = _TABLE
  INTO _NEXT_ID;

  RETURN _NEXT_ID;
END;

Since the information schema stores data about all databases and all tables, both the database and the table must be specified in the query. The database my_db is hard-coded in the query, while the table is the function's argument - _TABLE.

Executing the query: SELECT next_id('my_table') - the function returns the AUTO_INCREMENT value for the specified table. Such a query can be executed from both PHP and a MySQL trigger (procedure, function).