MySQL Cookbook, 2nd Edition

Using a Trigger to Define Dynamic Default Column Values

Problem

A column in a table needs to be initialized to a nonconstant value, but MySQL allows only constant default values.

Solution

Use a BEFORE INSERT trigger. This enables you to initialize a column to the value of an arbitrary expression. In other words, the trigger performs dynamic column initialization by calculating the default value.

Discussion

Other than TIMESTAMP columns, which can be initialized to the current date and time, the default value for a column in MySQL must be a constant value. You cannot define a column with a DEFAULT clause that refers to a function call (or other arbitrary expression), and you cannot define one column in terms of the value assigned to another column. That means each of these column definitions is illegal:

d       DATE DEFAULT NOW()
i       INT DEFAULT (... some subquery ...)
hashval CHAR(32) DEFAULT MD5(blob_col)

However, you can work around this limitation by setting up a suitable trigger, which enables you to initialize a column however you want. In effect, the trigger enables you to define dynamic (or calculated) default column values.

The appropriate type of trigger for this is BEFORE INSERT, because that enables you to set column values before they are inserted into the table. (An AFTER INSERT trigger can examine column values for a new row, but by the time the trigger activates, it’s too late to change the values.)

Suppose that you want to use a table for storing large data values such as PDF or XML ...