MySQL에서 테이블에 저장된 여러 행 중에서 임의의 행(random row) 하나만 골라 조회해야 하는 경우가 종종 있습니다. 이럴 때 PREPARE 문을 활용하면 동적으로 쿼리를 구성해 손쉽게 해결할 수 있습니다.
먼저 예제에 사용할 테이블을 생성해 보겠습니다.
mysql> create table DemoTable(
FirstName varchar(100),
CountryName varchar(100)
);
Query OK, 0 rows affected (0.53 sec)
테이블에 데이터 삽입하기
insert 명령을 사용해 몇 개의 레코드를 추가합니다.
mysql> insert into DemoTable values('Adam','US');
Query OK, 1 row affected (0.15 sec)
mysql> insert into DemoTable values('Chris','AUS');
Query OK, 1 row affected (0.15 sec)
mysql> insert into DemoTable values('Robert','UK');
Query OK, 1 row affected (0.32 sec)
저장된 전체 데이터 확인하기
select 문으로 테이블의 모든 레코드를 조회합니다.
mysql> select *from DemoTable;
실행 결과는 다음과 같습니다.
+-----------+-------------+ | FirstName | CountryName | +-----------+-------------+ | Adam | US | | Chris | AUS | | Robert | UK | +-----------+-------------+ 3 rows in set (0.00 sec)
단일 쿼리로 임의의 행 가져오기
아래 쿼리는 세 단계로 구성됩니다. 먼저 테이블의 전체 행 수에 rand() 값을 곱해 0부터 행 수 사이의 임의 정수를 만들고, 이 값을 LIMIT 절의 오프셋으로 사용하는 SELECT 문을 CONCAT으로 동적으로 생성한 뒤, PREPARE와 EXECUTE로 실행하는 방식입니다.
mysql> set @value := ROUND((select count(*) from DemoTable) * rand());
Query OK, 0 rows affected (0.00 sec)
mysql> set @query := CONCAT('select *from DemoTable LIMIT ', @value , ', 1');
Query OK, 0 rows affected (0.00 sec)
mysql> prepare myStatement from @query ;
Query OK, 0 rows affected (0.00 sec)
Statement prepared
mysql> execute myStatement;
+-----------+-------------+
| FirstName | CountryName |
+-----------+-------------+
| Chris | AUS |
+-----------+-------------+
1 row in set (0.00 sec)
mysql> deallocate prepare myStatement;
Query OK, 0 rows affected (0.00 sec)
위 실행 결과에서는 'Chris' 행이 무작위로 선택되었습니다. 쿼리를 실행할 때마다 서로 다른 행이 반환될 수 있으며, 마지막에는 DEALLOCATE PREPARE 문으로 준비된 문(statement)을 해제해 리소스를 정리했습니다.
참고로 간단한 경우에는 SELECT * FROM DemoTable ORDER BY RAND() LIMIT 1;처럼 ORDER BY RAND()를 사용하는 방법도 있습니다. 다만 이 방식은 테이블의 모든 행을 읽어 정렬하기 때문에 데이터가 많은 대형 테이블에서는 성능이 크게 저하될 수 있습니다. 따라서 위에서 소개한 LIMIT 오프셋 기반 방식이 더 효율적인 선택입니다.