Invisible columns allow us to hide columns from certain SQL queries, such as those that use the asterisk wildcard (*) to implicitly select all columns. But along with this comes some implications for when we want to insert data into those columns.
If we want to insert data into an invisible column, we need to explicitly name that column in the column list. We can’t use a blank column list to insert data into all columns like we can with visible columns. If we try to do that, we’ll get an error. That said, we can omit the invisible column from the column list in order to have its default value inserted.
Example
Suppose we create a table with an invisible column, like this:
CREATE TABLE t1
(
c1 varchar(20),
c2 varchar(20),
c3 varchar(20) DEFAULT 'Salad' INVISIBLE
);This invisible column has a default value of Salad.
Now let’s insert some data:
INSERT INTO t1( c1, c2, c3 )
VALUES( 'Bruno', 'Ballantine', 'Pasta' );
INSERT INTO t1( c1, c2, c3 )
VALUES( 'Sally', 'Smith', 'Fajita' );Done. Inserting data into our invisible column was no different to inserting data into our regular, visible, columns.
We can select the invisible column in our queries by explicitly naming it in our SELECT list:
SELECT c1, c2, c3 FROM t1;Result:
+-------+------------+--------+ | c1 | c2 | c3 | +-------+------------+--------+ | Bruno | Ballantine | Pasta | | Sally | Smith | Fajita | +-------+------------+--------+
So we can see that all data was inserted as intended. That’s because I explicitly named the invisible column in the column list.
Using the Invisible Column’s Default Value
It’s not necessarily a requirement to explicitly name the invisible column. We can omit the column from our INSERT statement altogether in order to have its default value inserted. But when we do this, we can’t provide a value for it either. This is where invisible columns differ from the regular, visible columns when it comes to inserting data.
Here’s an example of using the invisible column’s default value:
INSERT INTO t1
VALUES( 'Willy', 'Wilson' );Now let’s check the table’s contents again:
SELECT c1, c2, c3 FROM t1;Result:
+-------+------------+--------+ | c1 | c2 | c3 | +-------+------------+--------+ | Bruno | Ballantine | Pasta | | Sally | Smith | Fajita | | Willy | Wilson | Salad | +-------+------------+--------+
We omitted the invisible column entirely from the INSERT statement, and this time its default value (Salad) was inserted.
An Error to Avoid
It’s important to remember that when we omit the column list from our INSERT statement, we also have to omit any value for that column. We can’t explicitly provide a value for the invisible column if we omit the column list. We can only do this for the visible columns.
Here’s what happens when we try to specify a value for the invisible column:
INSERT INTO t1
VALUES( 'Pete', 'Peters', 'Pasta' );Result:
ERROR 1136 (21S01): Column count doesn't match value count at row 1
We got an error because we provided a value for the invisible column while omitting the column list.
As mentioned, when it comes to invisible columns we need to explicitly reference them in order to insert data.
Copying Tables with Invisible Columns
When we copy a table using the CREATE TABLE ... LIKE statement, invisible columns are included (and they’re invisible on the new table).
But if we use CREATE TABLE ... SELECT, invisible columns are only included if they are explicitly referenced in the SELECT list. But even when we do this, they are not invisible on the new table (they’re defined as visible on the new table).
To demonstrate, let’s copy our table using CREATE TABLE ... LIKE:
CREATE TABLE t2 LIKE t1;Now let’s check the t2 table’s metadata:
DESCRIBE t2;Result:
+-------+-------------+------+-----+---------+-----------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-----------+ | c1 | varchar(20) | YES | | NULL | | | c2 | varchar(20) | YES | | NULL | | | c3 | varchar(20) | YES | | Salad | INVISIBLE | +-------+-------------+------+-----+---------+-----------+
We can see that the c3 column is invisible as expected.
Now let’s copy the same table, but this time using CREATE TABLE ... SELECT *:
CREATE TABLE t3 SELECT * FROM t1;Now let’s check t3‘s metadata:
DESCRIBE t3;Result:
+-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | c1 | varchar(20) | YES | | NULL | | | c2 | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+
This time the invisible column wasn’t copied. That’s because we used the asterisk wildcard (*) in our query.
Let’s explicitly include the column in the query:
CREATE TABLE t4 SELECT *, c3 FROM t1;Now check the metadata for this new table:
DESCRIBE t4;Result:
+-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | c1 | varchar(20) | YES | | NULL | | | c2 | varchar(20) | YES | | NULL | | | c3 | varchar(20) | YES | | Salad | | +-------+-------------+------+-----+---------+-------+
This time the invisible column was copied, but it’s not invisible on the new table.
However, there is way of preserving the invisibility status while using the CREATE TABLE ... SELECT method. The trick is to redefine the column in the statement:
CREATE TABLE t5
(c3 varchar(20) DEFAULT 'Salad' INVISIBLE) AS
SELECT c1, c2, c3 FROM t1;And check the metadata:
DESCRIBE t5;Result:
+-------+-------------+------+-----+---------+-----------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-----------+ | c1 | varchar(20) | YES | | NULL | | | c2 | varchar(20) | YES | | NULL | | | c3 | varchar(20) | YES | | Salad | INVISIBLE | +-------+-------------+------+-----+---------+-----------+
This time it’s invisible.
So if you want any invisible columns to remain invisible on the new table, either use CREATE TABLE ... LIKE, or explicitly redefine the invisible column if using CREATE TABLE ... SELECT.