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!