问题描述
我在 MySQL 中有以下简单的插入查询
I have the following simple insert query in MySQL
insert into eventimages (eventid, imageid) values (x, y)
我想修改它,以便插入仅在不创建重复行时发生.我猜我需要在某处包含诸如
which I want to amend so that the insert only happens if it isn't creating a duplicate row.I'm guessing that somewhere I'd need to include something like
if not exists (select * from eventimages where eventid = x and imageid = y)
谁能帮忙解释一下语法.
Can anyone help with the syntax.
干杯
推荐答案
防止重复的正确"方法是在列对上放置唯一的约束/索引:
The "right" way to prevent duplicates is by putting a unique constraint/index on the column pair:
create unique index eventimages_eventid_imageid on eventimages(eventid, imageid);
那么这个条件将始终保证为真.常规插入将失败,创建重复项的 update
也会失败.以下是忽略此类错误的两种方法:
Then this condition will always be guaranteed to be true. A regular insert will fail, as will an update
that create a duplicate. Here are two ways to ignore such errors:
insert ignore into eventimages (eventid, imageid)
values (x, y);
这将忽略插入中的所有错误.那可能是矫枉过正了.你也可以这样做:
This will ignore all errors in the insert. That might be overkill. You can also do:
insert into eventimages(eventid, imageid)
values (x, y)
on duplicate key update eventid = x;
update
语句是空操作.目的只是为了抑制重复键错误.
The update
statement is a no-op. The purpose is just to suppress a duplicate key error.
这篇关于如果不存在语法 MySQL的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!