MySQL Invisible Column – part I

With the new MySQL 8.0.23, something very interesting has been released: Invisible Column.

This is the first post dedicated to this new feature, I expect to write a series of 3. This one is the introduction.

Prior to MySQL 8.0.23, all columns of a table were always visible (if you had the privilege to see it). Now, an invisible column can be specified and will be hidden to queries. It can always be accessed if explicitly referenced.

Let’s see how it works:

create table table1 (
   id int auto_increment primary key, 
   name varchar(20), 
   age int invisible);

In the table description we can see the INVISIBLE keyword in the Extra column:

desc table1;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int         | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | YES  |     | NULL    |                |
| age   | int         | YES  |     | NULL    | INVISIBLE      |
+-------+-------------+------+-----+---------+----------------+

With the show create table statement, we can notice a difference, I was expecting to see the INVISIBLE keyword as when I created the table, but this is not the case:

show create table table1\G
************************* 1. row *************************
        Table: table1
 Create Table: CREATE TABLE `table1` (
   id int NOT NULL AUTO_INCREMENT,
   name varchar(20) DEFAULT NULL,
   age int DEFAULT NULL /*!80023 INVISIBLE */,
   PRIMARY KEY (id)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

But I confirm that this statement will create the table as set the age column as invisible. So we have two different valid syntax to create a INVISIBLE column.

INFORMATION_SCHEMA also includes that info:

SELECT TABLE_NAME, COLUMN_NAME, EXTRA
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'table1';
+------------+-------------+----------------+
| TABLE_NAME | COLUMN_NAME | EXTRA          |
+------------+-------------+----------------+
| table1     | id          | auto_increment |
| table1     | name        |                |
| table1     | age         | INVISIBLE      |
+------------+-------------+----------------+

It’s time to add some data and see how it behaves:

insert into table1 values (0,'mysql', 25), 
                          (0,'kenny', 35), 
                          (0, 'lefred','44');
 ERROR: 1136: Column count doesn't match value count at row 1

We can see that as expected, if we don’t reference it we have an error even with the INSERT statement. Let’s reference the columns then:

insert into table1 (id, name, age) 
       values (0,'mysql', 25), 
              (0,'kenny', 35), 
              (0, 'lefred','44');
 Query OK, 3 rows affected (0.1573 sec

Time to query the data in that table:

select * from table1;
+----+--------+
| id | name   |
+----+--------+
|  1 | mysql  |
|  2 | kenny  |
|  3 | lefred |
+----+--------+

Once again, as expected, we can see that the invisible column is not displayed.

If we specify it then we have it:

select name, age from table1;
+--------+-----+
| name   | age |
+--------+-----+
| mysql  |  25 |
| kenny  |  35 |
| lefred |  44 |
+--------+-----+

Of course an column can be changed from VISIBLE to INVISIBLE and vice versa:

alter table table1 modify name varchar(20) invisible, 
                   modify age integer visible;
Query OK, 0 rows affected (0.1934 sec)

select * from table1;
+----+-----+
| id | age |
+----+-----+
|  1 |  25 |
|  2 |  35 |
|  3 |  44 |
+----+-----+

I’m very happy of this new feature and in the next post we will see why this is an important feature for InnoDB.