Auto-updating timestamp column i MariaDB/MySQL

Udgivet den august 9, 2021 af mike

If you need a colum that will automagically show when a row was created or changed you can use the following:

 CREATE TABLE `vars` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `key` varchar(45) DEFAULT NULL,
  `value` longtext DEFAULT NULL,
  `timestamp` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `key_UNIQUE` (`key`)
);

Now try to insert some data:

MariaDB [portal]> insert into vars (`key`,`value`) values ('foo','bar');
Query OK, 1 row affected (0.010 sec)

MariaDB [portal]> select * from vars;
+----+------+-------+---------------------+
| id | key  | value | timestamp           |
+----+------+-------+---------------------+
|  1 | foo  | bar   | 2021-08-09 10:43:30 |
+----+------+-------+---------------------+
1 row in set (0.001 sec)

MariaDB [portal]>

And then change those data:

MariaDB [portal]> update vars set value = "BAR" where id=1;
Query OK, 1 row affected (0.005 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [portal]> select * from vars;
+----+------+-------+---------------------+
| id | key  | value | timestamp           |
+----+------+-------+---------------------+
|  1 | foo  | BAR   | 2021-08-09 10:44:44 |
+----+------+-------+---------------------+
1 row in set (0.001 sec)

MariaDB [portal]>

Enjoy 😉

Dette indlæg blev udgivet i Uncategorized. Bogmærk permalinket.