title: MySQL bulk insert on multiple tables
body: <p>I have a MySQL database with 2 tables <code>products</code> and <code>product_variants</code>. A product has many product variants. Here a sample:</p>
  
  <pre><code>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 |
  +----+-------------+--------+
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>[
    {
      "name": "Foo",
      "variants": [{ "value": "red" }, { "value": "green" }, { "value": "blue" }]
    },
    {
      "name": "Bar",
      "variants": [{ "value": "red" }, { "value": "yellow" }]
    },
    ...
  ]
  </code></pre>
  
  <p>from which I should generate a query to insert the products.</p>
  
  <p>My idea is to use an <code>insert</code> query like this:</p>
  
  <pre><code>INSERT INTO `products` (name) VALUES ("foo"), ("bar"), ...;
  </code></pre>
  
  <p>But then I don't know what <code>product_id</code> (foreign key) to use in the insert query for <code>product_variants</code>:</p>
  
  <pre><code>INSERT INTO `product_variants` (product_id,value) VALUES (?,"red"), (?,"green"), ...;
  </code></pre>
  
  <p><em>(these queries inside a transaction)</em></p>
  
  <p>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.</p>
  
  <p>What strategy can I use to achieve my goal? Is there a standard way to do this?</p>
  
  <p>ps: if possible I would not want to change the structure of the 2 tables.</p>