MySQL UPSERT
UPSERT是DBMS软件
管理数据库的基本功能之一。此操作允许DML用户插入新记录或将现有数据更新到表中。 UPSERT由两个分别名为
UPDATE 和
INSERT 的单词组成。前两个字母,即UP代表UPDATE,而SERT代表INSERT。 UPSERT是一个原子操作,这意味着它是一步完成的操作。例如,如果一条记录是新的,它将触发INSERT命令。但是,如果表中已经存在该语句,则此操作将执行 UPDATE语句。
默认情况下, MySQL 为INSERT提供ON DUPLICATE KEY UPDATE选项,以完成此任务。但是,它也包含一些其他满足此目的的语句,例如INSERT IGNORE或REPLACE。我们将学习并详细了解所有这些解决方案。
MySQL UPSERT示例
我们主要可以通过以下三种方式执行MySQL UPSERT操作:
使用INSERT IGNORE进行UPSERT
使用REPLACE进行UPSERT
使用INSERT ON DUPLICATE KEY UPDATE进行UPSERT
使用INSERT IGNORE进行UPSERT
INSERT IGNORE语句用于忽略我们的执行错误。例如,主键列不允许我们存储重复值。如果尝试使用表中已存在的相同主键插入新记录,则会收到错误消息。但是,如果我们使用INSERT IGNORE命令执行此操作,它将生成警告而不是错误。
语法
以下是在MySQL中使用
INSERT IGNORE 语句:
INSERT IGNORE INTO table_name (column_names)
VALUES ( value_list), ( value_list) .....;
MySQL INSERT IGNORE示例
让我们了解MySQL中INSERT IGNORE语句的工作方式。首先,我们需要使用以下语句创建一个名为
" Student" 的表:
CREATE TABLE Student (
Stud_ID int AUTO_INCREMENT PRIMARY KEY,
Name varchar(45) default NULL,
Email varchar(45) NOT null UNIQUE,
City varchar(25) default NULL
);
唯一 约束可确保我们不能将重复值保留在
电子邮件列。接下来,需要将记录插入表中。以下语句用于将数据添加到表中:
INSERT INTO Student(Stud_ID, Name, Email, City)
VALUES (1,'Stephen', 'stephen@lidihuo.com', 'Texas'),
(2, 'Joseph', 'Joseph@lidihuo.com', 'Alaska'),
(3, 'Peter', 'Peter@lidihuo.com', 'California');
现在,我们可以使用
SELECT 语句来验证插入操作:
mysql> SELECT * FROM Student;
在表中有
3 行的地方,我们将获得以下输出:
现在,我们将执行以下语句,将两个记录添加到表中:
INSERT INTO Student(Stud_ID, Name, Email, City)
VALUES (4,'Donald', 'donald@lidihuo.com', 'New York'),
(5, 'Joseph', 'Joseph@lidihuo.com', 'Chicago');
执行上述语句后,我们将看到错误:
错误1062(23000): 密钥" student.Email"的重复条目" Joseph@lidihuo.com"
但是,当我们使用INSERT IGNORE命令执行相同的语句时,我们没有收到任何错误。相反,我们只会收到警告。
INSERT IGNORE INTO Student(Stud_ID, Name, Email, City)
VALUES (4,'Donald', 'donald@lidihuo.com', 'New York'),
(5, 'Joseph', 'Joseph@lidihuo.com', 'Chicago');
MySQL将产生一条消息: 添加了一行,而另一行被忽略了。
1 row affected, 1 warning(s): 1062 Duplicate entry for key email.
Records: 2 Duplicates: 1 Warning: 1
我们可以使用
SHOW WARNINGS 命令查看详细的警告:
因此,如果有重复项,并且我们使用INSERT IGNORE语句,MySQL会发出警告而不是发出错误,并将剩余的记录添加到表中。
使用REPLACE进行更新
在某些情况下,我们希望将现有记录更新到表中以保持更新。如果为此目的使用标准插入查询,它将为PRIMARY KEY错误提供重复条目。在这种情况下,我们将使用REPLACE语句执行我们的任务。当我们使用REPLACE命令时,会发生
两个可能的事件:
如果在现有数据行中找不到匹配值,则执行标准INSERT语句。
如果旧记录与新记录匹配,则replace命令将删除现有行,然后执行常规的INSERT语句,将新记录添加到表中。
在REPLACE语句中,更新分两个步骤执行。首先,它将删除现有记录,然后添加新更新的记录,类似于标准INSERT命令。因此,可以说REPLACE语句执行两个标准函数,即
DELETE 和
INSERT 。
REPLACE 的语法MySQL中的语句如下:
REPLACE [INTO] table_name(column_list)
VALUES(value_list);
示例
让我们在一个真实示例的帮助下理解它。在这里,我们将使用之前创建的
Student 表,其中包含以下数据:
现在,我们将使用REPLACE语句将ID为2的学生的城市更新为新的城市
洛杉矶。为此,请执行以下语句:
REPLACE INTO Student(Stud_ID, Name, Email, City)
VALUES(2, 'Joseph', 'joseph@lidihuo.com', 'Los Angeles');
成功执行后,我们将得到如下输出:
在在上面的图片中,我们仅更新了单行的值,我们看到的消息是
"受影响的2行" 。这是因为REPLACE命令首先删除了记录,然后将新记录添加到了表中。因此,该消息显示为"受影响的2行。"
使用INSERT ON DUPLICATE KEY UPDATE的UPSERT
到目前为止,我们已经看到了两个UPSERT命令,但是它们有一些限制。 INSERT IGNORE语句仅忽略重复错误,而不对表进行任何修改。并且REPLACE方法检测到INSERT错误,但是它将在添加新记录之前删除该行。因此,到目前为止,我们仍在寻找更完善的解决方案。
因此,我们将更完善的解决方案用作 INSERT ON DUPLICATE KEY UPDATE 语句。这是一种非破坏性方法,表示它不会删除重复的行。相反,当我们在SQL语句中指定ON DUPLICATE KEY UPDATE子句时,一行会在
UNIQUE或PRIMARY KEY 索引列中导致重复的错误值,然后更新
MySQL中
在重复密钥更新中插入语句的语法如下:
INSERT INTO table (column_names)
VALUES (data)
ON DUPLICATE KEY UPDATE
column1 = expression, column2 = expression...;
示例
让我们在一个真实示例的帮助下理解它。在这里,我们将使用之前创建的
Student 学生表。现在,使用以下查询在表中再添加一行:
INSERT INTO Student(Stud_ID, Name, Email, City)
VALUES (4,'John', 'john@lidihuo.com', 'New York');
此查询将成功将一个记录添加到表中,因为它没有任何重复的值。
接下来,执行下面的MySQL UPSERT命令,该命令更新
Stud_ID 列中的重复记录:
INSERT INTO Student(Stud_ID, Name, Email, City)
VALUES (4, 'John', 'john@lidihuo.com', 'New York')
ON DUPLICATE KEY UPDATE City = 'California';
成功执行后,MySQL给出以下消息:
Query OK, 2 rows affected.
在输出中,我们可以看到
行id = 4 已经存在。因此,该查询只会用
加利福尼亚更新
纽约市。