In my Dig Deep Health app, I wish to get started differentiating between the date on which a document was once created within the gadget and the date that stated document represents for the consumer. For instance, when back-filling exercise information, an “workout efficiency” from “3 years in the past” could be entered into the app “as of late”. As such, I wish to upload an executedAt date/time column; and, for all present rows, I wish to default stated column worth to compare the present createdAt column. As of MySQL 8.0.13, it seems that you’ll do that in the true desk column definition.

Given a desk resistance_exercise_instance, with an present column, createdAt, I will be able to upload a brand new column, executedAt, the usage of the next ALTER observation:

ALTER TABLE
	`resistance_exercise_instance`
ADD COLUMN
	`executedAt` datetime NOT NULL DEFAULT ( `createdAt` )
;

Right here, the assemble DEFAULT( `createdAt` ) tells MySQL to populate the brand new executedAt column with the corresponding createdAt worth for all present rows. Simple peasy, lemon squeezey!

After all, I am relatively anal about how my MySQL database columns are outlined. In my view, I do not like having default values as a result of it is an excessive amount of “magic”; and, I might relatively have all default values treated explicitly within the software code the place they are able to be observed and understood via the builders. As such, my exact workflow for the usage of an expression like this could be to:

1. Upload new executedAt column with DEFAULT( `createdAt` ) with a purpose to get the rules in position.

2. Replace my ColdFusion software code to begin offering the executedAt column worth all over INSERT operations.

3. Run a next ALTER TABLE observation for executedAt that eliminates the DEFAULT expression.

This fashion, my ColdFusion software (is pressured to) turn out to be the “supply of fact” for all information.

Previous to MySQL 8, with a purpose to upload this sort of column, I’d have had so as to add the column with a default worth of NULL (mega barf!); after which, run a next SQL script to populate the column with the required worth. With the ability to set a default the usage of an present column makes this somewhat slightly more uncomplicated!

Need to use code from this put up?
Take a look at the license.