Skip to content

CONCEPT Cited by 1 source

MySQL invisible column

A MySQL invisible column is a column that physically exists in a table and participates in explicit-column queries (SELECT col_name, INSERT INTO t (col_name) VALUES ...), but is excluded from SELECT * and other unqualified column-list expansions. Introduced in MySQL 8.0, invisible columns are a deliberate deprecation-discovery and schema-migration safety primitive.

ALTER TABLE repo MODIFY COLUMN star_count INT INVISIBLE;
-- Column still exists; SELECT * skips it.
-- SELECT star_count FROM repo WHERE id = 1 still works.

(Source: sources/2026-04-21-planetscale-backward-compatible-database-changes)

Why it exists

Invisible columns solve the "who is still reading this column?" discovery problem that blocks safe column drops.

The classical pattern for removing a column is:

  1. Audit every read-site that references the column.
  2. Remove all reads.
  3. Drop the column.

Step 1 is hard: SELECT * queries reference every column without naming them, so grep-for-column-name misses them. Deploy-the-drop-and-see-what-breaks is the de-facto alternative, which is a pure fire-and-react approach.

Making the column invisible inverts the discovery flow:

  • Code that referenced the column by name continues to work (possibly undesired — the caller hadn't noticed the column was on its way out).
  • Code that referenced the column via SELECT * silently loses access — but "silently" in the discovery sense, not the correctness sense. The missing data shows up in test suites, in APM traces, in downstream consumer assertions. Over a rollout window, readers surface themselves.

The column can be made visible again (ALTER TABLE ... MODIFY COLUMN ... VISIBLE) without data loss if the deprecation is rolled back. This makes the deprecation reversible, which the final DROP is not.

Canonical use in expand-migrate-contract

Invisible columns are the MySQL-specific safety primitive for Step 6 of the expand-migrate-contract pattern. The pattern has migrated reads to the new schema (Step 4) and stopped dual-writes (Step 5); Step 6 is "delete the old column." Making the old column invisible immediately before the DROP gives one final discovery pass: "Is anyone still reading via SELECT *?"

From the PlanetScale post:

"If it is a column you are changing, make the column invisible in MySQL from select * queries."

The invisibility acts as a last-chance deprecation signal: callers that had been getting lucky (the column was still populated by dual-writes, so their SELECT * worked) now see the column go missing from their result sets without the column being physically gone. Bug reports surface; fixes land; then the actual DROP is safe.

Mechanics

  • ALTER TABLE ... MODIFY COLUMN ... INVISIBLE flips the visibility flag. No table rewrite.
  • The column remains in the information_schema.COLUMNS view; the distinction is queryable via COLUMN_NAME + the EXTRA column (which reads INVISIBLE).
  • SELECT *, SHOW COLUMNS (without WHERE Extra = 'INVISIBLE' filtering), and JDBC/ODBC result-set metadata skip the column.
  • INSERT INTO t VALUES (...) without a column list still expects the full row including invisible columns — an invisibility surprise for batch-loader scripts that elide the column list.

Limitations and sibling primitives

  • MySQL 8.0+ only. Not available on MySQL 5.7.
  • Not a Postgres feature. PostgreSQL doesn't have column-level visibility; the rough equivalent is a view-over-the-table that omits the column (then rename the view to match the table). Less native.
  • Table-level analogue: rename the table to something like star_DEPRECATED_2026_04. Any code still referencing the old name errors loudly; after the waiting period, drop the renamed table.
  • Other-engine analogues: some databases implement "computed column" or "hidden column" facilities for similar deprecation-discovery purposes.

Anti-patterns

  • Using invisibility as a soft-delete mechanism. Data in invisible columns is not privacy-sanitised or GDPR- compliant; it's just hidden from default views. Don't use invisibility to "delete" user data.
  • Invisible columns as a permission boundary. The column is still readable by any session that knows to name it. INVISIBLE is a schema-migration tool, not an access-control primitive.
  • Forgetting to drop the invisible column. The column still occupies physical storage. Leaving it invisible indefinitely is a storage-cost bug.

Seen in

Last updated · 319 distilled / 1,201 read