MySQL에서 정렬 순서대로 열에 순번(INT) 값을 업데이트하기
MySQL에서는 사용자 변수(user variable)를 활용하면 특정 열을 기준으로 정렬한 순서대로 다른 열에 1, 2, 3처럼 증가하는 INT 값을 채워 넣을 수 있습니다. 예를 들어, 주문 금액(OrderAmount)이 비어 있는 테이블에 국가명 순서대로 순번을 자동으로 부여하고 싶을 때 유용합니다.
기본 문법
먼저 변수를 0으로 초기화한 뒤, UPDATE 문에서 변수를 1씩 증가시키며 열에 대입합니다. ORDER BY 절로 정렬 기준을 지정하면 해당 순서대로 번호가 매겨집니다.
SET @yourVariableName = 0; UPDATE yourTableName SET yourColumnName = (@yourVariableName := @yourVariableName + 1) ORDER BY yourColumnName ASC;
예제 테이블 생성
문법을 이해하기 위해 실습용 테이블을 만들어 보겠습니다.
mysql> CREATE TABLE updateColumnDemo
-> (
-> Id INT,
-> OrderCountryName VARCHAR(100),
-> OrderAmount INT
-> );
Query OK, 0 rows affected (1.76 sec)데이터 삽입
INSERT 명령으로 몇 개의 레코드를 추가합니다. 이때 OrderAmount 열은 아직 값이 없으므로 NULL 상태로 남게 됩니다.
mysql> INSERT INTO updateColumnDemo(Id, OrderCountryName) VALUES(10, 'US'); Query OK, 1 row affected (0.46 sec) mysql> INSERT INTO updateColumnDemo(Id, OrderCountryName) VALUES(20, 'UK'); Query OK, 1 row affected (0.98 sec) mysql> INSERT INTO updateColumnDemo(Id, OrderCountryName) VALUES(30, 'AUS'); Query OK, 1 row affected (0.77 sec) mysql> INSERT INTO updateColumnDemo(Id, OrderCountryName) VALUES(40, 'France'); Query OK, 1 row affected (1.58 sec)
현재 데이터 확인
SELECT 문으로 전체 레코드를 조회해 보겠습니다.
mysql> SELECT * FROM updateColumnDemo;
실행 결과는 다음과 같습니다.
+------+------------------+-------------+ | Id | OrderCountryName | OrderAmount | +------+------------------+-------------+ | 10 | US | NULL | | 20 | UK | NULL | | 30 | AUS | NULL | | 40 | France | NULL | +------+------------------+-------------+ 4 rows in set (1.00 sec)
순서에 따라 열 업데이트 실행
이제 변수를 초기화하고, ORDER BY와 함께 UPDATE 문을 실행하여 각 행에 순차적인 번호를 부여합니다.
mysql> SET @sequenceNumber = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> UPDATE updateColumnDemo
-> SET OrderAmount = (@sequenceNumber := @sequenceNumber + 1)
-> ORDER BY OrderAmount ASC;
Query OK, 4 rows affected (0.25 sec)
Rows matched: 4 Changed: 4 Warnings: 0업데이트 결과 확인
다시 테이블을 조회하면 각 행에 1부터 시작하는 순번이 순서대로 저장된 것을 확인할 수 있습니다.
mysql> SELECT * FROM updateColumnDemo;
+------+------------------+-------------+ | Id | OrderCountryName | OrderAmount | +------+------------------+-------------+ | 10 | US | 1 | | 20 | UK | 2 | | 30 | AUS | 3 | | 40 | France | 4 | +------+------------------+-------------+ 4 rows in set (0.00 sec)
참고 사항
위 방식은 MySQL 5.x 버전에서 널리 사용되던 기법입니다. 다만 MySQL 8.0 이상에서는 윈도우 함수(Window Function)를 사용하는 것이 더 권장됩니다. ROW_NUMBER() 함수를 활용하면 별도의 변수 없이도 깔끔하게 순번을 생성할 수 있으며, 코드의 가독성과 유지보수성도 향상됩니다.
SELECT Id,
OrderCountryName,
ROW_NUMBER() OVER (ORDER BY OrderCountryName ASC) AS OrderAmount
FROM updateColumnDemo;사용자 변수를 이용한 UPDATE 방식은 공식적으로 보장된 동작이 아니므로, 새 프로젝트에서는 가능한 한 윈도우 함수 기반의 방법을 사용하는 것이 좋습니다.