我有一个表test
,带4列
id name visited updated_at
1 abc 2016-03-20 2016-03-20
2 xyz 2016-03-23 2016-03-23
当我在查询下应用
时ALTER TABLE `test` CHANGE `updated_at` `updated_at` TIMESTAMP on update
CURRENT_TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00';
如果我更改 name
或 visited
,
,但我只想在visited
更改时更改它。
如何实现这一目标?感谢您的帮助。
听起来您需要触发来实现这一目标。假设您的表定义看起来像:
drop table if exists test;
create table test(
id BIGINT(20) PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255) NOT NULL DEFAULT '',
visited DATETIME NOT NULL DEFAULT '0000-00-00',
updated_at DATETIME NOT NULL DEFAULT '0000-00-00'
);
...您可以完成仅使用触发器的visited
列更新updated_at
的目标。
-- Create a trigger that will update the updated_at column iff visited changes.
drop trigger if exists upd_test;
delimiter //
create trigger upd_test BEFORE UPDATE ON test
FOR EACH ROW
BEGIN
IF ( (old.visited is not null and new.visited is not null and old.visited <> new.visited)
OR (old.visited is null and new.visited is not null)
OR (old.visited is not null and new.visited is null) ) THEN
SET NEW.updated_at = CURRENT_TIMESTAMP;
END IF;
END;//
delimiter ;
,您可以看到它与一个相当简单的示例一起工作。
insert into test(name) values ("Bran"), ("Catelyn"), ("Daenerys"), ("Eddard");
-- this statement will not cause updated_at to be updated
update test
set name = 'Jon'
where name = 'Eddard';
-- this statement will cause updated_at to be updated, via the trigger
update test
set visited = '2016-06-16'
where name = 'Jon';
drop table if exists `xyz1`;
create table `xyz1`
( id int auto_increment primary key,
theName varchar(20) not null,
visited timestamp null,
updated_at timestamp null
);
insert `xyz1` (theName) values('abc'),('xyz');
它对我们目前坐在那里的无效感到不满,错误1138:
ALTER TABLE `xyz1` CHANGE `updated_at` `updated_at` TIMESTAMP
NOT NULL DEFAULT CURRENT_TIMESTAMP;
让我们解决这个问题:
truncate table `xyz1`;
ALTER TABLE `xyz1` CHANGE `updated_at` `updated_at` TIMESTAMP
DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
-- works
insert `xyz1` (theName) values('abc'),('xyz');
select * from `xyz1`;
+----+---------+---------+---------------------+
| id | theName | visited | updated_at |
+----+---------+---------+---------------------+
| 1 | abc | NULL | 2016-06-16 14:39:38 |
| 2 | xyz | NULL | 2016-06-16 14:39:38 |
+----+---------+---------+---------------------+
update `xyz1` set `theName`='xyz Smith' where id=2;
select * from `xyz1`;
+----+-----------+---------+---------------------+
| id | theName | visited | updated_at |
+----+-----------+---------+---------------------+
| 1 | abc | NULL | 2016-06-16 14:39:38 |
| 2 | xyz Smith | NULL | 2016-06-16 14:41:04 |
+----+-----------+---------+---------------------+
请参阅手册自动初始化和更新