Difference Between Visible and Invisible Columns in MySQL

This article explores the differences between visible and invisible columns in MySQL, a feature introduced in MySQL 8.0.13. You will learn how these column types behave during data retrieval, how they impact insert operations, and the practical use cases for using invisible columns to make schema changes without breaking existing application code.

What is a Visible Column?

By default, all columns created in a MySQL table are visible. When a column is visible, it participates normally in all standard database operations.

• Select Queries: A visible column is always returned when you run a wildcard query, such as SELECT * FROM table_name.
• Insert Queries: When performing an INSERT statement without specifying a target column list (e.g., INSERT INTO table VALUES (...)), MySQL expects a value for every visible column in the table.

What is an Invisible Column?

An invisible column is a column that exists in the table schema but is hidden from default queries. You must explicitly reference an invisible column by its name to interact with it.

• Select Queries: An invisible column is excluded from the results of a SELECT * query. To retrieve its data, you must explicitly name the column in your query (e.g., SELECT visible_col, invisible_col FROM table_name).
• Insert Queries: MySQL does not expect a value for an invisible column in an INSERT INTO table VALUES (...) statement. If you want to insert a value into an invisible column, you must explicitly specify the column list in your INSERT statement.

Key Differences

1. Data Retrieval (SELECT)

• Visible: Automatically included in SELECT * results.
• Invisible: Excluded from SELECT *. Must be explicitly named (e.g., SELECT id, secret_column FROM users).

2. Data Insertion (INSERT)

• Visible: Must be provided a value in implicit inserts where no column list is defined.
• Invisible: Ignored in implicit inserts; it will take its default value (or NULL) unless the column list is explicitly defined in the query.

3. Metadata Visibility

Invisible columns are still part of the table structure. They are visible when you run DESCRIBE table_name or SHOW COLUMNS, where their “Extra” field will display INVISIBLE. They are also visible in the INFORMATION_SCHEMA.COLUMNS table.

How to Create and Use Invisible Columns

You define an invisible column by adding the INVISIBLE keyword to the column definition during table creation or modification.

Creating a Table with an Invisible Column

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    salary INT INVISIBLE
);

Querying the Table

If you run a wildcard query on the table above, the invisible salary column is excluded:

-- Returns only 'id' and 'name'
SELECT * FROM employees;

To retrieve the salary, you must explicitly request it:

-- Returns 'id', 'name', and 'salary'
SELECT id, name, salary FROM employees;

Altering an Existing Column

You can change a column’s visibility at any time using the ALTER TABLE statement:

-- Make an invisible column visible
ALTER TABLE employees MODIFY salary INT VISIBLE;

-- Make a visible column invisible
ALTER TABLE employees MODIFY salary INT INVISIBLE;

Why Use Invisible Columns?

The primary use case for invisible columns is application migration and schema evolution.

If you have a legacy application that uses SELECT * or implicit INSERT statements, adding a new column to a database table can break the application. By adding the new column as INVISIBLE, you can update the database schema without disrupting the existing application. Once the application code is updated to explicitly reference the new column, you can transition the column to VISIBLE if desired.