我想制作一个TIMESTAMP
字段DEFAULT CURRENT_TIMESTAMP
,用于"创建时间"目的。但如果有人或某事改变了TIMESTAMP,
,我的数据将不一致。
有没有一种方法可以确保它不会改变,除非我删除行并重新插入,而不是应用程序级别?
有了建议的答案,我可以使用类似于的东西
CREATE TRIGGER consistency1 BEFORE UPDATE ON table1
FOR EACH ROW
BEGIN
IF NEW.creationtime != OLD.creationtime THEN
SET NEW.creationtime = OLD.creationtime;
END IF;
END;
由于我的评论受到了赞赏,下面是扩展版本。
我个人认为这是不可能的。
无论如何,有几件事你可以尝试:
-
确保只有您的应用程序才能在数据库上写入
-
编写这样的触发器(伪代码!)
create trigger prevent_change_timestamp on tbl_name before update #fetch old row value #verify if the timestamp field has been changed #raise an error (any SQL error will do)
-
或者像这个
create trigger revert_change_timestamp on tbl_name after update #fetch pre-change row value #update the row with the "old" value in place of the new one
如果可能的话,我个人会选择第三种选择。不管怎样,第二个也不错。除非必要,否则我不会依赖第一个选项(例如:无法访问触发功能)
此处的更多信息:参考
有趣的是,数据库应用程序没有将此功能作为标准提供:不仅用于"创建"的时间戳字段,还用于自动递增id字段以及您可能希望在创建记录时设置的任何杂项值,然后决不允许更改。。。想知道理由是什么?
这里可以做的是,当一行被更新时,可以在表上写一个TRIGGER
。在该触发器中,您可以比较旧值和新值,如果它们不同,则可以用旧值覆盖新值。
我在MySQL 5.1中尝试过,但得到了一个错误
DELIMITER //
CREATE TRIGGER member_update_0
-> AFTER UPDATE ON members
-> FOR EACH ROW
-> BEGIN
-> IF NEW.id != OLD.id THEN
-> SET NEW.id = OLD.id;
-> END IF;
-> END;//
错误1362(HY000):在触发之后,不允许更新新行
接受将AFTER替换为BEFORE的相同触发器;对我来说,这是一种反直觉的方法,但它能在中工作
delimiter ;
UPDATE members SET id=11353 WHERE id=1353;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
如果使用InnoDB,实际上可以非常巧妙地做到这一点。
只创建一列的另一个表。该列应该有一个外键(因此该解决方案中的innodb要求),该外键指向所讨论的原始表的不可变列。
设置类似"更新限制"的限制。
总结:
CREATE TABLE original (
....
immutable_column ...
INDEX index1(immutable_column)
....
) ENGINE=INNODB;
CREATE TABLE restricter (
.....
col1,
INDEX index2(col1),
FOREIGN KEY (col1) REFERENCES original (immutable_colum) ON UPDATE RESTRICT ON DELETE CASCADE
) ENGINE=INNODB;
更进一步(对于那些仍然坚持使用MySQL遗留版本的人来说),您可以同时拥有受保护的&默认create_stamp和自动更新update_stamp如下:
如果你有这样的表格
CREATE TABLE `csv_status` (
`id` int(11) NOT NULL primary key AUTO_INCREMENT,
`create_stamp` datetime not null,
`update_stamp` timestamp default current_timestamp on update current_timestamp,
`status` enum('happy','sad') not null default 'happy'
);
然后你可以在上定义这些触发器
drop trigger if exists set_create_stamp ;
create definer = CURRENT_USER trigger set_create_stamp BEFORE INSERT on
csv_status for each row
set NEW.create_stamp = now();
drop trigger if exists protect_create_stamp ;
delimiter //
create definer = CURRENT_USER trigger protect_create_stamp BEFORE UPDATE on
csv_status for each row
begin
if NEW.create_stamp != OLD.create_stamp then
set NEW.create_stamp = OLD.create_stamp;
end if;
end;//
delimiter ;