mysql - 从数据库中存储的完整路径中提取目录名

Question

如何从存储在数据库中的完整路径中仅提取目录名。

我需要将扫描文件插入另一个表中，该表仅按目录名进行过滤。

ex.

1. /mnt/HD/HD_a2/Watch Me.mp3
2. /mnt/HD/HD_a2/mymusic/sample.mp3
   • full path stored in tbl_files

插入 tbl_transcode (file_id)
Select file_id from tbl_files where UPPER(file_path) Like CONCAT((Select source_path from tbl_transcode_folder where trancode_folder_id = 1 ),'%') and media_type = 'video'

但是我只需要在tbl_transcode中插入目录名，我认为LIKE不好用。

例如。我只想搜索这个 /mnt/HD/HD_a2/Watch Me.mp3 的目录名，但是当我使用 /mnt/HD/HD_a2% 时，它会像这样返回我这。它还显示了其他目录名。

1. /mnt/HD/HD_a2/Watch Me.mp3
2. /mnt/HD/HD_a2/mymusic/sample.mp3

Answer 1

如果我的理解是正确的，您正在收集每个文件的目录名称，而不是文件目录的完整路径。

以下答案特定于 Oracle，我们在其中使用 regexp_substr 方法。

To get the full path with directory name we can use the following regular expression:

substr(REGEXP_SUBSTR(c1,'^(.*/)*'),0,instr(REGEXP_SUBSTR(c1,'^(.*/)*'),'/',-1)-1)

To get only the directory name we can use the following regular expression:

replace(regexp_substr(substr(REGEXP_SUBSTR(c1,'^(.*/)*'),0,instr(REGEXP_SUBSTR(c1,'^(.*/)*'),'/',-1)-1),'/[a-zA-Z_0-9]+$'),'/','')

这里c1是我最后使用的示例列。

因此，您更新后的查询将如下所示:

INSERT INTO tbl_transcode (file_id) 
Select file_id from tbl_files where UPPER(file_path) = upper(substr(REGEXP_SUBSTR(source_path,'^(.*/)*'),0,instr(REGEXP_SUBSTR(source_path,'^(.*/)*'),'/',-1)-1)) and media_type = 'video';

您还可以查看this sqlfiddle

更新(特定于 MySQL 的答案):

我们可以利用MySQL中的substring_index、locate和substring方法来实现目录名称的提取。

substring_index 方法根据字符出现的次数返回子字符串。这里感兴趣的字符是/。因此，调用 substring_index(c1,'/',-1) 返回给定目录路径中的文件名。

接下来我们可以使用locate来查找文件名位置的索引，并使用substr获取直到文件名开头的子字符串。

To get the file names:

从 t1 中选择 substring_index(c1,'/',-1);

To get the directory name with full path:

从 t1 中选择子字符串(c1,1,locate(substring_index(c1,'/',-1),c1)-1);

To get only the directory name:

从 t1 中选择 substring_index(substring(c1,1,locate(substring_index(c1,'/',-1),c1)-2),'/',-1);

因此，在您的案例的更新查询中将如下所示:

插入 tbl_transcode (file_id)
从 tbl_files 中选择 file_id，其中 UPPER(file_path) = upper(substring_index(substring(source_path,1,locate(substring_index(source_path,'/',-1),source_path)-2),'/',-1)) 和 media_type = '视频';

检查this fiddle 工作示例