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

Question

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

Ardine

Answer 1

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
  SET 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
    SET NEW.stage_name = CONCAT(NEW.forename, ' ', NEW.surname);
  END IF;
END

Goddard

I haven't used MySQL, but will this ALWAYS overwrite the column with the concatenated value? If you want it to act as a default you should be able to override it, so I would think you would need a coalesce on the NEW.stage_name as well. – Chev

@bill-karwin I'm getting a syntax error on NEW.aka = CONCAT(NEW.fname, ' ', NEW.lname) ... see my complete command here on pastebin ... any pointers?? – Pulling

@mOrloff, I forgot the SET keyword. I have edited the code above to correct this. – Goddard

@BillKarwin Hmm, tried with SET and still no go :( .. I did eliminate concat from the suspect list though :) ... Please review my current status at this SO question here – Pulling

@mOrloff, I'd guess you forgot to use DELIMITER. – Goddard

That was it exactly. I added a DELIMITER, then it was smooth sailing :) – Pulling

Answer 2

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.

Reticulate

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.

Universalist

Although it defaults to the concatenated value, that doesn't mean that the default can't be overridden in the insert or updated later – Chev

Answer 4

As of MySQL 8.0.13, you can use DEFAULT clause for a column which can be a literal constant or an expression.

If you want to use an expression then, simply enclose the required expression within parentheses.

(concat(forename," ",surname))

There are two ways to accomplish what you are trying to do as per my knowledge: (important: consider backing up your table first before running below queries)

1- Drop the column "stage_name" all together and create a new one with DEFAULT constraint.

ALTER TABLE actors ADD COLUMN stage_name VARCHAR(20) DEFAULT (concat(forename," ",surname))

2- This will update newer entries in the column "stage_name" but not the old ones.

  ALTER TABLE actors alter stage_name set DEFAULT (concat(forename," ",surname));

After that, if you need to update the previous values in the column "stage_name" then simply run:

UPDATE actors SET stage_name=(concat(forename," ",surname));

I believe this should solve your problem.

Quartic