MySQL - 将字段的默认值设置为字符串连接函数 [英] MySQL - Set default value for field as a string concatenation function

问题描述

我有一张表，看起来有点像这个演员（forename，surname，stage_name）;

我想更新stage_name的默认值为

p>

  forename。.surname 
  

$ p
$ b

  insert into actors（forename，surname）values（'Stack'，'Overflow' ）; 
  

会产生记录

 'Stack''Overflow''Stack Overflow'
  

这可能吗？

感谢：）

解决方案

MySQL不支持计算列或表达式在列定义的 DEFAULT 选项中。

您可以在触发器中执行此操作（MySQL 5.0或更高版本）
$ b $ pre $ code CREATE TRIGGER format_stage_name
在插入之前执行者
为每一行
BEGIN
SET NEW.stage_name = CONCAT（NEW.forename，''，NEW.surname）;
END

您可能还想创建一个类似的触发器 BEFORE UPDATE 。

注意名字和姓氏中的 NULL ，因为concat of a NULL 与任何其他字符串产生 NULL 。根据需要，在每一列或串联的字符串上使用 COALESCE（）。

edit：以下示例仅在 NULL 时设置 stage_name 。否则，您可以在 INSERT 语句中指定 stage_name ，并将其保留。

  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 
  

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

解决方案

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

这篇关于MySQL - 将字段的默认值设置为字符串连接函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！