如何进一步限制MySQL/MariaDB避免主键用大于/小于的DELETE/UPDATE？

针对SQL_SAFE_MODE无法限制DELETE FROM table WHERE id > 0这类近乎清空表的语句问题，可以通过以下几种更严格的方式控制：

• 触发器实现行数量限制
  给目标表创建BEFORE DELETE触发器，检查本次操作匹配的行数，一旦超过预设的安全阈值（比如10行）就抛出错误终止操作。示例代码：

  DELIMITER //
  CREATE TRIGGER prevent_mass_delete BEFORE DELETE ON your_table
  FOR EACH ROW
  BEGIN
    DECLARE delete_count INT;
    SELECT COUNT(*) INTO delete_count FROM your_table WHERE id > 0;
    IF delete_count > 10 THEN
      SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '禁止批量删除超过10行的数据';
    END IF;
  END //
  DELIMITER ;
  

  注：触发器内的WHERE条件需根据实际业务场景调整，确保匹配当前删除操作的筛选逻辑。

• 精细化权限管控
  收回普通用户的直接DELETE、UPDATE权限，仅允许通过经过严格校验的存储过程执行数据删除操作。存储过程中强制要求传入明确的主键值，只允许删除单条或指定数量的行。示例：

  -- 创建限定删除逻辑的存储过程
  DELIMITER //
  CREATE PROCEDURE delete_single_row(IN target_id INT)
  BEGIN
    DELETE FROM your_table WHERE id = target_id;
  END //
  DELIMITER ;
  
  -- 仅授予用户存储过程执行权限
  GRANT EXECUTE ON PROCEDURE your_database.delete_single_row TO 'your_user'@'localhost';
  

• 启用sql_require_primary_key参数
  在MySQL 8.0.16+、MariaDB 10.5+版本中，开启sql_require_primary_key系统变量，强制所有表必须定义主键，配合SQL_SAFE_MODE进一步限制无主键的表操作，从结构层面减少误操作风险。

• 二进制日志行模式兜底
  开启行模式的二进制日志（Row-Based Replication），即便发生误操作，也能通过日志精准定位到被修改的行，提升数据恢复的准确性。

内容的提问来源于stack exchange，提问作者petekaner