If you’ve ever tried to copy a table with invisible columns in MySQL, you may have been baffled when you realised that none of the invisible columns were copied. Or, you may have copied them, but they ended up VISIBLE instead of INVISIBLE on the new table.

Fortunately, there’s an easy fix to this issue. Actually, there are at least a couple of ways we can approach it.

CREATE TABLE ... LIKE vs CREATE TABLE ... SELECT

Two common ways to copy tables in MySQL are with the CREATE TABLE ... LIKE statement and with the CREATE TABLE ... SELECT statement. Although very similar, these statements can differ in what they copy to the new table. When it comes to invisible columns, it pays to remember the following:

• When we use CREATE TABLE ... LIKE, invisible columns are included (and they’re invisible on the new table).
• When 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).

Below are examples that show what happens when we use these statements to copy tables that contain invisible columns.

Original Table

First, let’s create an initial table that we can copy:

CREATE TABLE t1
(
    c1     varchar(20),
    c2     varchar(20),
    c3     varchar(20) DEFAULT 'Salad' INVISIBLE
);

Here, we defined the c3 column as INVISIBLE (it’s an invisible column).

Using CREATE TABLE ... LIKE

Now 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.

Using CREATE TABLE ... SELECT *

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.

Using CREATE TABLE ... SELECT column_names

Let’s explicitly include the column name 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.

Preserve Invisibility when Using CREATE TABLE ... SELECT

However, there is way of preserving the column’s invisibility 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.