MySQL 如何保护行不被删除

在MySQL中，如果某些行的数据很重要，不能被删除或修改，我们可以通过以下方式来保护它们：

阅读更多：MySQL 教程

使用TRIGGER

MySQL中的触发器是一种特殊的存储过程，它与表相关联，当特定事件发生时，触发器会被自动激活执行。我们可以利用触发器的这个特性，在行被删除前先判断一些要求，从而保证数据不被删除。

例如，我们有一个名为users的表，其中的数据非常重要，不能随意删除。我们可以通过以下步骤添加一个触发器：

1. 创建一个表users和一个日志表users_log，用于记录删除操作。因为TRIGGER不支持ROLLBACK，所以我们需要自己记录删除操作，以便撤销操作。

   CREATE TABLE users (
      id INT UNSIGNED NOT NULL AUTO_INCREMENT,
      name VARCHAR(30) NOT NULL,
      email VARCHAR(50),
      PRIMARY KEY (id)
   );

   CREATE TABLE users_log (
      id INT UNSIGNED NOT NULL AUTO_INCREMENT,
      user_id INT UNSIGNED,
      deleted_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
      PRIMARY KEY (id)
   );

2. 创建一个BEFORE DELETE触发器，查找将要被删除的行，并将其插入日志表中，然后使用SIGNAL语句阻止删除。

   DELIMITER //
   CREATE TRIGGER protect_users BEFORE DELETE ON users
   FOR EACH ROW BEGIN
      INSERT INTO users_log (user_id) VALUES (OLD.id);
      SIGNAL SQLSTATE '45001' SET MESSAGE_TEXT = 'Cannot delete user';
   END; //
   DELIMITER ;

这个触发器将在每个对表users的DELETE语句执行之前被调用。

如果你试图删除users表中的任何行，MySQL会解析和执行触发器，然后抛出错误Cannot delete user，这会导致删除操作失败。被删除的行会被插入日志表中，便于后期审核。

3. 测试触发器

   INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');
   DELETE FROM users WHERE id = 1; -- 将失败并记录删除操作
   SELECT * FROM users_log;

如上，当你试图删除id为1的记录时，MySQL会抛出错误信息Cannot delete user。再查询日志表users_log可以看到删除操作的记录。

使用FOREIGN KEY

在MySQL中，外键是连接两个表的约束。在一个表中，外键指向另一个表中的某个列，这个列称为另一个表的主键。如果我们将外键约束设置为CASCADE（级联），则删除主表中的行时，从表中的关联行也会被删除。

在上面的例子中，假设我们有一个名为orders的表与用户相关联，如果这些订单记录存在，当我们删除用户记录时，我们需要在删用户记录之前删除相应的订单记录。

假设我们的orders表如下定义：

CREATE TABLE orders (
   id INT UNSIGNED NOT NULL AUTO_INCREMENT,
   user_id INT UNSIGNED NOT NULL,
   product_name VARCHAR(30) NOT NULL,
   quantity INT UNSIGNED NOT NULL DEFAULT 1,
   PRIMARY KEY (id),
   FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
);

这里我们指定了外键user_id，该外键指向users表，并设置ON DELETE CASCADE。这意味着，如果我们删除users表中的任何行，orders表中对应的行也将被自动删除。

请注意，使用CASCADE可能会有一些潜在的问题，例如对被引用表的数据的意外删除或更改，需要在实践中亲自测试。

使用MySQL的ACL

MySQL的ACL（访问控制列表）是一种授权机制，它与MySQL的账号相关联，可以自定义权限和控制用户可以访问哪些数据库和数据表。我们可以使用ACL来控制哪些用户可以删除或修改特定行。

ACL允许我们为具有DELETE权限的用户指定特定的条件，在删除行之前判断这些条件是否为真，如果为假，则用户将无法删除该行。

例如，我们可以创建一个具有DELETE权限的用户，并为该用户指定一个条件，只允许删除users表中名为admin的用户记录。我们可以按照以下步骤来设置ACL：

1. 创建一个具有DELETE权限的用户，并登录该用户。

   CREATE USER 'delete_user'@'localhost' IDENTIFIED BY 'password';
   GRANT DELETE ON database_name.users TO 'delete_user'@'localhost';

2. 指定条件

   SET GLOBAL sql_require_primary_key=ON;
   ALTER TABLE database_name.users ADD PRIMARY KEY (id);

这里我们指定了一个PRIMARY KEY约束，这是ACL中条件需要的。我们可以根据实际情况设定其他条件，如WHERE子句等。

3. 限制删除操作

   CREATE TRIGGER protect_users_acl BEFORE DELETE ON users
   FOR EACH ROW BEGIN
      IF OLD.name <> 'admin' THEN
         SIGNAL SQLSTATE '45001' SET MESSAGE_TEXT = 'Cannot delete user';
      END IF;
   END;

这里我们创建一个触发器，如果要删除的行不是admin，就抛出错误信息Cannot delete user。

4. 测试

   INSERT INTO users (name, email) VALUES ('admin', 'admin@example.com');
   INSERT INTO users (name, email) VALUES ('user1', 'user1@example.com');

你可以在users表中添加一些记录，然后尝试使用delete_user用户删除这些记录。如果你删除的记录不是名为admin的记录，MySQL会抛出Cannot delete user错误信息。

总结

通过使用TRIGGER、FOREIGN KEY和ACL等技术，我们可以在MySQL中保护数据行不被删除。每种方法都有其优点和局限性，应根据具体情况选择最适合的方法。在实践时，还需要特别注意备份和恢复数据，以避免数据丢失。