mysql> CREATE TABLE t2 AS SELECT col1, col2 FROM t1; mysql> SHOW CREATE TABLE t2\G *************************** 1. row *************************** Table: t2 Create Table: CREATE TABLE ‘t2‘ ( ‘col1‘ int DEFAULT NULL, ‘col2‘ int DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 

If invisibility should be preserved, provide a definition for the invisible column in the CREATE TABLE  part of the CREATE TABLE ... SELECT  statement: 

mysql> CREATE TABLE t1 (col1 INT, col2 INT INVISIBLE); mysql> CREATE TABLE t2 (col2 INT INVISIBLE) AS SELECT col1, col2 FROM t1; mysql> SHOW CREATE TABLE t2\G *************************** 1. row *************************** Table: t2 Create Table: CREATE TABLE ‘t2‘ ( ‘col1‘ int DEFAULT NULL, ‘col2‘ int DEFAULT NULL /*!80023 INVISIBLE */ ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 

Views can refer to invisible columns by explicitly referencing them in the SELECT  statement that defines the view. Changing a column’s visibility subsequent to defining a view that references the column does not change view behavior. 

## DML Statements and Invisible Columns 

For SELECT  statements, an invisible column is not part of the result set unless explicitly referenced in the select list. In a select list, the *  and tbl_name .*  shorthands do not include invisible columns. Natural joins do not include invisible columns. 

Consider the following statement sequence: 

mysql> CREATE TABLE t1 (col1 INT, col2 INT INVISIBLE); mysql> INSERT INTO t1 (col1, col2) VALUES(1, 2), (3, 4); mysql> SELECT * FROM t1; +------+ | col1 | +------+ | 1 | | 3 | +------+ mysql> SELECT col1, col2 FROM t1; +------+------+ | col1 | col2 | +------+------+ | 1 | 2 | | 3 | 4 | +------+------+ 

The first SELECT  does not reference the invisible column col2  in the select list (because *  does not include invisible columns), so col2  does not appear in the statement result. The second SELECT  does reference col2 , so it does appear in the result. 

For statements that create new rows, an invisible column is assigned its implicit default value unless explicitly referenced and assigned a value. For information about implicit defaults, see Implicit Default Handling. 

For INSERT  (and REPLACE , for non-replaced rows), implicit default assignment occurs with a missing column list, an empty column list, or a nonempty column list that does not include the invisible column: 

