MariaDB

MariaDB에서 AUTO_INCREMENT 컬럼(number)을 연속된 숫자로 다시 정렬하는 방법이다.

하지만 AUTO_INCREMENT는 기본적으로 삭제된 ID를 재사용하지 않기 때문에, 강제로 ID를 재정렬하려면 수동으로 업데이트해야 한다

---

🔹 1️⃣ AUTO_INCREMENT 컬럼을 연속된 숫자로 다시 정렬하기

방법 1: 새로운 테이블을 생성하고 다시 채우기 (추천)

이 방법은 기존 데이터의 순서를 유지하면서, number 컬럼을 1부터 다시 정렬하는 안전한 방법이야.

-- 1. 새로운 테이블 만들기
CREATE TABLE temp_table LIKE your_table;

-- 2. 기존 데이터 삽입 (number 컬럼을 재정렬)
INSERT INTO temp_table (number, other_column1, other_column2)
SELECT ROW_NUMBER() OVER (ORDER BY number) AS number, other_column1, other_column2
FROM your_table;

-- 3. 기존 테이블 삭제
DROP TABLE your_table;

-- 4. 새로운 테이블을 원래 테이블로 이름 변경
RENAME TABLE temp_table TO your_table;

✅ 이 방법의 장점

✔ 데이터 손실 없이 number 값을 1부터 순차적으로 정렬

✔ AUTO_INCREMENT 속성 유지

⚠ 주의: 이 방법은 테이블을 재생성하기 때문에, 외래 키(FK)가 걸려 있으면 주의해야 함.

---

방법 2: 직접 UPDATE로 정렬 (데이터가 적을 때)

데이터가 많지 않다면, UPDATE를 사용해서 직접 number 값을 다시 정렬할 수도 있어.

SET @new_id = 0;

UPDATE your_table
SET number = (@new_id := @new_id + 1)
ORDER BY number;

✅ 이 방법의 장점

✔ 기존 테이블을 유지하면서 간단하게 정렬 가능

✔ 빠르게 실행됨

❌ 단점

✖ AUTO_INCREMENT 속성이 초기화되지 않음 (즉, 다음 INSERT 시 기존 AUTO_INCREMENT 값을 유지함) - 초기화 후 처음 입력한 값이 primary key로 지정되어 +1 진행됨을 확인

✖ AUTO_INCREMENT 값이 비정상적으로 증가할 수도 있음 - 확인

---

🔹 2️⃣ AUTO_INCREMENT 값을 다시 1부터 시작하도록 초기화

위 방법을 사용한 후, AUTO_INCREMENT 값을 조정하려면 아래 SQL을 실행

ALTER TABLE your_table AUTO_INCREMENT = 1;

이렇게 하면, 새로운 데이터가 추가될 때 number 값이 마지막 값 +1부터 다시 증가하게 된다

---

🔥 최종 정리

방법 장점 단점 추천 상황

새 테이블 생성 후 재정렬 (CREATE TABLE ... SELECT ...)	데이터 손실 없이 깔끔하게 정렬 가능	외래 키(FK) 있으면 조심해야 함	데이터가 많고, 안전한 방법이 필요할 때
UPDATE로 직접 수정 (UPDATE ... SET number = (@new_id := @new_id + 1))	빠르고 간단	AUTO_INCREMENT 값이 초기화되지 않음	데이터가 적을 때
AUTO_INCREMENT 초기화 (ALTER TABLE AUTO_INCREMENT = 1)	다음 삽입 시 1부터 다시 증가	기존 레코드는 변경되지 않음	새로운 데이터 입력 시 순차 정렬이 필요할 때

👉 가장 안전한 방법: 새로운 테이블을 생성하고 데이터를 다시 삽입하는 방식 (방법 1)

👉 빠르게 해결하려면: UPDATE를 사용해서 ID를 재정렬하는 방식 (방법 2)

어떤 방법이든 백업을 먼저 해두고 실행하는 게 좋다 🚀

select id,name,age,addr from usrtbl where addr='a' or addr= 'b' order by age asc / desc;