MySQL Invisible Columns and Column Comments

A few MySQL 8 features help you keep tables tidy and make changes less risky.

Invisible columns

An invisible column exists, can be read and written by name, but is **left out of SELECT *** and of INSERT without a column list. It was added in MySQL 8.0.23.

query.sql

SQL

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    internal_code VARCHAR(20) INVISIBLE
);

INSERT INTO products (name, internal_code) VALUES ('Notebook', 'NB-01');
SELECT * FROM products;
SELECT id, name, internal_code FROM products;

Output

OUTPUT

+----+----------+
| id | name     |
+----+----------+
|  1 | Notebook |
+----+----------+
1 row in set

+----+----------+---------------+
| id | name     | internal_code |
+----+----------+---------------+
|  1 | Notebook | NB-01         |
+----+----------+---------------+
1 row in set

SELECT * shows id and name. Only a query that names internal_code returns it.

You can toggle it later:

query.sql

SQL

1
2

ALTER TABLE products ALTER COLUMN internal_code SET VISIBLE;
SELECT * FROM products;

Output

OUTPUT

+----+----------+---------------+
| id | name     | internal_code |
+----+----------+---------------+
|  1 | Notebook | NB-01         |
+----+----------+---------------+
1 row in set

Why use them

Safe schema changes. An application written as SELECT * or INSERT INTO t VALUES (...) breaks when you add a normal column. Add the new column as INVISIBLE, deploy the code that uses it by name, and only then make it visible, if ever.
Hide technical columns (a hash, a sync marker) from tools and reports.

Real applications should still avoid SELECT * and always name the columns in INSERT (SELECT).

Column comments

A COMMENT documents a column inside the database. It shows up in SHOW CREATE TABLE, SHOW FULL COLUMNS and INFORMATION_SCHEMA, so it travels with the schema.

query.sql

SQL

CREATE TABLE payments (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT 'Internal row id',
    amount_minor INT NOT NULL COMMENT 'Amount in the smallest currency unit, e.g. paise',
    status VARCHAR(10) NOT NULL DEFAULT 'new' COMMENT 'new | paid | failed | refunded'
) COMMENT = 'One row per payment attempt';

SELECT column_name, column_comment FROM information_schema.columns WHERE table_schema = 'learn' AND table_name = 'payments' ORDER BY ordinal_position;

Output

OUTPUT

+--------------+--------------------------------------------------+
| COLUMN_NAME  | COLUMN_COMMENT                                   |
+--------------+--------------------------------------------------+
| id           | Internal row id                                  |
| amount_minor | Amount in the smallest currency unit, e.g. paise |
| status       | new | paid | failed | refunded                   |
+--------------+--------------------------------------------------+
3 rows in set

Use comments for units, allowed values and anything a new developer would otherwise have to guess.

The invisible primary key (MySQL 8.0.30 and later)

A table without a primary key is a problem for replication and for tools. MySQL can create a hidden primary key for you with a server setting:

query.sql

SQL

1
2
3

SET SESSION sql_generate_invisible_primary_key = ON;
CREATE TABLE no_key (name VARCHAR(20));
SHOW CREATE TABLE no_key\G

Output

OUTPUT

*************************** 1. row ***************************
       Table: no_key
Create Table: CREATE TABLE `no_key` (
  `my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`my_row_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set

The column my_row_id was added as an invisible AUTO_INCREMENT primary key. It is a safety net for imported or legacy tables, not a design method. Design your own primary keys.

info

Check your version

Invisible columns need MySQL 8.0.23 or newer, and the invisible primary key needs 8.0.30 or newer. MariaDB uses different syntax.