mysql表中的不可更新列 [英] Non Updatable column in mysql table

问题描述

如何定义一个应具有不可更新的一列的mysql表，我的意思是一旦插入它就不应允许用户以任何方式进行更新. 例如，名为tbl_xyz的表有3列，其中一列是不可更新的，然后 tbl_xyz(c1，c2，c3)，假设我将值插入为(MBX，123，POQ)，然后说如果c3是不可更新的，那么如果有人尝试更新，则它应该始终具有POQ的值作为第一个条目给出一些受约束的列特定错误.

How to define a mysql table that should have one column as non updatable I mean once inserted it should not allow user to update in any way. For Example table named tbl_xyz have 3 columns where one is non updatable then tbl_xyz(c1,c2,c3), suppose I Insert values as (MBX,123,POQ) then lets say if c3 is non updatable then it should be always have value with first entry as POQ if some one tries to update then it should give some constrained column specific error.

如果有人在上述限制下进行了相同的表级别定义，那么请帮助并让我知道.

If someone have did same table level defination with above mentioned restriction then please help and let me know please.

谢谢

推荐答案

这可以通过

This can be done via a trigger in MySQL:

DELIMITER ;;
CREATE TRIGGER `trig_tbl_xyz_before_update`
BEFORE UPDATE ON `tbl_xyz` FOR EACH ROW
BEGIN
  IF NEW.c3 != OLD.c3 THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'tbl_xyz.c3 is not allowed to be updated, stop trying to update it.';
  END IF;
END;;
DELIMITER ;

当尝试将tbl_xyz.c3更新为其他值时，这将给出以下响应:

This will give the following response when trying to update tbl_xyz.c3 to a different value:

错误代码:1644.不允许更新tbl_xyz.c3，请停止尝试对其进行更新.

Error Code: 1644. tbl_xyz.c3 is not allowed to be updated, stop trying to update it.

我使用了 45000 错误代码，因为在 SIGNAL 文档.

I used the 45000 error code because it was suggested in the SIGNAL documentation.

要发出通用SQLSTATE值的信号，请使用'45000'，表示未处理的用户定义的异常".

To signal a generic SQLSTATE value, use '45000', which means "unhandled user-defined exception."

这篇关于mysql表中的不可更新列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！