Search code examples
mysqlauto-increment

MySQL auto-increment per distinct value entered in other column?

I'd like to create a column in my mysql database which auto increments in response to a specific value being entered into another column.

For example (data below), when a new record is created by a student using the site, he/she would input the name of their class and be assigned a number variable (specific to that class) which could be used by a teacher to identify the student. Is there any way I can do this without creating a new table for each class?

class_name / student_number

classa / 1

classa / 2

classa / 3

classb / 1

classb / 2

classa / 4

classb / 3
Solution

  • It seems like you're asking for an auto-increment that maintains a separate increment per distinct value in another column.

    As @RickJames answers, this feature exists in the MyISAM storage engine, but it doesn't work in InnoDB. The reason is related to InnoDB's row-level locking: in order to prevent a race condition with another concurrent client inserting to the same 'class' (per your example), InnoDB would have to create a gap lock on all the rows for the given class.

    This doesn't affect MyISAM, which does table-level locking anyway, but for InnoDB it would greatly harm throughput during concurrent workloads.

    This is not a good reason to use MyISAM.

    But you should abandon the idea that an auto-increment is some kind of ordinal or row number—it isn't. All it is is a unique value; it has no other meaning.

    If you need to assign consecutive student id's per class, then do so in your application and insert values explicitly. Don't rely on the auto-increment mechanism to do that.