jfg-mysql.blogspot.com
MySQL Best Practice : not using date / time types, nor ENUM
Today, I was reminded of a MySQL Best Practice, probably generalizable to all databases : using simple types, not complex types. Such complex types to avoid include the date and time data types (including TIMESTAMP) and ENUM. Let's see why.
A little history about this, Baron Schwartz, a MySQL Legend who is not involved in the community anymore, compared using the TIMESTAMP type to **_running with scissors_**. His blog is gone, but the Wayback Machine has a copy of his post from February 2014. His advice : storing epochs in a numeric field !
Also at Booking.com, we stopped using the ENUM type. One reason was because of their complex behavior, including removing a value having to rebuild the table. Using a numeric value instead of an ENUM was the best practice there (more about ENUM below).
The more general reason for not using complex / high-level types : you probably do not fully know how they behave. And if you think you master them, you are probably wrong. And in the unlikely case where you fully understand them, your colleagues will not. Time zones are complicated, which explains TIMESTAMP, but ENUM looks simple, no ?
And this brings me to the _Today I Learned_ moment of the day : I did not know that MIN and MAX on ENUM is on the _String value_ , not on the numeric / position value (ORDER BY is on the position). I learned that via Bug #121036: Creating a secondary index on an ENUM column changes the result of MIN()/MAX(). Could you imagine having to rebuild an index when changing an ENUM ? Me neither !
> **So do not use ENUM, nor SET, nor TIMESTAMP !**
And as Baron wrote : _Enjoy all the spare time you’ll have to do actually useful things as a result_.
