使用MySQL 8.0触发器自动生成内部产品编号

Question

我对SQL的模式端没有太多经验，当新行输入数据库时，我必须创建一个系统来生成内部产品编号。我已经决定使用SQL触发器将是最好的方法，而不是外部编程，因为它在SQL内部看起来更整洁、可行。

内部产品编号示例：101-001

内部产品编号有两部分，我分别称为group_id和product_id。group_id与category_id(它是如何存储在数据库中的(有关，我正在使用一个表PartCategory来连接它们。product_id在每个group_id中都是递增的，但如果删除了一行，它们将继续，就像没有删除一样，我认为MAX是合适的。

以下是我正在使用的两个表的示例：

DROP TABLE IF EXISTS Part;
CREATE TABLE Part (
id INT AUTO_INCREMENT PRIMARY KEY,
category_id INT,
internalPartNumber VARCHAR(255),
name VARCHAR(255),
createdate DATETIME,
group_id INT,
product_id INT
);
DROP TABLE IF EXISTS PartCategory;
CREATE TABLE PartCategory (
id INT AUTO_INCREMENT PRIMARY KEY,
category_id INT,
name VARCHAR(255),
internal_id INT,
last_product_id INT
);

INSERT INTO PartCategory (internal_id, category_id, name) VALUE (101, 2, 'category1');
INSERT INTO PartCategory (internal_id, category_id, name) VALUE (102, 3, 'category2');
INSERT INTO Part (category_id, name, createdate, group_id, product_id) VALUE (2,   'screw', NOW(), 101, 1);
INSERT INTO Part (category_id, name, createdate, group_id, product_id) VALUE (2, 'spanner', NOW(), 101, 2);
INSERT INTO Part (category_id, name, createdate, group_id, product_id) VALUE (3,  'hammer', NOW(), 102, 1);
INSERT INTO Part (category_id, name, createdate, group_id, product_id) VALUE (3,   'paint', NOW(), 102, 2);
DELIMITER //
CREATE TRIGGER add_group_id BEFORE INSERT ON Part
FOR EACH ROW
BEGIN
UPDATE Part
LEFT JOIN PartCategory
ON Part.category_id = PartCategory.category_id
SET NEW.group_id = PartCategory.internal_id
WHERE Part.group_id IS NULL;

UPDATE Part
SET NEW.product_id = MAX(Part.product_id) OVER(PARTITION BY Part.category_id ORDER BY createdate)
WHERE NEW.product_id IS NULL;

SET NEW.internalPartNumber = CONCAT(RIGHT(NEW.group_id+1000, 3),'-',RIGHT(NEW.product_id+1000, 3))
WHERE NEW.internalPartNumber IS NULL;
END; //
DELIMITER ;
INSERT INTO Part (category_id, name, createdate) VALUE (2, 'bolt', NOW());
INSERT INTO Part (category_id, name, createdate) VALUE (3, 'metal', NOW());
SELECT internalPartNumber, name FROM Part
ORDER BY internalPartNumber;

然而，在触发器内部，我不能使用表Part，因为它调用了触发器。我不知道该怎么做。前六个插入表示通过脚本输入数据库的数据，这些插入可以设置任何行，后两个插入表示用户将输入的数据(触发器需要处理的数据(。

从这个例子中，我想看到：

internalPartNumber | name
101-001            | screw
101-002            | spanner
101-003            | bolt
102-001            | hammer
102-002            | paint
102-003            | metal

Answer 1

CREATE TRIGGER tr_bi_Part
BEFORE INSERT
ON Part
FOR EACH ROW
SET NEW.internalPartNumber = CONCAT( ( SELECT internal_id 
FROM PartCategory 
WHERE category_id = NEW.category_id ),
'-',
( SELECT LPAD(1 + COALESCE(COUNT(*), 0), 3, '0')
FROM Part
WHERE category_id = NEW.category_id ) );

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=58fd1bc356c0a2f771d8160235be3c0c