如何从 MySQL 的文件名表中选择所有不同的文件扩展名？
您可以使用DISTINCT和SUBSTRING_INDEX()来提取文件扩展名。让我们首先创建一个表-
mysql> create table DemoTable ( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, FileName text );
使用插入命令在表中插入记录-
mysql> insert into DemoTable(FileName) values('AddTwoValue.java');
mysql> insert into DemoTable(FileName) values('Image1.png');
mysql> insert into DemoTable(FileName) values('MultiplicationOfTwoNumbers.java');
mysql> insert into DemoTable(FileName) values('Palindrome.c');
mysql> insert into DemoTable(FileName) values('FoodCart.png');
mysql> insert into DemoTable(FileName) values('Permutation.py');使用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)
以下是从文件名表中选择所有不同文件扩展名的查询-
mysql> SELECT DISTINCT SUBSTRING_INDEX(FileName,'.',-1) FROM DemoTable;
+----------------------------------+ | SUBSTRING_INDEX(FileName,'.',-1) | +----------------------------------+ | java | | png | | c | | py | +----------------------------------+ 4 rows in set (0.03 sec)