发布于 由admin

MySQL高级函数之：`SUBSTRING()` 和 `SUBSTR()`：其在字符串截取中的高级用法。

MySQL字符串截取高级用法：SUBSTRING() 和 SUBSTR()

大家好！今天我们来深入探讨MySQL中的字符串截取函数 SUBSTRING() 和 SUBSTR()。 实际上，SUBSTR() 只是 SUBSTRING() 的一个同义词，它们的功能完全相同。 因此，在本文中，我们将主要使用 SUBSTRING()，但请记住，所有示例和解释同样适用于 SUBSTR()。

SUBSTRING() 函数是MySQL中处理文本数据的利器，它允许我们从字符串中提取指定的子字符串。 虽然其基本用法相对简单，但通过结合不同的参数和技巧，可以实现非常灵活和强大的字符串处理功能。 掌握 SUBSTRING() 的高级用法，对于数据清洗、数据转换、报告生成等任务至关重要。

1. SUBSTRING() 的基本语法

SUBSTRING() 函数有以下几种语法形式：

  • SUBSTRING(str, pos): 从字符串 str 的位置 pos 开始截取子字符串，直到字符串末尾。
  • SUBSTRING(str FROM pos): 与第一种形式功能相同，只是使用了 FROM 关键字，可读性更好。
  • SUBSTRING(str, pos, len): 从字符串 str 的位置 pos 开始截取长度为 len 的子字符串。
  • SUBSTRING(str FROM pos FOR len): 与第三种形式功能相同，使用了 FROM 和 FOR 关键字，可读性更好。

其中：

  • str: 要截取的字符串。 可以是字符串字面量、列名或表达式。
  • pos: 截取起始位置。 注意：MySQL字符串的起始位置是从1开始，而不是从0开始。
    • pos 为正数时，表示从字符串的左侧开始计数。
    • pos 为负数时，表示从字符串的右侧开始计数。例如，-1 表示字符串的最后一个字符。
  • len: 要截取的子字符串的长度。 如果省略，则截取到字符串末尾。

示例：

SELECT SUBSTRING('Hello World', 7); -- 输出 'World'
SELECT SUBSTRING('Hello World' FROM 7); -- 输出 'World'
SELECT SUBSTRING('Hello World', 1, 5); -- 输出 'Hello'
SELECT SUBSTRING('Hello World' FROM 1 FOR 5); -- 输出 'Hello'
SELECT SUBSTRING('Hello World', -5); -- 输出 'World'
SELECT SUBSTRING('Hello World', -5, 3); -- 输出 'Wor'

2. 使用 SUBSTRING() 进行条件截取

SUBSTRING() 可以与 CASE WHEN 语句结合使用，实现基于条件的字符串截取。 这在需要根据不同的情况提取不同部分的字符串时非常有用。

示例：

假设我们有一个 products 表，其中包含一个 product_name 列，存储产品名称。 我们希望提取产品名称的前缀，规则如下：

  • 如果产品名称以 "A-" 开头，则提取 "A-" 之后的部分。
  • 如果产品名称以 "B-" 开头，则提取 "B-" 之后的部分。
  • 否则，提取整个产品名称。
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(255)
);

INSERT INTO products (product_name) VALUES
('A-Apple'),
('B-Banana'),
('C-Cherry'),
('A-Apricot'),
('B-Blueberry');

SELECT
    product_name,
    CASE
        WHEN product_name LIKE 'A-%' THEN SUBSTRING(product_name, 3)
        WHEN product_name LIKE 'B-%' THEN SUBSTRING(product_name, 3)
        ELSE product_name
    END AS extracted_name
FROM
    products;

这个查询会返回一个包含 product_name 和 extracted_name 两列的结果集。 extracted_name 列包含根据条件提取的名称。

3. 使用 SUBSTRING() 和 LOCATE() 动态截取字符串

LOCATE() 函数用于查找一个字符串在另一个字符串中首次出现的位置。 结合 SUBSTRING() 和 LOCATE()，我们可以实现基于特定分隔符的动态字符串截取。

语法：

LOCATE(substring, string, start_position)

  • substring: 要查找的子字符串。
  • string: 要在其中查找的字符串。
  • start_position: 可选参数，指定从字符串的哪个位置开始查找。 如果省略，则从字符串的开头开始查找。

示例：

假设我们有一个 emails 表，其中包含一个 email 列，存储电子邮件地址。 我们希望提取电子邮件地址的用户名部分 (即 @ 符号之前的部分)。

CREATE TABLE emails (
    id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(255)
);

INSERT INTO emails (email) VALUES
('[email protected]'),
('[email protected]'),
('[email protected]');

SELECT
    email,
    SUBSTRING(email, 1, LOCATE('@', email) - 1) AS username
FROM
    emails;

在这个例子中，LOCATE('@', email) 返回 @ 符号在 email 字符串中的位置。 然后，我们使用 SUBSTRING() 函数从 email 字符串的开头截取到 @ 符号之前的位置，从而得到用户名。

4. 使用 SUBSTRING_INDEX() 进行更高级的分隔符截取

SUBSTRING_INDEX() 函数是另一种基于分隔符截取字符串的函数，它比 LOCATE() 和 SUBSTRING() 的组合更简洁。

语法：

SUBSTRING_INDEX(str, delim, count)

  • str: 要截取的字符串。
  • delim: 分隔符。
  • count: 计数。
    • 如果 count 为正数，则返回从字符串的开头到第 count 个分隔符之前的子字符串。
    • 如果 count 为负数，则返回从字符串的末尾到倒数第 count 个分隔符之后的子字符串。

示例：

假设我们有一个 full_names 表，其中包含一个 full_name 列，存储完整的姓名，格式为 "FirstName LastName"。 我们希望提取姓氏。

CREATE TABLE full_names (
    id INT PRIMARY KEY AUTO_INCREMENT,
    full_name VARCHAR(255)
);

INSERT INTO full_names (full_name) VALUES
('John Doe'),
('Jane Smith'),
('Peter Jones');

SELECT
    full_name,
    SUBSTRING_INDEX(full_name, ' ', -1) AS last_name
FROM
    full_names;

在这个例子中，SUBSTRING_INDEX(full_name, ' ', -1) 返回从 full_name 字符串的末尾到倒数第一个空格之后的子字符串，即姓氏。

5. SUBSTRING() 在数据清洗中的应用

SUBSTRING() 函数在数据清洗过程中扮演着重要的角色。 例如，它可以用于去除字符串中的不需要的前缀或后缀，或者提取字符串中的关键信息。

示例：

假设我们有一个 phone_numbers 表，其中包含一个 phone_number 列，存储电话号码，但电话号码的格式不统一，有些以 "+1-" 开头，有些以 "001-" 开头，我们需要将这些前缀去除，统一格式。

CREATE TABLE phone_numbers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    phone_number VARCHAR(255)
);

INSERT INTO phone_numbers (phone_number) VALUES
('+1-555-123-4567'),
('001-555-987-6543'),
('555-246-8013');

SELECT
    phone_number,
    CASE
        WHEN phone_number LIKE '+1-%' THEN SUBSTRING(phone_number, 4)
        WHEN phone_number LIKE '001-%' THEN SUBSTRING(phone_number, 5)
        ELSE phone_number
    END AS cleaned_phone_number
FROM
    phone_numbers;

6. SUBSTRING() 处理多字节字符

在处理包含多字节字符（如中文、日文、韩文等）的字符串时，SUBSTRING() 函数的行为与处理单字节字符的字符串相同。 SUBSTRING() 是按照字符的位置进行截取的，而不是按照字节的位置。

示例：

SELECT SUBSTRING('你好世界', 1, 2); -- 输出 '你好'
SELECT SUBSTRING('你好世界', 3); -- 输出 '世界'

7. SUBSTRING() 与其他函数的结合应用

SUBSTRING() 可以与其他MySQL函数结合使用，实现更复杂的功能。 例如，可以与 REPLACE() 函数结合使用，替换字符串中的特定子字符串；可以与 TRIM() 函数结合使用，去除字符串开头和结尾的空格。

示例：

假设我们有一个 addresses 表，其中包含一个 address 列，存储地址信息，地址信息可能包含多余的空格。 我们希望去除地址信息开头和结尾的空格，并将地址信息中的 "Street" 替换为 "St."。

CREATE TABLE addresses (
    id INT PRIMARY KEY AUTO_INCREMENT,
    address VARCHAR(255)
);

INSERT INTO addresses (address) VALUES
('  123 Main Street  '),
('456 Oak Avenue  ');

SELECT
    address,
    REPLACE(TRIM(address), 'Street', 'St.') AS formatted_address
FROM
    addresses;

在这个例子中，TRIM(address) 首先去除 address 字符串开头和结尾的空格。 然后，REPLACE(..., 'Street', 'St.') 将处理后的字符串中的 "Street" 替换为 "St."。

8. SUBSTRING() 的性能考虑

虽然 SUBSTRING() 函数非常强大，但在处理大量数据时，需要注意其性能。 频繁地使用 SUBSTRING() 函数可能会影响查询的执行效率。

优化建议：

  • 避免在 WHERE 子句中使用 SUBSTRING() 函数进行过滤。 如果必须使用，请考虑创建索引来优化查询。
  • 尽量减少 SUBSTRING() 函数的调用次数。 如果可以在应用程序层面进行字符串处理，则尽量在应用程序层面处理。
  • 对于复杂的字符串处理逻辑，可以考虑使用存储过程或自定义函数。

表格总结 SUBSTRING, SUBSTR, LOCATE, SUBSTRING_INDEX 函数

函数 语法 功能
SUBSTRING() / SUBSTR() SUBSTRING(str, pos, len) / SUBSTR(str, pos, len) 从字符串 str 的位置 pos 开始截取长度为 len 的子字符串。
LOCATE() LOCATE(substring, string, start_position) 查找 substring 在 string 中首次出现的位置。
SUBSTRING_INDEX() SUBSTRING_INDEX(str, delim, count) 从字符串 str 中，根据分隔符 delim 截取子字符串。 count 为正数时，返回第 count 个分隔符之前的子字符串; 为负数时，返回倒数第 count 个分隔符之后的子字符串。

9. 示例：从URL提取域名

以下是一个更完整的示例，展示如何从 URL 中提取域名：

CREATE TABLE urls (
    id INT PRIMARY KEY AUTO_INCREMENT,
    url VARCHAR(255)
);

INSERT INTO urls (url) VALUES
('https://www.example.com/path/to/page'),
('http://subdomain.domain.net/another/path'),
('ftp://files.example.org');

SELECT
    url,
    CASE
        WHEN LOCATE('://', url) > 0 THEN
            SUBSTRING(
                SUBSTRING(url, LOCATE('://', url) + 3), -- Remove protocol
                1,
                CASE
                    WHEN LOCATE('/', SUBSTRING(url, LOCATE('://', url) + 3)) > 0 THEN
                        LOCATE('/', SUBSTRING(url, LOCATE('://', url) + 3)) - 1
                    ELSE
                        LENGTH(SUBSTRING(url, LOCATE('://', url) + 3))
                END
            )
        ELSE
            NULL  -- Handle URLs without protocol (optional)
    END AS domain
FROM
    urls;

这个查询首先检查 URL 是否包含 "://" 协议。 如果包含，它使用两个嵌套的 SUBSTRING() 调用：

  1. 外层 SUBSTRING(): SUBSTRING(url, LOCATE('://', url) + 3) 移除协议部分（例如，"https://" 或 "http://"）。
  2. 内层 SUBSTRING(): 提取协议之后的部分，直到找到第一个斜杠 "/" 或者到达字符串末尾。 CASE 语句用于确定域名结束的位置。 如果找到斜杠，则域名在斜杠之前结束。 否则，整个字符串都是域名。

总结：灵活运用字符串截取函数提升数据处理能力

SUBSTRING() 和 SUBSTR() 是MySQL中强大的字符串截取函数。 结合 LOCATE() 和 SUBSTRING_INDEX() 等其他函数，我们可以实现各种复杂的字符串处理任务。 理解并灵活运用这些函数，能够显著提高数据清洗、数据转换和报告生成等方面的效率和质量。 记住在处理大量数据时，要注意性能优化，以确保查询的执行效率。

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注