After my last blog post, where I wrote about Generated and Virtual Columns, I would like to dedicate this post to another type of database column: Invisible Columns. This feature exists in MySQL since version 8.0 and in Oracle Database since version 12c. PostgreSQL and MS SQL Server do not support this feature.
Invisible columns, as the name suggests, are columns within a table that are hidden from standard query results by default. Unlike traditional columns that are visible and accessible in query results, invisible columns are not included unless explicitly specified in the query.
This feature provides a level of control over data visibility, allowing developers to hide certain columns from applications or other database users while still retaining their functionality within the database.
Defining invisible columns
When creating a table in MySQL or Oracle, you can designate certain columns as invisible by using the INVISIBLE keyword in the column definition. For example:
CREATE TABLE your_table (
visible_column INT,
invisible_column INT INVISIBLE
);
In this example, the invisible_column is marked as invisible, while the visible_column remains visible by default. To alter an existing table and make a column invisible:
ALTER TABLE your_table
MODIFY COLUMN existing_column_name INVISIBLE;
Replace your_table with the name of your table and existing_column_name with the name of the column you want to make invisible.
When querying the your_table, the invisible column will not be included in the result set unless explicitly specified:
SELECT * FROM your_table;
visible_column
--------------
4
8
15
By default, invisible columns are hidden from query results, providing a cleaner and more concise view of the data. However, developers can still access invisible columns when needed by explicitly including them in the query:
SELECT visible_column, invisible_column FROM your_table;
visible_column | invisible_column
---------------------------------
4 | 16
8 | 23
15 | 42
Unveiling invisible columns
To list the invisible columns of a table in MySQL, you can query the information_schema.columns system table and filter the results based on the COLUMN_DEFAULT column. Invisible columns have NULL as their default value. Here’s a simple SQL query to accomplish this:
SELECT COLUMN_NAME
FROM information_schema.columns
WHERE TABLE_SCHEMA = 'your_database'
AND TABLE_NAME = 'your_table'
AND COLUMN_DEFAULT IS NULL;
In Oracle, you can query the USER_TAB_COLUMNS or ALL_TAB_COLUMNS data dictionary views to list the invisible columns of a table. Here’s how you can do it:
SELECT COLUMN_NAME
FROM USER_TAB_COLUMNS
WHERE TABLE_NAME = 'your_table'
AND INVISIBLE = 'YES';
If you want to list invisible columns from all tables in the current schema, you can use the ALL_TAB_COLUMNS view instead:
SELECT TABLE_NAME, COLUMN_NAME
FROM ALL_TAB_COLUMNS
WHERE INVISIBLE = 'YES';
Are invisible columns actually useful?
Invisible columns can make schema evolution easier by providing a flexible mechanism for evolving database schemas over time without disrupting existing applications or queries. You can test new features or data structures without committing to them fully. Invisible columns provide a way to add experimental columns to your tables without exposing them to production environments until they are fully tested and ready for use.
They can create cleaner and more concise views of your data by hiding less relevant columns. This can make it easier for developers, analysts, and users to work with the data without unnecessary clutter. However, I would argue that this is also achievable with normal database views.
The downside of introducing invisible columns is that they add complexity to the database schema, which can make it harder to understand and maintain, especially for developers who are not familiar with the invisible columns feature. They also add potential for confusion: Developers may forget about the presence of invisible columns, leading to unexpected behavior in queries or applications.
You probably shouldn’t use them to hide sensitive data, since invisible columns don’t have any additional access control, and security through obscurity is not a good idea. If you grant SELECT permission on the table to a user, they will be able to query visible and invisible columns alike.
Now that you know about them, you can make your own choice.
Schneide Blog 