MySQL 8.0 invisible columns

Last Updated on 4월 21, 2021 by 태랑(정현호)



1. invisible columns 개요

MySQL 은 MySQL 8.0.23부터 invisible columns을 지원합니다 invisible columns은 일반적으로 쿼리에 숨겨져 있지만 명시 적으로 참조되는 경우 액세스 할 수 있습니다.

invisible columns 이 유용 할 수 있는 경우에 대한 설명으로 응용 프로그램이 "SELECT * FROM 테이블" 유형의 쿼리를 사용하여 테이블에 액세스하고 있을 경우 invisible columns 을 사용하여 컬럼을 추가 하여 테이블을 변경 하더라도 응용 프로그램에서는 오류나 별도의 변경 없이 계속 작업을 진행 할 수 있게 됩니다. 수정 사항이 반영되어 새로 배포되는 응용 프로그램에서는 필요한 경우 명시 적으로 컬럼명을 지정하여 invisible column을 일반 컬럼 처럼 사용할 수 있게 됩니다.

• invisible column 은 보통 열 속성에 대해서 지원합니다 : NULL, NOT NULL, AUTO_INCREMENT, 등..

•인덱스 생성은 PRIMARY KEY 및 UNIQUE 인덱스에 대해서 invisible column 포함하여 생성 할 수 있습니다



2. 테이블 생성 및 확인

2-1 CREATE TABLE

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.23    |
+-----------+

mysql> create database test;

mysql> use test;
Database changed

mysql> create table table1 (
 id int auto_increment primary key, 
 name varchar(20), 
 age int invisible);

mysql> describe table1;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int         | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | YES  |     | NULL    |                |
| age   | int         | YES  |     | NULL    | INVISIBLE      |
+-------+-------------+------+-----+---------+----------------+

mysql> show create table table1\G
*************************** 1. row ***************************
       Table: table1
Create Table: CREATE TABLE `table1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `age` int DEFAULT NULL /*!80023 INVISIBLE */,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

show create 로 보게되면 8.0.23 버전에서 INVISIBLE 를 사용을 의미하는 힌트가 기재되어 있습니다 8.0.23 버전에서 위의 구문으로 테이블을 생성하게 되면 age 컬럼은 INVISIBLE 로 생성되게 됩니다.


2-2 CREATE TABLE … LIKE

CREATE TABLE … LIKE 로 테이블을 생성시 invisible 컬럼 속성이 포함되어 새 테이블이 생성되게 됩니다.

sql> describe table1
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int         | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | YES  |     | NULL    |                |
| age   | int         | YES  |     | NULL    | INVISIBLE      |
+-------+-------------+------+-----+---------+----------------+


sql> CREATE TABLE table2 like table1;

sql> describe table2
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int         | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | YES  |     | NULL    |                |
| age   | int         | YES  |     | NULL    | INVISIBLE      |
+-------+-------------+------+-----+---------+----------------+



2-3 CREATE TABLE … SELECT(CTAS)

CREATE TABLE … SELECT(CTAS) 로 생성시 "select * from 테이블" 로 생성하게 되면 invisible 컬럼을 포함하지 않게 됩니다

sql> describe table1
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int         | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | YES  |     | NULL    |                |
| age   | int         | YES  |     | NULL    | INVISIBLE      |
+-------+-------------+------+-----+---------+----------------+


sql> CREATE TABLE table3
  AS
  select * from table1;


sql>  describe table3;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   |     | 0       |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+



CTAS 의 경우에도 명시적으로 컬럼명을 지정하였다면 invisible columns 을 포함하여 생성하게 됩니다
 다만 CREATE TABLE … LIKE 와 다르게 컬럼에 invisible 속성을 제외하고 테이블 구조와 데이터가 생성 됩니다.

sql> CREATE TABLE table4
  AS
  select id,name,age from table1;

sql> describe table4;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   |     | 0       |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| age   | int         | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+


만약 CTAS 로 생성되는 테이블에도 invisible 속성이 유지되어야 한다면 테이블 생성 구문에 INVISIBLE 를 구문이 추가 되어야 합니다

sql> CREATE TABLE table5 (
  id int auto_increment primary key,
  name varchar(20 ),age int INVISIBLE)
  AS
  select id,name,age from table1;

sql> describe table5;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int         | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | YES  |     | NULL    |                |
| age   | int         | YES  |     | NULL    | INVISIBLE      |
+-------+-------------+------+-----+---------+----------------+



3. DML 과 Invisible Columns

table1 테이블에는 id,name,age 3개의 컬럼이 있고 age 컬럼은 INVISIBLE 로 생성된 상태 입니다 이 상태에서 아래와 같이 컬럼을 별도로 지정하지 않고 3개의 값을 insert 를 하게 되면 에러 ERROR 1136 발생되게 됩니다.

mysql> insert into table1 
values (0,'mysql', 25), (0,'kenny', 35), 
(0, 'lefred','44');
ERROR 1136 (21S01): Column count doesn't match value count at row 1


하지만 아래와 같이 컬럼명을 명시적으로 지정하게 되면 정상적으로 insert 가 정상 수행되게 됩니다.

mysql> insert into table1(id,name,age) 
values (0,'mysql', 25), (0,'kenny', 35), 
(0, 'lefred','44');

Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0


아래와 같이 * 로 조회를 할 경우 데이터가 입력 되긴 하였으나 invisible columns 은 display 되지 않습니다.

mysql> select * from table1;
+----+--------+
| id | name   |
+----+--------+
|  1 | mysql  |
|  2 | kenny  |
|  3 | lefred |
+----+--------+



insert 하였을 때와 같이 명시적으로 컬럼명을 지정하여 조회하면 조회가 가능 하게 됩니다.

mysql> select id, name, age from table1;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | mysql  |   25 |
|  2 | kenny  |   35 |
|  3 | lefred |   44 |
+----+--------+------+


UPDATE 명령문에서 invisible 컬럼을 갱신하려면 컬럼 이름을 지정하고 수행 해야 합니다.



4. Invisible Column Metadata

INFORMATION_SCHEMA 에서도 INVISIBLE 정보는 확인 할 수 있습니다.

mysql> SELECT TABLE_NAME, COLUMN_NAME, EXTRA
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'table1';

+------------+-------------+----------------+
| TABLE_NAME | COLUMN_NAME | EXTRA          |
+------------+-------------+----------------+
| table1     | age         | INVISIBLE      |
| table1     | id          | auto_increment |
| table1     | name        |                |
+------------+-------------+----------------+


위에서 확인 한것 처럼 describe 와 show create table 로도 테이블내에 INVISIBLE 정보를 확인 할 수 있습니다.

mysql> show create table table1\G
*************************** 1. row ***************************
       Table: table1
Create Table: CREATE TABLE `table1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `age` int DEFAULT NULL /*!80023 INVISIBLE */,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci




5. 테이블 속성 변경(DDL)

alter table 을 통해 invisible 에서 visible 로 또는 반대로 변경이 가능 합니다

mysql> alter table table1 modify age integer visible;

mysql> select * from table1;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | mysql  |   25 |
|  2 | kenny  |   35 |
|  3 | lefred |   44 |
+----+--------+------+



mysql> alter table table1 modify `name` varchar(20) invisible;

mysql> select * from table1;
+----+------+
| id | age  |
+----+------+
|  1 |   25 |
|  2 |   35 |
|  3 |   44 |
+----+------+



Ref link.
mysql.com/invisible-columns.html[Link]
lefred.be/mysql-invisible-column-part-i[Link]



연관된 다른 글

 

 

 

 

답글 남기기