[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 컬럼에 영향을줍니다.

Designed by JB FACTORY