第三十八例：删除记录（1）
- 题目地址：删除记录（一）牛客题霸牛客网 (nowcoder.com)
- 初始化代码：
droptable if EXISTS exam_record;
CREATETABLE IF NOT EXISTS exam_record (
id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
uid intNOTNULL COMMENT '用户ID',
exam_id intNOTNULL COMMENT '试卷ID',
start_time datetimeNOTNULL COMMENT '开始时间',
submit_time datetime COMMENT '提交时间',
score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8_general_ci;
TRUNCATE exam_record;
INSERTINTO 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分）的记录；
- 分析1：找出exam_record表中作答时间小于5分钟整，且分数不及格的。
- 作答时间差：提交时间减去开始时间，使用timestampdiff()函数
语法：timestampdiff(unit,starttime,submittime);
unit参数：FRAC_SECOND,毫秒;SECOND,秒;MINUTE。分钟;HOUR,小时;DAY。天;WEEK,星期;MONTH,月;QUARTER。季度;YEAR,年
select * from exam_record
where timestampdiff(minute,start_time,submit_time) < 5
and score < 60;
- 分析2：找出后再使用delete语句
deletefrom exam_record
where timestampdiff(minute,start_time,submit_time) < 5
and score < 60;
第三十九例：删除纪录（2）
- 题目地址：删除记录（二）牛客题霸牛客网 (nowcoder.com)
- 初始化数据：
droptable if EXISTS exam_record;
CREATETABLE IF NOT EXISTS exam_record (
id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
uid intNOTNULL COMMENT '用户ID',
exam_id intNOTNULL COMMENT '试卷ID',
start_time datetimeNOTNULL COMMENT '开始时间',
submit_time datetime COMMENT '提交时间',
score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8_general_ci;
TRUNCATE exam_record;
INSERTINTO 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条记录。
- 分析1：先找出未完成作答或者作答时间小于5分钟整的记录,开始作答最早的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;
- 分析2：再使用delete语句删除
- 注意：limit这里会报错，是因为delete 后面是支持 limit 关键字的，但仅支持单个参数，也就是 [limit row_count]，用于告知服务器在控制命令被返回到客户端前被删除的行的最大值。正确的写法：
deletefrom exam_record
where submit_time isnull
or timestampdiff(minute,start_time,submit_time) < 5
orderby start_time asc--- limit 0,3;
limit3;
- 扩展：在 delete 后加 limit 是个好习惯。原因如下：
- 1，delete from 是全表查找的，如果加上limit 时，删除指定的条数后，就会return了。效率提高不少。
- 2，降低写错 SQL 的代价，即使删错了，例如limit 100,也就删除了100条数据，也能通过binlog找回数据
- 3，避免长事务，delete执行时，涉及的行是会加锁，如果删除的数据量大，那业务功能都要不能用了
- 4，加锁都是基于索引的，如果查询字段没有加索引，那会扫描到主键索引上，那么就算查询出来的只有一条记录，也会锁表
- 5，delete数据量大时，容易占用cpu，导致越删除越慢
- 6，清空表数据建议直接用 truncate，效率上 truncate 远高于 delete，因为 truncate 不走事务，不会锁表，也不会生产大量日志写入日志文件；truncate table table_name 后立刻释放磁盘空间，并重置 auto_increment 的值。
