MySQL - How to set a default value as TIMESTAMP on varchar column

Question

I need to add current timestamp as a default value in a varchar column in MySQL 5.7

Here is table query I am trying

CREATE TABLE foo (
creation_time VARCHAR(100) DEFAULT CURRENT_TIMESTAMP, col1 VARCHAR(100)
)

Answer 1

Supposing the existing time has a varchar type like below:

create table foo (creation_time varchar(30));

If it's possible to change the date type, we can use:

alter table foo change column creation_time creation_time datetime default current_timestamp;

Note: The alter table background process includes creating a new table, then populating it based on data from the original table, and finally destroying the original table. So it should be done at an off-business time.

However, if the data type cannot be changed. Then we will have to use a trigger to do the timestamp initialization job.

delimiter //
create trigger timestamp_init before insert on foo for each row
begin
set new.creation_time=current_timestamp();
end//

delimiter ;

Now the insert trigger will automatically set the value for your varchar column to be the current datetime every time a new row is inserted.

insert foo (creation_time) values(default);