如何在 MySQL 中插入还不存在的行

Rayven Esplanada 2023年1月30日 2020年11月7日
  1. 在 MySQL 中使用 REPLACE 来插入还不存在的记录
  2. 在 MySQL 中使用 INSERT IGNORE 来插入如果不存在的信息
  3. 在 MySQL 中使用 INSERT ... ON DUPLICATE KEY UPDATE 插入如果不存在的记录
如何在 MySQL 中插入还不存在的行

本教程告诉你如何在 mySQL 中插入一条还不存在的记录到表中。

有三种简单的方法可以完成这个问题,通过使用 REPLACEINSERT IGNORE 或者 INSERT ... ON DUPLICATE KEY UPDATE

首先,我们想象一下,我们有一个表 person,结构是这样的。

CREATE TABLE person (
id INT NOT NULL PRIMARY KEY,
name VARCHAR(50) NOT NULL,
age INT NOT NULL,
address VARCHAR(128) 
);

有以下现有记录。

id name age address
1 John Doe 27 New York, New York
2 Jane Doe 26 Berlin, Germany
3 Chun Li 24 Beijing, China
4 Thor Odinson 40 Asgard

现在让我们进入实际的解决方案。

在 MySQL 中使用 REPLACE 来插入还不存在的记录

REPLACE 的作用是,当发现现有记录时,它将覆盖现有记录;如果它还不存在,它将插入一条新的记录。它本质上和 UPDATE 一样,但具有 INSERT 功能。

让我们提供一个示例查询,对 John Doe 执行 REPLACE

REPLACE INTO 'person'
SET id = 1,
name = 'John Doe'`,
age = 28,
address = 'Los Angeles, California'

如果 John Doe 存在,他将被新数据覆盖;如果他不存在,将为 John Doe 创建一个新条目。

然而,这种方法对于插入来说并不高效,因为它覆盖了现有的记录,而不是在发现时直接跳过。虽然它能完成工作,但并不是最好的方法。

当查询选择 John Doe 时,新的结果将是这样的。

id name age address
1 John Doe 28 Los Angeles, California

John Doe 的年龄和地址已经被 REPLACE 脚本修改或更新。

在 MySQL 中使用 INSERT IGNORE 来插入如果不存在的信息

我们在上面的表上也会使用同样的未编辑数据来进行。

INSERT IGNORE 上,如果记录的主键已经存在于数据库中,则会被悄悄忽略或跳过。

如果我们把 IGNORE 关键字去掉,插入一条 id 或主键已经存在的记录,会中止查询,并显示错误信息,说主键的记录已经存在。

INSERT 之后,IGNORE 则是代替了因为错误而中止,而只是继续查询,虽然没有插入新记录。

INSERT IGNORE person
SET 'id' = 4,
age = 1000,
address = 'Chicago'

根据我们上面声明的表,已经存在一条 id 或主键 4 的记录(Thor Odinson)。因此,这个查询被脚本忽略,从而没有在数据库中更新或插入一条记录。

如果我们在中查询 Thor,结果将是。

id name age address
4 Thor Odinson 40 Asgard

查询中的年龄和地址完全没有影响到 id 为 4 的记录。

在 MySQL 中使用 INSERT ... ON DUPLICATE KEY UPDATE 插入如果不存在的记录

如果你使用 ON DUPLICATE KEY UPDATE 子句,并且你要插入的行是 UNIQUE 索引或主键中的重复,该行将执行 UPDATE

这本质上和 REPLACE 做的事情是一样的。

比方说,我们要插入一条 id 为 2 的记录。

INSERT INTO person (id, name, age, address)
VALUES (1, 'Jane Deer', '33', 'Tallahassee Florida')
ON DUPLICATE KEY UPDATE id = id+1;

id 是递增的,以生成一个新的记录,而不是更新现有的记录。

如果我们查询 person 的所有记录,那么结果会是这样。

id name age address
1 John Doe 27 New York, New York
2 Jane Doe 26 Berlin, Germany
3 Chun Li 24 Beijing, China
4 Thor Odinson 40 Asgard
5 Jane Deer 33 Tallahasee, Florida

Jane Deer, 不是取代 Jane doe, 而是在表中增加了一条新记录。

处理插入和检查它的存在,最有效的方法肯定是使用 INSERT IGNORE

不过,另外两个关键字也是可以根据具体情况使用的,如果有一天你想更新你要插入的行是否存在,就使用 REPLACE。如果你不想修改行,但真的想插入新的记录,使用 INSERT ... ON DUPLICATE KEY UPDATE

Rayven Esplanada avatar Rayven Esplanada avatar

Skilled in Python, Java, Spring Boot, AngularJS, and Agile Methodologies. Strong engineering professional with a passion for development and always seeking opportunities for personal and career growth. A Technical Writer writing about comprehensive how-to articles, environment set-ups, and technical walkthroughs. Specializes in writing Python, Java, Spring, and SQL articles.

LinkedIn