MySQL 8.0: Invisible Columns

The Oracle company ported INVISIBLE COLUMN feature to it's little brother MySQL. Since version 8.0.23 the invisible column feature available for MySQL users.

Let's learn how it works!

You can create invisible column as regular when you create a table using INVISIBLE keyword:

CREATE TABLE test_table (
  a INT,
  b DATE INVISIBLE
) ENGINE = InnoDB;

or add new column to an exists table by ALTER TABLE command:

ALTER TABLE test_table ADD COLUMN c INT INVISIBLE;

When a column exists you can change its visibility using CHANGE, MODIFY or ALTER COLUMN command as you can see below:

ALTER TABLE test_table CHANGE COLUMN b b DATE VISIBLE;
ALTER TABLE test_table MODIFY COLUMN b DATE INVISIBLE;
ALTER TABLE test_table ALTER COLUMN c SET VISIBLE;

When you fetch table structure you will see all columns, but part of them will be marked by INVISIBLE flag:

SHOW COLUMNS FROM test_table;
SHOW CREATE TABLE test_table;

So you see, the invisible column manipulation pretty simple. Now look how the visibility affects DML.

First, when you try to select all columns using SELECT *, the invisible columns are not appears. So if you need to see they, you must to know the column name and select it by its name. Look below SQL code examples:

TABLE test_table; SELECT * FROM test_table; -- column hidden

SELECT a, b, c FROM test_table; -- column visible

Same way, when you need to insert data to invisible column you must call it by name:

INSERT INTO test_table VALUES (1, now(), 33); -- Error
INSERT INTO test_table () VALUES (1, now(), 33); -- Error too

INSERT INTO test_table VALUES (1, 22); -- NULL inserted
INSERT INTO test_table (a, b, c) VALUES (1, now(), 33); -- all values

Where you can use this feature? First - security - you can use invisible column for hide some column from DB users with low permissions. Second - when you use generated column you can prevent insert data to such column doing it invisible.
Look next example:

CREATE TABLE test_table (
  a INT,
  b INT
) ENGINE = InnoDB;

INSERT INTO test_table VALUES (1, 2), (2, 3), (3, 4);

ALTER TABLE test_table ADD COLUMN a_b_sum INT AS (a + b);

SELECT * FROM test_table;

In above example we added generated column to our test_table but this change can break our insert query. What we can do in this case? Right - use invisible column.

ALTER TABLE test_table ALTER COLUMN a_b_sum SET INVISIBLE;

INSERT INTO test_table VALUES (5, 7), (2, 9), (2, 3);

SELECT a, b, a_b_sum FROM test_table;

Here you can run SQL queries online and test this feature.

So, this is all about this feature. If you think about more use-cases please share it in comments.