MySQL에서 조회 결과의 각 행마다 일련번호를 매기는 새 열을 추가하려면 SELECT 문에서 사용자 정의 변수(전역 변수)를 활용하면 됩니다. 이 방법은 테이블 구조를 변경하지 않고도 쿼리만으로 순번을 부여할 수 있어 매우 유용합니다.
1. 예제 테이블 생성하기
먼저 실습에 사용할 테이블을 만들어 보겠습니다. 테이블 생성 쿼리는 다음과 같습니다:
mysql> create table addColumnToCountAsSerialNumber
-> (
-> Id int,
-> Name varchar(20),
-> Age int,
-> Salary int
-> );
Query OK, 0 rows affected (0.80 sec)
2. 데이터 삽입하기
INSERT 명령을 사용하여 테이블에 레코드를 몇 개 추가합니다. 쿼리는 다음과 같습니다:
mysql> insert into addColumnToCountAsSerialNumber values(10,'John',23,8576);
Query OK, 1 row affected (0.10 sec)
mysql> insert into addColumnToCountAsSerialNumber values(12,'Carol',21,4686);
Query OK, 1 row affected (0.14 sec)
mysql> insert into addColumnToCountAsSerialNumber values(9,'Mike',22,38585);
Query OK, 1 row affected (0.11 sec)
mysql> insert into addColumnToCountAsSerialNumber values(15,'Sam',25,38586);
Query OK, 1 row affected (0.16 sec)
mysql> insert into addColumnToCountAsSerialNumber values(20,'Bob',26,43544);
Query OK, 1 row affected (0.17 sec)
mysql> insert into addColumnToCountAsSerialNumber values(39,'Larry',29,485886);
Query OK, 1 row affected (0.16 sec)
3. 저장된 데이터 확인하기
SELECT 문으로 테이블의 모든 레코드를 조회합니다. 쿼리는 다음과 같습니다:
mysql> select *from addColumnToCountAsSerialNumber ;
실행 결과는 다음과 같습니다:
+------+-------+------+--------+
| Id | Name | Age | Salary |
+------+-------+------+--------+
| 10 | John | 23 | 8576 |
| 12 | Carol | 21 | 4686 |
| 9 | Mike | 22 | 38585 |
| 15 | Sam | 25 | 38586 |
| 20 | Bob | 26 | 43544 |
| 39 | Larry | 29 | 485886 |
+------+-------+------+--------+
6 rows in set (0.00 sec)
4. 일련번호를 매기는 쿼리 실행하기
각 행을 일련번호로 계산하는 쿼리는 다음과 같습니다. 서브쿼리에서 변수를 0으로 초기화한 후, 각 행을 읽을 때마다 값이 1씩 증가하는 원리입니다:
mysql> select @globalVar:=@globalVar+1 RowCount,tblColumns.*
-> from (select @globalVar:=0) initializeGlobalVariable,addColumnToCountAsSerialNumber tblColumns;
새로운 열 "RowCount"에 순번이 함께 출력된 결과는 다음과 같습니다:
+----------+------+-------+------+--------+
| RowCount | Id | Name | Age | Salary |
+----------+------+-------+------+--------+
| 1 | 10 | John | 23 | 8576 |
| 2 | 12 | Carol | 21 | 4686 |
| 3 | 9 | Mike | 22 | 38585 |
| 4 | 15 | Sam | 25 | 38586 |
| 5 | 20 | Bob | 26 | 43544 |
| 6 | 39 | Larry | 29 | 485886 |
+----------+------+-------+------+--------+
6 rows in set (0.06 sec)
참고: MySQL 8.0 이상에서는 ROW_NUMBER() 함수 사용 권장
MySQL 8.0부터는 윈도우 함수(Window Function)인 ROW_NUMBER()를 지원합니다. 사용자 정의 변수 방식은 최신 버전에서 공식적으로 권장되지 않으므로(deprecated), 최신 환경이라면 아래와 같이 작성하는 것이 더 안전하고 가독성이 좋습니다:
SELECT ROW_NUMBER() OVER () AS RowCount, t.*
FROM addColumnToCountAsSerialNumber t;
정렬 기준이 필요하다면 OVER (ORDER BY Id)처럼 ORDER BY 절을 함께 지정하면 원하는 순서대로 일련번호를 부여할 수 있습니다.