mysql - 如果不存在则插入一对多关系(在单个查询中)？

Question

考虑下表:

产品

+----+------+-------------+
| id | name | category_id |
+----+------+-------------+
| 1  | foo  |           1 |
+----+------+-------------+

类别

+----+------+
| id | name |
+----+------+
| 1  | food |
+----+------+

现在假设我有人发布了新产品:

{
  "name": "bar",
  "category": "drink"
}

在这种情况下，我们需要自动创建新类别:

INSERT IGNORE INTO categories (name) VALUES ('drink')

然后我们终于可以插入实际的产品行:

INSERT INTO products (name, category_id) VALUES ('bar', SELECT id FROM categories WHERE name = 'drink')

但是，尽管这有效，但它需要安全的事务设置，并且由于这似乎不是一个 super 复杂的查询，我想知道是否可以将两个查询合并在一起(例如，将将类别查询插入产品插入的选择子查询中)？

Answer 1

我建议不要 INSERT IGNORE ，原因是@Bill Karwin so eloquently explains 。此外，如 INSERT ... ON DUPLICATE KEY UPDATE Syntax 下所述:

If a table contains an AUTO_INCREMENT column and INSERT ... ON DUPLICATE KEY UPDATE inserts or updates a row, the LAST_INSERT_ID() function returns the AUTO_INCREMENT value. Exception: For updates, LAST_INSERT_ID() is not meaningful prior to MySQL 5.1.12. However, you can work around this by using LAST_INSERT_ID(<em>expr</em>). Suppose that id is the AUTO_INCREMENT column. To make LAST_INSERT_ID() meaningful for updates, insert rows as follows:

INSERT INTO table (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id), c=3;

然后可以获得 id (无论是预先存在的还是新插入的)如How to Get the Unique ID for the Last Inserted Row下记录的:

If you insert a record into a table that contains an AUTO_INCREMENT column, you can obtain the value stored into that column by calling the mysql_insert_id() function.

[ deletia ]

When a new AUTO_INCREMENT value has been generated, you can also obtain it by executing a SELECT LAST_INSERT_ID() statement with mysql_query() and retrieving the value from the result set returned by the statement.

例如，人们可以简单地这样做:

INSERT INTO categories (name) VALUES ('drink')
  ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id);

INSERT INTO products (name, category_id) VALUES ('bar', LAST_INSERT_ID());

当然，如果您需要原子性，这些语句仍然需要在事务中执行。