[MySQL] auuto_increment_increment와 auto_increment_offset과의 관계

auto_increment_increment 및 auto_increment_offset은 소스와 소스간(Source to Source) 복제와 함께 사용하기위한 것이며 AUTO_INCREMENT 컬럼의 작업을 제어하는데 사용할 수 있습니다. 두 변수 모두 전역 및 세션 값이 있으며 각각 1에서 65,535 사이의 정수 값을 가정할 수 있습니다. 이 두 변수 중 하나의 값을 0으로 설정하면 값이 대신 1로 설정됩니다. 이 두 변수중 하나의 값을 65,535보다 크거나 0보다 작은 정수로 설정하려고 하면 값이 대신 65,535로 설정됩니다. auto_increment_increment 또는 auto_increment_offset의 값을 정수가 아닌 값으로 설정하려고하면 오류가 발생하고 변수의 실제 값은 변경되지 않습니다.

 

서버에서 그룹 복제가 시작되면 auto_increment_increment 값이 group_replication_auto_increment_increment 값으로 변경되며 기본값은 7이며 auto_increment_offset 값은 서버 ID로 변경됩니다. 그룹 복제가 중지되면 변경 사항이 되돌려집니다. 이러한 변경 사항은 auto_increment_increment 및 auto_increment_offset의 기본값이 각각 1 인 경우에만 수행되고 되돌려집니다. 해당 값이 이미 기본값에서 수정된 경우 그룹 복제는 해당 값을 변경하지 않습니다.

 

auto_increment_increment 및 auto_increment_offset은 다음과 같이 AUTO_INCREMENT컬럼 동작에 영향을줍니다.

 

• auto_increment_increment는 연속 컬럼값 사이의 간격을 제어합니다. 예를 들면 :

mysql> SHOW VARIABLES LIKE 'auto_inc%';

+--------------------------+-------+

| Variable_name            | Value |

+--------------------------+-------+

| auto_increment_increment | 1     |

| auto_increment_offset    | 1     |

+--------------------------+-------+

2 rows in set (0.00 sec)

 

mysql> CREATE TABLE autoinc1

    -> (col INT NOT NULL AUTO_INCREMENT PRIMARY KEY);

  Query OK, 0 rows affected (0.04 sec)

 

mysql> SET @@auto_increment_increment=10;

Query OK, 0 rows affected (0.00 sec)

 

mysql> SHOW VARIABLES LIKE 'auto_inc%';

+--------------------------+-------+

| Variable_name            | Value |

+--------------------------+-------+

| auto_increment_increment | 10    |

| auto_increment_offset    | 1     |

+--------------------------+-------+

2 rows in set (0.01 sec)

 

mysql> INSERT INTO autoinc1 VALUES (NULL), (NULL), (NULL), (NULL);

Query OK, 4 rows affected (0.00 sec)

Records: 4  Duplicates: 0  Warnings: 0

 

mysql> SELECT col FROM autoinc1;

+-----+

| col |

+-----+

|   1 |

|  11 |

|  21 |

|  31 |

+-----+

4 rows in set (0.00 sec)

auto_increment_increment 값이 10이기 때문에 10씩 증가해서 생성됩니다. 여기서 보는바와 같이 auto_increment_increment는 값의 증가값을 제어하는것을 알 수 있습니다.

 

• auto_increment_offset은 AUTO_INCREMENT 컬럼값의 시작점을 결정합니다. auto_increment_increment에 대한 설명에 제공된 예제와 동일한 세션 동안 이러한 명령문이 실행된다고 가정합니다.

mysql> SET @@auto_increment_offset=5;

Query OK, 0 rows affected (0.00 sec)

 

mysql> SHOW VARIABLES LIKE 'auto_inc%';

+--------------------------+-------+

| Variable_name            | Value |

+--------------------------+-------+

| auto_increment_increment | 10    |

| auto_increment_offset    | 5     |

+--------------------------+-------+

2 rows in set (0.00 sec)

 

mysql> CREATE TABLE autoinc2

    -> (col INT NOT NULL AUTO_INCREMENT PRIMARY KEY);

Query OK, 0 rows affected (0.06 sec)

 

mysql> INSERT INTO autoinc2 VALUES (NULL), (NULL), (NULL), (NULL);

Query OK, 4 rows affected (0.00 sec)

Records: 4  Duplicates: 0  Warnings: 0

 

mysql> SELECT col FROM autoinc2;

+-----+

| col |

+-----+

|   5 |

|  15 |

|  25 |

|  35 |

+-----+

4 rows in set (0.02 sec)

auto_increment_offset이 5이기 때문에 초기값은 5부터 시작합니다. 그리고 auto_increment_increment이 10이기 때문에 10씩 증가합니다.

이 증가값은 다음 계산식에 의해 생성됩니다.

5(offset) + N(증가 순서) × 10(auto_increment_increment)

참고로 auto_increment_offset의 값이 auto_increment_increment의 값보다 크면 auto_increment_offset의 값은 무시됩니다.

 

 

이러한 변수중 하나가 변경된 다음 AUTO_INCREMENT 컬럼이 포함된 테이블에 새 행이 삽입되면 일련의 AUTO_INCREMENT 값이 컬럼에 이미 있는 값과 관계없이 계산되고 다음 값이 삽입되기 때문에 결과가 직관적이지 않은것처럼 보일 수 있습니다. AUTO_INCREMENT 컬럼의 기존 최대값보다 큰 계열의 최소값입니다. 시리즈는 다음과 같이 계산됩니다.

auto_increment_offset + N × auto_increment_increment

 

여기서 N은 계열 [1, 2, 3, ...]의 양의 정수값입니다. 예를 들면 :

mysql> SHOW VARIABLES LIKE 'auto_inc%';

+--------------------------+-------+

| Variable_name            | Value |

+--------------------------+-------+

| auto_increment_increment | 10    |

| auto_increment_offset    | 5     |

+--------------------------+-------+

2 rows in set (0.00 sec)

 

mysql> SELECT col FROM autoinc1;

+-----+

| col |

+-----+

|   1 |

|  11 |

|  21 |

|  31 |

+-----+

4 rows in set (0.00 sec)

 

mysql> INSERT INTO autoinc1 VALUES (NULL), (NULL), (NULL), (NULL);

Query OK, 4 rows affected (0.00 sec)

Records: 4  Duplicates: 0  Warnings: 0

 

mysql> SELECT col FROM autoinc1;

+-----+

| col |

+-----+

|   1 |

|  11 |

|  21 |

|  31 |

|  35 |

|  45 |

|  55 |

|  65 |

+-----+

8 rows in set (0.00 sec)

 

auto_increment_increment 및 auto_increment_offset에 대해 표시된 값은 시리즈 5 + N × 10, 즉 [5, 15, 25, 35, 45, ...]를 생성합니다. INSERT 이전의 col 열에있는 가장 높은 값은 31이고 AUTO_INCREMENT 시리즈에서 다음으로 사용 가능한 값은 35이므로 col에 대해 삽입된 값은 해당 지점에서 시작되고 결과는 SELECT 쿼리에 대해 표시된 것과 같습니다.

 

이 두 변수의 효과를 단일 테이블로 제한하는 것은 불가능합니다. 이러한 변수는 MySQL 서버의 모든 테이블에있는 모든 AUTO_INCREMENT 컬럼의 동작을 제어합니다. 두 변수의 전역 값이 설정되면 전역 값이 변경되거나 세션 값을 설정하여 재정의 될때까지 또는 mysqld가 다시 시작될 때까지 그 효과가 지속됩니다. 로컬 값이 설정된 경우 해당 세션 중에 값이 변경되지 않는 한 새 값은 세션 기간 동안 현재 사용자가 새 행을 삽입한 모든 테이블의 AUTO_INCREMENT 컬럼에 영향을줍니다.