CODEWITHSUNDEEP
mysqldatabase
usernameabc
usernameabc

Reputation: 674

How to auto populate a name based on first and last name in mysql?

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

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?

Example:

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

Upvotes: 1

Views: 1734

Answers (1)

ratsbane
ratsbane

Reputation: 890

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.

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;

I hope that helps.

Upvotes: 1

Related Questions