mysql 条件插入 - 如果不存在插入 [英] mysql conditional insert - if not exists insert

问题描述

是否有一个查询只检查记录，如果它不存在插入?我不想重复更新或替换.寻找一个查询解决方案，查看其他答案，但不是我所希望的.

Is there a query that will just check for the record and if it doesn't exists insert? I don't want to on duplicate update or replace. Looking for a one query solution, looked at other answer but not really what I was hoping for.

表:

name|value|id
------------------
phill|person|12345

伪查询:

IF NOT EXISTS(name='phill', value='person', id=12345) INSERT INTO table_name

推荐答案

使用 REPLACE - 工作方式与 INSERT 完全相同，只是如果表中的旧行与新行具有相同的值PRIMARY KEY 或 UNIQUE 索引，在插入新行之前删除旧行.

Use REPLACE - works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted.

http://dev.mysql.com/doc/refman/5.0/en/replace.html

-- For your example query
REPLACE INTO table_name(name, value, id) VALUES
('phill', 'person', 12345) 

由于您不能使用 REPLACE 另一个选项是:为表数据设置约束索引(主键、唯一性)并使用 INSERT IGNORE

Since you can't use REPLACE another option is to: set constraint indexes for the table data (primary key, uniqueness) and use INSERT IGNORE

INSERT IGNORE INTO table_name
SET name = 'phill',
    value = 'person',
    id = 12345;

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