Problem
For computed columns, SHOW CREATE TABLE shows the expression used to compute the virtual or persisted column. However, the information_schema.COLUMNS does not show this information. The EXTRA column just has the values
- VIRTUAL GENERATED
- STORED GENERATED
Solution
Extend the Information schema to show the expression for the computed columns, just in the same way that you can see the DEFAULT clause.
Reproduce
create or replace table gold_stars (
|
emp_id int(10),
|
count int(10),
|
ts DATETIME DEFAULT NOW(),
|
expires DATETIME as (DATE_ADD(ts, interval 1 year)) virtual,
|
rolling DATETIME as (DATE_ADD(now(), interval 3 month)) virtual,
|
sbox DATETIME as (DATE_ADD(ts, interval 6 year)) persistent
|
)
|
The computed columns (and default) can be seen thus
MariaDB [test]> show create table gold_stars;
|
+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| gold_stars | CREATE TABLE `gold_stars` (
|
`emp_id` int(10) DEFAULT NULL,
|
`count` int(10) DEFAULT NULL,
|
`ts` datetime DEFAULT current_timestamp(),
|
`expires` datetime GENERATED ALWAYS AS (`ts` + interval 1 year) VIRTUAL,
|
`rolling` datetime GENERATED ALWAYS AS (current_timestamp() + interval 3 month) VIRTUAL,
|
`sbox` datetime GENERATED ALWAYS AS (`ts` + interval 6 year) STORED
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
|
+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
1 row in set (0.00 sec)
|
Looking at the information_schema.COLUMNS, the expression cannot be seen
MariaDB [test]> select * from information_schema.COLUMNS where table_name = "gold_stars";
|
+---------------+--------------+------------+-------------+------------------+---------------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+----------------+-------------+------------+-------------------+---------------------------------+----------------+
|
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | ORDINAL_POSITION | COLUMN_DEFAULT | IS_NULLABLE | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH | NUMERIC_PRECISION | NUMERIC_SCALE | DATETIME_PRECISION | CHARACTER_SET_NAME | COLLATION_NAME | COLUMN_TYPE | COLUMN_KEY | EXTRA | PRIVILEGES | COLUMN_COMMENT |
|
+---------------+--------------+------------+-------------+------------------+---------------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+----------------+-------------+------------+-------------------+---------------------------------+----------------+
|
| def | test | gold_stars | emp_id | 1 | NULL | YES | int | NULL | NULL | 10 | 0 | NULL | NULL | NULL | int(10) | | | select,insert,update,references | |
|
| def | test | gold_stars | count | 2 | NULL | YES | int | NULL | NULL | 10 | 0 | NULL | NULL | NULL | int(10) | | | select,insert,update,references | |
|
| def | test | gold_stars | ts | 3 | current_timestamp() | YES | datetime | NULL | NULL | NULL | NULL | 0 | NULL | NULL | datetime | | | select,insert,update,references | |
|
| def | test | gold_stars | expires | 4 | NULL | YES | datetime | NULL | NULL | NULL | NULL | 0 | NULL | NULL | datetime | | VIRTUAL GENERATED | select,insert,update,references | |
|
| def | test | gold_stars | rolling | 5 | NULL | YES | datetime | NULL | NULL | NULL | NULL | 0 | NULL | NULL | datetime | | VIRTUAL GENERATED | select,insert,update,references | |
|
| def | test | gold_stars | sbox | 6 | NULL | YES | datetime | NULL | NULL | NULL | NULL | 0 | NULL | NULL | datetime | | STORED GENERATED | select,insert,update,references | |
|
+---------------+--------------+------------+-------------+------------------+---------------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+----------------+-------------+------------+-------------------+---------------------------------+----------------+
|
6 rows in set (0.00 sec)
|
|