mysql - 使 AUTO_INCREMENT 在父 ID 更改时重置自身

Question

在我的一项项目作业中，我想要 PRIMARY KEY 的一部分在其他部分更改值后重置。例如:

CREATE TABLE shopping_center(
    centerID INTEGER AUTO_INCREMENT,
    centerName CHAR(50),
    CONSTRAINT center_PK PRIMARY KEY(centerID));

CREATE TABLE staff(
    staffID INTEGER AUTO_INCREMENT,
    centerID INTEGER,
    name VARCHAR(50),
    CONSTRAINT staff_PK PRIMARY KEY(staffID, centerID));

ALTER TABLE staff
    ADD CONSTRAINT staff_FK
    FOREIGN KEY(centerID) 
    REFERENCES shopping_center(centerID);

AUTO_INCREMENT在这种情况下，MySQL 的功能无法按我的预期工作。当我插入这些值时(请记住 ID 上的 AUTO_INCREMENT 功能，因为我没有在 centerID 语句中定义 staffID 或 INSERT):

INSERT INTO shopping_center (centerName) VALUES("A Shopping Center");
INSERT INTO shopping_center (centerName) VALUES("Another Shopping Center");

INSERT INTO staff (centerID, name) VALUES(1, "Staffmember 1 in centerID 1");
INSERT INTO staff (centerID, name) VALUES(1, "Staffmember 2 in centerID 1");
INSERT INTO staff (centerID, name) VALUES(2, "Staffmember 1 in centerID 2");
INSERT INTO staff (centerID, name) VALUES(2, "Staffmember 2 in centerID 2");

我得到这个结果:

SELECT centerID, staffID, name FROM staff; 
+----------+---------+-----------------------------+
| centerID | staffID | name                        |
+----------+---------+-----------------------------+
|         1|        1| Staffmember 1 in centerID 1 |
|         1|        2| Staffmember 2 in centerID 1 |
|         2|        3| Staffmember 1 in centerID 2 |
|         2|        4| Staffmember 2 in centerID 2 |
+----------+---------+-----------------------------+

但我想要的是 staffID当 centerID 时重置为 1改变值。这就是我想要的结果(注意staffID值的变化):

SELECT centerID, staffID, name FROM staff; 
+----------+---------+-----------------------------+
| centerID | staffID | name                        |
+----------+---------+-----------------------------+
|         1|        1| Staffmember 1 in centerID 1 |
|         1|        2| Staffmember 2 in centerID 1 |
|         2|        1| Staffmember 1 in centerID 2 |
|         2|        2| Staffmember 2 in centerID 2 |
+----------+---------+-----------------------------+

作为PRIMARY KEY在这种情况下，所需的始终是唯一的，我不明白为什么这不起作用。我想在这里实现的目标有解决方案吗？

Answer 1

我会尝试回答您的问题，并解释发生了什么。

首先，您想要的功能在 MyISAM 存储引擎中可用(有点不同)。但是，事务和引用完整性不可用。

现在对于auto_increment - 它为行提供唯一的值。但是，它是通过考虑并发性来做到这一点的 - 这意味着如果两个或更多人连接并执行某些工作，则 auto_increment 将在这种情况下为两个人正确计算，如果他们正在访问同一个表。

这意味着对于每次插入，auto_increment都会递增。与任何记录都没有关系，这就是为什么 auto_increment 速度很快，并且您可以 100% 确定地获得唯一标识符。

发生的另一个有用的事情是 InnoDB 根据这个数字执行集群。换句话说，它使用这个数字来执行其内部数据结构平衡，以便在后期(读取/更新记录)提供性能。

现在为什么这很重要 - 如果您以任何方式更改此数字，InnoDB 将重新平衡其 B 树，并且需要一些时间才能做到这一点。这意味着 - 永远不要触及主键值。

但是，所有这些并不是真正相关。相关内容如下 - 为什么您需要更改staffID？如果保持原样，就不会发生任何不好的事情。