mysql implements adding time automatically adding and updating time automatically updating operation

Time fields are often used in database usage. Commonly used ones are creation time and update time.

However, when using it, you want the creation time to be automatically set to the current time when creating, and the update time to be automatically updated to the current time when updating.

Create table stu

CREATE TABLE `stu` (
'id' int NOT NULL AUTO_INCREMENT,
'createTime' timestamp DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation time',
'moditiyTime' timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Update time',
PRIMARY KEY ('id'));

Set the current time when creating

DEFAULT CURRENT_TIMESTAMP

When updating, set the update time to the current time

DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

Supplement: MySQL adds default time to the field (insert time)

Application scenarios:

1. In the data table, to record when each piece of data was created, the application does not need to record it specifically, but the data database obtains the current time and automatically records the creation time;

2. In the database, to record when each piece of data was modified, the application does not need to record it specifically, but the data database obtains the current time and automatically records the modification time;

Implementation:

1. Set the field type to TIMESTAMP

2. Set the default value to CURRENT_TIMESTAMP

Example application:

1. MySQL script implementation use case

–Add CreateTime to set the default time CURRENT_TIMESTAMP

ALTER TABLE table_name
ADD COLUMN CreateTime datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation time';

–Modify CreateTime to set the default time CURRENT_TIMESTAMP

ALTER TABLE table_name
MODIFY COLUMN CreateTime datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation time';

– Add UpdateTime Set the default time CURRENT_TIMESTAMP Set the update time to ON UPDATE CURRENT_TIMESTAMP

ALTER TABLE table_name
ADD COLUMN UpdateTime timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Creation time';

– Modify UpdateTime and set the default time to CURRENT_TIMESTAMP and set the update time to ON UPDATE CURRENT_TIMESTAMP

ALTER TABLE table_name
MODIFY COLUMN UpdateTime timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Creation time';

2. MySQL tool settings

MySQL automatically manages and maintains database time consistency.

The above is my personal experience. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM. If there are any mistakes or incomplete considerations, please feel free to correct me.