MySQL 8.0.23 Invisible Columns Feature Overview

MySQL introduced a new feature in version 8.0.23 that allows columns to be defined with the INVISIBLE attribute. Invisible columns are omitted from generic SELECT * queries and only appear when explicitly referenced in SQL statements.

Official documentation: https://dev.mysql.com/doc/refman/8.0/en/invisible-columns.html

Feature Demonstration

1. Create a table with an invisible column:

mysql> CREATE TABLE t1 (id INT, name varchar(10) ,age INT INVISIBLE);
Query OK, 0 rows affected (0.02 sec)

2. The table definition shows the invisible column:

| t1    | CREATE TABLE `t1` (
  `id` int DEFAULT NULL,
  `name` varchar(10) COLLATE utf8mb4_bin DEFAULT NULL,
  `age` int DEFAULT NULL /*!80023 INVISIBLE */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+-------+-------------+------+-----+---------+-----------+
| Field | Type        | Null | Key | Default | Extra     |
+-------+-------------+------+-----+---------+-----------+
| id    | int         | YES  |     | NULL    |           |
| name  | varchar(10) | YES  |     | NULL    |           |
| age   | int         | YES  |     | NULL    | INVISIBLE |
+-------+-------------+------+-----+---------+-----------+

3. The INVISIBLE attribute can also be seen in INFORMATION_SCHEMA.COLUMNS :

mysql> select TABLE_NAME, COLUMN_NAME, EXTRA from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='t1';
+------------+-------------+-----------+
| TABLE_NAME | COLUMN_NAME | EXTRA     |
+------------+-------------+-----------+
| t1         | id          |           |
| t1         | name        |           |
| t1         | age         | INVISIBLE |
+------------+-------------+-----------+

Precautions

1. Each table must contain at least one visible column.

2. Invisible columns can be part of primary keys or secondary indexes and may have AUTO_INCREMENT .

3. DML statements must explicitly reference invisible columns; otherwise they are ignored. For example:

mysql> insert into t1  values(2,'mqd',23);
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql> insert into t1  values(2,'mqd');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t1;
+------+------+ 
| id   | name |
+------+------+ 
|    1 | wfx  |
|    2 | mqd  |
+------+------+

mysql> select id,name,age from t1;
+------+------+------+
| id   | name | age  |
+------+------+------+
|    1 | wfx  |   25 |
|    2 | mqd  | NULL |
+------+------+------+

4. LOAD DATA and SELECT ... OUTFILE ignore invisible columns unless they are explicitly listed.

mysql> load data infile "/tmp/t1.sql" into table t1;
ERROR 1262 (01000): Row 1 was truncated; it contained more data than there were input columns

mysql> load data infile "/tmp/t1.sql" into table t1 (id,name,age);
Query OK, 2 rows affected (0.02 sec)

5. Existing columns can be altered to become invisible:

mysql> ALTER TABLE t1 MODIFY COLUMN name varchar(10) INVISIBLE;
Query OK, 0 rows affected (0.02 sec)

mysql> desc t1;
+-------+-------------+------+-----+---------+-----------+
| Field | Type        | Null | Key | Default | Extra     |
+-------+-------------+------+-----+---------+-----------+
| id    | int         | YES  |     | NULL    |           |
| name  | varchar(10) | YES  |     | NULL    | INVISIBLE |
| age   | int         | YES  |     | NULL    | INVISIBLE |
+-------+-------------+------+-----+---------+-----------+

6. mysqldump includes invisible columns in the generated CREATE TABLE statements and inserts.

DROP TABLE IF EXISTS `t1`;
...
CREATE TABLE `t1` (
  `id` int DEFAULT NULL,
  `name` varchar(10) COLLATE utf8mb4_bin DEFAULT NULL,
  `age` int DEFAULT NULL /*!80023 INVISIBLE */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
...
INSERT INTO `t1` (`id`, `name`, `age`) VALUES (1,'wfx',25),(2,'mqd',NULL);

7. Binary logs record the invisible attribute; in STATEMENT mode the original DML is logged, while in ROW mode the column values are logged if present.

Conclusion

The invisible column feature extends MySQL's table schema capabilities, allowing developers to add columns, primary keys, or indexes without affecting existing applications, thereby improving flexibility and fault tolerance in database design.