Example of setting automatic creation time and modification time by MySQL

Time:2020-11-24

This paper describes the method of setting automatic creation time and modification time by mysql. For your reference, the details are as follows:

The first is defined by DDL

CREATE TABLE `course` (
 `course` varchar(255) DEFAULT NULL,
 `user` varchar(255) DEFAULT NULL,
 `score` int(11) DEFAULT NULL,
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `create_ time` datetime DEFAULT CURRENT_ Timestamp comment 'creation time',
 `update_ time` datetime DEFAULT CURRENT_ TIMESTAMP ON UPDATE CURRENT_ Timestamp comment 'update time',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

Second, modify the table design directly through the tool

Third, you can also modify the fields through SQL statements

--Modify create_ Time set the default time current_ TIMESTAMP 
ALTER TABLE `course`
MODIFY COLUMN `create_ time` datetime NULL DEFAULT CURRENT_ Timestamp comment 'creation time';
--Add update_ Time set the default time current_ Timestamp sets the update time to on update current_ TIMESTAMP 
ALTER TABLE `course`
ADD COLUMN `update_ time` datetime NULL DEFAULT CURRENT_ TIMESTAMP ON UPDATE CURRENT_ Timestamp comment 'update time';

For more interested readers about MySQL related content, you can view the special topics of this website: the complete collection of MySQL query skills, the summary of MySQL transaction operation skills, the complete collection of MySQL stored procedure skills, the summary of MySQL database lock related skills and the summary of MySQL common functions

I hope this article will help you with MySQL database design.