MySQLでファイル名が格納された列からファイル拡張子のみを取得する方法

MySQLで「AddTwoNumber.java」や「vector.cpp」のようなファイル名が文字列として保存されている列から、拡張子の部分だけを取り出したいケースはよくあります。このような場合には、substring_index() 関数を使用するのが最も簡単な方法です。

substring_index()関数の構文

substring_index() 関数は、指定した区切り文字を基準に文字列を分割し、その一部を取得できる関数です。第3引数に -1 を指定すると、区切り文字より後ろ側（右端）の部分文字列を返します。つまり、ドット（.）を区切り文字にすれば、ファイル名の末尾にある拡張子だけを抜き出せるという仕組みです。

SELECT SUBSTRING_INDEX(対象の列名, '.', -1) AS 別名
FROM テーブル名;

それでは、実際にテーブルを作成して動作を確認してみましょう。

サンプルテーブルの作成

まず、ファイル情報を保存するテーブルを作成します。

mysql> CREATE TABLE AllFiles
    -> (
    -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> UserName varchar(10),
    -> FileName varchar(100)
    -> );
Query OK, 0 rows affected (0.65 sec)

Id、UserName（ユーザー名）、FileName（ファイル名）の3つの列を持つシンプルなテーブルです。

テストデータの挿入

次に、INSERT文を使っていくつかのレコードを挿入します。拡張子の異なる複数のファイル名を登録しておきます。

mysql> INSERT INTO AllFiles(UserName,FileName) VALUES('Larry','AddTwoNumber.java');
Query OK, 1 row affected (0.18 sec)
mysql> INSERT INTO AllFiles(UserName,FileName) VALUES('Mike','AddTwoNumber.python');
Query OK, 1 row affected (0.15 sec)
mysql> INSERT INTO AllFiles(UserName,FileName) VALUES('Sam','MatrixMultiplication.c');
Query OK, 1 row affected (0.16 sec)
mysql> INSERT INTO AllFiles(UserName,FileName) VALUES('Carol','vector.cpp');
Query OK, 1 row affected (0.15 sec)

登録データの確認

SELECT文ですべてのレコードを表示してみます。

mysql> SELECT * FROM AllFiles;

実行結果は以下の通りです。

+----+----------+------------------------+
| Id | UserName | FileName               |
+----+----------+------------------------+
|  1 | Larry    | AddTwoNumber.java      |
|  2 | Mike     | AddTwoNumber.python    |
|  3 | Sam      | MatrixMultiplication.c |
|  4 | Carol    | vector.cpp             |
+----+----------+------------------------+
4 rows in set (0.00 sec)

拡張子のみを取得するクエリ

ここで本題の、ファイル名から拡張子だけを抽出するクエリを実行します。

mysql> SELECT SUBSTRING_INDEX(FileName,'.',-1) AS ALLFILENAMEEXTENSIONS FROM AllFiles;

実行すると、次のように各ファイル名から拡張子部分のみが抽出されます。

+-----------------------+
| ALLFILENAMEEXTENSIONS |
+-----------------------+
| java                  |
| python                |
| c                     |
| cpp                   |
+-----------------------+
4 rows in set (0.00 sec)

ポイントのまとめ

• SUBSTRING_INDEX(列名, '.', -1) のように、区切り文字にドットを指定し、位置に -1 を渡すことで右側の文字列（＝拡張子）を取得できます。
• 逆に 1 を指定すれば、ドットより左側（＝拡張子を除いたファイル名本体）を取得することも可能です。
• AS 句で別名を付けることで、結果セットの列名を見やすくできます。

このように substring_index() 関数を使えば、追加の処理を行うことなくSQLだけで簡単にファイル拡張子を抽出できます。ログ解析やファイル管理システムなど、拡張子ごとにデータを分類・集計したい場面でぜひ活用してください。