仅当项目不存在时才插入表中 [英] Only insert into table if item does not exist

问题描述

我的数据库有一个名为fruit的表:

My database has a table called fruit:

+-------------+
| id | name   |
+ ----------- +
| 1  | apple  |
| 2  | orange |
| 3  | banana |
| 4  | grape  |
+-------------+

id是主键.我想将条目添加到表中，但前提是它们尚不存在.

id is the a primary key. I want to add entries to the table, but only if they don't exist already.

IF NOT EXISTS (SELECT name FROM fruit WHERE name = 'mango')
INSERT INTO fruit (name) 
VALUES ('mango')

错误

我使用一个名为 Sequel Pro 的SQL GUI应用程序，该查询出现以下错误:

Error

I use a SQL GUI app called Sequel Pro, and this query errors with the following:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF NOT EXISTS (SELECT name FROM fruit WHERE name = 'mango') INSERT INTO frui' at line 1

可能有些腥味.查询可能在INSERT INTO frui处停止.应用程式有问题吗?还是我的查询错了?

Something fishy may be going on. The query may be stopping at INSERT INTO frui. Problem with the app? Or is my query wrong?

推荐答案

您必须使用

ALTER TABLE fruit ADD UNIQUE (name) 

然后使用

INSERT IGNORE INTO fruit (name) VALUES ('mango')

这篇关于仅当项目不存在时才插入表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！