MySQL插入条件 [英] MySQL Insert if Condition

问题描述

我有这只猫的 id - post id 关系表.

I Have this cat id - post id relation table.

+----+--------+---------+
| id | cat_id | post_id |
|    |        |         |
| 1  |   11   |   32    |
| 2  |   ...  |   ...   |
+----+--------+---------+

我使用 SELECT WHERE cat_id = 11 AND post_id = 32 然后如果没有找到结果，我执行 INSERT.我可以将这两个查询合二为一吗?

I use SELECT WHERE cat_id = 11 AND post_id = 32 and then if no result found, I do INSERT. Can I rewrite these two queries in One?

推荐答案

你可以这样做:

insert into cats_rel(cat_id, post_id)
    select 11, 32
    where not exists (select 1 from cats_rel where cat_id = 11 and post_id = 32);

糟糕.以上在 MySQL 中不起作用，因为它缺少 from 子句(尽管在许多其他数据库中有效).无论如何，我通常将这些值放在子查询中，因此它们只会在查询中出现一次:

Oops. That above doesn't work in MySQL because it is missing a from clause (works in many other databases, though). In any case, I usually write this putting the values in a subquery, so they only appear in the query once:

insert into cats_rel(cat_id, post_id)
    select toinsert.cat_id, toinsert.post_id
    from (select 11 as cat_id, 32 as post_id) toinsert
    where not exists (select 1
                      from cats_rel cr
                      where cr.cat_id = toinsert.cat_id and cr.post_id = toinsert.post_id
                     );

这篇关于MySQL插入条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！