Before MySQL 8.0.23, all user-defined columns in a table were always visible by default. However, starting from MySQL 8.0.23, a new feature called Invisible Columns was introduced, allowing users to hide specific columns within a table. With this feature, these invisible columns do not appear in the “SELECT *” query, providing a way to hide them from the regular query results.

To view the values of invisible columns, they need to be explicitly referenced in the query. This means that you need to mention the specific column names in your SELECT statement to include the invisible columns in the result set. This feature offers greater flexibility and control over column visibility, allowing users to selectively hide and reveal columns based on their specific requirements.


Some of the use cases of Invisible columns,

  • Invisible columns can be used to hide sensitive information from regular queries and show them only when explicitly referenced.  For example, a table might contain customer’s personal information as email address or phone number. By making such column invisible, users can prevent accidental exposure of sensitive data in applications.
  • Invisible columns can help maintain backward compatibility with existing applications. If a user wants to introduce a new column to a database without impacting existing applications, then the user can make such column invisible and explicitly reference it in the new application. This way, the existing application can continue to function without any modifications.
  • Invisible columns are helpful even when dropping the column. Instead of dropping the column immediately, just mark the column invisible. When applications seems to work properly without the column being dropped, drop the invisible column. This way, user can avoid data loss and other impacts like performance due to column drop.

Generated Invisible Primary Key feature introduced in MySQL 8.0.30 uses an INVISIBLE column to generate a primary key for tables created without a primary key.
 

How to create an Invisible Column ?

To create a table with an invisible column, use new column attribute “INVISIBLE” in the CREATE TABLE statement. Here is an example to illustrate table creation with invisible column:

1
2
3
4
5
6
-- Creating table with Invisible Columns
CREATE TABLE users(id INT NOT NULL,
                  name VARCHAR(50) NOT NULL,
                  internal_id INT INVISIBLE,
                  PRIMARY KEY (id));
Query OK, 0 rows affected (0.05 sec)

 

Please note, a table must have at least one visible column. Invisible columns support all attributes of a column and all constraints.
 

How to insert values to the Invisible Columns?

To insert a value to the invisible column(s), the column name should be explicitly mentioned in the INSERT statement.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
-- Inserting a value for an invisible column without explicitly mentioning
-- it in a column list.
INSERT INTO users VALUES(1, "manu", 10);
ERROR 1136 (21S01): Column count doesn't match value count at row 1
 
-- Values can be inserted for all visible columns.
INSERT INTO users VALUES(1, "manu");
Query OK, 1 row affected (0.01 sec)
INSERT INTO users VALUES(2, "shatarupa");
Query OK, 1 row affected (0.01 sec)
 
-- Verify SELECT * output.
SELECT * FROM users\G
*************************** 1. row ***************************
id: 1
name: manu
*************************** 2. row ***************************
id: 2
name: shatarupa
 
-- Verify invisible column value by explicitly referring it in SELECT
SELECT *, internal_id FROM users\G
*************************** 1. row ***************************
id: 1
name: manu
internal_id: NULL
*************************** 2. row ***************************
id: 2
name: shatarupa
internal_id: NULL
 
-- Explicitly mention invisible column name in column list to insert value
INSERT INTO users(id, name, internal_id) VALUES(3, "manushya",  9);
Query OK, 1 row affected (0.01 sec)
 
-- Verify invisible column value by explicitly referring it in SELECT
SELECT *, internal_id FROM users\G
*************************** 1. row ***************************
id: 1
name: manu
internal_id: NULL
*************************** 2. row ***************************
id: 2
name: shatarupa
internal_id: NULL
*************************** 3. row ***************************
id: 3
name: manushya
internal_id: 9

In the provided SQL code snippet, several insertion scenarios for a table with both visible and invisible columns are demonstrated.

  • Line 1: An attempt to insert a value into an invisible column without specifying it in the column list results in a failure. Since the invisible column ‘internal_id’ is not included in the column list, the INSERT statement fails.

  • Line 6: This line illustrates the insertion of values into all visible columns.  Values for all visible columns (id, name) are provided. For the invisible column, the default value (NULL in this case) is inserted.

  • Line 32: In this example, a value is inserted into an invisible column named ‘internal_id’. The column name is explicitly specified in the column list, and the value is inserted accordingly.

These examples showcase different scenarios of inserting data into tables containing both visible and invisible columns, highlighting the behavior of the invisible columns in relation to insertion operations.


How to ALTER visibility attribute of a column?

Columns are visible by default. To alter the visibility attribute of a column, use the VISIBLE or INVISIBLE clause in the ALTER TABLE column-modification operations.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
-- Make column internal_id VISIBLE using CHANGE COLUMN operation
ALTER TABLE users CHANGE COLUMN internal_id internal_id INT VISIBLE;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
-- Verify table definition
SHOW CREATE TABLE users\G
CREATE TABLE `users` (
`id` int NOT NULL,
`name` varchar(50) NOT NULL,
`internal_id` int DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
 
-- Make column internal_id INVISIBLE using MODIFY COLUMN operation
ALTER TABLE users MODIFY COLUMN internal_id INT INVISIBLE;
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
 
-- Verify table definition
SHOW CREATE TABLE users\G
CREATE TABLE `users` (
`id` int NOT NULL,
`name` varchar(50) NOT NULL,
`internal_id` int DEFAULT NULL /*!80023 INVISIBLE */,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
 
-- Make column internal_id VISIBLE using ALTER COLUMN operation
ALTER TABLE users ALTER COLUMN internal_id SET VISIBLE;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
 
 -- Verify table definition
SHOW CREATE TABLE users\G
CREATE TABLE `users` (
`id` int NOT NULL,
`name` varchar(50) NOT NULL,
`internal_id` int DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.01 sec)
 
-- Make column internal_id INVISIBLE using ALTER COLUMN operation
ALTER TABLE users ALTER COLUMN internal_id SET INVISIBLE;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
 
-- Verify table definition
SHOW CREATE TABLE users\G
CREATE TABLE `users` (
`id` int NOT NULL,
`name` varchar(50) NOT NULL,
`internal_id` int DEFAULT NULL /*!80023 INVISIBLE */,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

The provided SQL code snippet demonstrates various alterations to column visibility within a table.

  • Line 1: The ALTER statement is used with the CHANGE COLUMN clause to change an invisible column into a visible column. 

  • Line 16: Continuing from the previous alteration, the same column is then modified back to an invisible column. This transformation is achieved using the MODIFY COLUMN clause within the ALTER statement.

  • Line 31 and 46: These lines exhibit the alteration of a column’s visibility attribute using the ALTER COLUMN clause within the ALTER statement. This allows for changes in visibility without necessitating changes to the column’s data type or other attributes.

These examples showcase the flexibility of the ALTER statement in modifying column visibility attributes, including changing columns between invisible and visible states and adjusting visibility without affecting other column properties.
 

Where to find Invisible Column Metadata?

Information about invisible column is available in the SHOW CREATE TABLE, SHOW COLUMNS and INFORMATION_SCHEMA.COLUMNS tables.

1
2
3
4
5
6
7
8
9
10
SHOW CREATE TABLE users\G
*************************** 1. row ***************************
       Table: users
Create Table: CREATE TABLE `users` (
  `id` int NOT NULL,
  `name` varchar(50) NOT NULL,
  `internal_id` int DEFAULT NULL /*!80023 INVISIBLE */,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)(


EXTRA column of SHOW COLUMNS and INFORMATION_SCHEMA.COLUMNS result set shows the visibility attribute.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
SHOW COLUMNS FROM users\G
*************************** 1. row ***************************
Field: id
Type: int
Null: NO
Key: PRI
Default: NULL
Extra:
*************************** 2. row ***************************
Field: name
Type: varchar(50)
Null: NO
Key:
Default: NULL
Extra:
*************************** 3. row ***************************
Field: internal_id
Type: int
Null: YES
Key:
Default: NULL
Extra: INVISIBLE
3 rows in set (0.02 sec)
SELECT table_name, column_name, extra FROM INFORMATION_SCHEMA.COLUMNS
       WHERE table_schema = 'test' AND table_name = 'users'\G
*************************** 1. row ***************************
TABLE_NAME: users
COLUMN_NAME: id
EXTRA:
*************************** 2. row ***************************
TABLE_NAME: users
COLUMN_NAME: name
EXTRA:
*************************** 3. row ***************************
TABLE_NAME: users
COLUMN_NAME: internal_id
EXTRA: INVISIBLE


References:


Please check out this new feature and let us know your feedback.
 

Thanks for using MySQL!