MySQL 파일명 테이블에서 모든 고유한 확장자를 추출하는 방법 (DISTINCT + SUBSTRING_INDEX)

MySQL에서 파일 이름이 저장된 테이블로부터 고유한(중복 없는) 확장자만 추출하려면 DISTINCT 키워드와 SUBSTRING_INDEX() 함수를 함께 사용하면 됩니다. SUBSTRING_INDEX()는 지정한 구분자를 기준으로 문자열을 잘라내는 함수로, 세 번째 인자에 -1을 넣으면 마지막 마침표(.) 뒤의 문자열, 즉 파일 확장자를 그대로 가져올 수 있습니다.

1단계: 샘플 테이블 생성하기

먼저 예제에 사용할 테이블을 만들어 보겠습니다.

mysql> create table DemoTable
    (
    Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    FileName text
    );
Query OK, 0 rows affected (0.75 sec)

2단계: 파일 이름 데이터 삽입하기

INSERT 명령을 사용해 서로 다른 확장자를 가진 파일 이름들을 테이블에 추가합니다.

mysql> insert into DemoTable(FileName) values('AddTwoValue.java');
Query OK, 1 row affected (0.16 sec)
mysql> insert into DemoTable(FileName) values('Image1.png');
Query OK, 1 row affected (0.16 sec)
mysql> insert into DemoTable(FileName) values('MultiplicationOfTwoNumbers.java');
Query OK, 1 row affected (0.16 sec)
mysql> insert into DemoTable(FileName) values('Palindrome.c');
Query OK, 1 row affected (0.16 sec)
mysql> insert into DemoTable(FileName) values('FoodCart.png');
Query OK, 1 row affected (0.25 sec)
mysql> insert into DemoTable(FileName) values('Permutation.py');
Query OK, 1 row affected (0.18 sec)

3단계: 저장된 데이터 확인하기

SELECT 문으로 테이블의 전체 레코드를 조회합니다.

mysql> select * from DemoTable;

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

+----+---------------------------------+
| Id | FileName                        |
+----+---------------------------------+
| 1  | AddTwoValue.java                |
| 2  | Image1.png                      |
| 3  | MultiplicationOfTwoNumbers.java |
| 4  | Palindrome.c                    |
| 5  | FoodCart.png                    |
| 6  | Permutation.py                  |
+----+---------------------------------+
6 rows in set (0.00 sec)

4단계: 고유한 확장자만 조회하기

파일 이름 테이블에서 모든 고유한 확장자를 선택하는 쿼리는 다음과 같습니다.

mysql> SELECT DISTINCT SUBSTRING_INDEX(FileName,'.',-1) FROM DemoTable;

위 쿼리를 실행하면 다음과 같은 결과가 출력됩니다.

+----------------------------------+
| SUBSTRING_INDEX(FileName,'.',-1) |
+----------------------------------+
| java                             |
| png                              |
| c                                |
| py                               |
+----------------------------------+
4 rows in set (0.03 sec)

결과를 살펴보면 중복되던 java와 png 확장자가 각각 한 번씩만 표시되고, 총 4개의 고유한 확장자가 반환된 것을 확인할 수 있습니다. 이처럼 DISTINCT와 SUBSTRING_INDEX()를 조합하면 별도의 확장자 컬럼을 두지 않고도 파일 확장자 정보를 간편하게 추출하고 집계할 수 있습니다.