how to create calculated field in mysql?

Alaa picture Alaa · Nov 21, 2010 · Viewed 19.2k times · Source

i have a table like below:

create table info (username varchar(30),otherinfo varchar(100));

now i want to alter this table to have new field and this field has to have default value as

md5(username)

something like below:

alter table info add NewField varchar(100) default md5(username);

how to do so?
Thanks for your help

Answer

mechanical_meat picture mechanical_meat · Nov 21, 2010

Per MySQL docs (emphasis added) you cannot have an expression in a default value:

10.1.4. Data Type Default Values

The DEFAULT value clause in a data type specification indicates a default value for a column. With one exception, the default value must be a constant; it cannot be a function or an expression. This means, for example, that you cannot set the default for a date column to be the value of a function such as NOW() or CURRENT_DATE. The exception is that you can specify CURRENT_TIMESTAMP as the default for a TIMESTAMP column.

I've tested that the following trigger works for your intent:

CREATE TRIGGER MyTriggerName
  BEFORE INSERT ON info
  FOR EACH ROW
    SET NEW.NewField = md5(NEW.username);