MySQL 쿼리 결과에 일련번호(행 번호) 생성하는 방법

MySQL 쿼리 결과에 일련번호(행 번호) 생성하기

MySQL에서 조회된 결과에 일련번호, 즉 행 번호를 부여하려면 사용자 변수(user variable)를 활용하면 됩니다. 기본 문법은 다음과 같습니다.

SELECT @변수명 := @변수명 + 1 AS 별칭,
      컬럼명1, 컬럼명2, 컬럼명3, ...N
FROM 테이블명,
    (SELECT @변수명 := 0) AS 변수초기화;

이 문법의 핵심은 크로스 조인(cross join)을 통해 변수를 0으로 초기화하는 서브쿼리를 함께 사용하고, 각 행을 읽을 때마다 변수 값을 1씩 증가시키는 방식입니다.

1단계: 예제 테이블 생성

문법을 이해하기 위해 먼저 학생 정보 테이블을 생성해 보겠습니다.

mysql> create table tblStudentInformation
    -> (
    -> StudentName varchar(20),
    -> StudentAge int,
    -> StudentMathMarks int
    -> );
Query OK, 0 rows affected (0.68 sec)

2단계: 데이터 삽입

INSERT 명령으로 몇 개의 레코드를 추가합니다.

mysql> insert into tblStudentInformation values('Carol',23,89);
Query OK, 1 row affected (0.18 sec)

mysql> insert into tblStudentInformation values('Bob',25,92);
Query OK, 1 row affected (0.22 sec)

mysql> insert into tblStudentInformation values('John',21,82);
Query OK, 1 row affected (0.15 sec)

mysql> insert into tblStudentInformation values('David',26,98);
Query OK, 1 row affected (0.21 sec)

3단계: 전체 레코드 조회

SELECT 문으로 테이블의 모든 레코드를 확인합니다.

mysql> select * from tblStudentInformation;

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

+-------------+------------+------------------+
| StudentName | StudentAge | StudentMathMarks |
+-------------+------------+------------------+
| Carol      |        23 |              89 |
| Bob        |        25 |              92 |
| John       |        21 |              82 |
| David      |        26 |              98 |
+-------------+------------+------------------+
4 rows in set (0.00 sec)

4단계: 일련번호 생성 쿼리 실행

이제 사용자 변수를 이용해 각 행에 일련번호를 부여하는 쿼리를 실행해 보겠습니다.

mysql> SELECT @serialNumber := @serialNumber + 1 AS yourSerialNumber,
    -> StudentName, StudentAge, StudentMathMarks
    -> FROM tblStudentInformation,
    -> (SELECT @serialNumber := 0) AS serialNumber;

아래 출력 결과에서 각 행마다 순차적으로 증가하는 일련번호가 함께 표시되는 것을 확인할 수 있습니다.

+------------------+-------------+------------+------------------+
| yourSerialNumber | StudentName | StudentAge | StudentMathMarks |
+------------------+-------------+------------+------------------+|               2 | Bob        |        25 |              92 |
|               3 | John       |        21 |              82 |
|               4 | David      |        26 |              98 |
+------------------+-------------+------------+------------------+
4 rows in set (0.00 sec)

참고: MySQL 8.0 이상에서는 ROW_NUMBER() 함수 권장

MySQL 8.0부터는 윈도우 함수인 ROW_NUMBER()를 사용하는 것이 더 좋습니다. 코드가 간결하고 가독성이 뛰어나며, 정렬 기준도 명확하게 지정할 수 있습니다.

SELECT ROW_NUMBER() OVER (ORDER BY StudentMathMarks DESC) AS yourSerialNumber,
      StudentName, StudentAge, StudentMathMarks
FROM tblStudentInformation;

사용자 변수를 이용한 방식은 MySQL 5.x 버전에서 유용하게 쓸 수 있으며, 최신 버전에서는 ROW_NUMBER() 윈도우 함수를 우선적으로 고려하는 것이 좋습니다.