How to Combine Columns Values Into a New Column in MySQL

How can we add a new column that depends on other columns in MySQL?

Suppose the current table in MySQL contains the following fields.

We want to create a new column fullName that contains both fields.

This concatenation can be done through a simple CONCAT operation.

SELECT CONCAT(firstName, ' ', lastName) AS fullName FROM table_name;

Let’s update our table to include this new full name field.

1. Using generated columns

We can use generated column expressions to compute column values based on other columns in the table (even other generated columns that are defined earlier in the table definition).

Let’s add a new generated column.

ALTER TABLE table_name 
ADD COLUMN fullName VARCHAR(100) AS (CONCAT(firstName, ' ', lastName));

AS indicates that the column is generated and will define the expression used to compute the column value.

We can also append the VIRTUAL or STORED keyword to this ALTER TABLE statement.

• VIRTUAL (default): column values are not stored (require no storage) but are evaluated when rows are read
• STORED: column values are evaluated and stored when rows are inserted or updated (can also be indexed)

To store the fullName field in our database, we’ll need to append STORED.

ALTER TABLE table_name 
ADD COLUMN fullName VARCHAR(100) AS (CONCAT(firstName, ' ', lastName)) STORED;

2. Using UPDATE and TRIGGER

2.1. Create the new column

First, we want to add a new nullable column.

ALTER TABLE table_name ADD COLUMN fullName VARCHAR(100);

2.2. Populate the new column

Then, we want to populate this new column with the correct values.

UPDATE table_name SET fullName = CONCAT(firstName, ' ', lastName);

2.3. Update the new column for future values

Finally, we need to ensure all new inserts and updates of the old column update the new column.

We can add a trigger for all INSERT and UPDATE statements on this table.

Whenever a new row is inserted or updated on table_name, we will create or update the fullName column with the correct, concatenated value.

CREATE TRIGGER insert_trigger
BEFORE INSERT ON table_name
FOR EACH ROW
SET new.fullName = CONCAT(firstName, ' ', lastName);

CREATE TRIGGER update_trigger
BEFORE UPDATE ON table_name
FOR EACH ROW
SET new.fullName = CONCAT(firstName, ' ', lastName);