How to implement automatic time setting in MySQL?

2 years ago
Liam
2 minutes

In MySQL, you can automatically set the time using either CURRENT_TIMESTAMP or DEFAULT CURRENT_TIMESTAMP.

For example, when creating a table, you can set a default value for a field to CURRENT_TIMESTAMP. This means that if a value is not specified for that field when inserting data, MySQL will automatically use the current time as its value. Example is as follows:

CREATE TABLE mytable (
    id INT PRIMARY KEY AUTO_INCREMENT,
    content VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

When inserting data, you can specify the value of the content field only, without needing to specify the value of the created_at field.

INSERT INTO mytable (content) VALUES ('Hello World');

In this way, the value of the created_at field will be automatically set to the current time.

If you need the timestamp to automatically update when data is updated, you can use ON UPDATE CURRENT_TIMESTAMP. For example:

CREATE TABLE mytable (
    id INT PRIMARY KEY AUTO_INCREMENT,
    content VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

When updating data, it is possible to only update the value of the content field without needing to update the value of the updated_at field.

UPDATE mytable SET content = 'Updated Content' WHERE id = 1;

In this way, the value of the updated_at field will be automatically updated to the current time.

bannerAds