问插入不匹配的元组
EN

Stack Overflow用户

提问于 2020-05-28 12:41:13

回答 1查看 40关注 0票数 0

我有一个包含以下列的表：id、store、category_id和option_id。此表的内容是通过web-hook从不同服务器上的原始服务器更新的。一旦收到内容，我将需要检查哪些行需要删除，哪些需要插入。

为了简单起见，让我们假设我从web-hook收到的类别和选项id元组是(1,1)和(1,2)，并且数据库已经包含(1,1)和(1,3)。因此(1,3)将需要删除，(1,2)将需要插入。

我可以像这样很好地删除：

DELETE FROM store_category_options 
 WHERE store=1 AND (category_id, option_id) NOT IN ((1,1), (1,2));

但是，插入需要两个查询，其中一个用于检索数据库中已有的值

SELECT category_id, option_id FROM store_category_options WHERE store=1

然后在MYSQL之外计算出差值后，再插入一次：

INSERT INTO store_category_option (category_id, option_id) VALUES (1,2)

我想知道是否有一种方法可以用一个查询而不是两个查询来执行插入。

mysql

sql

tuples

where-in

回答 1

Stack Overflow用户

发布于 2020-05-31 08:49:44

最后我自己弄明白了。首先，我们可以使用WHERE NOT IN将webhook数据转换为可供选择的派生表

SELECT 1 as store_category_id, 1 as store_option_id UNION SELECT 1, 2;

在Mysql CLI中，这将生成以下输出：

+-------------------+-----------------+
| store_category_id | store_option_id |
+-------------------+-----------------+
|                 1 |               1 |
|                 1 |               2 |
+-------------------+-----------------+
2 rows in set (0.00 sec)

要使用where子句查询此表，需要将其包含在另一个select的子查询中，并为该表指定一个别名：

SELECT store_category_id, store_option_id FROM (
  SELECT 1 as store_category_id, 1 as store_option_id 
  UNION SELECT 1, 2
) AS vt WHERE store_option_id = 1;

这将生成以下输出：

+-------------------+-----------------+
| store_category_id | store_option_id |
+-------------------+-----------------+
|                 1 |               1 |
+-------------------+-----------------+
1 row in set (0.00 sec)

现在，我们可以使用wherein子句和另一个子查询来选择要插入的行：

SELECT store_category_id, store_option_id FROM (
  SELECT 1 as store_category_id, 1 as store_option_id 
  UNION SELECT 1, 2
) AS vt WHERE (category_id, option_id) NOT IN (
  SELECT store_category_id, store_option_id 
  FROM store_category_options
);

结果是：

+-------------------+-----------------+
| store_category_id | store_option_id |
+-------------------+-----------------+
|                 1 |               2 |
+-------------------+-----------------+
1 row in set (0.00 sec)

最后，为了插入我们计算出的行，我们将前面的select语句包含在insert INTO SELECT语句中

INSERT INTO store_category_options 
(store_category_id, store_option_id)
SELECT store_category_id, store_option_id FROM (
    SELECT 1 as store_category_id, 1 as store_option_id 
    UNION SELECT 1, 2
) AS vt WHERE (store_category_id, store_option_id) NOT IN (
    SELECT store_category_id, store_option_id 
    FROM store_category_options
);