DEFAULT & ON UPDATE TIMESTAMP in MySQL 8: Explained with examples

Introduction

Working with date and time data is a common requirement in software development. MySQL 8, a widely used database management system, provides several functionalities to make handling temporal data easier, and two of these handy features are ‘DEFAULT TIMESTAMP’ and ‘ON UPDATE TIMESTAMP’. This tutorial will explain how to use these features effectively, with examples to showcase them in action.

Understanding DEFAULT TIMESTAMP

The DEFAULT TIMESTAMP feature allows you to automatically set a default value for a TIMESTAMP column at the time of record insertion when no specific value is provided. The most common default is the current timestamp, which captures the exact date and time a record is added to the database.

Basic Example

Here’s a simple example of how to create a table that automatically populates a created_at field with the current timestamp whenever a new record is inserted:

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

The above SQL statement defines a created_at column that will default to the timestamp at the moment a row is inserted, in case a specific timestamp is not provided during the insert operation.

Inserting Data Without Specifying Timestamp

When inserting data without specifying the created_at column:

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

As you can see, we did not specify the created_at column. MySQL will automatically set the current timestamp as its value. To check the inserted data along with its created_at timestamp, you could use:

SELECT * FROM users;

You should see that the created_at column for the john_doe record has been populated with the timestamp that corresponds to the insertion time.

Working with ON UPDATE TIMESTAMP

The ON UPDATE TIMESTAMP feature automatically updates the timestamp column to the current timestamp whenever a record is updated, provided that other columns in the row are also modified.

Basic Example

Consider the following table with an updated_at column that utilizes ON UPDATE TIMESTAMP:

CREATE TABLE products (
  id INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
);

This SQL statement creates a updated_at column that defaults to the current timestamp on insert, and also updates to the current timestamp whenever the row is modified.

Updating Data and Viewing Timestamp Changes

After inserting an item:

INSERT INTO products (name, price) VALUES ('Gaming Mouse', 49.99);

If we were to update the price of the ‘Gaming Mouse’:

UPDATE products SET price = 54.99 WHERE name = 'Gaming Mouse';

Querying the data again with:

SELECT * FROM products;

You’ll see that the updated_at column reflects the timestamp of when the last update occurred.

Advanced Usage and Considerations

In more complex database designs, you may want to have multiple timestamp columns for different events, such as for soft deletes, approved_at timestamps, etc. To use DEFAULT TIMESTAMP and ON UPDATE TIMESTAMP in these scenarios, you need to consider the rules and constraints set by MySQL, such as the restriction on having multiple columns with CURRENT_TIMESTAMP as the default value.

As of MySQL 8, these limitations have been mostly lifted, allowing for more flexibility. Still, it’s important to use these features judaiciously to avoid confusion and maintain clear data models.

Combining DEFAULT and ON UPDATE TIMESTAMP

CREATE TABLE articles (
  id INT NOT NULL AUTO_INCREMENT,
  title VARCHAR(255) NOT NULL,
  content TEXT NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at TIMESTAMP,
  PRIMARY KEY (id)
);

The example shows a scenario where both created_at and updated_at columns are used in conjunction with DEFAULT and ON UPDATE TIMESTAMP, whereas deleted_at can be set to a timestamp using a manual update, usually to signal a soft deletion.

Conclusion

Implementing DEFAULT TIMESTAMP and ON UPDATE TIMESTAMP within MySQL 8 can greatly simplify the process of tracking when records are created and modified. By understanding and utilizing these features, developers can ensure data consistency and integrity, reducing the need for manual timestamp entries and the potential for errors that accompany them.