Today, somebody asked me how he could track the creation of a record (who created it and when) and who and when it was last modified.
Usually, this is performed in a different table, called audit table. In MySQL you can create an audit table and populate it using triggers.
But of course, this person had some constraints, otherwise it would have been too easy:
- no other table could be used/created
- the application should keep working without any changes
He was desperate for help…
MySQL 8.0 Invisible Columns
The answer is : Invisible Columns.
Let’s have a look at a simple table, containing an ID as primary key, a first name and a last name. And of course the application must work as expected… and unfortunately, it seems the application is abusing select * from:
mysql> select * from demo; +----+-----------+----------+ | id | firstname | lastname | +----+-----------+----------+ | 1 | Frederic | Descamps | | 2 | Kenny | Gryp | +----+-----------+----------+
Let’s have a look at the table’s definition:
CREATE TABLE `demo` ( `id` int unsigned NOT NULL AUTO_INCREMENT, `firstname` varchar(20) DEFAULT NULL, `lastname` varchar(20) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
We need to add some extra information to this table:
created_at: the timestamp when the record was insertedcreated_by: the user who inserted the recordupdated_at: the timestamp of the last update of the recordupdated_by: the last user who modified the record
To default the timestamp fields, it’s easy and we also need to not forget to set those extra field as invisible:
mysql> alter table demo
add created_at timestamp
default current_timestamp invisible,
add created_by varchar(60) not null
default '' invisible,
add updated_at timestamp
on update current_timestamp invisible,
add updated_by varchar(60) not null
default '' invisible ;
If we let it like this, the user information won’t be filled. We still need to create triggers:
mysql> create trigger demo_insert before insert on demo
for each row
set new.created_by=session_user();
mysql> create trigger demo_update before update on demo
for each row
set new.updated_by=session_user();
Let’s test
mysql> select * from demo;
+----+-----------+----------+
| id | firstname | lastname |
+----+-----------+----------+
| 1 | frederic | descamps |
| 2 | kenny | gryp |
+----+-----------+----------+
2 rows in set (0.0010 sec)
mysql> select *, created_at, created_by, updated_at, updated_by
from demo;
+----+-----------+----------+-----------------+---+--------+--------+
| id | firstname | lastname | created_at |by | updated| by |
+----+-----------+----------+-----------------+---+--------+--------+
| 1 | frederic | descamps | 2022-04-14 23.. | | NULL | |
| 2 | kenny | gryp | 2022-04-14 23.. | | NULL | |
+----+-----------+----------+-----------------+---+--------+--------+
We can notice that the hidden columns are displayed only when we specifically request so.
Now let’s add a new record:
mysql> insert into demo (firstname, lastname)
values ('miguel', 'araujo');
mysql> select * from demo;
+----+-----------+----------+
| id | firstname | lastname |
+----+-----------+----------+
| 1 | frederic | descamps |
| 2 | kenny | gryp |
| 3 | miguel | araujo |
+----+-----------+----------+
3 rows in set (0.0002 sec)
mysql> select *, created_at, created_by, updated_at, updated_by
from demo where id=3\G
*************************** 1. row ***************************
id: 3
firstname: miguel
lastname: araujo
created_at: 2022-04-14 23:22:25
created_by: root@localhost
updated_at: NULL
updated_by:
Now from another session:
mysql> update demo set firstname='fred' where id=1;
mysql> select id, updated_at, updated_by from demo;
+----+---------------------+----------------+
| id | updated_at | updated_by |
+----+---------------------+----------------+
| 1 | 2022-04-14 23:30:20 | fred@localhost |
| 2 | NULL | |
| 3 | NULL | |
+----+---------------------+----------------+
mysql> insert into demo (firstname, lastname)
values ('dimitri', 'k');
mysql> select id, firstname, created_by, updated_by from demo;
+----+-----------+----------------+----------------+
| id | firstname | created_by | updated_by |
+----+-----------+----------------+----------------+
| 1 | fred | | fred@localhost |
| 2 | kenny | | |
| 3 | miguel | root@localhost | |
| 4 | dimitri | fred@localhost | |
+----+-----------+----------------+----------------+
And of course we can use the new hidden column to also query the records:
mysql> select * from demo
where created_by like 'fred%'
or updated_by like 'fred%';
+----+-----------+----------+
| id | firstname | lastname |
+----+-----------+----------+
| 1 | fred | descamps |
| 4 | dimitri | k |
+----+-----------+----------+
JSON
Then I had in mind to provide the audit information as JSON. Let’s create a second table with the following triggers:
mysql> CREATE TABLE `demo2` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`firstname` varchar(20) DEFAULT NULL,
`lastname` varchar(20) DEFAULT NULL,
`audit_info` json DEFAULT NULL /*!80023 INVISIBLE */,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
mysql> create trigger demo2_insert before insert on demo2
for each row
set new.audit_info=json_object('created_at', now(),
'created_by', session_user());
mysql> create trigger demo2_update before update on demo2
for each row
set new.audit_info=json_merge_patch(old.audit_info,
json_object('update_at', now(),
'updated_by', session_user()));
And let’s test it:
mysql> insert into demo2 (firstname, lastname)
values ('frederic', 'descamps'), ('kenny', 'gryp');
mysql> select *, audit_info from demo2;
+----+-----------+----------+---------------------------------------+
| id | firstname | lastname | audit_info |
+----+-----------+----------+---------------------------------------+
| 3 | frederic | descamps | {"created_at": "2022-04-15 |
| | 00:03:37.000000", |
| | "created_by": "fred@localhost"} |
| 4 | kenny | gryp | {"created_at": "2022-04-15 |
| | 00:03:37.000000", |
| | "created_by": "fred@localhost"} |
+----+-----------+----------+---------------------------------------+
mysql> update demo2 set firstname='fred' where id=3;
mysql> select *, json_pretty(audit_info) from demo2\G
*************************** 1. row ***************************
id: 3
firstname: fred
lastname: descamps
json_pretty(audit_info): {
"update_at": "2022-04-15 00:04:57.000000",
"created_at": "2022-04-15 00:03:37.000000",
"created_by": "fred@localhost",
"updated_by": "fred@localhost"
}
*************************** 2. row ***************************
id: 4
firstname: kenny
lastname: gryp
json_pretty(audit_info): {
"created_at": "2022-04-15 00:03:37.000000",
"created_by": "fred@localhost"
}
There is something even more interesting when using a JSON data type for the audit information, we can keep track of all updates.
Let’s replace the demo2_update trigger by this one:
mysql> create trigger demo2_update before update on demo2
for each row
set new.audit_info=json_merge_preserve(old.audit_info,
json_object('update_at', now(),
'updated_by', session_user()));
We used the function json_merge_preserve() instead of json_patch() and this is the result:
mysql> update demo2 set firstname='lefred' where id=3;
mysql> select *, json_pretty(audit_info) from demo2 where id=3\G
*************************** 1. row ***************************
id: 3
firstname: lefred
lastname: descamps
json_pretty(audit_info): {
"update_at": [
"2022-04-15 00:04:57.000000",
"2022-04-15 00:14:47.000000"
],
"created_at": "2022-04-15 00:03:37.000000",
"created_by": "fred@localhost",
"updated_by": [
"fred@localhost",
"fred@localhost"
]
}
Cool isn’t it ? We have the time of all modifications and by who. Of course you can extend this solution by adding even more information like old and new values…
Conclusion
As you could see, MySQL 8.0 invisible columns provide the possibility to have some audit information in the same table.
That information can be simple hidden fields or even a single JSON containing all the information yo need.
Enjoy MySQL !
