mysql - 从 select mysql 中的 url 中提取 id

Question

我有一个表格，比如说数据。

数据表包含id和url。

id  url
1   https://gourmet.com/detail/12345/?where=tokyo 
2   https://dental-clinic.com/source/detail/4123/?site_code=tokyo
3   https://dental-hospital.com/detail/index/sdf2344/?where=tokyo
4   https://gourmet.com/detail/234ff/?where=tokyo
5   https://dental-clinic.com/source/detail/4123ss/?site_code=tokyo
6   https://gourmet.com/detail/43543//?where=tokyo 

我只想在选择期间获取 url 内的 id。就像我已经有了正则表达式模式，但我想知道如何在“SELECT”mysql 部分使用它。

我有 3 种网址模式。

注意:url 中的 ID 是字母数字。

注意:有些网址是这样的。远端有双斜杠:

https://gourmet.com/detail/12345//?where=tokyo

期望的输出:

id  api_id  url
1   12345   https://gourmet.com/detail/12345/?where=tokyo 
2   4123    https://dental-clinic.com/source/detail/4123/?site_code=tokyo
3   sdf2344 https://dental-hospital.com/detail/index/sdf2344/?where=tokyo
4   234ff   https://gourmet.com/detail/234ff/?where=tokyo
5   4123ss  https://dental-clinic.com/source/detail/4123ss/?site_code=tokyo
6   43543   https://gourmet.com/detail/43543//?where=tokyo 

选择 sql 应如下所示:

SELECT (regex?) as api_id, id, url FROM data;

Answer 1

我们可以使用两个对SUBSTRING_INDEX的链式调用来处理这个问题:

SELECT
    id,
    SUBSTRING_INDEX(SUBSTRING_INDEX(url, '/', -2), '/', 1) AS api_id,
    url
FROM yourTable;

Demo

第一次使用 -2 调用 SUBSTRING_INDEX 会删除 URL 中除最后一个路径元素和查询字符串之外的所有内容。第二次调用 1 会删除查询字符串，留下 API id。

根据下面的评论推测，如果有时路径分隔符显示为两个正斜杠，那么您可以将所有 // 替换为单个 / 正向斜杠:

SELECT
    id,
    SUBSTRING_INDEX(
        SUBSTRING_INDEX(REPLACE(url, '//', '/'), '/', -2), '/', 1) AS api_id,
    url
FROM yourTable;