MySQL supports generated columns. If you’re not sure whether a table has a generated column or not, you can check it using any of the following methods.
The SHOW COLUMNS Statement
The SHOW COLUMNS statement displays information about the columns in a given table or view. We can pass the name of the table or view to return information about its columns. One of the columns returned by this statement is the Extra column, which will tell us whether or not the column is generated.
Example:
SHOW COLUMNS FROM Employees;Result:
+-----------+--------------+------+-----+---------+-------------------+ | Field | Type | Null | Key | Default | Extra | +-----------+--------------+------+-----+---------+-------------------+ | Id | int | NO | PRI | NULL | | | FirstName | varchar(50) | YES | | NULL | | | LastName | varchar(50) | YES | | NULL | | | FullName | varchar(101) | YES | | NULL | VIRTUAL GENERATED | +-----------+--------------+------+-----+---------+-------------------+
In this case we can see that the FullName column is a generated column. We know this because it has GENERATED in the EXTRA column. More specifically, it has VIRTUAL GENERATED, which means that it’s a virtual generated column (as opposed to a stored generated column).
It’s also possible to add a WHERE clause to the SHOW COLUMNS statement to narrow it down to a specific column or columns.
The DESCRIBE / DESC Statement
The DESCRIBE statement is a shortcut for the SHOW COLUMNS FROM syntax:
DESCRIBE Employees;Result:
+-----------+--------------+------+-----+---------+-------------------+ | Field | Type | Null | Key | Default | Extra | +-----------+--------------+------+-----+---------+-------------------+ | Id | int | NO | PRI | NULL | | | FirstName | varchar(50) | YES | | NULL | | | LastName | varchar(50) | YES | | NULL | | | FullName | varchar(101) | YES | | NULL | VIRTUAL GENERATED | +-----------+--------------+------+-----+---------+-------------------+
To return information about just one column, simply include it after the table name.
The COLUMNS Information Schema Table
You can query the information_schema.columns table. This table contains information about columns in each table on the server. It includes a column called EXTRA that will tell us if a column is generated.
Example:
SELECT
column_name,
extra,
generation_expression
FROM information_schema.columns
WHERE table_schema = 'KrankyKranes'
AND table_name = 'Employees';Result:
+--------------+-------------------+----------------------------------------------+ | COLUMN_NAME | EXTRA | GENERATION_EXPRESSION | +--------------+-------------------+----------------------------------------------+ | FirstName | | | | FullName | VIRTUAL GENERATED | concat(`FirstName`,_utf8mb4\' \',`LastName`) | | Id | | | | LastName | | | | EmployeeId | | | | EmployeeName | | | +--------------+-------------------+----------------------------------------------+
I’ve also included the GENERATION_EXPRESSION column in the example. This column returns the expression that the generated uses to generate its value. In this case, the generated column concatenates the FirstName and LastName columns.
The SHOW CREATE TABLE Statement
Another way to do it is with the SHOW CREATE TABLE statement.
The SHOW CREATE TABLE statement shows the CREATE TABLE statement that created the given table. Therefore, when we use this, we’ll see the definition of each column in the table – including any generated 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 GENERATED ALWAYS, which means it’s a generated column. It also includes the VIRTUAL keyword, which means that it is a virtual column (its value is not stored – it’s generated dynamically when the row is read).
The mysqlshow Client
Another way to do it is with the mysqlshow client. This is a separate client, so you run this from the command line (i.e. not from within MySQL itself).
So, we can check the above table by opening the command line (e.g. open a new Terminal window) and entering the following:
mysqlshow -u root KrankyKranes EmployeesResult:
+-----------+--------------+--------------------+------+-----+---------+-------------------+---------------------------------+---------+ | Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment | +-----------+--------------+--------------------+------+-----+---------+-------------------+---------------------------------+---------+ | Id | int | | NO | PRI | | | select,insert,update,references | | | FirstName | varchar(50) | utf8mb4_0900_ai_ci | YES | | | | select,insert,update,references | | | LastName | varchar(50) | utf8mb4_0900_ai_ci | YES | | | | select,insert,update,references | | | FullName | varchar(101) | utf8mb4_0900_ai_ci | YES | | | VIRTUAL GENERATED | select,insert,update,references | | +-----------+--------------+--------------------+------+-----+---------+-------------------+---------------------------------+---------+
Here, I specified the KrankyKranes database and the root user. I specified the Employees table after the database.
We can see that various columns of information are returned, including the Extra column, which tells us whether or not the column is generated. Again, we can see that it’s a virtual column (i.e. it’s VIRTUAL GENERATED).