how to create calculated field in mysql?

Question

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

Solution 1

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);

Solution 2

You should write a Trigger to achieve this functionality, given that MySQL has a md5 function built in or you are capable to either write or find a md5 algorithm for MySQL :)

Take a look @ http://dev.mysql.com/doc/refman/5.0/en/triggers.html

for the md5 function take a look here

http://dev.mysql.com/doc/refman/5.0/en/encryption-functions.html

Also, you should know that MD5 is not a secure algorithm anymore.