Auto-Incrementing by Groups in MySQL

MySQL users occasionally face the need to assign auto-incrementing values to rows based on a specific grouping column. For example, consider a table containing records with columns for id, name, and group_field.

To achieve auto-incrementing by group, one can utilize a strategy involving creating a secondary part of the key for MyISAM and BDB tables:

CREATE TABLE foo (
   id          INT AUTO_INCREMENT NOT NULL,
   group_field INT NOT NULL,
   name        VARCHAR(128),

   PRIMARY KEY(group_field, id)
);

As per the MySQL manual, the auto-increment value for the 'id' column is determined using the formula:

MAX(auto_increment_column) 1 WHERE prefix=given-prefix

where 'prefix' corresponds to the value in the 'group_field' column. This approach proves particularly useful for organizing data into sequential groups.