Search code examples
mysqlgroup-byauto-increment

Set auto_increment value to each unique group

I've got a MySQL innodb table (sqlfiddle demo) with

id, name_id, name, content

And content like

1, NULL, 'Brian', 'Bridge to terabithia'
2, NULL, 'Brian', 'Pulp fiction'
3, NULL, 'Brian', 'Trainspotting'
4, NULL, 'Luke', 'Watchmen'
5, NULL, 'Luke', 'Constantine'
6, NULL, 'Tony', 'Dark knight'
7, NULL, 'Tony', 'Shutter Island'
8, NULL, 'John', 'Machinist'
9, NULL, 'John', 'Matrix'
10, NULL, 'John', 'Sin city'
11, NULL, 'John', 'Mad Max'

The id is unique to each row. But I can't get, how to set auto_increment name_id to each unique name.

Here's (sqlfiddle) what I'm trying to achieve.

1, 1, 'Brian', 'Bridge to terabithia'
2, 1, 'Brian', 'Pulp fiction'
3, 1, 'Brian', 'Trainspotting'
4, 2, 'Luke', 'Watchmen'
5, 2, 'Luke', 'Constantine'
6, 3, 'Tony', 'Dark knight'
7, 3, 'Tony', 'Shutter Island'
8, 4, 'John', 'Machinist'
9, 4, 'John', 'Matrix'
10, 4, 'John', 'Sin city'
11, 4, 'John', 'Mad Max'

Is it possible to do with MySQL only? Thank you for reading.

Solution

  • The short answer is you can't do this automatically with only auto_increment, and you probably shouldn't either.

    If really have reason to do this you would be much better off normalizing your database, creating a "names" table (name_id PK, name) and removing the "name" column from this table. Honestly I'm not sure what you can really gain from doing this in your example, but I imagine it might be an abstraction of the problem.