A generated column is one whose value is derived from an expression, as opposed to a fixed value. The expression typically uses other columns in the same table to compute the derived value.

We can create a generated column in MySQL very easily. But what if we want to go back later and see its definition?

Here are two options for returning the definition of a generated column in MySQL.

Option 1: The COLUMNS Information Schema Table

The information_schema.columns table contains information about columns in each table on the server. Here’s an example of querying this table for the definition of a generated column:

SELECT 
  column_name, 
  extra,
  generation_expression
FROM information_schema.columns
WHERE table_name = 'Employees';

Result:

+--------------+-------------------+----------------------------------------------+
| COLUMN_NAME  | EXTRA             | GENERATION_EXPRESSION                        |
+--------------+-------------------+----------------------------------------------+
| FirstName    |                   |                                              |
| FullName     | VIRTUAL GENERATED | concat(`FirstName`,_utf8mb4\' \',`LastName`) |
| Id           |                   |                                              |
| LastName     |                   |                                              |
| EmployeeId   |                   |                                              |
| EmployeeName |                   |                                              |
+--------------+-------------------+----------------------------------------------+

Here, I returned information about the columns from the Employees table. In this case I queried the whole table (I returned all rows from the table).

The information_schema.columns table includes a column called GENERATION_EXPRESSION that contains the expression used for computing the column value in a generated column. In our case, only one column (FullName) is generated, and so only its expression is listed.

There’s also another column called EXTRA. I included this column because it tells us whether the column is generated, and if so, whether it’s virtual or stored/persistent. Stored columns take up space in the table (because their value is stored in the table), whereas virtual columns don’t (because their value is generated dynamically when the row is read).

Option 2: The SHOW CREATE TABLE Statement

Another way to return the definition of a generated column is with the SHOW CREATE TABLE statement.

The SHOW CREATE TABLE statement shows the CREATE TABLE statement that created the given table. Therefore, we can use it to show us the definition of any generated columns in a table (as well as the definition of other columns).

Example:

SHOW CREATE TABLE Employees;

Result:

+-----------+---------------------------------------------------+
| Table     | Create Table                                                                                                                                                                                                                                                                                                               |
+-----------+---------------------------------------------------+
| Employees | CREATE TABLE `Employees` (
              `Id` int NOT NULL,
              `FirstName` varchar(50) DEFAULT NULL,
              `LastName` varchar(50) DEFAULT NULL,
              `FullName` varchar(101) GENERATED ALWAYS AS (concat(`FirstName`,_utf8mb4' ',`LastName`)) VIRTUAL,
              PRIMARY KEY (`Id`)
              ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-----------+---------------------------------------------------+ 

Here, I queried the Employees table again. We can see each column and its definition. We can also see that our definition for the generated column (FullName) contains VIRTUAL, which means that it is a virtual column (its value is not stored – it’s generated dynamically when the row is read).