"on update CURRENT_TIMESTAMP" for only one column in mysql

Question

I have one table test with 4 columns

id     name   visited      updated_at
1      abc    2016-03-20   2016-03-20 
2      xyz    2016-03-23   2016-03-23

When I applied below query

ALTER TABLE `test` CHANGE `updated_at` `updated_at` TIMESTAMP on update 
CURRENT_TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00'; 

It change updated_at if I change name or visited,

But I want to It changes only if visited changes.

How to achieve this ? thanks for your help.

Answer 1

Sounds like you need a trigger to accomplish that. Assuming your table definition looks like so:

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'
);

... you can accomplish your goal of updating updated_at for only the visited column with a trigger, like so.

-- 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 ;

And you can see it working with a fairly straight-forward example.

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';