Virtual column in MySQL is generated column, this column data or value comes from expression or function or other columns.

The difference between virtual column and normal column is normal table column value saved to disk

but virtual column value generated dynamically from any expression or function or from other columns.

So the virtual column has following characteristics

• Virtual column is generated column
• Virtual column value comes from expression or function or from other columns
• Virtual column data is not stored to disk

The following example demonstrates how to create virtual column using MySQL SELECT

Example 1:

SELECT id, email, IF(is_active = 1, 'enabled', 'disabled') AS status
FROM customers

Example 2:

SELECT users.id, users.email, IF(countries.id IS NULL, 'not selected', countries .name) as country_name
FROM users
LEFT JOIN countries ON users.country_id = countries.id

Example 3:

create table users(
    user_id int(11) NOT NULL AUTO_INCREMENT primary key,
    first_name varchar(20),
    last_name varchar(20),
    full_name varchar(100) GENERATED ALWAYS AS (concat(first_name,' ',last_name)),
    email varchar(25)
);

Tags : MySQL,