MySQL插入不存在/如果不存在 [英] MySQL insert where not exists / if not exists

问题描述

我尝试了以下查询:

INSERT INTO `surfed_site` (user, site)
VALUES ('123', '456')
WHERE NOT EXISTS (SELECT site FROM `surfed_site` WHERE site=456)

但是我不断收到MySQL错误:

But I keep getting a MySQL error:

我不知道我在做什么错，有人可以指导我吗?

I have no clue what I'm doing wrong, would anybody be able to guide me?

推荐答案

INSERT语句支持两种 ¹ 语法:一种使用VALUES，一种使用查询.您不能将它们组合在一起，只有查询语法支持WHERE条款.所以:

INSERT statements support two¹ syntaxes: one that uses VALUES, and one that uses a query. You can't combine them, and only the query syntax supports WHERE clauses. So:

INSERT INTO `surfed_site` (user, site)
SELECT '123', '456' FROM (SELECT 1) t
WHERE NOT EXISTS (SELECT site FROM `surfed_site` WHERE site=456)

---

1. 实际上是三种语法；您也可以使用SET.如果您只插入一条记录，则该条记录在功能上等同于VALUES，但可读性更高.

1. Actually three syntaxes; you can also use SET. If you're only inserting one record, this one is functionally equivalent to VALUES, but arguably more readable.

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