MySQL批量插入多个表

Question

我有一个带有 2 个表的 MySQL 数据库 products和 product_variants .一个产品有多个产品变体。这里有一个示例:

products
+----+------+
| id | name |
+----+------+
|  1 | Foo  |
|  2 | Bar  |
+----+------+

product_variants
+----+-------------+--------+
| id | product_id  | value  |
+----+-------------+--------+
| 10 |           1 | red    |
| 11 |           1 | green  |
| 12 |           1 | blue   |
| 13 |           2 | red    |
| 14 |           2 | yellow |
+----+-------------+--------+

现在我需要以最有效和最快的方式批量插入大量产品及其变体。我有一个包含许多产品(100k+)的 JSON，如下所示:

[
  {
    "name": "Foo",
    "variants": [{ "value": "red" }, { "value": "green" }, { "value": "blue" }]
  },
  {
    "name": "Bar",
    "variants": [{ "value": "red" }, { "value": "yellow" }]
  },
  ...
]

我应该从中生成一个查询来插入产品。

我的想法是使用 insert像这样查询:

INSERT INTO `products` (name) VALUES ("foo"), ("bar"), ...;

但后来我不知道是什么 product_id (外键)在 product_variants 的插入查询中使用:

INSERT INTO `product_variants` (product_id,value) VALUES (?,"red"), (?,"green"), ...;

(这些查询在事务中)

我想手动指定产品 id，从最后一个 id 开始递增，但是当并发连接同时插入产品或同时运行 2 个或更多批量插入进程时，我会收到错误消息。

我可以使用什么策略来实现我的目标？有没有标准的方法来做到这一点？

ps:如果可能的话，我不想改变这两个表的结构。

Answer 1

您可以使用 last_insert_id()从最后一条语句中获取最后生成的 ID。但是，正如前面提到的，这只会获取语句的最后一个 ID，因此需要您单独处理每个产品。不过，您可以批量插入变体。但是从给定 JSON 的结构来看，我认为这使得遍历该 JSON 变得更加容易。每个产品及其变体都应插入到交易中，以便在 INSERT 时，产品的变体不会被添加到前一个产品中。由于某种原因进入产品表失败。

START TRANSACTION;
INSERT INTO products
            (name)
            VALUES ('Foo');
INSERT INTO product_variants
            (product_id,
             value)
            VALUES (last_insert_id(),
                    'red'),
                   (last_insert_id(),
                    'green'),
                   (last_insert_id(),
                    'blue');
COMMIT;

START TRANSACTION;
INSERT INTO products
            (name)
            VALUES ('Bar');
INSERT INTO product_variants
            (product_id,
             value)
            VALUES (last_insert_id(),
                    'red'),
                   (last_insert_id(),
                    'yellow');
COMMIT;

db<>fiddle