IP 주소처럼 점(.)으로 구분된 문자열 데이터를 다룰 때, 전체 값이 아닌 특정 부분만 추출하고 싶은 경우가 자주 있습니다. 예를 들어 192.168.130.67과 같은 IP 주소에서 마지막 옥텟을 제외한 192.168.130 부분만 가져와서 그룹별로 개수를 집계하려면 어떻게 해야 할까요?
이런 문자열 조작에는 MySQL의 SUBSTRING_INDEX() 함수가 가장 적합합니다. 이 함수는 지정한 구분자를 기준으로 문자열을 나눈 후, 원하는 위치까지의 부분 문자열을 반환합니다.
1. 테이블 생성하기
먼저 실습에 사용할 테이블을 만들어 보겠습니다.
mysql> create table DemoTable
(
SystemIpAddress text
);
Query OK, 0 rows affected (0.58 sec)2. 샘플 데이터 삽입하기
INSERT 명령을 사용해 IP 주소 레코드를 몇 개 추가합니다.
mysql> insert into DemoTable values('192.168.130.67');
Query OK, 1 row affected (0.09 sec)
mysql> insert into DemoTable values('192.168.130.87');
Query OK, 1 row affected (0.13 sec)
mysql> insert into DemoTable values('192.168.131.47');
Query OK, 1 row affected (0.31 sec)
mysql> insert into DemoTable values('192.168.134.50');
Query OK, 1 row affected (0.12 sec)
mysql> insert into DemoTable values('192.168.131.12');
Query OK, 1 row affected (0.21 sec)3. 저장된 데이터 확인하기
SELECT 문으로 테이블의 모든 레코드를 조회해 보겠습니다.
mysql> select *from DemoTable;
실행 결과는 다음과 같습니다.
+-----------------+ | SystemIpAddress | +-----------------+ | 192.168.130.67 | | 192.168.130.87 | | 192.168.131.47 | | 192.168.134.50 | | 192.168.131.12 | +-----------------+ 5 rows in set (0.00 sec)
4. SUBSTRING_INDEX()로 그룹별 개수 집계하기
이제 핵심 쿼리입니다. SUBSTRING_INDEX(컬럼명, '.', 3)은 점(.)을 구분자로 하여 앞에서부터 세 번째 구분자까지의 문자열, 즉 IP 주소의 첫 세 옥텟(192.168.130)을 반환합니다. 이 값을 기준으로 GROUP BY와 COUNT(*)를 조합하면 네트워크 대역별 IP 개수를 손쉽게 집계할 수 있습니다.
mysql> select substring_index(tbl.SystemIpAddress, '.', 3) , count(*) as Total from DemoTable tbl
group by substring_index(tbl.SystemIpAddress, '.', 3)
order by Total desc limit 5;실행 결과는 다음과 같습니다.
+----------------------------------------------+-------+ | substring_index(tbl.SystemIpAddress, '.', 3) | Total | +----------------------------------------------+-------+ | 192.168.130 | 2 | | 192.168.131 | 2 | | 192.168.134 | 1 | +----------------------------------------------+-------+ 3 rows in set (0.00 sec)
결과 분석
출력 결과를 보면 192.168.130 대역에는 2개, 192.168.131 대역에도 2개, 192.168.134 대역에는 1개의 IP 주소가 있는 것을 확인할 수 있습니다. ORDER BY Total DESC 덕분에 개수가 많은 순서대로 정렬되어 출력됩니다.
참고: SUBSTRING_INDEX() 동작 원리
SUBSTRING_INDEX(str, delim, count)의 세 번째 인자인 count 값에 따라 동작이 달라집니다.
- 양수(count > 0): 왼쪽(앞)에서부터 count번째 구분자 앞까지의 문자열을 반환합니다. 예:
SUBSTRING_INDEX('192.168.130.67', '.', 3)→192.168.130 - 음수(count < 0): 오른쪽(뒤)에서부터 count번째 구분자 뒤의 문자열을 반환합니다. 예:
SUBSTRING_INDEX('192.168.130.67', '.', -1)→67
이처럼 SUBSTRING_INDEX()를 활용하면 복잡한 파싱 로직 없이도 SQL문 하나로 IP 주소, 도메인, 파일 경로 등 구분자로 나뉜 문자열 데이터를 유연하게 가공할 수 있습니다.