How to set automatic creation time and automatic update time in MySQL8

Business scenario:

1. The data in the database table is required to record the creation time of each new piece of data. The time format requirements are as follows: Year, month, day: hours, minutes and seconds.

2. The data in the database table is required to record the update time of each updated data. The time format is required to be clear: year, month, day: hours, minutes and seconds.

Function implementation:

1. Add create_time and update_time fields to each business table, and set the field type to: timestamp

2. Add the create_time and update_time fields to each business table. Create_time and update_time fields, set the default value: CURRENT_TIMESTAMP

DDL script syntax:

1. New field create_time syntax

ALTER TABLE table name

ADD COLUMN 字段名 timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间' ;

Example: Add create_time to the User table and set the default time CURRENT_TIMESTAMP

ALTER TABLE User
ADD COLUMN create_time datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间' ;

2. Modify the field create_time syntax

ALTER TABLE 表名
MODIFY COLUMN  字段名 timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间' ;

Example: Modify the create_time in the User table to set the default time CURRENT_TIMESTAMP

ALTER TABLE User
MODIFY COLUMN  create_time timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间' ;

3. New field update_time syntax

ALTER TABLE 表名
ADD COLUMN 字段名 timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间' ;

Example: Add update_time to the User table and set the default time CURRENT_TIMESTAMP

ALTER TABLE User
ADD COLUMN Update_time timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间' ;

4. Modify the field update_time syntax

ALTER TABLE 表名
MODIFY COLUMN 字段名 timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间' ;

Example: Modify The update_time in the User table sets the default time CURRENT_TIMESTAMP

ALTER TABLE User
MODIFY COLUMN update_time timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间' ;

The above is the detailed content of How to set automatic creation time and automatic update time in MySQL8. For more information, please follow other related articles on the PHP Chinese website!