mysql8.0 이전의 auto increment 는 auto-increment를 메모리에 저장했기 때문에 DB서버 재기동 시 값이 보존이 안되었습니다. 때문에 auto increment 컬럼을 참조하는 다른 테이블들을 고려해야 하는 등의 관리포인트가 있습니다.
mysql 8.0 부터는 auto increment 컬럼을 redo log 에 저장해서 DB가 재기동 되어도 그대로 보존되도록 변경되었는데 이번 글에서는 바뀐 auto increment를 살펴보겠습니다.
- 5.7.18
delimiter ;; create procedure test.ai_test() begin declare i int default 1; while i < 30 do insert into test.test_ai values (null); set i:=i+1; end while; end ;;
mysql> call ai_test(); Query OK, 1 row affected (0.04 sec) mysql> show create table test_ai; | test_ai | CREATE TABLE `test_ai` ( `i` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`i`) ) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8
=> test data 준비, row 29개 insert 하여 다음 ai 값이 30인 상태
mysql> delete from test_ai order by i desc limit 10; Query OK, 10 rows affected (0.01 sec) mysql> select max(i) from test_ai; +--------+ | max(i) | +--------+ | 19 | +--------+ 1 row in set (0.00 sec) mysql> show create table test_ai; | test_ai | CREATE TABLE `test_ai` ( `i` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`i`) ) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8 |
=> row 10개 delete 후, 테이블의 최대값이 19인 상황
mysql> shutdown; Query OK, 0 rows affected (0.00 sec) mysql> show create table test.test_ai; | test_ai | CREATE TABLE `test_ai` ( `i` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`i`) ) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8 |
=>ai 값이 보존되지 않고 테이블의 max 값으로 설정됨
- 8.0.17
mysql> call ai_test(); Query OK, 1 row affected (0.10 sec) mysql> show create table test_ai; | test_ai | CREATE TABLE `test_ai` ( `i` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`i`) ) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci | 1 row in set (0.00 sec)
=> test data 준비, 29번 insert 하여 다음 ai 값이 30인 상태
mysql> delete from test_ai order by i desc limit 10; Query OK, 5 rows affected (0.01 sec) mysql> select max(i) from test_ai; +--------+ | max(i) | +--------+ | 19 | +--------+ 1 row in set (0.00 sec) mysql> show create table test_ai; | test_ai | CREATE TABLE `test_ai` ( `i` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`i`) ) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
=> row 10개 delete, test_ai 테이블의 max 값이 19인 상황
mysql> shutdown; Query OK, 0 rows affected (0.00 sec) mysql> show create table test_ai; | test_ai | CREATE TABLE `test_ai` ( `i` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`i`) ) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
=> 재기동 후에 테이블의 ai 값이 테이블의 max 값으로 설정되었던 5.7버전과는 달리 그대로 보존되는 것 확인