mysql 设置read only,如何将MySQL行设置为READ-ONLY？

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.