如何将MySQL行设置为只读?

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?

是否可以将MySQL行设置为只读，以便不能以任何方式更新它?如果是这样,如何?

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.

谢谢。

1 个解决方案

#1

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.

如果“锁定”记录即将更新，则可以创建一个BEFORE更新触发器，从而引发错误;由于在执行操作之前发生了错误，因此MySQL停止继续操作。如果还想防止删除记录，则需要在删除之前创建一个类似的触发器。

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;

请注意，这个信号是在MySQL 5.5中引入的。在早期版本中，必须执行一些导致MySQL出错的错误操作:我经常调用一个不存在的过程，例如调用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?

我不能在这个表上创建一个额外的列，但是行在其中一个列中有一个惟一的id，那么我该如何为这个场景做这个呢?

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:

同样，如果您必须将此逻辑放在存储层中—并且不能通过任何方式识别锁定的记录，而不是通过pk—您可以将测试硬编码到您的触发器中;例如，用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.

但这绝对是可怕的，我会尽一切可能避免它。

#1