如何将 MySQL 行设置为只读?

2021-11-20 00:00:00 sql database mysql

我不想更改表格中的一行(永远).

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

是否可以将 MySQL 行设置为 READ-ONLY，使其无法以任何方式更新?如果是，怎么办?

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?

谢谢.

推荐答案

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

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.

您可以创建一个 BEFORE UPDATE 触发器，在锁定"记录即将更新时引发错误；由于在执行操作之前发生错误，MySQL 停止继续进行.如果您还想防止记录被删除，您需要创建一个类似的触发器BEFORE DELETE.

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.

要确定记录是否锁定"，您可以创建一个布尔值 locked 列:

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 ...;

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

我无法在此表上创建额外的列，但该行在其中一列中具有唯一 ID，那么对于这种情况我将如何执行此操作?

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?

同样，如果您绝对必须将此逻辑放在存储层中——并且无法通过 PK 以外的任何方式识别锁定的记录——您可以硬编码测试进入你的触发器；例如，使用 id_column = 1234 来锁定"记录:

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.

相关文章