[DB] MariaDB에서 UPDATE 시 AUTO_INCREMENT 컬럼의 새로운 레코드 생성 이슈 해결 방법

MariaDB에서는 AUTO_INCREMENT로 설정된 컬럼이 UPDATE 시에 자동으로 다음 값을 생성하는 기능이 있습니다.

이러한 동작은 특히 AUTO_INCREMENT로 설정된 기본 키 컬럼에 대해 주로 발생합니다.

이로 인해 의도치 않게 UPDATE 시에 새로운 레코드가 추가될 수 있습니다.

---

원인

1. AUTO_INCREMENT로 설정된 컬럼은 INSERT 시에 자동으로 다음 값을 생성합니다.
2. UPDATE 쿼리에서 AUTO_INCREMENT 컬럼에 값을 지정하지 않으면, 데이터베이스가 자동으로 다음 값을 생성합니다.

---

해결 방법

1. AUTO_INCREMENT 컬럼은 일반적으로 데이터베이스에서 자동으로 관리되므로 UPDATE 시에 명시적으로 값을 지정하지 않는 것이 좋습니다.
2. UPDATE 시에 AUTO_INCREMENT 컬럼에 값을 유지하고 싶다면, 해당 컬럼에 대해 명시적으로 값을 지정해야 합니다.

자세한 방법는 아래 쓰도록 하겠습니다.

---

테이블 구조 예시

CREATE TABLE `nht_event` (
  `N_EVENT_SEQ` int(22) NOT NULL AUTO_INCREMENT COMMENT 'N 이벤트 시퀀스',
  -- ... (다른 컬럼들)
  PRIMARY KEY (`N_EVENT_SEQ`),
  -- ...
) ENGINE=InnoDB AUTO_INCREMENT=297170 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='이벤트 정보';

MariaDB에서 AUTO_INCREMENT 컬럼을 사용할 때, 해당 컬럼은 주로 INSERT 시에 활용되며,
UPDATE 시에는 명시적으로 값을 지정해주어야 하는데, 이를 통해 의도치 않은 레코드 생성을 방지할 수 있습니다.

---

해결방법 1. UPDATE 시 AUTO_INCREMENT 컬럼에 값을 명시적으로 지정하기

UPDATE nht_event
SET
	V_END_TIME = #{evtEndTime}
  , V_EVENT_STATUS_CD = #{evtStatusCd}
WHERE
	V_DEVICE_ID = #{devId}
AND
	V_EVENT_NM = #{evtName}
AND
  N_EVENT_SEQ = #{seq}

이렇게 UPDATE 쿼리에서 **N_EVENT_SEQ**에 현재 값을 명시적으로 지정하면,
AUTO_INCREMENT 컬럼의 새로운 값이 생성되지 않고 현재 값을 유지하게 됩니다.

 

해결방법 2. INSERT ... ON DUPLICATE KEY UPDATE 사용하기

INSERT INTO nht_event (N_EVENT_SEQ, other_columns)
VALUES (existing_value, new_values)
ON DUPLICATE KEY UPDATE N_EVENT_SEQ = N_EVENT_SEQ;

위의 쿼리는 이미 존재하는 기본 키 값에 대해 **INSERT**를 시도하고, 이미 존재하는 경우 **UPDATE**를 수행합니다.
이때, **N_EVENT_SEQ**에 현재 값을 지정하여 AUTO_INCREMENT의 동작을 방지할 수 있습니다.
이 방법은 기본 키가 중복되는 경우에만 사용 가능합니다.

 

참고

• AUTO_INCREMENT 컬럼은 일반적으로 데이터베이스에서 관리되므로, 특별한 경우가 아니라면
  직접적인 변경은 피하는 것이 좋습니다.
• INSERT ... ON DUPLICATE KEY UPDATE 방법은 기본 키 중복 시에만 사용 가능하며,
  테이블의 고유 제약 조건이 필요합니다.

 

이러한 방법들은 특별한 상황에서만 필요하며,
일반적으로 AUTO_INCREMENT 컬럼은 데이터베이스에서 자동으로 관리되는 것이 좋습니다.

---