MySQL在其他列中输入的每个不同的值会自动递增? [英] MySQL auto-increment per distinct value entered in other column?
问题描述
我想在我的mysql数据库中创建一列,该列会根据在另一列中输入的特定值而自动递增.
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
推荐答案
您似乎正在要求一种自动递增,该自动递增应在另一列中为每个不同的值保持单独的递增.
It seems like you're asking for an auto-increment that maintains a separate increment per distinct value in another column.
正如@RickJames回答的那样,该功能存在于MyISAM存储引擎中,但在InnoDB中不起作用.原因与InnoDB的行级锁定有关:为了防止竞争条件与另一个并发客户端插入相同的类"(按照您的示例),InnoDB必须在 all 上创建间隙锁em>给定类的行.
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.
这不会影响MyISAM,后者无论如何都会执行表级锁定,但是对于InnoDB,这将严重损害并发工作负载期间的吞吐量.
This doesn't affect MyISAM, which does table-level locking anyway, but for InnoDB it would greatly harm throughput during concurrent workloads.
这不是使用MyISAM的好理由.
This is not a good reason to use MyISAM.
但是您应该放弃自动递增是某种序数或行号的想法,而并非如此.它只是一个 unique 值;它没有其他含义.
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.
如果您需要为每个班级分配连续的学生ID,请在您的应用程序中进行分配,并显式插入值.不要依靠自动增量机制来做到这一点.
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.
这篇关于MySQL在其他列中输入的每个不同的值会自动递增?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!