如何在表 MYSQL 的子集中自动递增

2021-11-30 00:00:00 mysql auto-increment

为了说明我的问题，我将使用作者和书籍的类比.

To illustrate my problem I will use the analogy of authors and books.

我有 2 个表作者"和书".作者是独一无二的，书籍使用外键约束与特定作者相关联.

I have 2 tables "author" and "books". Authors are unique and books are tied to a specific authors using a foreign key constraint.

我想知道是否有可能在books"表中有一个名为booknum"的列，它会在单个作者的子集中自动递增.因此，如果该表有 100 行并且我插入了作者的第 4 本书，那么它会将 4 放入booknum"列中.

I was wondering if it was possible to have a column called "booknum" in the "books" table that auto-increment within the subset of a single author. So if the table has 100 rows and im inserting the 4th book of an author it puts a 4 into the "booknum" column.

例如，如果书籍表有 6 行:

For example if the books table had 6 rows:

id | authors_id | booknum | name

1  | 1          | 1       | "hello"

2  | 1          | 2       | "goodbye"

3  | 2          | 1       | "booktitle"

4  | 3          | 1       | "more title"

5  | 1          | 3       | "nametwo"

6  | 2          | 2       | "nameone"

这在 mysql 中是可能的还是我需要去检查最后创建的书并在添加书时手动增加?

Is this possible within mysql or do I need to go and check for the last created book and manually increment when I add a book?

推荐答案

您可以使用触发器:

CREATE TRIGGER biBooks 
  BEFORE INSERT ON books 
  FOR EACH ROW SET NEW.booknum = (
    SELECT COALESCE(MAX(booknum), 0) + 1 
      FROM books 
      WHERE authors_id = NEW.authors_id
  )
;

相关文章