如何将MySQL行设置为READ-ONLY？ [英] How to set a MySQL row to READ-ONLY?

问题描述

我可以将一个MySQL行设置为READ-ONLY，它不能以任何方式更新？如果是，如何？

如果不是，是否可以在该行的某一列中设置永久值，以便不能更改？如果是，如何？

感谢。

解决方案

成为业务逻辑，这可能不属于您的数据存储层。但是，它仍然可以使用触发器来完成。

您可以创建一个 BEFORE UPDATE 触发器，如果​​锁定记录即将更新，则会引发错误;由于在执行操作之前发生错误，MySQL将停止继续执行操作。如果您还希望阻止记录被删除，您需要创建一个类似的触发器 BEFORE DELETE 。

要确定记录是否锁定，您可以创建一个布尔锁定列：

  ALTER TABLE my_table ADD COLUMN locked BOOLEAN NOT NULL DEFAULT FALSE; 
 
 DELIMITER ;; 
 
 CREATE TRIGGER foo_upd BEFORE UPDATE on my_table FOR EACH ROW 
 IF OLD.locked THEN 
 SIGNAL SQLSTATE'45000'SET MESSAGE_TEXT ='无法更新锁定记录'; 
 END IF ;; 
 
 CREATE TRIGGER foo_del BEFORE DELETE ON my_table FOR EACH ROW 
如果OLD.locked THEN 
 SIGNAL SQLSTATE'45000'SET MESSAGE_TEXT ='无法删除锁定的记录'; 
 END IF ;; 
 
 DELIMITER; 
 
 UPDATE my_table SET locked = TRUE WHERE ...; 
  

注意 SIGNAL 。在早期版本中，您必须执行一些错误的操作，导致MySQL引发错误：我经常调用不存在的过程，例如。 CALL raise_error;

---

我无法在此表上创建一个附加列，但该行在其中一个列中有唯一的ID，那么我该如何在这种情况下这样做？

再次，如果你绝对必须将这个逻辑放置在存储层中，并且不能通过除了PK之外的任何方式来识别锁定的记录。将测试硬编码到触发器中;例如，使用 id_column = 1234 锁定记录：

  DELIMITER ;; 
 
 CREATE TRIGGER foo_upd BEFORE UPDATE on my_table FOR EACH ROW 
如果OLD.id_column< => 1234 THEN 
 SIGNAL SQLSTATE'45000'SET MESSAGE_TEXT ='无法更新已锁定的记录'; 
 END IF ;; 
 
 CREATE TRIGGER foo_del BEFORE DELETE ON my_table FOR EACH ROW 
如果OLD.id_column< => 1234 THEN 
 SIGNAL SQLSTATE'45000'SET MESSAGE_TEXT ='无法删除锁定的记录'; 
 END IF ;; 
 
 DELIMITER; 
  

但这是绝对可怕的 尽可能避免。

I have a row in a table that I do not want to be changed (ever).

Is it possible to set a MySQL row to READ-ONLY so that it cannot be updated in any way? If so, how?

If not, is it possible to set a permanent value in one of the columns of that row so that it cannot be changed? If so, how?

Thanks.

解决方案

This is likely to be business logic, which probably doesn't belong in your data storage layer. However, it can nonetheless be accomplished using triggers.

You can create a BEFORE UPDATE trigger that raises an error if a "locked" record is about to be updated; since an error occurs before the operation is undertaken, MySQL ceases to proceed with it. If you also want to prevent the record from being deleted, you'd need to create a similar trigger BEFORE DELETE.

To determine whether a record is "locked", you could create a boolean locked column:

ALTER TABLE my_table ADD COLUMN locked BOOLEAN NOT NULL DEFAULT FALSE;

DELIMITER ;;

CREATE TRIGGER foo_upd BEFORE UPDATE ON my_table FOR EACH ROW
IF OLD.locked THEN
  SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Cannot update locked record';
END IF;;

CREATE TRIGGER foo_del BEFORE DELETE ON my_table FOR EACH ROW
IF OLD.locked THEN
  SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Cannot delete locked record';
END IF;;

DELIMITER ;

UPDATE my_table SET locked = TRUE WHERE ...;

Note that SIGNAL was introduced in MySQL 5.5. In earlier versions, you must perform some erroneous action that causes MySQL to raise an error: I often call an non-existent procedure, e.g. with CALL raise_error;

---

I cannot create an additional column on this table, but the row has a unique id in one of the columns, so how would I do this for that scenario?

Again, if you absolutely must place this logic in the storage layer—and cannot identify the locked records through any means other than the PK—you could hard-code the test into your trigger; for example, to "lock" the record with id_column = 1234:

DELIMITER ;;

CREATE TRIGGER foo_upd BEFORE UPDATE ON my_table FOR EACH ROW
IF OLD.id_column <=> 1234 THEN
  SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Cannot update locked record';
END IF;;

CREATE TRIGGER foo_del BEFORE DELETE ON my_table FOR EACH ROW
IF OLD.id_column <=> 1234 THEN
  SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Cannot delete locked record';
END IF;;

DELIMITER ;

But this is absolutely horrible and I would do almost anything to avoid it whenever possible.

这篇关于如何将MySQL行设置为READ-ONLY？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！