问题描述
为了说明我的问题，我将使用作者和书籍的类比.
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
)
;
这篇关于如何在表 MYSQL 的子集中自动递增的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！