Use an updated_at Column in Your MySQL Table (and make it update automatically)

Nothing is worse than trying to figure out what went wrong with application data and finding out your tables don’t have created_at or updated_at columns to store when rows are inserted or updated.

RAGE DROP TABLE TABLE_NAME

Another benefit you get from having an updated_at column is efficient data replication into systems like Pipeline, one of our products at RJMetrics. If we know which rows have been updated recently, we can sort by that column and prevent redundant queries.

Don’t rely on properly formed queries to populate these columns! Let the database do it. Unfortunately, not all support it. Here’s how to make an automatically updating created_at timestamp in MySQL 5.5.

CREATE TABLE orders(
  id INT NOT NULL AUTO_INCREMENT,
  user_id INT NULL,
  amount FLOAT NULL,
  updated_at TIMESTAMP NOT NULL DEFAULT NOW() ON UPDATE NOW(),
  created_at TIMESTAMP NOT NULL,
  PRIMARY KEY(id)
);
Query OK, 0 rows affected (0.00 sec)

So now your table has been created. The important part is the “updated_at TIMESTAMP NOT NULL DEFAULT NOW() ON UPDATE NOW()”
This ensures that when new rows are added, the default value is NOW() and then it updates again on every update to that row.

Let’s insert a few rows.

INSERT INTO orders (user_id, amount, created_at) VALUES (1,101.45,NOW());
Query OK, 1 row affected (0.00 sec)
INSERT INTO orders (user_id, amount, created_at) VALUES (1,45.32,NOW());
Query OK, 1 row affected (0.00 sec)

INSERT INTO orders (user_id, amount, created_at) VALUES (2,12.00,NOW());
Query OK, 1 row affected (0.01 sec)

SELECT * FROM orders;
+----+---------+--------+---------------------+---------------------
| id | user_id | amount | created_at          | updated_at          
+----+---------+--------+---------------------+---------------------
|  1 |       1 | 101.45 | 2016-05-04 15:59:21 | 2016-05-04 15:59:21 
|  2 |       1 |  45.32 | 2016-05-04 15:59:21 | 2016-05-04 15:59:21 
|  3 |       2 |     12 | 2016-05-04 15:59:22 | 2016-05-04 15:59:22 
+----+---------+--------+---------------------+---------------------
3 rows in set (0.00 sec)

And now we’ll make an update to the third row.

UPDATE orders SET amount = 120.00 WHERE id = 3;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

SELECT * FROM orders;
+----+---------+--------+---------------------+---------------------
| id | user_id | amount | created_at          | updated_at          
+----+---------+--------+---------------------+---------------------
|  1 |       1 | 101.45 | 2016-05-04 15:59:21 | 2016-05-04 15:59:21 
|  2 |       1 |  45.32 | 2016-05-04 15:59:21 | 2016-05-04 15:59:21 
|  3 |       2 |    120 | 2016-05-04 15:59:22 | 2016-05-04 16:00:07 
+----+---------+--------+---------------------+---------------------
3 rows in set (0.00 sec)

So even though we didn’t make a change to the updated_at column in our UPDATE statement, MySQL knew to update it. The good news is that in MySQL 5.6 you can have the database handle both columns.

CREATE TABLE orders(
  id INT NOT NULL AUTO_INCREMENT,
  user_id INT NULL,
  amount FLOAT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP NOT NULL DEFAULT NOW() ON UPDATE now(),
  PRIMARY KEY(id)
);
Query OK, 0 rows affected (0.02 sec)

Don’t rely on developers to craft proper values for INSERT and UPDATE statements. Let MySQL do the work.

INSERT INTO orders (user_id, amount) VALUES (1,101.45);
Query OK, 1 row affected (0.00 sec)

INSERT INTO orders (user_id, amount) VALUES (1,45.32);
Query OK, 1 row affected (0.01 sec)

INSERT INTO orders (user_id, amount) VALUES (2,12.00);
Query OK, 1 row affected (0.00 sec)
UPDATE orders SET amount = 120.00 WHERE id = 3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

SELECT * FROM orders;
+----+---------+--------+---------------------+---------------------
| id | user_id | amount | created_at          | updated_at          
+----+---------+--------+---------------------+---------------------
|  1 |       1 | 101.45 | 2016-05-04 17:30:19 | 2016-05-04 17:30:19 
|  2 |       1 |  45.32 | 2016-05-04 17:30:19 | 2016-05-04 17:30:19 
|  3 |       2 |    120 | 2016-05-04 17:30:19 | 2016-05-04 17:30:27 
+----+---------+--------+---------------------+---------------------
3 rows in set (0.00 sec)

Fascinating

Awesome, right? But what if you’re stuck on MySQL 5.5 and you absolutely need this? My colleague, Buck Ryan, sent me this excellent trigger to make that work, too.

CREATE TRIGGER orders__before_insert
BEFORE INSERT ON orders
FOR EACH ROW SET NEW.created_at = NOW(), NEW.updated_at = NOW();

CREATE TRIGGER orders__before_update
BEFORE UPDATE ON orders
FOR EACH ROW SET NEW.created_at = OLD.created_at, NEW.updated_at = NOW();