MySQL 8 Cookbook

How it works...

We introduce a column called soft_delete which will be set by trigger. The soft_delete column will be part of range column partitioning.

Partitioning will be like (soft_delete, expires). The soft_delete and expires together controls which partition a row should go. soft_delete column decides the retention of the row. If the expires is 0, the trigger sets the soft_delete value to 0 which puts the row in a no_retention partition and if the value of expires is out of partition bounds, trigger sets the soft_delete value to 1 and the row will be put into a long_retention partition. If the value of expires is with in the partition bounds, trigger sets the soft_delete value to 2. Depending on the value of the expires, the row will ...