MySQL 8.0.13 Can Use An Existing Column As The DEFAULT Value For A New Column

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 statement:

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:

1. Add new executedAt column with DEFAULT( `createdAt` ) in order to get the foundations in place.

2. Update my ColdFusion application code to start providing the executedAt column value during INSERT operations.

3. Run a subsequent ALTER TABLE statement for executedAt that removes the DEFAULT expression.

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.