mysql字符串截取揭秘数据处理利器：深入理解MySQL字符串截取的“是什么、为什么、哪里、多少、如何、怎么”

理解MySQL字符串截取：数据处理的核心技艺

在数据管理的日常工作中，我们经常需要从庞大的文本信息中提取出精确、有用的部分。MySQL数据库提供了强大的字符串处理功能，其中“字符串截取”是使用频率极高、也最能体现数据处理灵活性的操作之一。它不仅仅是简单地剪掉一段文字，更是一种智能地从复杂数据中提炼出关键信息的能力。

为什么我们需要字符串截取？

字符串截取在数据处理中扮演着不可或缺的角色，其应用场景广泛而多样。理解其背后的“为什么”能帮助我们更好地利用这项功能：

数据清洗与规范化： 原始数据往往格式不一，可能包含多余的前缀、后缀或不必要的字符。截取操作可以帮助我们去除这些干扰，使数据符合预设的规范，便于后续的分析和存储。
信息提取与解析： 从结构化的字符串中（如URL、文件路径、电子邮件地址、身份证号等）提取特定组成部分，例如从“[email protected]”中截取“user”或“example.com”。
数据展示与报表： 在用户界面或报表中，通常不希望展示过长的文本内容，而是需要显示精简的摘要或预览。字符串截取能够生成指定长度的描述，提升用户体验。
数据比对与筛选： 有时我们只需要根据字符串的某一部分进行匹配或过滤，例如查找所有以特定邮政编码开头的地址。
数据脱敏与安全： 对于敏感信息（如手机号、银行卡号等），可以通过截取并替换部分字符的方式进行脱敏处理，确保数据安全。
生成唯一标识或编码： 根据现有数据生成短而唯一的代码或标识符。

MySQL字符串截取的核心函数有哪些？

MySQL提供了多个内建函数来实现字符串的截取，它们各自有不同的侧重点和使用场景，满足从简单到复杂的截取需求：

SUBSTRING() 或其别名 SUBSTR()：这是最通用和强大的截取函数，允许指定起始位置和截取长度。
LEFT()：从字符串的左侧（开头）截取指定长度的字符。
RIGHT()：从字符串的右侧（末尾）截取指定长度的字符。
SUBSTRING_INDEX()：根据指定的分隔符来截取字符串，非常适合处理由特定分隔符连接起来的数据。

如何进行字符串截取？具体操作与示例

本节将详细介绍这些函数的语法和实际应用示例，帮助您掌握它们的用法。

为了更好地演示，我们先创建一个示例表并插入一些数据：

CREATE TABLE `products` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `product_name` VARCHAR(255) NOT NULL,
    `description` TEXT,
    `sku` VARCHAR(100),
    `url` VARCHAR(500)
);

INSERT INTO `products` (`product_name`, `description`, `sku`, `url`) VALUES
('智能手机X', '这是一款功能强大的智能手机，配备高清屏幕和长续航电池。', 'SPH-X-2023-A001', 'https://example.com/products/mobile/smartphonex.html?ref=new'),
('无线耳机Pro', '享受纯粹的音乐体验，无感佩戴，超长待机。', 'EAR-PRO-2023-B002', 'https://example.com/audio/earphonepro.html'),
('智能手表Lite', '轻巧设计，健康生活，随时随地监测你的心率。', 'WAT-LIT-2023-C003', 'https://example.com/wearable/watchlite.html'),
('便携式充电宝', '快速充电，小巧便携，旅行必备。', 'POW-BAN-2023-D004', 'https://example.com/accessories/powerbank.html'),
('超薄笔记本电脑', '轻量化机身，强劲性能，满足您日常办公和娱乐需求。', 'LAP-ULT-2023-E005', 'https://example.com/computing/laptopultra.html');

1. SUBSTRING() / SUBSTR()：通用截取利器

这两个函数功能相同，SUBSTR()是SUBSTRING()的别名。

语法：

SUBSTRING(str, pos)：从字符串str的pos位置开始，截取到字符串末尾。
SUBSTRING(str, pos, len)：从字符串str的pos位置开始，截取len个字符。
SUBSTRING(str FROM pos FOR len)：等同于SUBSTRING(str, pos, len)，可读性更好。

参数说明：

str：要截取的原始字符串。
pos：起始位置。
- 正数：从字符串的开头算起，第一个字符是1。
- 负数：从字符串的末尾算起，-1表示最后一个字符，-2表示倒数第二个字符。
len：要截取的长度。如果len是负数，则返回空字符串。如果pos加上len超出字符串长度，则截取到字符串末尾。

示例：

从指定位置开始，截取到字符串末尾：

例如，截取产品描述的第10个字符之后的所有内容作为简介。

        SELECT
            product_name,
            SUBSTRING(description, 10) AS description_from_char10
        FROM
            products
        WHERE id = 1;

结果：

        +---------------+---------------------------+
        | product_name  | description_from_char10   |
        +---------------+---------------------------+
        | 智能手机X     | 强大的智能手机，配备高清屏幕和长续航电池。 |
        +---------------+---------------------------+

从指定位置开始，截取指定长度：

例如，获取产品SKU的前三个字符作为类别标识。

        SELECT
            product_name,
            sku,
            SUBSTRING(sku, 1, 3) AS sku_category
        FROM
            products;

结果（部分）：

        +-----------------------+------------------+--------------+
        | product_name          | sku              | sku_category |
        +-----------------------+------------------+--------------+
        | 智能手机X             | SPH-X-2023-A001  | SPH          |
        | 无线耳机Pro           | EAR-PRO-2023-B002  | EAR          |
        | 智能手表Lite          | WAT-LIT-2023-C003  | WAT          |
        ...

使用负数作为起始位置（从末尾开始截取）：

例如，获取SKU的最后五个字符（通常是编号部分）。

        SELECT
            product_name,
            sku,
            SUBSTRING(sku, -5) AS sku_suffix
        FROM
            products;

结果（部分）：

        +-----------------------+------------------+------------+
        | product_name          | sku              | sku_suffix |
        +-----------------------+------------------+------------+
        | 智能手机X             | SPH-X-2023-A001  | A001       |
        | 无线耳机Pro           | EAR-PRO-2023-B002  | B002       |
        | 智能手表Lite          | WAT-LIT-2023-C003  | C003       |
        ...

使用FROM ... FOR ...语法：

例如，截取URL中从第9个字符开始的10个字符。

        SELECT
            product_name,
            url,
            SUBSTRING(url FROM 9 FOR 10) AS url_part
        FROM
            products
        WHERE id = 1;

结果：

        +---------------+-------------------------------------------------+------------+
        | product_name  | url                                             | url_part   |
        +---------------+-------------------------------------------------+------------+
        | 智能手机X     | https://example.com/products/mobile/smartphonex.html?ref=new | example.co |
        +---------------+-------------------------------------------------+------------+

2. LEFT()：从左侧截取

LEFT(str, len)函数从字符串str的开头（左侧）截取len个字符。

示例：

获取产品名称的前5个字符作为短名称：

        SELECT
            product_name,
            LEFT(product_name, 5) AS short_name
        FROM
            products;

结果（部分）：

        +-----------------------+------------+
        | product_name          | short_name |
        +-----------------------+------------+
        | 智能手机X             | 智能手机X  |
        | 无线耳机Pro           | 无线耳机Pr |
        | 智能手表Lite          | 智能手表Li |
        ...

3. RIGHT()：从右侧截取

RIGHT(str, len)函数从字符串str的末尾（右侧）截取len个字符。

示例：

获取SKU的最后4个字符：

        SELECT
            product_name,
            sku,
            RIGHT(sku, 4) AS sku_last_4
        FROM
            products;

结果（部分）：

        +-----------------------+------------------+------------+
        | product_name          | sku              | sku_last_4 |
        +-----------------------+------------------+------------+
        | 智能手机X             | SPH-X-2023-A001  | A001       |
        | 无线耳机Pro           | EAR-PRO-2023-B002  | B002       |
        | 智能手表Lite          | WAT-LIT-2023-C003  | C003       |
        ...

4. SUBSTRING_INDEX()：按分隔符截取

SUBSTRING_INDEX(str, delim, count)函数根据指定的分隔符delim来截取字符串str。

参数说明：

str：要截取的原始字符串。
delim：分隔符，可以是单个字符或一个字符串。
count：一个整数。
- 正数：表示从左边数起，在第count个分隔符之前截取。
- 负数：表示从右边数起，在第count个分隔符之后截取。

示例：

截取URL中的域名（第一个“/”之前）：

        SELECT
            product_name,
            url,
            SUBSTRING_INDEX(SUBSTRING_INDEX(url, '//', -1), '/', 1) AS domain_name
        FROM
            products;

解析：

首先，SUBSTRING_INDEX(url, '//', -1)从右侧截取，得到“example.com/products/mobile/smartphonex.html?ref=new”。

然后，对这个结果再次使用SUBSTRING_INDEX(..., '/', 1)，从左侧截取第一个“/”之前的部分，即“example.com”。

结果（部分）：

        +-----------------------+-------------------------------------------------+-------------+
        | product_name          | url                                             | domain_name |
        +-----------------------+-------------------------------------------------+-------------+
        | 智能手机X             | https://example.com/products/mobile/smartphonex.html?ref=new | example.com |
        | 无线耳机Pro           | https://example.com/audio/earphonepro.html      | example.com |
        ...

从文件路径中截取文件名（最后一个“/”之后）：

        SELECT
            product_name,
            url,
            SUBSTRING_INDEX(url, '/', -1) AS file_name
        FROM
            products;

结果（部分）：

        +-----------------------+-------------------------------------------------+----------------------------------------+
        | product_name          | url                                             | file_name                              |
        +-----------------------+-------------------------------------------------+----------------------------------------+
        | 智能手机X             | https://example.com/products/mobile/smartphonex.html?ref=new | smartphonex.html?ref=new             |
        | 无线耳机Pro           | https://example.com/audio/earphonepro.html      | earphonepro.html                       |
        ...

截取SKU中的年份（第二个“-”之前）：

        SELECT
            product_name,
            sku,
            SUBSTRING_INDEX(SUBSTRING_INDEX(sku, '-', 3), '-', -1) AS sku_year
        FROM
            products;

解析：

首先，SUBSTRING_INDEX(sku, '-', 3)从左侧截取，得到“SPH-X-2023”。

然后，对这个结果再次使用SUBSTRING_INDEX(..., '-', -1)，从右侧截取最后一个“-“之后的部分，即“2023”。

结果（部分）：

        +-----------------------+------------------+----------+
        | product_name          | sku              | sku_year |
        +-----------------------+------------------+----------+
        | 智能手机X             | SPH-X-2023-A001  | 2023     |
        | 无线耳机Pro           | EAR-PRO-2023-B002  | 2023     |
        ...

字符串截取函数能在“哪里”使用？

字符串截取函数不仅限于SELECT语句中用于数据查询，它们在MySQL的各个层面上都发挥着作用：

SELECT语句： 这是最常见的用法，用于在查询结果中显示截取后的数据。例如，SELECT product_name, LEFT(description, 50) AS short_desc FROM products;
WHERE子句： 用于基于字符串的截取部分进行条件过滤。例如，SELECT * FROM products WHERE SUBSTRING(sku, 1, 3) = 'SPH';
UPDATE语句： 用于修改表中的现有数据，将截取后的值写入列。例如，UPDATE products SET sku = LEFT(sku, LENGTH(sku) - 5) WHERE sku LIKE '%-A%'; （此例用于移除sku最后的“-A001”类似的后缀）
INSERT语句： 在插入数据时，对输入值进行截取处理后再存储。例如，INSERT INTO logs (message) VALUES (LEFT('非常长的日志信息...', 20));
CREATE VIEW定义： 在创建视图时，可以包含字符串截取操作，使得视图始终提供处理过的数据。
存储过程和函数： 在复杂的业务逻辑中，可以在存储过程或自定义函数内部使用这些截取函数进行数据处理。

处理字符串截取时需要考虑“多少”？

在使用字符串截取功能时，有一些重要的细节和潜在问题需要您提前考虑，这些决定了截取操作的“多少”复杂度或影响：

1. 多字节字符集与长度计算：`LENGTH()` vs. `CHAR_LENGTH()`

这是在处理包含中文、日文、韩文等UTF-8字符集数据时，一个非常关键的考量点。

LENGTH(str)：返回字符串的字节长度。对于UTF-8字符，一个中文字符通常占3个字节。
CHAR_LENGTH(str)：返回字符串的字符数（即真正的人类可读的字符数量）。一个中文字符被计算为1个字符。

当您希望按字符数而不是字节数来截取时，务必使用CHAR_LENGTH()作为SUBSTRING()、LEFT()、RIGHT()等函数的长度参数。

示例：

SELECT
    '你好世界' AS original_string,
    LENGTH('你好世界') AS byte_length,
    CHAR_LENGTH('你好世界') AS char_length,
    LEFT('你好世界', 2) AS left_2_chars_by_char, -- 按字符截取
    SUBSTRING('你好世界', 2, 2) AS substr_2_chars_by_char; -- 按字符截取

结果：

+-----------------+-------------+-------------+----------------------+------------------------+
| original_string | byte_length | char_length | left_2_chars_by_char | substr_2_chars_by_char |
+-----------------+-------------+-------------+----------------------+------------------------+
| 你好世界        | 12          | 4           | 你好                 | 好世界                 |
+-----------------+-------------+-------------+----------------------+------------------------+

可以看到，对于“你好世界”这个4个字符的字符串，其字节长度是12。LEFT('你好世界', 2)和SUBSTRING('你好世界', 2, 2)都正确地按照字符数进行了截取。

2. 性能考量

索引失效： 如果您在WHERE子句中对一个列使用字符串截取函数，例如WHERE SUBSTRING(column_name, 1, 5) = 'ABCDE'，那么即使column_name列上有索引，MySQL也无法直接利用这个索引，因为它需要计算每个行的截取结果。这会导致全表扫描，对于大表来说性能影响巨大。
优化建议：
- 如果经常需要根据字符串的某个固定前缀或后缀进行筛选，可以考虑在表中新增一个列来存储这个截取后的值，并为其建立索引。
- MySQL 8.0及更高版本支持函数式索引（Functional Index）或虚拟列（Virtual Column），您可以创建一个基于截取函数结果的虚拟列并对其建立索引，从而优化这类查询。
- 如果查询条件是固定的前缀匹配，可以直接使用LIKE 'ABCDE%'，这样可以利用列上的索引。

3. 边界条件与NULL值处理

NULL输入： 如果传递给截取函数的字符串参数是NULL，那么截取操作的结果也将是NULL。

例如：SELECT SUBSTRING(NULL, 1, 5); 返回 NULL。
起始位置和长度超出范围：
- 如果pos超出字符串长度，SUBSTRING()、LEFT()、RIGHT()会返回空字符串''。
- 如果len指定的值超过了从pos开始到字符串结尾的实际可用长度，函数会截取到字符串末尾。
例如：SELECT SUBSTRING('abc', 5); 返回 ''。

例如：SELECT SUBSTRING('abc', 1, 10); 返回 'abc'。

结合其他函数的高级截取“怎么”做？

字符串截取函数经常与其他MySQL字符串函数结合使用，以实现更复杂、更灵活的数据解析和提取。

1. 结合`LOCATE()`或`INSTR()`：查找子串位置

LOCATE(substr, str[, pos])：返回子串substr在字符串str中第一次出现的位置。INSTR(str, substr)功能类似。

示例：动态截取URL中的路径部分（从域名之后到查询参数之前）：

我们想要从类似 `https://example.com/products/mobile/smartphonex.html?ref=new` 的URL中提取出 `/products/mobile/smartphonex.html`。

SELECT
    product_name,
    url,
    SUBSTRING(
        url,
        LOCATE('/', url, INSTR(url, '//') + 2) -- 找到第一个 '/' 出现的位置，但要跳过 '://' 后的第一个 '/'
    ) AS full_path_with_query,
    SUBSTRING_INDEX(
        SUBSTRING(
            url,
            LOCATE('/', url, INSTR(url, '//') + 2) -- 从协议后的第一个 '/' 开始
        ),
        '?',
        1
    ) AS path_without_query_params
FROM
    products
WHERE id = 1;

解析：

INSTR(url, '//')：找到 `//` 的起始位置。
INSTR(url, '//') + 2：跳过 `//`，得到域名开始的位置。
LOCATE('/', url, INSTR(url, '//') + 2)：从域名开始的位置之后，找到第一个 `/` 的位置。这个 `/` 就是路径部分的开始。
外层SUBSTRING()：从这个路径开始的位置，截取到字符串末尾，得到带查询参数的完整路径。
对于path_without_query_params，我们对上一步的结果再使用SUBSTRING_INDEX(..., '?', 1)，在第一个问号?之前截取，从而去除查询参数。

结果：

+---------------+-------------------------------------------------+----------------------------+-----------------------------+
| product_name  | url                                             | full_path_with_query       | path_without_query_params   |
+---------------+-------------------------------------------------+----------------------------+-----------------------------+
| 智能手机X     | https://example.com/products/mobile/smartphonex.html?ref=new | /products/mobile/smartphonex.html?ref=new | /products/mobile/smartphonex.html |
+---------------+-------------------------------------------------+----------------------------+-----------------------------+

2. 结合`LENGTH()`或`CHAR_LENGTH()`：动态截取末尾部分

当需要从字符串末尾动态截取一段内容，但又不知道其确切起始位置时，LENGTH()（或CHAR_LENGTH()）结合SUBSTRING()非常有用。

示例：截取产品描述的最后10个字符（考虑UTF-8字符）：

SELECT
    product_name,
    description,
    SUBSTRING(description, CHAR_LENGTH(description) - 9) AS last_10_chars_desc
FROM
    products
WHERE id = 1;

解析：

CHAR_LENGTH(description) - 9计算出从字符串开头算起，要获取最后10个字符，起始位置应该在哪里。例如，如果总长是50个字符，那么起始位置就是 50 – 9 = 41。SUBSTRING()函数从第41个字符开始截取，默认到字符串末尾。

结果：

+---------------+----------------------------------------------------------+--------------------+
| product_name  | description                                              | last_10_chars_desc |
+---------------+----------------------------------------------------------+--------------------+
| 智能手机X     | 这是一款功能强大的智能手机，配备高清屏幕和长续航电池。 | 高清屏幕和长续航电池。 |
+---------------+----------------------------------------------------------+--------------------+

总结

MySQL的字符串截取功能是数据库操作中不可或缺的利器。无论是基础的SUBSTRING()、LEFT()、RIGHT()，还是基于分隔符的SUBSTRING_INDEX()，它们都为我们提供了灵活高效的文本处理能力。通过深入理解这些函数的“是什么、为什么、哪里、多少、如何、怎么”，以及结合其他字符串函数进行高级组合，您将能够更有效地从复杂数据中提取出所需信息，进行数据清洗、格式化、分析和展示，从而更好地应对各种数据管理挑战。记住，在实际应用中，尤其是在处理多字节字符和大型数据集时，细致考虑字符集和性能影响将是构建健壮高效数据库应用的关键。

mysql字符串截取揭秘数据处理利器：深入理解MySQL字符串截取的“是什么、为什么、哪里、多少、如何、怎么”

理解MySQL字符串截取：数据处理的核心技艺

为什么我们需要字符串截取？

MySQL字符串截取的核心函数有哪些？

如何进行字符串截取？具体操作与示例

1. SUBSTRING() / SUBSTR()：通用截取利器

2. LEFT()：从左侧截取

3. RIGHT()：从右侧截取

4. SUBSTRING_INDEX()：按分隔符截取

字符串截取函数能在“哪里”使用？

处理字符串截取时需要考虑“多少”？

1. 多字节字符集与长度计算：LENGTH() vs. CHAR_LENGTH()

2. 性能考量

3. 边界条件与NULL值处理

结合其他函数的高级截取“怎么”做？

1. 结合LOCATE()或INSTR()：查找子串位置

2. 结合LENGTH()或CHAR_LENGTH()：动态截取末尾部分

总结

1. 多字节字符集与长度计算：`LENGTH()` vs. `CHAR_LENGTH()`

1. 结合`LOCATE()`或`INSTR()`：查找子串位置

2. 结合`LENGTH()`或`CHAR_LENGTH()`：动态截取末尾部分