组合键中auto_increment的用法 [英] auto_increment usage in composite key

问题描述

我正在使用带组合键的桌子

I am having a table with the composite key

emp_tbl(
companyId int not null,
empId int not null auto_increment,
name varchar2,
....
...
primary key(companyId,empId)
);

在mysql中，当我开始插入数据时发生了什么

In mysql whats happening is while i starts inserting the data

Emp_tbl

companyId    empId
1             1
1             2
1             3
2             1
2             2

请注意，当companyId更改时，auto_increament值将再次重置为1.我想禁用它.我的意思是我不想重置auto_increament.我期望这样的结果.

Note that when the companyId changes the auto_increament value is resetted to 1 again. I want to disable that. I mean i don't want to reset the auto_increament. I am expecting the result like this.

companyId    empId
1             1
1             2
1             3
2             4
2             5

有可能做到吗? 谢谢

推荐答案

这是结合有auto_increment的复合主键发生的情况.重新创建主键，使其完全是您的auto_increment字段(empId)，然后在companyId和empId上创建唯一索引

This is what happens with a composite primary key that incorporates a auto_increment. Recreate the primary key so that it's purely your auto_increment field (empId) then create a unique index on companyId and empId

编辑

请注意，这仅适用于MyISAM和BDB表.如果您在表中使用InnoDB，那么它也可以按需工作

Note that this only applies to MyISAM and BDB tables. If you used InnoDB for your tables, then it would also work as you wanted

这篇关于组合键中auto_increment的用法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！