MySQL 8.0 invisible columns

Last Updated on 1월 26, 2023 by Jade(정현호)

안녕하세요.
이번 포스팅은 MySQL 8.0 에서 새로 추가된 invisible columns 기능에 대해서 확인 해보려고 합니다.

Contents

1. invisible columns 개요
2. 테이블 생성 및 확인
3. DML 과 Invisible Columns
4. Invisible Column Metadata
5. 테이블 속성 변경(DDL)
6. Reference

1. invisible columns 개요

invisible columns 은 MySQL 8.0.23 버전 부터 추가된 기능으로 일반적으로 쿼리에 숨겨져 있지만 명시 적으로 컬럼을 지정하여 조회하는 경우 액세스 할 수 있는 컬럼 기능 입니다.

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 은 출력 되지 않습니다.

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

insert 하였을 때와 동일한 방법으로 명시적으로 컬럼명을 SELECT 절에 기재하여 실행하면 조회가 가능 합니다.

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 |
+----+------+

6. Reference

Reference Link
• mysql.com/invisible-columns.html
• lefred.be/mysql-invisible-column-part-i

연관된 다른 글

MySQL 8 - Generated Invisible Primary Keys - GIPK

안녕하세요 이번 글은 MySQL 8.0.30 버전에서 추가 된 Generated Invisible Primary Key 에 대해서 확인 해보려고 합니다. Generated Invisible Primary Key(줄여서 GIPK) 는 MySQL 8.0.30 버전에 새로 추가 된 기능 입니다.

MySQL Invisible Columns 을 사용한 table audit 기능 활용

안녕하세요. 이번 포스팅은 MySQL Invisible Columns 을 활용한 table audit 내용 기록과 관련된 내용을 살펴보도록 하겠습니다.

MySQL8 해시조인(Hash Join)

MySQL 에서는 오랜 기간 조인의 Method는 nested lopp join 방식만 지원되어왔습니다

MySQL NOW()와 SYSDATE()의 차이

MySQL 에서는 현재 시각을 조회하는 함수로 NOW() 와 SYSDATE 함수가 있으며 현재의 시간을 반환하는 같은 기능을 수행합니다

MySQL Delete 문 성능 - IN절에서 서브쿼리 사용시 - Multiple Delete / CTAS

MySQL 에서의 서브쿼리 MySQL 은 전통적으로 서브쿼리 최적화에 약한 면모를 보이고 있습니다 5.6 버전에서 개선되어( subquery materialization) 서브쿼리가 내부적으로 조인 형태로 변경되어 성능적 개선이 되었으나 상황에 따라서 서브쿼리는 DEPENDENT SUBQUERY 타입으로 실행되는 경우가 많습니다

MySQL 8.0.14 CHECK TABLE - Parallel Query Execution - CHECK TABLE 병렬 쿼리 수행

MySQL 에서는 예전부터 Native Parallel Query를 실행할 수 있는 기능이 추가되기를 많은 사람들은 희망 하였습니다 하지만 오랜 시간동안 기능이 반영되지는 못하였습니다.

MySQL - Percona Toolkit - ONLINE-SCHEMA-CHANGE

Percona Toolkit 는 Percona 사에서 개발한 오픈소스 기반의 Mysql/MariaDB 의 유틸리티로 기술지원 시 사용하던 유용한 기능의 유틸리티 도구를 하나로 묶은 패키지 입니다.

Jade(정현호)

Principal DBA(MySQL, AWS Aurora, Oracle)
핀테크 서비스인 핀다에서 데이터베이스를 운영하고 있어요(at finda.co.kr)
Previous - 당근마켓, 위메프, Oracle Korea ACS / Fedora Kor UserGroup 운영중

Database 외에도 NoSQL , Linux , Python, Cloud, Http/PHP CGI 등에도 관심이 있습니다
purityboy83@gmail.com / admin@hoing.io

hoing.io

bug.mysql.com

Statistics

MySQL 8.0 invisible columns

1. invisible columns 개요

2. 테이블 생성 및 확인

3. DML 과 Invisible Columns

4. Invisible Column Metadata

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

6. Reference

You may also like...

1. invisible columns 개요

2. 테이블 생성 및 확인

3. DML 과 Invisible Columns

4. Invisible Column Metadata

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

6. Reference

You may also like...

MySQL 복제 문제 발생시 Skip - Replication Error Skip

Aurora MySQL 과 RDS for MySQL 에서 validate_password 사용하기

MySQL - 확장 검색 - 전문검색 FullText Search - Real MySQL 8.0