如果不存在语法 MySQL [英] If Not Exists syntax MySQL

问题描述

我在 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的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！