InnoDB中的自增复合键

Question

我想为一个InnoDB MySQL表创建类似于MyISAM的行为。 我想要一个组合主键：

PRIMARY KEY(id1，id2)

其中id1根据id2的值自动递增。 使用InnoDB实现这一点的最佳方法是什么？

+----------+-------------+--------------+
|      id1 |         id2 | other_column |
+----------+-------------+--------------+
|        1 |           1 | Foo          |
|        1 |           2 | Bar          |
|        1 |           3 | Bam          |
|        2 |           1 | Baz          |
|        2 |           2 | Zam          |
|        3 |           1 | Zoo          |
+----------+-------------+--------------+

- CaptainStiggz

---

“Where id1 auto increments based on the value of id2.” 这句话是什么意思？ - Cameron Martin

那么id2是手动插入的，只有在主键冲突时id1才会递增？ - Cameron Martin

你为什么想要这种行为？ - Cameron Martin

id1是消息ID，id2是聊天室ID。我希望每个聊天室的第一条消息都具有ID为1。 - CaptainStiggz

@CameronMartin 我猜CaptainStiggz想要避免为每个评论存储一个唯一的整数，因为数字可能会变得非常大？或者他正在将MyISAM表更改为InnoDB，并且需要这样做以避免更改代码或表格？ - Timo Huovinen

Answer 1

您可以使用此BEFORE INSERT触发器来替换零ID值 -

CREATE TRIGGER trigger1
  BEFORE INSERT
  ON table1
  FOR EACH ROW
BEGIN

  SET @id1 = NULL;

  IF NEW.id1 = 0 THEN
    SELECT COALESCE(MAX(id1) + 1, 1) INTO @id1 FROM table1;
    SET NEW.id1 = @id1;
  END IF;

  IF NEW.id2 = 0 THEN

    IF @id1 IS NOT NULL THEN
      SET NEW.id2 = 1;
    ELSE
      SELECT COALESCE(MAX(id2) + 1, 1) INTO @id2 FROM table1 WHERE id1 = NEW.id1;
      SET NEW.id2 = @id2;
    END IF;

  END IF;

END

然后插入零值（id1或id2）以生成新值 -

INSERT INTO table7 VALUES(0, 0, '1');
INSERT INTO table7 VALUES(0, 0, '2');
INSERT INTO table7 VALUES(1, 0, '3');
INSERT INTO table7 VALUES(1, 0, '4');
INSERT INTO table7 VALUES(1, 0, '5');
...

- Devart