MySQL supports invisible columns (from MySQL 8.0.23), which means we can control whether a column is considered visible or invisible.

Invisible columns are hidden from queries that use the asterisk wildcard (*) in their SELECT list, but can be selected by explicitly naming the column.

We can create invisible columns when creating a table. We can also add invisible columns to existing tables. And we can modify existing columns to be invisible (and vice-versa).

Creating a Table with an Invisible Column

To create an invisible column, we simply use the INVISIBLE keyword in the table’s definition for that column:

CREATE TABLE t1
(
    c1     varchar(20),
    c2     varchar(20),
    c3     varchar(20) INVISIBLE
);

In the above code example, I create a table with three columns. The first two are visible by default, but the third column is invisible (because I used the INVISIBLE keyword against that column).

Setting an Existing Column to Invisible

If the column already exists, we can modify it to be invisible:

ALTER TABLE t1
MODIFY COLUMN c2 varchar(20) INVISIBLE;

That sets the c2 column to invisible.

We can also use the VISIBLE keyword to make an invisible column visible. VISIBLE is the default value, so if we don’t specify INVISIBLE or VISIBLE then it will be VISIBLE.

However, the VISIBLE keyword won’t necessarily turn up in metadata (but INVISIBLE will). For example, when using DESCRIBE, SHOW COLUMNS, or similar methods to return metadata about the table, we’ll see INVISIBLE in places like the Extra column, but for visible columns that column will be empty. See “Checking the Visibility of a Column” below for an example of what I mean.

Adding a New Invisible Column

We can make columns invisible when adding them to a table:

ALTER TABLE t1 
ADD COLUMN c4 varchar(20) INVISIBLE;

In this case I added an invisible column called c4.

Checking the Visibility of a Column

We can use commands such as DESCRIBE and SHOW COLUMNS to see whether a column is invisible or not.

Let’s check our table for invisible columns:

DESCRIBE t1;

Result:

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

We can see by looking at the Extra column that three of our columns are invisible and one is visible. Visible columns are the default, and so no visibility information is presented for visible columns.