MySQL 如何从文件名表中选择所有不同的文件名扩展名

您可以使用DISTINCT与SUBSTRING_INDEX（）一起提取文件名扩展名。首先让我们创建一个表 –

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

使用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)

使用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行（0.00秒）

以下是从文件名表中选择所有不同文件名扩展名的查询 –

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

这将产生以下输出 –

+----------------------------------+
| SUBSTRING_INDEX(FileName,'.',-1) |
+----------------------------------+
| java                             |
| png                              |
| c                                |
| py                               |
+----------------------------------+
4行（0.03秒）

阅读更多：MySQL 教程