MySQL8实用系列（四）常用DML汇总：插入、更新、删除数据以及在MySQL中实现UPSERT操作（最新）

bumerang�

已于 2024-05-10 16:32:32 修改

阅读量745

点赞数 15

分类专栏： Mysql 文章标签：数据库 mysql sql

于 2024-02-19 11:05:18 首次发布

本文链接：https://blog.csdn.net/weixin_44512005/article/details/136152966

版权

Mysql 专栏收录该内容

8 篇文章 2 订阅

订阅专栏

DROP、TRUNCATE、DELETE的区别（面试常问）

五、IGNORE关键字

六、更新和删除的指导原则

DML数据操作语言，主要用于操作数据，插入、删除、修改表中的数据，和SQL中的SELECT称为CRUD（增删改查）。MySQL中如何使用SQL插入、删除、更新数据？MySQL中如何进行UPSERT操作？如何使用INSERT ON DUPLICATE KEY UPDATE对数据进行插入或更新？水位线是什么？如何降低高水位线？我将在本文进行详细介绍。

环境：MySQL 8.0.32

下文语法中[]包括的内容表示可选项

一、插入数据 INSERT

插入单条数据

INSERT INTO `表名`[(`字段1`,`字段2`,...)] VALUES (值1,值2,...);

说明：

值和字段必须一一对应，个数一致，位置对应
字段不为空必须插入值；可以为空则插入空值时字段和对应值可都省略不写，或者使值为NULL
字段类型为字符型或日期类型，值需加单引号；字段类型为数值型则不需要
表名后的字段可全部省略，表示所有字段，顺序和表中字段顺序一致（可通过表结构查看）
数据库管理工具中可使用添加记录插入单条数据。

批量插入数据

插入多条数据的两种方式

方式1

INSERT INTO `表名`[(`字段1`,`字段2`,...)] VALUES (值1,值2),(值3,值4),...;

方式2

INSERT INTO 表名 [(字段1,字段2,...)] 数据来源SELECT语句;

说明：数据来源SELECT语句返回的结果和插入数据的表的字段数量、类型和顺序必须一致。参考系列文章MySQL8实用系列（五）DQL关键字汇总详解：查询、分组、排序、分页限制-CSDN博客

二、更新数据 UPDATE

单表更新

UPDATE `表名` [[AS] `别名`] 
SET [`别名`.]`字段1`=值1,[`别名`.]`字段2`=值2,... 
[WHERE条件];

说明：可使用AS别名替代表名。不要省略WHERE作为更新条件，如果不限制则全表更新。如两表之间无需关联，建议使用单表更新。

多表更新

UPDATE `表1` [[AS] `别名1`],`表名2` [[AS] `别名2`]
SET [`别名`.]`字段`=值1,[`别名`.]`字段`=值2,...
[WHERE条件];

说明：WHERE可作为更新条件，表与表之间通过关联条件使用表的字段值更新。例如一个表的字段可以使用另一个表的字段的值更新。

如需删除某个列的值，可使用UPDATE更新使其值为NULL。

三、插入或更新 UPSERT

平时我们设计数据库的时候一般会加上PRIMARY KEY，这时候我们插入一条数据经常出现一种情况：若数据表中存在以相同主键的记录，我们就更新该条记录；否则就插入一条新的记录。

这时候就需要UPSERT操作。默认情况下，MySQL提供了ON DUPLICATE KEY UPDATE选项给INSERT语句来实现UPSERT功能，它是一个原子操作，特别适用于并发状态下的批量插入或更新数据。

我们可以三个方法实现MySQL UPSERT操作：

INSERT IGNORE
REPLACE
INSERT ON DUPLICATE KEY UPDATE

INSERT IGNORE

详见下文第五点

REPLACE INTO

表中需有PRIMARY KEY或UNIQUE索引，否则此语句无意义。插入新记录，如因PRIMARY KEY或UNIQUE索引出现duplicate error而造成插入失败时，从表中删除出现关键字冲突的行，再执行插入。

有以下三种语法：

REPLACE [INTO] `表名`(`字段1`, `字段2`, ...) VALUES(`值1`, `值2`);
REPLACE [INTO] `表名`(`字段1`, `字段2`, ...) SELETE语句;
REPLACE [INTO] `表名` SET `字段1` = `值1`, `字段2` = `值2`, ...;

注意：

所有字段的值均取自在REPLACE INTO语句中被指定的值。所有缺失的字段被设置为各自的默认值，即所有字段都需赋值！
使用REPLACE INTO，必须拥有表的INSERT和DELETE权限。

INSERT ON DUPLICATE KEY UPDATE

此语句不会删除已有行，当使用此子句并且有一行数据在PRIMARY KEY或UNIQUE索引上产生duplicate error时，会在已有的数据上做更新，否则直接执行插入。

INSERT INTO `表名`(`字段1`, `字段2`, ...)  
VALUES (`值1`, `值2`, ...)  
ON DUPLICATE KEY UPDATE   
`更新字段`= `更新值`, ...;

注意：

主键和唯一索引同时冲突时，只更新主键冲突的记录
唯一索引冲突，则根据更新字段进行更新。如更新字段中有主键索引，则需避免主键索引冲突，否则出现duplicate error。
主键索引冲突，则根据更新字段进行更新。如更新字段中有唯一索引，则需避免唯一索引冲突，否则出现duplicate error。

后续文章会针对INSERT ON DUPLICATE KEY UPDATE进行详解

四、删除数据

DELETE单表删除

DELETE FROM `表名` [[AS] `别名`] [WHERE条件];

说明：可使用AS别名替代表名。不要省略WHERE作为筛选条件，如果不限制则删除所有数据。DELETE删除的是整行而不是删除列，不需要列名或通配符。

DELETE多表删除

DELETE [`别名`或`表名`] FROM `表1` [[AS] `别名1`],`表2` [[AS] `别名2`] [WHERE条件];

说明：WHERE可作为筛选条件，表与表之间可通过关联进行删除。例如可以根据一个表的数据删除另一个表存在的相同记录；或同时删除两个表的相同记录。

TRUNCATE表截断

TRUNCATE `表名`;

说明：截断表。只能作用于表。

注意：执行TRUNCATE需要DROP权限（不建议给账号此权限）

DROP、TRUNCATE、DELETE的区别（面试常问）

TRUNCATE、DROP是DDL语句，执行后无法回滚；DELETE是DML语句，可回滚
TRUNCATE只能作用于表；DROP、DELETE可作用于表、视图等
TRUNCATE会清空表中的所有行，但表结构及其约束、索引等保持不变；DROP会删除表的结构及其所依赖的约束、索引等
TRUNCATE会重置表的自增值，降低水位线；DELETE分情况，如果数据库被重启，自增列会初始化；如果数据库未被重启，则不变。
TRUNCATE、DROP不会激活与表有关的删除触发器trigger；DELETE会触发
TRUNCATE后会使表和索引所占用的空间会恢复到初始大小；DELETE操作不会减少表或索引所占用的空间，DROP语句将表所占用的空间全释放掉

高水位线：在 MYSQL中，高水位线 (High Watemark)是指数据表中已分配但是尚未使用的空间。当我们在表中插入数据时，MYSQL会为新数据分配一定的空间，但是使用DELETE删除数据时，这些空间并不会被立即释放，而是被标记为可重用的。当表的高水位线达到一定程度时，它可能导致数据库性能下降并浪费存储空间。

降低高水位线的方法

方法一：OPTIMIZE TABLE

OPTIMIZE TABLE是一种优化数据表的方法，它可以重新组织数据并去除高水位线。当我们运行命令时，MySOL会将数据表重新组织，并释放被删除数据所占用的空间。

注意：运行 OPTIMIZE TABLE 命令会锁定数据表，并且可能会占用较长时间，因此在生产环境中需要谨慎使用。

方法二：TRUNCATE TABLE

使用场景：

删除部分数据，或者有FOREIGN KEY约束引用的表，用DELETE，注意带上where子句
删除表，用DROP
保留表而将所有教据删除且和事务无关，用TRUNCATE即可
和事务有关，或者需要触发trigger，用DELETE
整理表内部的碎片，TRUNCATE后再重新插入数据

注意：

删除速度：DROP>TRUNCATE>DELETE

五、IGNORE关键字

通常与数据修改语句（如INSERT、UPDATE和DELETE）一起使用（位于关键字后），其作用是使得操作在遇到错误时不会终止执行，而是忽略错误并继续处理后续的数据。

INSERT语句插入数据，并且在插入一行或多行时出现违反唯一索引约束等错误时，整个操作会回滚并且生成一个错误。使用IGNORE关键字这条记录的将被忽略，不会插入表中，同时不会产生错误。相比于无关键字插入，对性能的影响较小，因为它减少了错误处理的开销。
UPDATE语句更新数据，并且在更新这些行中的一行或多行时出现违反唯一索引约束等错误时，则整个更新操作会被取消（错误发生前更新的所有行会恢复到它们原来的值）。使用IGNORE关键字这条记录的更新将被忽略，但是其他记录的更新仍然会继续执行。
DELETE语句不会因约束违反而失败。使用IGNORE关键字可防止由于删除操作违反外键约束而导致的错误。

注意： 使用IGNORE关键字可以提高大批量数据处理的容错性，但同时也要意识到可能会静默地忽略掉一些重要的错误信息。因此，在使用IGNORE时应当谨慎，确定了解可能会发生的数据丢失或数据不一致的风险。

六、更新和删除的指导原则

实际工作中，我们必须遵循一下习惯

确实更新或删除每一行，否则绝对绝对不要使用不带WHERE子句的UPDATE和DELETE语句。
在对UPDATE或DELETE语句使用WHERE子句前，应先使用SELECT进行测试，保证过滤的是要操作的正确的记录，以防编写的WHERE子句不正确。
应该非常小心使用UPDATE和DELETE，不能撤销！
谨慎使用IGNORE关键字，否则可能产生数据丢失或数据不一致等的问题。
小心使用DROP和TRUNCATE，每次操作前务必备份。

后续系列中会介绍MySQL更多知识。如有问题和建议，可私信或评论，非常感谢。

bumerang�

关注

15
点赞
踩
11

收藏

觉得还不错? 一键收藏
1
评论
MySQL8实用系列（四）常用DML汇总：插入、更新、删除数据以及在MySQL中实现UPSERT操作（最新）

MySQL的DML汇总，插入、删除、更新数据，upsert插入或更新的操作和出现索引冲突时数据如何变化，drop、truncate和delete的区别，以及水位线和降低高水位的方法
复制链接

扫一扫