Implementing caching storage on top of Mysql is easy. First of all, we have to implement key-value storage. After that we have to add expiration functionality.

Add expiration column

In order to control keys expiration we're going to use expire column:

CREATE TABLE `cache` (
  `key` varchar(32) NOT NULL PRIMARY KEY,
  `val` TEXT,
  `expire` timestamp

So when saving data for specific key we can set expiration time into expire column:

SET val = '{"name": "val"}', `key` = 'test', expire = NOW() + 60
ON DUPLICATE KEY UPDATE val = '{"name": "val"}', expire = NOW() + 60;

In this case our test key should expire in 60 seconds.

Enabling expiration

Now we return value for a specific key only if expire column value is more than current timestamp:

SELECT val FROM cache WHERE `key` = 'test' AND expire >= NOW();

That's it. As well as key-value implementation we can leverage all Mysql benefits. This approach in implementation is used for Mysqly library caching component.

written on Jan, 2022