MySQL 예제가 포함된 AUTO_INCREMENT
마커스 알렌
⚡ 스마트 요약
MySQL AUTO_INCREMENT 속성은 행이 삽입될 때마다 숫자 열에 순차적인 번호를 자동으로 생성합니다. 이 속성을 사용하면 고유 식별자를 수동으로 계산할 필요가 없어지므로 기본 키를 설정하는 표준 방식이 되었습니다.
자동 증가란 무엇입니까?
자동 증가는 숫자 데이터 유형에서 작동하는 기능입니다. 자동 증가로 정의된 필드에 대한 레코드가 테이블에 삽입될 때마다 연속적인 숫자 값을 자동으로 생성합니다.
이 속성은 TINYINT부터 BIGINT까지 모든 정수형에서 작동합니다. 또한 해당 열에는 인덱싱이 되어 있어야 하며, 기본 키로 선언되면 자동으로 인덱싱됩니다.
자동 증가는 언제 사용하나요?
수업에서 데이터베이스 정규화우리는 기본 키와 외래 키를 사용하여 서로 연결된 여러 개의 작은 테이블에 데이터를 저장함으로써 데이터 중복을 최소화하는 방법을 살펴보았습니다.
기본 키는 데이터베이스에서 각 행을 고유하게 식별하므로 반드시 고유해야 합니다. 그렇다면 기본 키가 항상 고유하도록 어떻게 보장할 수 있을까요?
가능한 해결책 중 하나는 기본 키를 생성하는 수식을 사용하는 것입니다. 이 수식은 데이터를 추가하기 전에 테이블에 해당 키가 존재하는지 확인합니다. 이 방법은 효과가 있을 수 있지만, 복잡하고 완벽하지는 않습니다. 두 세션이 동시에 데이터를 삽입할 경우 동일한 최대값을 읽어 충돌이 발생할 수 있습니다.
이러한 복잡성을 피하고 기본 키가 항상 고유하도록 보장하기 위해 다음을 사용할 수 있습니다. MySQL 자동 증가 기능을 사용하여 기본 키를 생성합니다. 자동 증가는 INT 데이터 형식과 함께 사용됩니다. INT 데이터 형식은 부호 있는 값과 부호 없는 값을 모두 지원합니다. 부호 없는 데이터 형식은 양수만 포함할 수 있습니다. 모범 사례로, 자동 증가 기본 키에 부호 없는 제약 조건을 정의하는 것이 좋습니다.
자동 증가 구문
추론 과정이 정리되었으니, 이제 영화 카테고리 표를 만드는 데 사용된 스크립트를 살펴보세요.
CREATE TABLE `categories` ( `category_id` int UNSIGNED NOT NULL AUTO_INCREMENT, `category_name` varchar(150) DEFAULT NULL, `remarks` varchar(500) DEFAULT NULL, PRIMARY KEY (`category_id`) );
category_id 필드에 "AUTO_INCREMENT"가 표시되어 있는 것을 확인하세요. 이 옵션 때문에 테이블에 새 행이 삽입될 때마다 카테고리 ID가 자동으로 생성됩니다. 데이터를 삽입할 때 카테고리 ID는 제공되지 않습니다. MySQL 그것을 생성합니다.
참고 : UNSIGNED 키워드는 열의 양수 범위를 두 배로 늘리고, 이전에 int(11)로 표기되었던 표시 너비는 더 이상 사용되지 않습니다. MySQL 8.0.17 버전 이후. 일반 INT 현재 형태입니다.
기본적으로 AUTO_INCREMENT의 초기값은 1이며, 새 레코드가 추가될 때마다 1씩 증가합니다.
카테고리 테이블의 현재 내용을 살펴보겠습니다.
SELECT * FROM `categories`;
위 스크립트를 실행하면 MySQL Workbench를 사용하여 myflixdb를 테스트한 결과는 다음과 같습니다.
| category_id | category_name | remarks |
|---|---|---|
| 1 | Comedy | Movies with humour |
| 2 | Romantic | Love stories |
| 3 | Epic | Story acient movies |
| 4 | Horror | NULL |
| 5 | Science Fiction | NULL |
| 6 | Thriller | NULL |
| 7 | Action | NULL |
| 8 | Romantic Comedy | NULL |
현재 8개의 행이 있으므로 다음에 생성될 ID는 9입니다. 이제 이름만 입력하여 categories 테이블에 새 카테고리를 삽입해 보겠습니다.
INSERT INTO `categories` (`category_name`) VALUES ('Cartoons');
myflixdb에 대해 위 스크립트를 실행합니다. MySQL 워크 벤치 그러면 아래에 보이는 결과가 나옵니다.
| category_id | category_name | remarks |
|---|---|---|
| 1 | Comedy | Movies with humour |
| 2 | Romantic | Love stories |
| 3 | Epic | Story acient movies |
| 4 | Horror | NULL |
| 5 | Science Fiction | NULL |
| 6 | Thriller | NULL |
| 7 | Action | NULL |
| 8 | Romantic Comedy | NULL |
| 9 | Cartoons | NULL |
참고로 저희는 카테고리 ID를 제공하지 않았습니다. MySQL 카테고리 ID가 자동 증가로 정의되어 있기 때문에 자동으로 생성되었습니다.
다음에 의해 생성된 마지막 삽입 ID를 얻으려는 경우 MySQL, LAST_INSERT_ID 함수를 사용하여 이를 수행할 수 있습니다. 아래 표시된 스크립트는 생성된 마지막 ID를 가져옵니다.
SELECT LAST_INSERT_ID();
위 스크립트를 실행하면 INSERT 쿼리에서 생성된 마지막 자동 증가 번호가 표시됩니다. 결과는 아래와 같습니다.
Tip LAST_INSERT_ID()는 사용자의 연결 범위 내에 있으므로 다른 사용자의 삽입 작업으로 생성된 값이 실수로 사용자에게 반환되는 일은 절대 없습니다.
AUTO_INCREMENT 시작 값을 설정하거나 재설정하는 방법
기본 순서는 1부터 시작하지만, 프로젝트에 항상 필요한 것은 아닙니다. 송장 번호를 기존 시스템에서 이어받아야 할 수도 있고, 테스트 테이블을 재설정해야 하는 경우도 있습니다. MySQL 카운터를 직접 노출하므로 두 경우 모두 단일 절로 처리됩니다. 시작 숫자를 제어하려면 다음 단계를 따르십시오.
- 생성 시점에 값을 설정하십시오. CREATE TABLE 문에 AUTO_INCREMENT 절을 추가하세요. 그러면 처음 삽입되는 행에 1 대신 해당 숫자가 할당됩니다.
- 기존 테이블의 값을 변경합니다. ALTER TABLE 동일한 조항으로. MySQL 현재 저장된 가장 큰 식별자보다 새로운 번호가 더 큰 경우에만 새 번호를 허용합니다.
- 비워둔 테이블을 초기화합니다. TRUNCATE TABLE은 모든 행을 삭제하고 카운터를 1로 되돌리는 단일 작업이며, DELETE 단독으로는 이러한 작업을 수행할 수 없습니다.
- 변경을 확인하십시오. 새로운 시퀀스를 사용하기 전에 행을 삽입하고 LAST_INSERT_ID()를 사용하여 식별자를 다시 읽어오십시오.
-- Start a brand-new table at 1000 CREATE TABLE `invoices` ( `invoice_id` int UNSIGNED NOT NULL AUTO_INCREMENT, `amount` decimal(10,2), PRIMARY KEY (`invoice_id`) ) AUTO_INCREMENT = 1000; -- Move the counter on an existing table ALTER TABLE `categories` AUTO_INCREMENT = 100; -- Empty the table and reset the counter to 1 TRUNCATE TABLE `categories`;
단계 크기는 auto_increment_increment 시스템 변수를 사용하여 변경할 수도 있지만, 이 변수는 특정 테이블이 아닌 서버 전체에 적용됩니다. 주로 두 서버가 동일한 식별자를 생성해서는 안 되는 복제 환경에서 사용됩니다.
AUTO_INCREMENT 시퀀스에 공백이 생기는 이유는 무엇입니까?
결국 테이블에는 1, 2, 5, 6과 같은 식별자가 표시됩니다. 아무런 문제가 없습니다. 카운터는 숫자의 연속성을 보장하는 것이 아니라 고유성을 보장하도록 설계되었으며, 동일한 값을 두 번 출력하지 않습니다.
다음과 같은 이유로 공백이 발생합니다.
- 삭제된 행: 테이블에서 행이 삭제될 때, 해당 행의 자동 증가 ID는 재사용되지 않습니다. MySQL 계속해서 새로운 숫자를 순차적으로 생성합니다.
- 롤백된 거래: 해당 번호는 삽입 작업이 실행되는 순간 사용 가능해집니다. 트랜잭션이 롤백되면 해당 행은 사라지지만 번호는 이미 사용된 상태입니다.
- 삽입 실패: UNIQUE 제약 조건에 의해 거부된 문이라도 실패하기 전에 식별자를 사용할 수 있습니다.
- 대량 삽입물: InnoDB는 여러 행을 삽입할 때 숫자 블록을 예약하고 사용하지 않는 숫자는 버릴 수 있습니다.
이러한 간격을 메우려는 시도는 잘못된 것입니다. 행 번호를 다시 매기면 해당 행을 가리키는 모든 외래 키가 깨지고, 그 값 자체는 비즈니스 의미가 없습니다. 보고서에 연속적인 목록이 필요한 경우, 저장된 데이터를 다시 작성하는 대신 쿼리에서 행 번호를 생성해야 합니다.
