如何在mysql中根据姓氏和名字自动填充名称? [英] How to auto populate a name based on first and last name in mysql?

问题描述

我需要创建一个包含表Member的数据库，该表具有四个属性:名字，中间名，姓氏和名字.

I am need to create a database that contains a table Member, who have four attributes: first, middle, last name, and name.

是否可以在使用"CREATE TABLE成员"时指示将名称设置为"First + Middle + Last"?还是在创建表后必须将其设置为必填项?

Is it possible to indicate that name should be set to First + Middle + Last while using the 'CREATE TABLE member' or does this have to occur after creating the table?

示例:

CREATE TABLE成员(first_name varchar(80)NOT NULL，middle_name varchar(80)NULL，last_name varchar(80)NOT NULL，名称varchar(255)NOT NULL);

CREATE TABLE member (first_name varchar(80) NOT NULL, middle_name varchar(80) NULL, last_name varchar(80) NOT NULL, name varchar(255) NOT NULL);

推荐答案

您可以创建 BEFORE INSERT触发器和BEFORE UPDATE触发器，将名称字段设置为CONCAT_WS(''，first_name，middle_name，last_name)的值，如下所示...但是不要那样做.这是一个可怕的主意.根本不存储名称列.要选择名称时，只需选择CONCAT_WS(''，first_name，middle_name，last_name)AS全名.

You could create a BEFORE INSERT trigger and a BEFORE UPDATE trigger to set the name field to the value of CONCAT_WS(' ', first_name, middle_name, last_name) as follows... but don't do that. It's a terrible idea. Don't store the name column at all. When you want to select the name, just select CONCAT_WS(' ', first_name, middle_name, last_name) AS full_name.

请注意，如果要保留的值中的任何一个为null，那么CONCAT将返回null，因此您可能想使用CONCAT_WS(带分隔符)-如果列表中的任何值为null，则它将忽略该值并使用剩下的.

Note that CONCAT will return null if any of the values you're concatting is null so you probably want to use CONCAT_WS (with separator) instead - if any value in the list is null it will just omit that value and use the remaining ones.

如果您决定这样做，触发器可能看起来像这样:

Your triggers might look something like this if you decided to do that:

 CREATE TRIGGER name_update BEFORE UPDATE ON member
    FOR EACH ROW
    BEGIN
        SET NEW.name = CONCAT_WS(' ', NEW.first_name, NEW.middle_name, NEW.last_name);
    END;

 CREATE TRIGGER name_insert BEFORE INSERT ON member
    FOR EACH ROW
    BEGIN
        SET NEW.name = CONCAT_WS(' ', NEW.first_name, NEW.middle_name, NEW.last_name);
    END;

我希望能帮上忙.

这篇关于如何在mysql中根据姓氏和名字自动填充名称?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！