In my Dig Deep Fitness app, I need to start differentiating between the date on which a record was created in the system and the date that said record represents for the user. For example, when back-filling workout data, an "exercise performance" from "3 years ago" might be entered into the app "today". As such, I need to add an
executedAt date/time column; and, for all existing rows, I need to default said column value to match the existing
createdAt column. As of MySQL 8.0.13, it turns out that you can do this in the actual table column definition.
Given a table
resistance_exercise_instance, with an existing column,
createdAt, I can add a new column,
executedAt, using the following
ALTER TABLE `resistance_exercise_instance` ADD COLUMN `executedAt` datetime NOT NULL DEFAULT ( `createdAt` ) ;
Here, the construct
DEFAULT( `createdAt` ) tells MySQL to populate the new
executedAt column with the corresponding
createdAt value for all existing rows. Easy peasy, lemon squeezey!
Of course, I'm somewhat anal about how my MySQL database columns are defined. Personally, I don't like having default values because it's too much "magic"; and, I'd rather have all default values handled explicitly in the application code where they can be seen and understood by the developers. As such, my actual workflow for using an expression like this would be to:
DEFAULT( `createdAt` )in order to get the foundations in place.
Update my ColdFusion application code to start providing the
executedAtcolumn value during
Run a subsequent
ALTER TABLEstatement for
executedAtthat removes the
This way, my ColdFusion application (is forced to) become the "source of truth" for all data.
Prior to MySQL 8, in order to add this type of column, I would have had to add the column with a default value of
NULL (mega barf!); and then, run a subsequent SQL script to populate the column with the desired value. Being able to set a default using an existing column makes this quite a bit easier!
Want to use code from this post? Check out the license.