通用表表达式实现 UPDATE/DELETE LIMIT

文章目录

        使用场景
        通用表表达式
        随机更新或者删除
        总结

    🍺少而好学，如日出之阳；壮而好学，如日中之光；志而好学，如炳烛之光。——刘向

大家好！我是只谈技术不剪发的 Tony 老师。

在 SQL 查询语句中，我们可以使用 FETCH（标准语法）、LIMIT（MySQL、PostgreSQL、SQLite）、TOP（Microsoft SQL Server）、ROWNUM（Oracle）限定返回结果的数量，从而实现 Top-N 以及分页查询等功能。同时，MySQL 和 SQLite 还支持在 UPDATE 和 DELETE 语句中使用 LIMIT 子句限定更新和删除的行数。但是这种方法不适用于其他数据库，因此本文介绍一种更加通用的实现方法。

如果觉得文章有用，欢迎评论📝、点赞👍、推荐🎁
使用场景

在介绍具体方法之前，我们先来了解一下这种语法的使用场景。简单来说，它的主要作用是将大型事务分解为多个小的事务，包括：

    批量更新和删除，减少锁资源的争用，限制对系统的影响。
    减少数据文件和重做日志磁盘的使用。
    分区之间的批量数据迁移。

通用表表达式

我们首先创建一个测试表和示例数据：

CREATE TABLE big_table (id INT PRIMARY KEY, v VARCHAR(10));

WITH RECURSIVE d(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM d WHERE n < 100000
)
INSERT INTO big_table
SELECT n, concat('id:', n) FROM d;

SELECT * FROM big_table ORDER BY id LIMIT 10;
id|v    |
--+-----+
 1|id:1 |
 2|id:2 |
 3|id:3 |
 4|id:4 |
 5|id:5 |
 6|id:6 |
 7|id:7 |
 8|id:8 |
 9|id:9 |
10|id:10|   

   
我们使用递归通用表表达式（WITH 子句）为示例表 big_table 生成了 100000 条记录。

    📝关于通用表表达式的语法和使用案例，可以参考这篇文章和这篇文章。

由于 SELECT 语句支持查询结果的限制，我们可以将一个这样的查询语句的结果用于 DELETE 或者 UPDATE 语句，作为删除或者更新的过滤条件。使用通用表表达式实现的语法如下：

WITH deleted_rows AS (
  SELECT id
  FROM big_table
  LIMIT 10
)
DELETE FROM big_table
WHERE id IN (SELECT id FROM deleted_rows);
 

更多请见：http://www.mark-to-win.com/tutorial/51641.html