In MySQL 8.0.23 and later, we have the ability to create invisible columns when creating or altering a table.

An invisible column is one that is hidden to certain SQL queries, but can be accessed if explicitly referenced.

Basically, invisible columns are hidden to queries that use the asterisk wildcard (*) in their SELECT list. But they can be accessed by explicitly referencing the column by its name.

Invisible tables are hidden from the TABLE statement, as this statement provides no way of selecting individual columns.

Creating 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).

Inserting Data into Invisible Columns

Let’s insert some data into our table:

INSERT INTO t1( c1, c2, c3 )
VALUES( 'Bruno', 'Ballantine', 'Dog' );
INSERT INTO t1( c1, c2, c3 )
VALUES( 'Scratch', 'Smith', 'Cat' );
INSERT INTO t1( c1, c2, c3 )
VALUES( 'Wag', 'Wilson', 'Dog' );

We must explicitly include the invisible column name in the list of column names if we want to insert specific data into the invisible column. If we don’t do this, we’ll need to omit a value for that column, and the implicit default value will be inserted.

Here’s an example of what I mean:

INSERT INTO t1
VALUES( 'Purr', 'Peters', 'Cat' );

Result:

ERROR 1136 (21S01): Column count doesn't match value count at row 1

Same when using the following syntax (with the parentheses):

INSERT INTO t1 ()
VALUES( 'Purr', 'Peters', 'Cat' );

Result:

ERROR 1136 (21S01): Column count doesn't match value count at row 1

So we either need to explicitly include the column name in the column list, or omit a value for the invisible column altogether, in which case the implicit default value will be inserted for that column.

See the MySQL documentation for more about loading data into tables with invisible columns.

Queries Against Invisible Columns

Now let’s use the asterisk wildcard (*) to select all data from the table:

SELECT * FROM t1;

Result:

+---------+------------+
| c1      | c2         |
+---------+------------+
| Bruno   | Ballantine |
| Scratch | Smith      |
| Wag     | Wilson     |
+---------+------------+

Our query only returned the first two columns. It didn’t return the third column because that column’s invisible.

To include the the third column in our query, we need to reference it explicitly:

SELECT *, c3 FROM t1;

Result:

+---------+------------+------+
| c1      | c2         | c3   |
+---------+------------+------+
| Bruno   | Ballantine | Dog  |
| Scratch | Smith      | Cat  |
| Wag     | Wilson     | Dog  |
+---------+------------+------+

We can also explicitly reference the other columns too:

SELECT c1, c2, c3 FROM t1;

Result:

+---------+------------+------+
| c1      | c2         | c3   |
+---------+------------+------+
| Bruno   | Ballantine | Dog  |
| Scratch | Smith      | Cat  |
| Wag     | Wilson     | Dog  |
+---------+------------+------+

So the asterisk wildcard (*) is a quick way to return all columns from a table, but only if they’re not invisible. Fortunately we can still get the contents of invisible columns by explicitly referencing those columns.

Checking the Definition of Invisible Columns

We can use commands such as DESCRIBE to check which columns (if any) are invisible on a table:

DESCRIBE t1;

Result:

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

We can see that the Extra column contains INVISIBLE for our c3 column. Our other two columns (c1 and c2) don’t show anything in that column, because they are visible by default.

Hiding a Visible Column

We can use the ALTER TABLE statement to modify existing (visible) columns to be invisible:

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

After running that, we can check our table again:

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 |
+-------+-------------+------+-----+---------+-----------+

We can see that the second column is now invisible.

Making an Invisible Column Visible

Just as we used the ALTER TABLE statement to make our column invisible, we can make it visible again with the VISIBLE keyword:

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

After running that, we can check our table again:

DESCRIBE t1;

Result:

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

We can see that the second column is now invisible.