Using virtual columns

The virtual columns feature of MariaDB allows us to create columns which contain precalculated or calculated on-the-fly values.

How to do it...

1. Launch the mysql command-line client and connect to our MariaDB database.

2. Create a test database and switch to that database using the following command:

   CREATE DATABASE IF NOT EXISTS test;
   USE test;
   

3. Create a table with virtual columns using the following command:

   CREATE TABLE virt_cols (
     id SERIAL PRIMARY KEY,
     surname VARCHAR(64),
     givenname VARCHAR(64),
     uid INT AS (id + 1000) VIRTUAL,
     username VARCHAR(6) AS 
       (LOWER(CONCAT(LEFT(givenname,1),(LEFT(surname,5))))) PERSISTENT);
   

4. Examine the structure of the table using the following command:

   DESCRIBE virt_cols;
   

   The DESCRIBE command displays the structure of the table as shown in the following screenshot:

   

5. Show a CREATE TABLE command that will recreate the exact table (including the virtual columns):

   SHOW CREATE TABLE virt_cols\G
   

   The preceding command displays the following...