【第9天】SQL进阶-删除记录（SQL 小虚竹)

原创

小虚竹51CTO 2022-10-24 00:29:03 ©著作权

文章标签 sql 数据库数据加锁 文章分类 代码人生

©著作权归作者所有：来自51CTO博客作者小虚竹51CTO的原创作品，请联系作者获取转载授权，否则将追究法律责任

文章目录

零、前言
一、练习题目
二、SQL思路

删除记录:SQL115 删除记录（一）

初始化数据
解法

删除记录:SQL116 删除记录（二）

初始化数据
解法

三、扩展

零、前言

今天是学习 SQL 打卡的第 9 天，每天我会提供一篇文章供群成员阅读

希望大家先自己思考，如果实在没有想法，再看下面的解题思路，自己再实现一遍。在小虚竹JAVA社区中对应的【打卡贴】打卡，今天的任务就算完成了，养成每天学习打卡的好习惯。

虚竹哥会组织大家一起学习同一篇文章，所以有什么问题都可以在群里问，群里的小伙伴可以迅速地帮到你，一个人可以走得很快，一群人可以走得很远，有一起学习交流的战友，是多么幸运的事情。

我的学习策略很简单，题海策略+ 费曼学习法。如果能把这些题都认认真真自己实现一遍，那意味着 SQL 已经筑基成功了。后面的进阶学习，可以继续跟着我，一起走向架构师之路。

今天的学习内容是：SQL进阶-删除记录

一、练习题目

题目链接	难度
删除记录:SQL115 删除记录（一）	★☆☆☆☆
删除记录:SQL116 删除记录（二）	★★☆☆☆

二、SQL思路

删除记录:SQL115 删除记录（一）

【第9天】SQL进阶-删除记录（SQL 小虚竹)_sql

初始化数据

drop table if EXISTS exam_record;
CREATE TABLE IF NOT EXISTS exam_record (
id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
uid int NOT NULL COMMENT '用户ID',
exam_id int NOT NULL COMMENT '试卷ID',
start_time datetime NOT NULL COMMENT '开始时间',
submit_time datetime COMMENT '提交时间',
score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8_general_ci;
TRUNCATE exam_record;
INSERT INTO exam_record(uid, exam_id, start_time, submit_time, score) VALUES
(1001, 9001, '2020-01-01 22:11:12', '2020-01-01 23:16:12', 50),
(1001, 9002, '2020-01-02 09:01:01', '2020-01-02 09:06:00', 58),
(1002, 9001, '2021-05-02 10:01:01', '2021-05-02 10:05:58', 60),
(1002, 9002, '2021-06-02 19:01:01', '2021-06-02 19:05:01', 54),
(1003, 9001, '2021-09-05 19:01:01', '2021-09-05 19:40:01', 49),
(1003, 9001, '2021-09-05 19:01:01', '2021-09-05 19:15:01', 70),
(1003, 9001, '2021-09-06 19:01:01', '2021-09-06 19:05:01', 80),
(1003, 9002, '2021-09-09 07:01:02', null, null);

解法

要求处理：

请删除exam_record表中作答时间小于5分钟整且分数不及格（及格线为60分）的记录；
后台会执行您的SQL，然后通过 SELECT * FROM exam_record; 语句来筛选出剩下的数据，与正确数据进行对比。

分析：

删除exam_record表中作答时间小于5分钟整，作答时间的时间差，是由开始时间start_time与提交时间submit_time的时间差来算的
这里要用到timestampdiff()函数

语法:timestampdiff(unit,datetime_expr1,datetime_expr2)
返回datetime_expr1与datetime_expr2之间的整数差。
unit参数可以是：FRAC_SECOND。毫秒;SECOND。秒;MINUTE。分钟;HOUR。小时;DAY。天;WEEK。星期;MONTH。月;QUARTER。季度;YEAR。年

先查询，后面再替换删除语句

SELECT *
FROM exam_record
WHERE 
timestampdiff(minute,start_time,submit_time) < 5

【第9天】SQL进阶-删除记录（SQL 小虚竹)_sql_02

且分数不及格（及格线为60分）的记录，那就是score<60

SELECT *
FROM exam_record
WHERE 
timestampdiff(minute,start_time,submit_time) < 5
AND
score < 60 ;

【第9天】SQL进阶-删除记录（SQL 小虚竹)_数据库_03

确认查询的数据就是要删除时，就替换删除语句

DELETE FROM exam_record
WHERE 
timestampdiff(minute,start_time,submit_time) < 5
AND
score < 60 ;

【第9天】SQL进阶-删除记录（SQL 小虚竹)_加锁_04

删除记录:SQL116 删除记录（二）

【第9天】SQL进阶-删除记录（SQL 小虚竹)_数据库_05

初始化数据

drop table if EXISTS exam_record;
CREATE TABLE IF NOT EXISTS exam_record (
id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
uid int NOT NULL COMMENT '用户ID',
exam_id int NOT NULL COMMENT '试卷ID',
start_time datetime NOT NULL COMMENT '开始时间',
submit_time datetime COMMENT '提交时间',
score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8_general_ci;
TRUNCATE exam_record;
INSERT INTO exam_record(uid, exam_id, start_time, submit_time, score) VALUES
(1001, 9001, '2020-01-01 22:11:12', '2020-01-01 23:16:12', 50),
(1001, 9002, '2020-01-02 09:01:01', '2020-01-02 09:06:00', 58),
(1001, 9002, '2020-01-02 09:01:01', '2020-01-02 09:05:01', 58),
(1002, 9001, '2021-05-02 10:01:01', '2021-05-02 10:06:58', 60),
(1002, 9002, '2021-06-02 19:01:01', null, null),
(1003, 9001, '2021-09-05 19:01:01', null, null),
(1003, 9001, '2021-09-05 19:01:01', null, null),
(1003, 9002, '2021-09-09 07:01:02', null, null);

解法

要求处理：

请删除exam_record表中未完成作答或作答时间小于5分钟整的记录中，开始作答时间最早的3条记录。
后台会通过 SELECT * FROM exam_record 语句来对比结果。

分析：

作答记录表exam_record：start_time是试卷开始时间；submit_time 是交卷时间，即结束时间，如果未完成的话，则为空
先查询，后面再替换删除语句
查询exam_record表中未完成作答

select * 
from exam_record
where submit_time is null

【第9天】SQL进阶-删除记录（SQL 小虚竹)_数据_06

或作答时间小于5分钟整的记录也一样查询出来,作答时间的时间差，是由开始时间start_time与提交时间submit_time的时间差来算的
这里要用到timestampdiff()函数

语法:timestampdiff(unit,datetime_expr1,datetime_expr2)
返回datetime_expr1与datetime_expr2之间的整数差。
unit参数可以是：FRAC_SECOND。毫秒;SECOND。秒;MINUTE。分钟;HOUR。小时;DAY。天;WEEK。星期;MONTH。月;QUARTER。季度;YEAR。年

select * 
from exam_record
where submit_time is null
or timestampdiff(minute,start_time,submit_time) < 5

【第9天】SQL进阶-删除记录（SQL 小虚竹)_加锁_07

开始作答时间最早的3条记录,这里包含两层意思：1、按作答时间升序排序；2、取前3条记录

select * 
from exam_record
where submit_time is null
or timestampdiff(minute,start_time,submit_time) < 5
order by start_time asc
limit 0,3

【第9天】SQL进阶-删除记录（SQL 小虚竹)_数据_08

替换成删除语句

delete from exam_record
where submit_time is null
or timestampdiff(minute,start_time,submit_time) < 5
order by start_time asc
limit 0,3;

【第9天】SQL进阶-删除记录（SQL 小虚竹)_加锁_09

这里会报错，是因为delete 后面是支持 limit 关键字的，但仅支持单个参数，也就是 [limit row_count]，用于告知服务器在控制命令被返回到客户端前被删除的行的最大值。
正确的写法：

delete from exam_record
where submit_time is null
or timestampdiff(minute,start_time,submit_time) < 5
order by start_time asc
limit 3;

三、扩展

在 delete 后加 limit 是个好习惯。原因如下：

1、delete from是全表查找的，如果加上limit 时，删除指定的条数后，就会return了。效率提高不少。
2、降低写错 SQL 的代价，即使删错了，例如limit 100,也就删除了100条数据，也能通过binlog找回数据
3、避免长事务，delete执行时，涉及的行是会加锁，如果删除的数据量大，那业务功能都要不能用了
4、加锁都是基于索引的，如果查询字段没有加索引，那会扫描到主键索引上，那么就算查询出来的只有一条记录，也会锁表
5、delete数据量大时，容易占用cpu，导致越删除越慢

delete limit 最佳实践：

MySQL 大佬丁奇有一道题
如果你要删除一个表里面的前 10000 行数据，有以下三种方法可以做到：
第一种，直接执行 delete from T limit 10000;
第二种，在一个连接中循环执行 20 次 delete from T limit 500;
第三种，在 20 个连接中同时执行 delete from T limit 500。

先说答案：第二种会比较好。
第一种limit 10000 ，单个语句占用的时间长，锁的时间会比较长，会造成长事务问题，影响业务功能的使用。
第三种，20个连接同时执行limit 500,delete 是会加锁的，会造成锁冲突的问题。
总结：delete limit的最佳实践：删除数据时尽量加limit，同时减少加锁的范围，limit的值不要太大。