在MySQL中插入到另一个表时，使用自动增量连接多个表的值

Question

MySQL中有两个表，如下所示：

请注意，category_id是类别表中category_key的FOREIGN KEY

我想在第三个表（名为“catalog”）中插入目录项，这样每个目录ID都将从其类别和子类别ID生成，每个类别子类别ID的串联编号都会自动递增。

例如：

假设“categories”表中有一行的categories_键为“ABC”，而“sub_categories”表中也有一行的sub_categories_键为“EFG”，那么目录表中的一行的键为“ABC-EFG-0001”，同一子类别中的另一行的键为“ABC-EFG-0002”，依此类推。

而对于其他ID值，例如.category_id="OMG"和sub_category_id="YYY"，数字的增量将从1开始，因此ID将是"OMG-YYY-0001"。

我可以举一个插入查询的例子吗？

谢谢

Answer 1

您可以使用ROW\u NUMBER（）（在MySQL 8.0中提供）来分配数字，并使用LPAD（）来格式化数字。请注意，您不需要使用表类别，因为所有相关信息都可以在子类别中找到（但如果您确实需要它，您只需添加一个联接）。

INSERT INTO catalog
SELECT 
    sc.*,
    CONCAT(
        category_key,
        '-',
        sub_category_key,
        '-',
        LPAD(
            ROW_NUMBER() OVER(PARTITION BY category_key, sub_category_key ORDER BY sub_category_key),
            4,
            0
        ) 
    )
FROM sub_categories sc