auto_increment by group

auto-increment, mysql

Solution

For MyISAM and BDB tables you can have an auto_increment field as a secondary part of key, e.g.

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

   PRIMARY KEY(group_field, id)
);

Here's what the manual says about this

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.

Problem

Is there a way with MySQL (5.0 specifically) to have an auto_increment field who's value is based on a grouping column? Example: ``` id name group_field 1 test 1 2 test2 1 1 test3 2 2 test4 2 1 test5 3 2 test6 3 ``` I'd like to not have to go through any 'crazy' methods to achive this, but will if necessary.

Original source