how to create calculated field in mysql?

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

Contents hide

Answers:

Method 1

Method 2

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

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

Method 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.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating