如果项目不存在，只插入表格[重复]

This question already has an answer here:

这个问题在这里已有答案：

• MySQL: Insert record if not exists in table 15 answers
• MySQL：如果表15中没有答案，则插入记录

My database has a table called fruit:

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

Table fruit

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

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

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

The query

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

Error

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

我使用一个名为Sequel Pro的SQL GUI应用程序，此查询错误如下：

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

您的SQL语法有错误;查看与您的MySQL服务器版本对应的手册，以便在'IF NOT EXISTS（SELECT name FROM fruit WHERE name ='mango'）附近使用正确的语法INSERT INTO frui'在第1行

Perhaps

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

可能会发生一些可疑的事情。查询可能正在INSERT INTO frui停止。应用程序有问题吗？或者我的查询错了？

1 个解决方案

#1

You'd have to use

你必须使用

ALTER TABLE fruit ADD UNIQUE (name) 

and then use

然后使用

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