MySQL: the next ID value (next_id, AUTO_INCREMENT)

Problem is as old as a world – how to get next ID value in MySQL assuming, AUTO_INCREMENT is in use.

Such a need might occur in two cases:

1. Create new record (insert) and ability to use new ID value;
2. Make some processing (for example, generate SSID value, which is tight to next ID value), before real insertion is made.

If ID value is needed after record is submitted:

1.  I hope each language has function fetching inserted ID value, like mysql_insert_id. This function returns ID value from the last inserted record. In case multiple records inserted, ID value will be fetched only for the last one.

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

2. In MySQL trigger, you may get next ID value using NEW variable, assuming it is executed after. Bad news are that it is impossible to make any changes in dataset after trigger is executed. This scenario works well only if operation is applied to other table (ie. supplementary table, etc).

3. If the number of insert transaction is low, you may use following sql:

SELECT max(ID) AS curr_id FROM my_table

Although, probability exists, that during execution of this sql statement, some new record might be inserted.

If ID value is needed before insertion is made.

1. Not safe, but sometimes usable:

SELECT max(ID) + 1 AS next_id FROM my_table

Not safe because, next id value achieved by incrementing might not exist. That might occur if last record is deleted, or transaction revoked.

2. By querying table status:

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

Such method might be used also in triggers, but I have no idea how to fetch AUTO_INCREMENT value from array using MySQL.

3. Request data from information schemes:

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;

Because information schema stores information about all databases and all tables, executing query, you have to specify as database, as table. Note database my_db is hardcoded into the function, but _TABLE is argument of the same function.

Executing such function you will get next ID value. Such function can be executed withing sql, as well as from any other programming language.