MySQL 8: created_at and updated_at columns – A practical guide

Introduction

MySQL, one of the most popular open-source relational databases, provides powerful features to manage and query data efficiently. Tracking when records are created or modified is a common requirement in application development. The created_at and updated_at are often used for this purpose.

The created_at column records the time when a new record is inserted into a table, whereas the updated_at column records the time when any modification is made to the record. This tutorial covers how to implement and manage these temporal columns in MySQL 8.

Configuring the Table

Let’s begin by creating a table with automatic created_at and updated_at columns. The DEFAULT clause specifies the current timestamp when a record is inserted, and the ON UPDATE CURRENT_TIMESTAMP clause updates the time when any changes are made to a record.

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT, 
    username VARCHAR(50) NOT NULL, 
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, 
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Inserting Data

When you insert a new record, the CREATED_AT column automatically gets the current timestamp:

INSERT INTO users (username) VALUES ('JohnDoe');

After the insert, MySQL automatically populates both created_at and updated_at with the time of creation.

Updating Data

When updating the record, MySQL automatically changes the updated_at timestamp to reflect the new modification time:

UPDATE users SET username = 'JaneDoe' WHERE id = 1;

Handling NULLs and Overrides

You may come across situations where you need the updated_at column to remain NULL until the first update, or you might need to override the timestamp. Here’s how to adjust for those cases.

Allowing NULL

Modify the table definition to allow NULLs:

ALTER TABLE users 
CHANGE updated_at updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP;

Overriding Timestamps

To override the auto-generated timestamps, simply provide a value for them during insert or update:

INSERT INTO users (username, created_at, updated_at) VALUES ('OverrideUser', '2023-01-01 00:00:00', NULL);

UPDATE users SET updated_at = '2023-01-02 00:00:00' WHERE username = 'OverrideUser';

Tips for Effective Use

Here are a few tips for maximizing the usefulness of created_at and updated_at columns:

• Always use the same type of timestamp columns (`TIMESTAMP` or `DATETIME`) consistently across your entire schema.
• Avoid manual updates to these columns as it defeats their purpose and may introduce errors or inconsistencies.
• Ensure your server’s time zone is correctly set since TIMESTAMP columns are affected by the time zone.

Conclusion

By utilizing created_at and updated_at columns, MySQL allows developers to automatically record and track changes over time. With careful design and considerate usage, these columns can greatly enrich data audits, debugging, and historical analysis.