MySQL bulk insert on multiple tables

Question

I have a MySQL database with 2 tables products and product_variants. A product has many product variants. Here a sample:

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 |
+----+-------------+--------+

Now I need to bulk insert a lot of products with their variants in the most efficient and fastest way. I have a JSON with many products (100k+) like this:

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

from which I should generate a query to insert the products.

My idea is to use an insert query like this:

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

But then I don't know what product_id (foreign key) to use in the insert query for product_variants:

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

(these queries inside a transaction)

I've thought to specify the product ids manually, in increment way from the last id, but I get errors when concurrency connection insert products at the same time or when 2 or more bulk insert processes run concurrently.

What strategy can I use to achieve my goal? Is there a standard way to do this?

ps: if possible I would not want to change the structure of the 2 tables.

Answer 1

You can use the last_insert_id() to get the last generated ID from the last statement. But since this, as mentioned, only gets the last ID of the statement, requires that you handle each product individually. You can bulk insert the variants though. But from the structure of the given JSON I'd think that makes it even easier to traverse that JSON. Each product and it's variant should be inserted in an transaction so that the variants of a product don't get added to the previous product if the INSERT into the product table fails for some reason.

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