MySQL 8 - Generated Invisible Primary Keys - GIPK

Last Updated on 3월 21, 2023 by Jade(정현호)

안녕하세요
이번 글은 MySQL 8.0.30 버전에서 추가 된 Generated Invisible Primary Key 에 대해서 확인 해보려고 합니다.

Contents

Generated Invisible Primary Keys
GIPK 기능 확인 및 테스트
추가 정보 및 제약사항
Reference

Generated Invisible Primary Keys

Generated Invisible Primary Key(줄여서 GIPK) 는 MySQL 8.0.30 버전에 새로 추가 된 기능 입니다.

InnoDB 에서 Primary Key(기본키) 없이 생성된 모든 테이블에 대해서 보이지 않은 기본키(Invisible Primary Key) 를 기능을 지원 합니다.

관련된 시스템 변수로 sql_generate_invisible_primary_key 이며, 해당 시스템 변수가 ON 으로 설정되어 있을 경우, MySQL 서버는 자동으로 테이블에 generated invisible primary key (GIPK) 를 추가 하게 됩니다.

sql_generate_invisible_primary_key 시스템 변수의 기본값은 OFF 입니다. 기본적으로 해당 기능은 비활성화되어 있음을 의미 합니다.

해당 기능을 사용하려면 명시적으로 ON 으로 설정 해야 하며, Dynamic 변수이고 Global 및 Session 레벨로 적용 가능 합니다.

Generated Invisible Primary Key 에 대한 기능을 조금 더 이해 하기 위해서는 아래에서 진행되는 테스트 예제를 통해서 확인 하실 수 있습니다.

포스팅에서 사용한 버전은 MySQL 8.0.31 입니다.

GIPK 기능 확인 및 테스트

GIPK 기능을 확인 해보기 위해서 Primary Key(기본키)가 없는 두개의 테이블을 생성 해보도록 하겠습니다.

-- 기본 상태에서의 파라미터 값 조회
mysql> select @@sql_generate_invisible_primary_key;
+--------------------------------------+
| @@sql_generate_invisible_primary_key |
+--------------------------------------+
|                                    0 |
+--------------------------------------+
-- 0 임으로 기능은 비활성화 상태 임

-- 첫번째 테이블 생성
mysql> create table tb_a(col1 varchar(20), col2 int);



-- 파라미터 변경
mysql> set sql_generate_invisible_primary_key=on;

-- 파라미터 조회
mysql> select @@sql_generate_invisible_primary_key;
+--------------------------------------+
| @@sql_generate_invisible_primary_key |
+--------------------------------------+
|                                    1 |
+--------------------------------------+


-- 두번째 테이블 생성
mysql> create table tb_b(col1 varchar(20), col2 int);

이렇게 생성한 2개 테이블에 대해서 테이블 생성 구문으로 확인 해보도록 하겠습니다.

mysql> show create table tb_a\G
*************************** 1. row ***************************
       Table: tb_a
Create Table: CREATE TABLE `tb_a` (
  `col1` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `col2` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci


mysql> show create table tb_b\G
*************************** 1. row ***************************
       Table: tb_b
Create Table: CREATE TABLE `tb_b` (
  `my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */,
  `col1` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `col2` int DEFAULT NULL,
  PRIMARY KEY (`my_row_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

tb_a 테이블을 생성시에 사용된 CREATE TABLE 문에서 지정한 Primary key(기본 키)가 없으므로 기본키가 없는 상태 입니다.

tb_b 테이블은 sql_generate_invisible_primary_key = ON 으로 설정 후에 생성한 테이블로 tb_a 와는 달리 my_row_id 라는 컬럼이 추가 된 것을 확인할 수 있고 MySQL 전용 확장 주석으로 /*!80023 INVISIBLE */ 으로 표시 된 걸 확인 할 수 있습니다.

tb_a 테이블은 생성될 때 sql_generate_invisible_primary_key 시스템 변수가 OFF 였기 때문에 해당 테이블에서 보이지 않는 기본키인 my_row_id 가 추가가 되지 않았습니다.

Primary key가 MySQL 서버에 의해 테이블에 자동으로 추가되면 컬럼 및 키 이름은 항상 my_row_id 가 됩니다.

my_row_id 는 보이지 않은 컬럼 입니다. 그 의미는 select * from 테이블명; 으로 조회하면 보이지 않음을 의미 합니다.

-- 데이터 입력
mysql> insert into tb_b(col1,col2)
values('a','10');

-- * 로 조회
mysql> select * from tb_b;
+------+------+
| col1 | col2 |
+------+------+
| a    |   10 |
+------+------+

invisible 컬럼은 명시적으로 선택(입력)해야 출력 됩니다.

mysql> select my_row_id,col1,col2 from tb_b;
+-----------+------+------+
| my_row_id | col1 | col2 |
+-----------+------+------+
|         1 | a    |   10 |
+-----------+------+------+

이 부분은 MySQL 8.0 에서 추가된 Invisible Columns 기능과 동일 하며, 이전의 포스팅에서 추가적인 정보를 확인 하시면 됩니다.

MySQL 8.0 invisible columns

안녕하세요. 이번 포스팅은 MySQL 8.0 에서 새로 추가된 invisible columns 기능에 대해서 확인 해보려고 합니다. invisible columns 은 MySQL 8.0.23 버전 부터 추가된 기능으로 일반적으로 쿼리에 숨겨져 있지만 명시 적으로 컬럼을 지정하여 조회하는 경우 액세스 할 수 있는 컬럼 기능 입니다.

GIPK가 활성화되면 생성된 기본 키를 VISIBLE과 INVISIBLE 간에 전환하는 것 외에는 변경할 수 없습니다.

위의 예재에서 생성한 tb_b 테이블에서 생성된 보이지 않는 기본 키를 표시하려면(보이게 하려면,visible) 다음 ALTER TABLE 문을 실행합니다.

mysql> alter table tb_b alter column my_row_id set visible;

mysql> show create table tb_b\G
*************************** 1. row ***************************
       Table: tb_b
Create Table: CREATE TABLE `tb_b` (
  `my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `col1` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `col2` int DEFAULT NULL,
  PRIMARY KEY (`my_row_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

alter 명령어를 통해서 invisible 에서 visible 으로 변경하면 위에서 확인 할 수 있듯이 my_row_id 컬럼에 힌트 없어지게 됩니다.

이 기본키를 다시 보이지 않게 하려면 아래 명령어를 수행 합니다.

mysql> alter table tb_b alter column my_row_id set invisible;

GIPK는 기본적으로 항상 보이지 않습니다.(invisible)

추가 정보 및 제약사항

기본적으로 GIPK는 SHOW CREATE TABLE, SHOW COLUMNS 그리고 SHOW INDEX 명령어, Information Schema 의 COLUMNS 과 STATISTICS 테이블을 통해서 invisible 컬럼을 확인 할 수 있습니다.

mysql> select column_name, ordinal_position, data_type, column_key,extra
from information_schema.columns
where table_name = 'tb_b'
order by ORDINAL_POSITION;
+-------------+------------------+-----------+------------+--------------------------+
| COLUMN_NAME | ORDINAL_POSITION | DATA_TYPE | COLUMN_KEY | EXTRA                    |
+-------------+------------------+-----------+------------+--------------------------+
| my_row_id   |                1 | bigint    | PRI        | auto_increment INVISIBLE |
| col1        |                2 | varchar   |            |                          |
| col2        |                3 | int       |            |                          |
+-------------+------------------+-----------+------------+--------------------------+

이렇게 invisible 컬럼 속성을 가지는 GIPK 컬러 정보는 show_gipk_in_create_table_and_information_schema 시스템 변수에
의해서 내용 확인 유무를 결정 할 수 있습니다.

기본값은 ON 이며 OFF 로 변경하여 GIPK 생성한 정보를 숨길 수 있습니다.

mysql set show_gipk_in_create_table_and_information_schema=off;

mysql> select column_name, ordinal_position, data_type, column_key,extra
from information_schema.columns
where table_name = 'tb_b'
order by ORDINAL_POSITION;
+-------------+------------------+-----------+------------+-------+
| COLUMN_NAME | ORDINAL_POSITION | DATA_TYPE | COLUMN_KEY | EXTRA |
+-------------+------------------+-----------+------------+-------+
| col1        |                2 | varchar   |            |       |
| col2        |                3 | int       |            |       |
+-------------+------------------+-----------+------------+-------+

information_schema.columns 뿐만 아니라 아래 처럼 show create table 을 통한 테이블 생성 구문에서도 GIPK 정보를 숨기게 됩니다.

mysql> show create table tb_b\G
*************************** 1. row ***************************
       Table: tb_b
Create Table: CREATE TABLE `tb_b` (
  `col1` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `col2` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

GIPK가 활성화 된 상태에서 다음의 두 조건 중 하나라도 있다면 생성된 기본 키를 삭제 할 수 없습니다.

테이블에 기본키가 없을 경우
기본 키는 삭제 되지만 기본 키 열은 삭제 되지 않습니다.

sql_generate_invisible_primary_key 효과는 InnoDB 스토리지 엔진을 사용하는 테이블에만 적용됩니다.

ALTER TABLE 문을 사용하여 생성된 보이지 않는 기본 키가 있는 테이블에서 사용하는 스토리지 엔진을 변경할 수 있습니다.
이 경우 기본 키와 열은 그대로 유지되지만 테이블과 키는 더 이상 특별한 처리를 받지 않습니다.

GIPK는 CREATE TABLE ... SELECT 문 사용시 row-based 복제 환경에서 지원 합니다.
row-based 복제 환경에서는 CREATE TABLE ... SELECT 명령문에 대한 바이너리 로그에 기록된 정보에는 GIPK 정의가 포함되므로 올바르게 복제됩니다.

그러나 명령문 기반(Statement-based) 복제에서는 sql_generate_invisible_primary_key = ON 으로 설정 상태에서의 CREATE TABLE ... SELECT 이 지원되지 않습니다.

GIPK 를 통해서 자동으로 추가된 기본 키 컬럼인 my_row_id 은 보이지 않은 상태(기본값, invisible) 에서는 컬럼명을 변경 할 수 없습니다.

mysql> alter table tb_b rename column my_row_id to id;
ERROR 4110 (HY000): Altering generated invisible primary key column 'my_row_id' is not allowed.

invisible 에서 visible 으로 변경 후에 컬럼 이름을 변경 할 수 있습니다.

mysql> alter table tb_b alter column my_row_id set visible;
Query OK, 0 rows affected (0.01 sec)

mysql> alter table tb_b rename column my_row_id to id;
Query OK, 0 rows affected (0.01 sec)

mysql> show create table tb_b\G
*************************** 1. row ***************************
       Table: tb_b
Create Table: CREATE TABLE `tb_b` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `col1` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `col2` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

my_row_id 에서 id 로 변경된 컬럼에 대해서는 invisible column 으로 만들 수 있습니다.

mysql> alter table tb_b alter column id set invisible;
Query OK, 0 rows affected (0.01 sec)

mysql> show create table tb_b\G
*************************** 1. row ***************************
       Table: tb_b
Create Table: CREATE TABLE `tb_b` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */,
  `col1` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `col2` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

invisible column 컬럼으로 정상적으로 되었는지는 다음과 같이 조회를 해보면 확인 할 수 있습니다.

mysql> select * from tb_b;
+------+------+
| col1 | col2 |
+------+------+
| a    |   10 |
+------+------+


-- 컬럼에 명시적으로 지정함
mysql> select id,col1,col2 from tb_b;
+----+------+------+
| id | col1 | col2 |
+----+------+------+
|  1 | a    |   10 |
+----+------+------+

mysqldump 에서 GIPK 에 대한 정보를 제외하고 백업을 수행 할 수 있습니다.
--skip-generated-invisible-primary-key 옵션을 사용 하면 GIPK 정보가 프로그램 출력에서 제외됩니다.

Reference

Reference URL
• mysql.com/create-table-gipks
• mysql.com/sysvar_sql_generate_invisible_primary_key

관련된 다른 글

MySQL 8.0 invisible columns

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

안녕하세요. 이번 포스팅은 MySQL Invisible Columns 을 활용한 table audit 내용 기록과 관련된 내용을 살펴보도록 하겠습니다. 포스팅은 lefred.be 사이트에서 다룬 내용을 번역 및 테스트를 진행한 내용으로 작성되어 있습니다.

MySQL - PK가 없을 경우 복제 지연 현상과 8.0의 REQUIRE_TABLE_PRIMARY_KEY_CHECK 기능

안녕하세요. 이번 포스팅에서는 PK가 없는 테이블을 사용하는 환경에서의 복제 지연 현상과 8.0 버전에서 새로 추가된 REQUIRE_TABLE_PRIMARY_KEY_CHECK 기능에 대해서 확인 해보려고 합니다.

MySQL - Generated Columns - 가상 컬럼

안녕하세요 이번 포스팅에서는 MySQL의 Generated Columns 기능에 대해서 살펴 보도록 하겠습니다. Generated Columns Generated Columns은 MySQL 5.7 버전에서 추가 된 기능으로 이 컬럼의 데이터는 지정된 식을 기반으로 계산되어 생성되거나 지정된 표현 형태로 출력을 하는 기능을 합니다.

MySQL - 파라미터 SET PERSIST 과 RESET PERSIST

안녕하세요. 이번 포스팅에서는 MySQL 8 버전에서 사용할 수 있는 파라미터 관리에 대한 SET/RESET PERSIST 에 관한 내용에 대해서 확인 해보려고 합니다.

MySQL 8.0 - User 생성 구문 Comment 와 Attribute - 유저 생성

안녕하세요 이번 포스팅에서는 MySQL 8.0 버전에서의 User Comment 와 User Attribute 기능에 대해서 내용을 확인 해보려고 합니다. Comment 와 Attribute MySQL 8.0.21 버전에서 user 생성이나 변경(alter) 시 user comment 와 user attribute 를 추가할 수 있는 기능이 추가 되었습니다.

MySQL 8.0 -innodb parallel threads ddl - MySQL 8.0 신기능 New Feature

안녕하세요 이번 포스팅에서는 MySQL 8.0.27 에서 새로 기능이 추가된 innodb parallel threads dll 기능에 대해서 확인 해보도록 하겠습니다.

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 - Generated Invisible Primary Keys - GIPK

Generated Invisible Primary Keys

GIPK 기능 확인 및 테스트

추가 정보 및 제약사항

Reference

You may also like...

Generated Invisible Primary Keys

GIPK 기능 확인 및 테스트

추가 정보 및 제약사항

Reference

You may also like...

MySQL - 트랜잭션 과 잠금 (2) - Real MySQL 8.0 - MySQL Transaction

Amazon RDS for MySQL (5) - Multi-threaded replication - Replica Ignore

MySQL 에서 LargePage(HugePage) 사용 및 설정 - mysql hugepage