How to Implement Auto-Increment by Group in MySQL 5.0?

Achieving Auto-Increment by Group in MySQL 5.0

The task of having an auto-increment field based on a grouping column in MySQL 5.0 arises when organizing data with unique identifiers within specific groups. To achieve this, leverage the following method specific to MyISAM and BDB table types.

Creating a Table with Auto-Increment Grouping

Create a table with an auto-increment field as a secondary part of the primary key. For example:

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

   PRIMARY KEY(group_field, id)
);

Key Generation

The auto-increment field's value is calculated using the following formula:

MAX(auto_increment_column) 1 WHERE prefix=given-prefix

This ensures that records within each group are assigned sequential identifiers.

Reference

For further details, refer to the MySQL manual:

In this case, the generated value for
the AUTO_INCREMENT column is
calculated as
MAX(auto_increment_column) 1 WHERE
prefix=given-prefix. This is useful
when you want to put data into ordered
groups.

The above is the detailed content of How to Implement Auto-Increment by Group in MySQL 5.0?. For more information, please follow other related articles on the PHP Chinese website!