MySQL에서 데이터를 정렬하면서 값 증가시키기

MySQL에서는 UPDATE 명령문과 사용자 정의 변수(user-defined variable)를 함께 사용하여, 레코드를 특정 기준으로 정렬하면서 동시에 값을 순차적으로 증가시킬 수 있습니다. 이 방법은 게시판 순서, 우선순위(Position) 등을 일괄 재정렬할 때 매우 유용합니다.

1. 테이블 생성하기

먼저 예제로 사용할 테이블을 생성해 보겠습니다.

mysql> create table DemoTable
(
    FirstName varchar(20),
    Position int
);
Query OK, 0 rows affected (0.71 sec)

FirstName(이름)과 Position(순번) 두 개의 컬럼을 가진 간단한 테이블입니다.

2. 샘플 데이터 삽입하기

INSERT 명령문을 사용하여 테이블에 몇 개의 레코드를 추가합니다.

mysql> insert into DemoTable values('Chris',100);
Query OK, 1 row affected (0.12 sec)
mysql> insert into DemoTable values('Robert',120);
Query OK, 1 row affected (0.23 sec)
mysql> insert into DemoTable values('David',130);
Query OK, 1 row affected (0.16 sec)

3. 현재 데이터 확인하기

SELECT 문으로 테이블의 모든 레코드를 조회해 보겠습니다.

mysql> select *from DemoTable;

실행 결과는 다음과 같습니다.

+-----------+----------+
| FirstName | Position |
+-----------+----------+
| Chris     | 100      |
| Robert    | 120      |
| David     | 130      |
+-----------+----------+
3 rows in set (0.00 sec)

4. 정렬하면서 값 증가시키기

이제 핵심 부분입니다. 먼저 사용자 정의 변수 @myPosition에 시작값 100을 설정하고, UPDATE 문에서 ORDER BY 절과 함께 이 변수를 활용하여 Position 값을 10씩 증가시켜 보겠습니다.

mysql> SET @myPosition := 100;
Query OK, 0 rows affected (0.00 sec)
mysql> update DemoTable set Position = @myPosition:=@myPosition+10 ORDER BY
Position;
Query OK, 3 rows affected (0.17 sec)
Rows matched: 3 Changed: 3 Warnings: 0

동작 원리: MySQL은 UPDATE 실행 시 ORDER BY 절에 지정된 순서대로 행을 처리합니다. 각 행이 업데이트될 때마다 @myPosition := @myPosition + 10 표현식이 평가되어 변수 값이 10씩 증가하고, 그 결과가 해당 행의 Position 컬럼에 저장됩니다.

5. 변경 결과 확인하기

업데이트 후 테이블을 다시 조회해 보겠습니다.

mysql> select *from DemoTable;