10

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.

ekad
  • 14,436
  • 26
  • 44
  • 46
Glen Solsberry
  • 11,960
  • 15
  • 69
  • 94

3 Answers3

17

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.

Paul Dixon
  • 295,876
  • 54
  • 310
  • 348
  • As the manual only mentions MyISAM and BDB, I would guess not. See also http://dev.mysql.com/doc/refman/5.0/en/innodb-auto-increment-handling.html – Paul Dixon Mar 24 '09 at 14:15
  • AUTO_INCREMENT has to be on the primary key - if you attempt to create a table where you use it outside the key, you'll get error 1075 "Incorrect table definition; there can be only one auto column and it must be defined as a key" – Paul Dixon May 06 '15 at 13:06
  • Is this safe for multiple concurrent inserts? – Gannet Sep 09 '20 at 19:52
  • Yes, take a look at https://dev.mysql.com/doc/refman/8.0/en/innodb-auto-increment-handling.html for more depth on InnoDB handling of auto_increment – Paul Dixon Sep 11 '20 at 19:41
0

Have you thought about composite primary keys?

karim79
  • 339,989
  • 67
  • 413
  • 406
0

You can achieve that with trigger on insert, setting max(id) from table group by group_field having group_fileld = @inserted_group;

vartec
  • 131,205
  • 36
  • 218
  • 244