MySQL 5.6 : how to limit the number of update or delete operation

Question

我想要这个...

当更新或删除操作影响的行数大于1000时，禁止。

我怎样才能做到这一点？

Answer 1

MySQL 没有内置功能来实现此禁止，自动取消影响多于 N 行的 DML 语句(INSERT、UPDATE 或 DELETE 语句)。

而且实际上并没有一种方法可以对任何和所有可能执行的 INSERT、UPDATE 或 DELETE 语句实现这种限制。

---

我们可以采用多种方法来实现这种限制，但所有这些方法都需要一些额外的 SQL 语句和逻辑。

一种可能性是运行单独的“预检查”查询，使用与要发出的 UPDATE 或 DELETE 语句相同的条件，并收集将受影响的行数。然后检查返回的计数以确定是否应该发出 UPDATE 或 DELETE。此方法不对 DML 语句本身提供任何限制或禁止。这些是需要在实际 DML 语句之外执行的额外步骤。

---

要实现这种类型的限制，另一种可能性是在跟踪触发触发器次数的特定表上实现 FOR EACH ROW TRIGGER。

触发器需要在用户定义的变量(例如 @rowlimit)中递增计数器，并且需要将累积计数器与指定限制(编码到触发器中)进行比较，然后 SIGNAL(或以其他方式抛出错误)当达到或超过指定的限制时。抛出错误将导致操作被取消。

这种方法还需要在发出每个 INSERT、UPDATE 或 DELETE 语句之前将用户定义的变量重置为零的初步语句，例如设置@rowlimit = 0。

请务必注意，不会为受 F​​OREIGN KEY 约束操作影响的行触发触发器。为了还包括这些行的计数，父表上的更新前/删除前触发器需要执行查询以查找子表中会受到影响的行数。

要全面实现这种限制，对于很多表，需要定义很多触发器。

---

如果我们不禁止 DML 语句影响任何行，而是允许 DML 语句影响一定数量的行，并且在达到该限制时停止，允许为了成功完成语句，我们可以在 DML 语句上添加 LIMIT 子句。这种方式显然需要修改DML语句，并没有禁止整个操作。它仅指定“最大”行数，当达到“最大”数时，语句将停止处理更多行，留下“最大”行数受影响，并成功完成。

---

禁止影响多于 N 行的 DML 语句的唯一方法是完全禁止所有可能影响多于 0 行的 DML 语句(y 撤销 UPDATE、DELETE 和 INSERT 权限。

作为替代方案，我们可以创建执行明确定义的“更新和删除操作”的 MySQL 存储程序(即过程)。这些过程可以定义 (DEFINER) 以在确实有权对表执行 DML 语句的用户权限下运行。其他用户可以被授予对这些过程的 EXECUTE 权限，这样这些用户就可以执行定义明确的“更新和删除”操作。

---

这些方法都不能完全满足规范...禁止影响超过指定行数限制的 DML 操作。