<think>我们有一个URL字段，需要从中提取域名部分。例如，对于URL 'https://www.example.com/path/to/page?query=1'，我们希望提取出'www.example.com'。
步骤分析：
1. 常见的URL格式：协议://域名[:端口][/路径][?查询][#片段]
2. 我们需要提取域名部分（可能包含子域名，如www.example.com）
3. 使用MySQL的字符串函数：SUBSTRING, LOCATE等。
思路：
1. 首先，去除协议部分（如'http://', 'https://'）。注意，有些URL可能没有协议部分。
2. 然后，提取域名。域名之后通常是第一个'/'或':'（如果有端口的话）或字符串结束。
具体步骤：
a. 找到协议结束位置：如果存在'://'，则域名从'://'之后开始；否则从字符串开头开始。
b. 在域名开始位置之后，查找第一个出现的'/'或':'（端口分隔符）或'?'（查询参数开始）或'#'（片段开始）的位置。但实际上，我们只需要找到第一个不属于域名的字符位置（即路径分隔符、查询参数起始符等）。
c. 截取从域名开始位置到上述分隔符位置之间的字符串。
注意：域名中可能包含端口号（如:8080），但我们通常不需要端口，所以如果遇到冒号，则域名部分到冒号结束。
因此，我们可以这样设计：
1. 使用LOCATE('://', url) 查找协议位置。如果找到，则域名起始位置为协议位置+3；否则从1开始。
2. 从域名起始位置开始，查找第一个出现的'/', ':', '?', '#'的位置。注意，这些字符中可能不会全部出现，所以我们要找其中出现位置最小的那个（且大于0，即存在）。
但是，MySQL的LOCATE只能查找一个子串的位置。我们可以使用多个LOCATE分别查找这些分隔符，然后取最小值（注意：如果某个分隔符不存在，则返回0，我们需要排除0的情况，然后取非0的最小值）。
然而，我们可以使用一个技巧：将这些分隔符组合成一个字符串，然后逐个比较位置？但MySQL没有直接提供一次查找多个字符中任意一个的函数。因此，我们可以分别查找每个分隔符的位置，然后取最小值（非0的最小值，如果都是0则取字符串长度）。
具体步骤：
1. 计算域名起始位置：start_pos = IFNULL(NULLIF(LOCATE('://', url), 0) + 3, 1)
2. 从start_pos开始，分别查找以下字符的位置（如果找不到则返回0）：
- 斜杠 '/' 的位置：LOCATE('/', url, start_pos)
- 冒号 ':' 的位置：LOCATE(':', url, start_pos) 注意：这里要小心，因为协议部分已经去掉了，所以冒号可能是端口分隔符。但是，在域名中也可能有冒号（如IPv6地址），但这种情况较少，我们先不考虑IPv6。
- 问号 '?' 的位置：LOCATE('?', url, start_pos)
- 井号 '#' 的位置：LOCATE('#', url, start_pos)
3. 然后，取这些位置的最小值（但要排除0值）。如果所有位置都是0，则说明域名一直延续到字符串末尾，那么结束位置就是字符串长度。
但是，如果某个位置为0，我们无法直接取最小值（因为0表示不存在），所以我们可以用NULLIF将0转换为NULL，然后取最小值（因为MIN函数会忽略NULL）。或者，我们可以用一个很大的数（比如字符串长度+1）来代替0，然后取最小值，再与字符串长度比较。
更简单的方法：使用LEAST函数，但LEAST如果遇到NULL则返回NULL，所以我们可以用COALESCE来处理。
步骤：
1. 计算start_pos。
2. 计算每个分隔符的位置（从start_pos开始查找）：
- pos1 = LOCATE('/', url, start_pos)
- pos2 = LOCATE(':', url, start_pos)
- pos3 = LOCATE('?', url, start_pos)
- pos4 = LOCATE('#', url, start_pos)
3. 将0转换为NULL（因为0表示没找到，我们不想考虑）：
- 然后取最小值：min_pos = LEAST(COALESCE(pos1,999999), COALESCE(pos2,999999), COALESCE(pos3,999999), COALESCE(pos4,999999))
这里999999是一个很大的数，如果所有分隔符都没找到，则min_pos会是一个很大的数，然后我们将其与字符串长度比较，取较小的那个（因为如果没找到分隔符，则应该取到字符串末尾）。
但是，更合理的方式是：如果min_pos大于字符串长度，则取字符串长度，否则取min_pos。但注意，我们设置的大数应该大于字符串长度（假设字符串长度不超过一定值，比如10000）。
4. 然后，截取字符串：SUBSTRING(url, start_pos, min_pos - start_pos)
但是，还有一种情况：如果min_pos等于0（在转换为NULL后，我们用了COALESCE，所以不会出现0），那么min_pos会是我们设置的大数，然后我们将其与字符串长度比较，取字符串长度作为结束位置。
然而，我们可以简化：如果min_pos大于字符串长度，则结束位置就是字符串长度+1（因为截取长度需要是结束位置-开始位置，而结束位置应该是字符串的末尾，即从start_pos到字符串末尾，所以截取长度就是LENGTH(url) - start_pos + 1？不对，SUBSTRING的第三个参数是长度，不是结束位置索引）。
所以，我们需要计算截取的长度：
length =
CASE
WHEN min_pos > LENGTH(url) THEN LENGTH(url) - start_pos + 1
ELSE min_pos - start_pos
END
但是，SUBSTRING的第三个参数是长度，所以应该是：min_pos - start_pos（如果min_pos是结束位置的索引，那么从start_pos到min_pos-1，所以长度是min_pos - start_pos）。如果min_pos大于字符串长度，那么长度就是LENGTH(url) - start_pos + 1？不对，因为SUBSTRING函数如果第三个参数超过剩余长度，会自动截取到末尾。所以我们可以直接使用：
SUBSTRING(url, start_pos, min_pos - start_pos) 如果min_pos大于字符串长度，那么min_pos - start_pos会大于剩余长度，所以会自动截取到末尾。
但是，我们也可以这样：如果min_pos比字符串长度大，那么我们就直接取从start_pos到结尾。但SUBSTRING的第三个参数如果是一个很大的数，它会自动处理到字符串末尾，所以我们可以直接使用min_pos - start_pos，即使这个值很大。
因此，我们可以这样写：
但是，注意：min_pos可能是999999，而字符串长度可能小于999999，那么min_pos - start_pos可能是一个很大的数，但SUBSTRING函数会处理这种情况，只截取到字符串末尾。
所以，完整的表达式：
我们使用嵌套的表达式：
先计算start_pos:
SET @start_pos = IF(LOCATE('://', url) > 0, LOCATE('://', url) + 3, 1);
然后，计算每个位置（注意：从start_pos开始查找）：
SET @pos_slash = LOCATE('/', url, @start_pos);
SET @pos_colon = LOCATE(':', url, @start_pos);
SET @pos_question = LOCATE('?', url, @start_pos);
SET @pos_hash = LOCATE('#', url, @start_pos);
然后，计算最小位置（非0的最小值，如果都是0则用一个大数代替）：
SET @min_pos = LEAST(
IF(@pos_slash=0, 999999, @pos_slash),
IF(@pos_colon=0, 999999, @pos_colon),
IF(@pos_question=0, 999999, @pos_question),
IF(@pos_hash=0, 999999, @pos_hash)
);
然后，截取：
SUBSTRING(url, @start_pos, @min_pos - @start_pos)
但是，我们也可以用一个查询语句完成，不使用变量。
所以，我们可以写成：
SELECT
SUBSTRING(
url,
@start_pos := IF(LOCATE('://', url) > 0, LOCATE('://', url) + 3, 1),
IF(
(@min_pos := LEAST(
IF(LOCATE('/', url, @start_pos)=0, 999999, LOCATE('/', url, @start_pos)),
IF(LOCATE(':', url, @start_pos)=0, 999999, LOCATE(':', url, @start_pos)),
IF(LOCATE('?', url, @start_pos)=0, 999999, LOCATE('?', url, @start_pos)),
IF(LOCATE('#', url, @start_pos)=0, 999999, LOCATE('#', url, @start_pos))
)) = 999999,
LENGTH(url) - @start_pos + 1, -- 这里实际上不需要+1，因为SUBSTRING的第三个参数是长度，从@start_pos开始，取到末尾的长度应该是LENGTH(url)-@start_pos+1？不对，因为字符串索引从1开始，从第@start_pos字符开始，到最后一个字符，个数是LENGTH(url)-@start_pos+1。但是，如果@start_pos大于字符串长度，则返回空字符串。
@min_pos - @start_pos
)
) AS domain
但是，这样写非常复杂，而且我们注意到，如果@min_pos为999999，那么说明后面没有分隔符，那么就应该截取到末尾。而截取到末尾可以直接用SUBSTRING(url, @start_pos)（省略第三个参数）或者指定一个足够大的数。
因此，我们可以简化：不需要判断@min_pos是否为999999，直接使用@min_pos - @start_pos作为长度，因为如果@min_pos是999999，那么长度就会很大，SUBSTRING函数会自动截取到末尾。
所以，更简洁的写法：
SELECT
SUBSTRING(
url,
IF(LOCATE('://', url) > 0, LOCATE('://', url) + 3, 1),
LEAST(
IFNULL(NULLIF(LOCATE('/', url, IF(LOCATE('://', url) > 0, LOCATE('://', url) + 3, 1)), 0), 999999),
IFNULL(NULLIF(LOCATE(':', url, IF(LOCATE('://', url) > 0, LOCATE('://', url) + 3, 1)), 0), 999999),
IFNULL(NULLIF(LOCATE('?', url, IF(LOCATE('://', url) > 0, LOCATE('://', url) + 3, 1)), 0), 999999),
IFNULL(NULLIF(LOCATE('#', url, IF(LOCATE('://', url) > 0, LOCATE('://', url) + 3, 1)), 0), 999999)
) - IF(LOCATE('://', url) > 0, LOCATE('://', url) + 3, 1)
) AS domain
但是，这样写重复计算了多次，而且很冗长。
我们可以使用子查询或者变量来避免重复计算。但是，在MySQL中，我们可以使用内联视图（子查询）来定义变量，或者使用重复表达式。
为了可读性，我们可以使用用户变量，或者将开始位置计算一次并存储。
另外，注意：如果URL中没有协议部分，那么LOCATE('://', url)返回0，然后我们使用1作为开始位置。
但是，还有一种情况：URL可能是以"//"开头的（协议相对URL），例如"//www.example.com/path"。对于这种情况，我们通常认为域名从"//"之后开始。所以，我们也要考虑这种情况。
因此，我们需要扩展：
1. 如果存在'://'，则开始位置为LOCATE('://', url)+3
2. 否则，如果存在'//'，则开始位置为LOCATE('//', url)+2
3. 否则，开始位置为1
所以，修改开始位置的计算：
SET @start_pos =
CASE
WHEN LOCATE('://', url) > 0 THEN LOCATE('://', url) + 3
WHEN LOCATE('//', url) > 0 THEN LOCATE('//', url) + 2
ELSE 1
END;
然后，再计算分隔符位置。
考虑到这些，我们写一个完整的SQL表达式：
假设表名为`url_table`，字段名为`url`，我们提取域名：
SELECT
url,
SUBSTRING(
url,
@start_pos := CASE
WHEN LOCATE('://', url) > 0 THEN LOCATE('://', url) + 3
WHEN LOCATE('//', url) > 0 THEN LOCATE('//', url) + 2
ELSE 1
END,
LEAST(
IFNULL(NULLIF(LOCATE('/', url, @start_pos), 0), 999999),
IFNULL(NULLIF(LOCATE(':', url, @start_pos), 0), 999999),
IFNULL(NULLIF(LOCATE('?', url, @start_pos), 0), 999999),
IFNULL(NULLIF(LOCATE('#', url, @start_pos), 0), 999999)
) - @start_pos
) AS domain
FROM url_table;
注意：在同一个SELECT中，MySQL允许使用用户变量，但是要注意顺序。这里我们先计算@start_pos，然后在同一个表达式中使用它。
但是，在SELECT语句中，用户变量的赋值顺序在MySQL中可能不是确定的（尤其是当有多个行时，可能会出现问题）。所以，为了避免使用用户变量，我们可以将开始位置的计算重复写几次（虽然冗长，但安全）。
或者，我们可以使用子查询：
SELECT
url,
SUBSTRING(
url,
start_pos,
LEAST(
IFNULL(NULLIF(LOCATE('/', url, start_pos), 0), 999999),
IFNULL(NULLIF(LOCATE(':', url, start_pos), 0), 999999),
IFNULL(NULLIF(LOCATE('?', url, start_pos), 0), 999999),
IFNULL(NULLIF(LOCATE('#', url, start_pos), 0), 999999)
) - start_pos
) AS domain
FROM (
SELECT
url,
CASE
WHEN LOCATE('://', url) > 0 THEN LOCATE('://', url) + 3
WHEN LOCATE('//', url) > 0 THEN LOCATE('//', url) + 2
ELSE 1
END AS start_pos
FROM url_table
) AS t;
这样更清晰，而且避免了用户变量的问题。
但是，注意：如果URL中在域名之后没有任何分隔符（即整个URL就是域名），那么LEAST函数会得到999999，然后减去start_pos，得到一个很大的长度，SUBSTRING函数会截取到字符串末尾，所以是没问题的。
测试用例：
1. 'https://www.example.com/path' -> 域名: 'www.example.com'
2. 'http://example.com' -> 'example.com'
3. '//example.com' -> 'example.com'
4. 'example.com' -> 'example.com' （注意：这里没有协议，也没有'//'，所以从1开始。但是，在查找分隔符时，找不到'/'等，所以截取整个字符串）
5. 'https://example.com:8080/path' -> 域名应该到冒号结束，所以是'example.com'
6. 'https://example.com/path?query=1' -> 'example.com'
7. 'https://example.com#anchor' -> 'example.com'
但是，有一个特殊情况：如果URL是'https://example.com'，那么后面没有分隔符，所以会截取到末尾，正确。
另一个特殊情况：URL中包含端口，如'https://example.com:8080'，那么域名部分应该到冒号结束，所以是'example.com'。
但是，IPv6地址的URL可能会包含方括号，如'http://[2001:db8::1]:8080'，这种情况比较复杂，我们这里暂时不考虑。
因此，这个查询可以满足大多数情况。
但是，我们还可以优化：如果域名后面紧跟着的是冒号（端口），我们只想要域名，所以冒号的位置是我们要找的。同样，如果后面有路径（/）或查询（?）或片段（#），我们都要截断。
所以，上面的方法已经考虑了这些分隔符。
但是，注意：在域名中可能包含点（.），但点不是分隔符，所以我们不需要考虑。
最后，我们写出SQL语句：
SELECT
url,
SUBSTRING(
url,
start_pos,
LEAST(
IFNULL(NULLIF(LOCATE('/', url, start_pos), 0), 999999),
IFNULL(NULLIF(LOCATE(':', url, start_pos), 0), 999999),
IFNULL(NULLIF(LOCATE('?', url, start_pos), 0), 999999),
IFNULL(NULLIF(LOCATE('#', url, start_pos), 0), 999999)
) - start_pos
) AS domain
FROM (
SELECT
url,
CASE
WHEN LOCATE('://', url) > 0 THEN LOCATE('://', url) + 3
WHEN LOCATE('//', url) > 0 THEN LOCATE('//', url) + 2
ELSE 1
END AS start_pos
FROM url_table
) AS t;
注意：如果URL中在域名之后有多个分隔符，我们取第一个出现的位置，所以用LEAST。
如果觉得999999不够大，可以用更大的数，比如1000000，或者使用LENGTH(url)+1，这样即使字符串很长，也能保证不会截断。但是，使用LENGTH(url)+1需要动态计算，我们可以这样：
将IFNULL(NULLIF(LOCATE(...),0), 999999) 改为 IFNULL(NULLIF(LOCATE(...),0), LENGTH(url)+1)
所以，改进：
SELECT
url,
SUBSTRING(
url,
start_pos,
LEAST(
IFNULL(NULLIF(LOCATE('/', url, start_pos), 0), LENGTH(url)+1),
IFNULL(NULLIF(LOCATE(':', url, start_pos), 0), LENGTH(url)+1),
IFNULL(NULLIF(LOCATE('?', url, start_pos), 0), LENGTH(url)+1),
IFNULL(NULLIF(LOCATE('#', url, start_pos), 0), LENGTH(url)+1)
) - start_pos
) AS domain
FROM (
SELECT
url,
CASE
WHEN LOCATE('://', url) > 0 THEN LOCATE('://', url) + 3
WHEN LOCATE('//', url) > 0 THEN LOCATE('//', url) + 2
ELSE 1
END AS start_pos
FROM url_table
) AS t;
这样，如果找不到分隔符，则用LENGTH(url)+1代替，那么LEAST的结果就是LENGTH(url)+1，然后长度就是LENGTH(url)+1 - start_pos，这个值大于剩余长度，所以SUBSTRING会截取到末尾。
但是，注意：如果start_pos大于字符串长度，那么LOCATE函数会返回0，然后我们使用LENGTH(url)+1，那么长度就是LENGTH(url)+1 - start_pos，这个值可能是负数？但是，SUBSTRING函数对于长度为负数的情况，会返回空字符串。所以，我们需要保证start_pos不超过字符串长度。
在子查询中，start_pos的计算：
- 如果使用'://'，则位置至少为4（因为'://'至少在第1位，加3后为4），所以如果url长度小于4，那么LOCATE('://',url)返回0，所以不会进入这个分支，会进入后面的分支。
- 同样，'//'的位置至少为2，加2后为4。如果url长度小于4，那么进入ELSE分支，start_pos=1。
所以，当url长度小于4时，start_pos=1，然后截取：如果url长度是3，那么LEAST得到的值最小是4（因为找不到分隔符，所以用LENGTH(url)+1=4），那么长度=4-1=3，所以截取整个字符串。这是合理的。
但是，如果url是空字符串，那么LOCATE函数返回0，start_pos=1，然后LOCATE(...,1)会返回0（因为空字符串中找不到任何字符），然后我们用LENGTH(url)+1=1，所以LEAST得到1，然后长度=1-1=0，所以截取长度为0，返回空字符串。正确。
因此，这个查询是健壮的。
但是，还有一种情况：URL中可能包含用户名密码，如'http://user:pass@example.com'。这种情况，我们的方法会出错，因为我们在'://'后直接开始，但域名前面还有'user:pass@'。所以，我们需要进一步处理。
不过，这个问题比较复杂，而且不是常见情况。如果我们需要处理这种情况，那么应该先去除用户名密码部分。
考虑到时间，我们这里不展开。如果确实需要，可以在提取开始位置后，再查找'@'符号，如果有，则域名从'@'符号之后开始。但是，注意：'@'符号也可能出现在查询参数中（如URL编码），所以不能简单处理。
因此，我们假设URL中不包含用户名密码。
综上所述，我们使用上述查询来提取域名。
另外，我们还可以使用正则表达式，但MySQL在8.0版本之前不支持正则表达式提取（8.0支持REGEXP_SUBSTR，但需要8.0以上版本）。如果使用较低版本，则无法使用。
所以，如果版本较低，我们使用上述方法；如果版本是8.0+，则可以使用正则表达式，但这里我们按照要求使用SUBSTRING和LOCATE。
最后，给出完整的SQL查询语句。
注意：如果表中数据量很大，这样的函数操作可能无法使用索引，性能会较差。建议在应用层处理，或者在数据库中使用存储过程。但这里我们只讨论查询方法。</think>在 MySQL 中，要从 URL 字段提取域名部分，需处理协议前缀（`http://`、`https://`、`//`）并截取到第一个路径/参数分隔符（`/`、`?`、`#`、`:`）之前的位置。以下是完整解决方案：
### 解决方案代码
```sql
SELECT
url,
SUBSTRING(
url,
start_pos,
LEAST(
IFNULL(NULLIF(LOCATE('/', url, start_pos), 0), LENGTH(url) + 1),
IFNULL(NULLIF(LOCATE('?', url, start_pos), 0), LENGTH(url) + 1),
IFNULL(NULLIF(LOCATE('#', url, start_pos), 0), LENGTH(url) + 1),
IFNULL(NULLIF(LOCATE(':', url, start_pos), 0), LENGTH(url) + 1)
) - start_pos
) AS domain
FROM (
SELECT
url,
CASE
WHEN LOCATE('://', url) > 0 THEN LOCATE('://', url) + 3
WHEN LOCATE('//', url) = 1 THEN 3 -- 处理 "//example.com"
ELSE 1
END AS start_pos
FROM urls_table
) AS prepared;
```
### 关键步骤解析
1. **定位域名起始位置**：
- 若存在 `://`（如 `https://`），域名起始位置 = 协议位置 + 3
- 若以 `//` 开头（协议相对URL），域名起始位置 = 3
- 否则从字符串开头开始（如 `example.com`）
2. **确定域名结束位置**：
- 查找首个分隔符位置：`/`、`?`、`#`、`:`（端口号）
- 若未找到分隔符，使用 `LENGTH(url) + 1` 确保截取到末尾
3. **截取域名**：
- `SUBSTRING(url, start_pos, end_pos - start_pos)`
- 动态计算截取长度，适配不同URL格式
### 测试用例
| 输入 URL | 输出域名 |
|----------------------------------------|------------------|
| `https://www.example.com/path` | `www.example.com`|
| `http://sub.domain.com:8080/?q=test` | `sub.domain.com` |
| `//cdn.example.net/img.jpg` | `cdn.example.net`|
| `example.com/#contact` | `example.com` |
| `https://user:pass@site.com` | `user:pass@site.com`* |
> *注：含用户名密码的URL需额外处理，本方案聚焦标准格式。
### 处理说明
- **IPv6地址**：本方案暂不支持 `[2001:db8::1]` 格式，需正则表达式扩展
- **性能建议**：大数据量时建议应用层处理或存储过程
- **MySQL 8.0+ 优化**：可使用 `REGEXP_SUBSTR(url, '^[^:]+://([^/?:#]+)|^//([^/?:#]+)')` 简化[^1]
扫一扫
1816
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
