From MySQL 8.0.23 we’ve had the ability to create invisible columns. But with that comes a potential “gotcha” with our existing queries, and even with new queries we write, depending on how we write them.

The issue is that invisible columns are not returned whenever we use the asterisk wildcard (*) to select data. One of the most common ways to start a query is with SELECT *. This typically selects all columns from the table.

But it doesn’t select invisible columns.

So if we want to include invisible columns, we need to explicitly include them in our SELECT list.

Sample Table

Suppose we create a table with an invisible column:

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

And we insert data:

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’ve explicitly inserted data into our invisible column (the c3 column).

Select the Data

Now let’s select all data from the table:

SELECT c1, c2, c3 FROM t1;

Result:

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

We successfully selected the data from the invisible column because we explicitly included it in our SELECT list.

Omitting the Invisible Column

As mentioned, if we use the asterisk wildcard (*) the query will only return visible columns. Any invisible columns will not be returned.

To demonstrate, let’s do that now:

SELECT * FROM t1;

Result:

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

So we only got the visible columns.

Combining * and Invisible Column

However, there’s nothing to stop us from using both the asterisk wildcard and also explicitly including the invisible column:

SELECT *, c3 FROM t1;

Result:

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

So in this case we returned all visible columns, plus one specific invisible column.

What About the TABLE Statement?

You may be familiar with the TABLE statement, which can be used as a more concise way of returning all data from a table than with the SELECT statement. Queries that use this statement will only return visible columns. This is because the TABLE statement has no way of explicitly naming which columns to return – it simply returns all of them (well, all of the visible ones).

To demonstrate what I mean, here’s an example:

TABLE t1;

Result:

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

So only the visible columns are returned.

To return any invisible columns, you may need to switch to one of the SELECT statement solutions above.