5 Ways to Check a Table for Invisible Columns in MySQL

Since MySQL 8.0.23 we’ve had the ability to create invisible columns, which are columns that are normally hidden to queries (but can easily be accessed if required).

With the introduction of invisible columns comes the inevitable requirement to check a table to see if it contains any invisible columns.

Fortunately there are many ways we can do this. Below are five ways to check a table for invisible columns.

The `DESCRIBE` Statement

One way to check a table for invisible columns is with the DESCRIBE statement:

DESCRIBE t1;

Sample result:

+-------+-------------+------+-----+---------+-----------+
| Field | Type        | Null | Key | Default | Extra     |
+-------+-------------+------+-----+---------+-----------+
| c1    | varchar(20) | YES  |     | NULL    |           |
| c2    | varchar(20) | YES  |     | NULL    |           |
| c3    | varchar(20) | YES  |     | Salad   | INVISIBLE |
+-------+-------------+------+-----+---------+-----------+

The DESCRIBE command returns information about table structure, including columns and their data types, along with an Extra column that contains INVISIBLE if the column is invisible.

In the above example I checked a table called t1, which contains one invisible column called c3.

I should mention that the Extra column isn’t used solely for invisible columns. It can contain other data too. For example if it’s a generated column, then it would contain information that tells us that. So if the above invisible column was also generated, we might see something like this:

+-------+-------------+------+-----+---------+-----------------------------+
| Field | Type        | Null | Key | Default | Extra                       |
+-------+-------------+------+-----+---------+-----------------------------+
| c1    | varchar(20) | YES  |     | NULL    |                             |
| c2    | varchar(20) | YES  |     | NULL    |                             |
| c3    | varchar(20) | YES  |     | NULL    | VIRTUAL GENERATED INVISIBLE |
+-------+-------------+------+-----+---------+-----------------------------+

We can actually follow up the table name with a column name if required. This allows us to narrow it down to just one specific column:

DESCRIBE t1 c3;

Sample result:

+-------+------+------+-----+---------+-----------+
| Field | Type | Null | Key | Default | Extra     |
+-------+------+------+-----+---------+-----------+
| c3    | int  | YES  |     | NULL    | INVISIBLE |
+-------+------+------+-----+---------+-----------+

The `DESC` Statement

We can shorten the above statement even further by using DESC instead of DESCRIBE:

DESC t1;

Sample result:

+-------+-------------+------+-----+---------+-----------+
| Field | Type        | Null | Key | Default | Extra     |
+-------+-------------+------+-----+---------+-----------+
| c1    | varchar(20) | YES  |     | NULL    |           |
| c2    | varchar(20) | YES  |     | NULL    |           |
| c3    | varchar(20) | YES  |     | Salad   | INVISIBLE |
+-------+-------------+------+-----+---------+-----------+

DESC does exactly the same thing as DESCRIBE, as seen in this example.

The `EXPLAIN` Statement

EXPLAIN and DESCRIBE are synonyms, so we can alternatively use EXPLAIN instead of DESCRIBE or DESC:

EXPLAIN t1;

Sample result:

+-------+-------------+------+-----+---------+-----------+
| Field | Type        | Null | Key | Default | Extra     |
+-------+-------------+------+-----+---------+-----------+
| c1    | varchar(20) | YES  |     | NULL    |           |
| c2    | varchar(20) | YES  |     | NULL    |           |
| c3    | varchar(20) | YES  |     | Salad   | INVISIBLE |
+-------+-------------+------+-----+---------+-----------+

So we can see that the same result was returned as in the previous examples.

The `SHOW COLUMNS` Statement

DESCRIBE (and its synonyms) is actually a shortcut for the SHOW COLUMNS statement. So we can use the SHOW COLUMNS statement to get the same result that we got in the previous examples:

SHOW COLUMNS FROM t1;

Sample result:

+-------+-------------+------+-----+---------+-----------+
| Field | Type        | Null | Key | Default | Extra     |
+-------+-------------+------+-----+---------+-----------+
| c1    | varchar(20) | YES  |     | NULL    |           |
| c2    | varchar(20) | YES  |     | NULL    |           |
| c3    | varchar(20) | YES  |     | Salad   | INVISIBLE |
+-------+-------------+------+-----+---------+-----------+

The SHOW COLUMNS statement also allows us to filter the results, and even show extended info:

SHOW EXTENDED FULL COLUMNS FROM t1
LIKE 'c3';

Sample result:

+-------+-------------+--------------------+------+-----+---------+-----------+---------------------------------+---------+
| Field | Type        | Collation          | Null | Key | Default | Extra     | Privileges                      | Comment |
+-------+-------------+--------------------+------+-----+---------+-----------+---------------------------------+---------+
| c3    | varchar(20) | utf8mb4_0900_ai_ci | YES  |     | Salad   | INVISIBLE | select,insert,update,references |         |
+-------+-------------+--------------------+------+-----+---------+-----------+---------------------------------+---------+

We can also return only invisible columns by using a query like this:

SHOW COLUMNS FROM t1
WHERE Extra LIKE '%invisible%';

Sample result:

+-------+-------------+------+-----+---------+-----------------------------+
| Field | Type        | Null | Key | Default | Extra                       |
+-------+-------------+------+-----+---------+-----------------------------+
| c3    | varchar(20) | YES  |     | NULL    | VIRTUAL GENERATED INVISIBLE |
+-------+-------------+------+-----+---------+-----------------------------+

In this example the column was also generated. This is why I used the LIKE operator instead of the equals operator (=).

The `COLUMNS` Information Schema Table

The information_schema.columns 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 invisible. So we can query this table to check a table for invisible columns:

SELECT 
  column_name, 
  extra
FROM information_schema.columns
WHERE table_schema = 'KrankyKranes' 
AND table_name = 't1';

Result:

+-------------+-----------+
| COLUMN_NAME | EXTRA     |
+-------------+-----------+
| c1          |           |
| c2          |           |
| c3          | INVISIBLE |
+-------------+-----------+

One advantage of the information_schema.columns table is that we could run a query that returns all invisible columns in the database or even all invisible columns on the server.

To do this, all we need to do is change our WHERE clause to something like this:

SELECT
    table_schema,
    table_name,
    column_name,
    data_type,
    extra
FROM information_schema.columns
WHERE extra LIKE '%invisible%';

Sample result:

+--------------+------------+-------------+-----------+-----------------------------+
| TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | DATA_TYPE | EXTRA                       |
+--------------+------------+-------------+-----------+-----------------------------+
| krankykranes | t2         | c3          | varchar   | INVISIBLE                   |
| krankykranes | t5         | c3          | varchar   | INVISIBLE                   |
| krankykranes | t1         | c3          | varchar   | VIRTUAL GENERATED INVISIBLE |
| PetHotel     | Dogs       | GoodDog     | bit       | INVISIBLE                   |
+--------------+------------+-------------+-----------+-----------------------------+

The `SHOW CREATE TABLE` Statement

We can alternatively use the SHOW CREATE TABLE statement to return the CREATE TABLE statement used to create the table:

SHOW CREATE TABLE t1;

Result:

+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                      |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `c1` varchar(20) DEFAULT NULL,
  `c2` varchar(20) DEFAULT NULL,
  `c3` varchar(20) DEFAULT 'Salad' /*!80023 INVISIBLE */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

We can see the /*!80023 INVISIBLE */ part, which indicates that it’s an invisible column.

The `mysqlshow` Client

Another way to find invisible columns 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 t1

Result:

+-------+-------------+--------------------+------+-----+---------+-----------+---------------------------------+---------+
| Field | Type        | Collation          | Null | Key | Default | Extra     | Privileges                      | Comment |
+-------+-------------+--------------------+------+-----+---------+-----------+---------------------------------+---------+
| c1    | varchar(20) | utf8mb4_0900_ai_ci | YES  |     |         |           | select,insert,update,references |         |
| c2    | varchar(20) | utf8mb4_0900_ai_ci | YES  |     |         |           | select,insert,update,references |         |
| c3    | varchar(20) | utf8mb4_0900_ai_ci | YES  |     | Salad   | INVISIBLE | select,insert,update,references |         |
+-------+-------------+--------------------+------+-----+---------+-----------+---------------------------------+---------+

Here, I specified the KrankyKranes database and the root user. I specified the t1 table after the database.

We can see that various columns of information are returned, including the Extra column, which tells us if the column is invisible.

The DESCRIBE Statement

The DESC Statement

The EXPLAIN Statement

The SHOW COLUMNS Statement

The COLUMNS Information Schema Table

The SHOW CREATE TABLE Statement

The mysqlshow Client

The `DESCRIBE` Statement

The `DESC` Statement

The `EXPLAIN` Statement

The `SHOW COLUMNS` Statement

The `COLUMNS` Information Schema Table

The `SHOW CREATE TABLE` Statement

The `mysqlshow` Client