MySQL - Set default value for field as a string concatenation function

Question

Hi

I have a table that looks a bit like this actors(forename, surname, stage_name);

I want to update stage_name to have a default value of

forename." ".surname

So that

insert into actors(forename, surname) values ('Stack', 'Overflow');

would produce the record

'Stack'     'Overflow'    'Stack Overflow'

Is this possible?

Thanks :)

Answer 1

According to 10.1.4. Data Type Default Values no, you can't do that. You can only use a constant or CURRENT_TIMESTAMP.

OTOH if you're pretty up-to-date, you could probably use a trigger to accomplish the same thing.

Answer 2

MySQL does not support computed columns or expressions in the DEFAULT option of a column definition.

You can do this in a trigger (MySQL 5.0 or greater required):

CREATE TRIGGER format_stage_name 
BEFORE INSERT ON actors
FOR EACH ROW
BEGIN
  NEW.stage_name = CONCAT(NEW.forename, ' ', NEW.surname);
END

You may also want to create a similar trigger BEFORE UPDATE.

Watch out for NULL in forename and surname, because concat of a NULL with any other string produces a NULL. Use COALESCE() on each column or on the concatenated string as appropriate.

edit: The following example sets stage_name only if it's NULL. Otherwise you can specify the stage_name in your INSERT statement, and it'll be preserved.

CREATE TRIGGER format_stage_name 
BEFORE INSERT ON actors
FOR EACH ROW
BEGIN
  IF (NEW.stage_name IS NULL) THEN
    NEW.stage_name = CONCAT(NEW.forename, ' ', NEW.surname);
  END IF;
END

Answer 3

My first thought is if you have the two values in other fields what is the compelling need for redundantly storing them in a third field? It flies in the face of normalization and efficiency.

If you simply want to store the concatenated value then you can simply create a view (or IMSNHO even better a stored procedure) that concatenates the values into a pseudo actor field and perform your reads from the view/sproc instead of the table directly.

If you absolutely must store the concatenated value you could handle this in two ways:

1) Use a stored procedure to do your inserts instead of straight SQL. This way you can receive the values and construct a value for the field you wish to populate then build the insert statement including a concatenated value for the actors field.

2) So I don't draw too many flames, treat this suggestion with kid gloves. Use only as a last resort. You could hack this behavior by adding a trigger to build the value if it is left null. Generally triggers are not good. They add unseen cost and interactions to fairly simple interactions. You can, though, use the CREATE TRIGGER to update the actors field after a record is inserted or updated. Here is the reference page:

http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html