Some time back, in my live cohort, we were discussing BLOB vs TEXT, and while I was skimming MySQL documentation to brush up on some concepts, I stumbled upon something interesting.
MySQL supports “generated columns” and you can provide an expression that will be used by MySQL to automatically create a new column (virtual or stored) and populate the value as per it.
Two interesting use cases of generated columns can be
-
A complicated condition can be created as a virtual generated column making it simpler for engineers to not type complex WHERE clause every time.
-
Generated columns can be used as a materialized cache e.g. Hash of a subset of columns for quick lookups and saving you the effort of computing and inserting every time.
The best part about this feature is that you cannot update the value of the column, which means no accidental edits to the data by humans or buggy code. Pretty cool.
Link to read more about it in the comment.
I keep writing and sharing content about engineering, career growth, and becoming a better engineer. The link to all my write-ups is in the comment below.