MySQL高级函数之：`INSTR()`：其在字符串查找中的应用与`LOCATE()`的区别。 - 智猿学院-前后端，数据库，人工智能，云计算等领域前沿技术讲座

MySQL 高级函数：INSTR() 字符串查找利器

各位朋友，大家好！今天我们来深入探讨 MySQL 中一个常用的字符串查找函数 INSTR()。虽然 LOCATE() 函数也能完成类似的功能，但 INSTR() 在某些方面更具优势。我们将从 INSTR() 的基本语法、应用场景、与 LOCATE() 的对比，以及性能考虑等方面进行详细讲解，并通过大量的代码示例帮助大家理解。

INSTR() 函数的基本语法

INSTR() 函数用于在一个字符串 (str) 中查找指定子字符串 (substr) 第一次出现的位置。其基本语法如下：

INSTR(str, substr)

str: 要搜索的字符串。
substr: 要查找的子字符串。

INSTR() 函数返回子字符串 substr 在字符串 str 中第一次出现的位置。如果 substr 未找到，则返回 0。位置是从 1 开始的，而不是从 0 开始。

示例：

SELECT INSTR('hello world', 'world'); -- 返回 7
SELECT INSTR('hello world', 'o');     -- 返回 5
SELECT INSTR('hello world', 'abc');   -- 返回 0

INSTR() 函数的应用场景

INSTR() 函数在实际开发中有很多应用场景，常见的包括：

数据验证： 检查字符串是否包含特定字符或模式。例如，验证邮箱地址是否包含 "@" 符号。
数据清洗： 根据子字符串的位置提取或替换字符串的部分内容。例如，提取文件名中的扩展名。
搜索功能： 在数据库中查找包含特定关键词的记录。例如，搜索包含 "MySQL" 的文章标题。
数据分析： 统计特定子字符串在字符串中出现的次数 (需要配合其他函数)。例如，统计一段文本中某个词语出现的频率。
字符串分割： 与其他字符串函数 (如 SUBSTRING()) 结合，实现简单的字符串分割功能。

下面通过具体的例子来展示这些应用场景。

示例 1：验证邮箱地址

SELECT
    email,
    CASE
        WHEN INSTR(email, '@') > 0 THEN 'Valid'
        ELSE 'Invalid'
    END AS email_status
FROM
    (
        SELECT '[email protected]' AS email UNION ALL
        SELECT 'invalid-email' AS email UNION ALL
        SELECT '[email protected]' AS email
    ) AS emails;

这个例子中，我们使用 INSTR() 检查 email 列是否包含 "@" 符号。如果包含，则认为是有效的邮箱地址，否则认为是无效的。

示例 2：提取文件名扩展名

SELECT
    filename,
    SUBSTRING(filename, INSTR(filename, '.') + 1) AS file_extension
FROM
    (
        SELECT 'document.pdf' AS filename UNION ALL
        SELECT 'image.jpg' AS filename UNION ALL
        SELECT 'data.csv' AS filename
    ) AS files
WHERE INSTR(filename,'.') > 0;

这里，我们使用 INSTR() 找到文件名中 "." 的位置，然后使用 SUBSTRING() 提取从 "." 之后的所有字符，即文件扩展名。 WHERE INSTR(filename,'.') > 0 用于排除没有扩展名的文件。

示例 3：搜索包含关键词的文章标题

假设我们有一个 articles 表，包含 id 和 title 两列。我们可以使用以下 SQL 查询查找包含 "MySQL" 关键词的文章：

SELECT id, title
FROM articles
WHERE INSTR(title, 'MySQL') > 0;

示例 4：简单的字符串分割

SELECT
    data,
    SUBSTRING(data, 1, INSTR(data, ',') - 1) AS first_part,
    SUBSTRING(data, INSTR(data, ',') + 1) AS second_part
FROM
    (
        SELECT 'value1,value2' AS data UNION ALL
        SELECT 'itemA,itemB' AS data
    ) AS data_points
WHERE INSTR(data,',') > 0;

此示例演示了如何使用 INSTR() 和 SUBSTRING() 将以逗号分隔的字符串分割成两部分。同样， WHERE INSTR(data,',') > 0 用来过滤掉没有逗号分隔符的数据。

INSTR() 与 LOCATE() 的区别

LOCATE() 函数与 INSTR() 函数的功能非常相似，都是用于在一个字符串中查找子字符串的位置。但是，它们之间存在一些细微的差别：

参数顺序： INSTR() 的参数顺序是 INSTR(str, substr)，而 LOCATE() 的参数顺序是 LOCATE(substr, str)。也就是说， INSTR() 先写要查找的字符串，再写子字符串；而LOCATE() 正好相反，先写子字符串，再写要查找的字符串。
可选起始位置参数： LOCATE() 函数可以接受一个可选的起始位置参数，用于指定从字符串的哪个位置开始搜索。 INSTR() 函数没有这个参数。
别名： LOCATE() 函数还有一个别名 POSITION()，它们的用法完全相同。 INSTR() 没有别名。

下面是一个对比的例子：

SELECT
    INSTR('hello world', 'world') AS instr_result,
    LOCATE('world', 'hello world') AS locate_result;
-- instr_result = 7, locate_result = 7

SELECT
    LOCATE('o', 'hello world', 6) AS locate_with_start; -- locate_with_start = 8  从第6个位置开始查找'o'

总结：

特性	`INSTR()`	`LOCATE()`
参数顺序	`INSTR(str, substr)`	`LOCATE(substr, str)`
起始位置参数	无	可选，`LOCATE(substr, str, pos)`
别名	无	`POSITION()`

性能考虑

INSTR() 和 LOCATE() 的性能通常取决于以下几个因素：

字符串长度： 字符串越长，搜索时间越长。
子字符串长度： 子字符串越长，搜索时间可能也会增加。
索引： 如果在查询的列上建立了索引，可以显著提高搜索速度。
数据量： 数据量越大，搜索时间越长。

优化建议：

使用索引： 在经常需要进行字符串搜索的列上建立索引。
避免在 WHERE 子句中使用 INSTR() 或 LOCATE() 对未索引的列进行全表扫描。
尽量缩小搜索范围： 如果可能，在 WHERE 子句中添加其他条件，限制搜索范围。
考虑使用全文索引： 对于需要进行复杂文本搜索的场景，可以考虑使用 MySQL 的全文索引功能。

示例：索引优化

假设我们有一个 products 表，包含 id 和 name 两列。如果我们需要经常查找包含特定关键词的产品名称，可以在 name 列上建立索引：

CREATE INDEX idx_product_name ON products (name);

这样，在使用 INSTR() 或 LOCATE() 进行搜索时，MySQL 就可以利用索引快速定位到包含关键词的记录，而无需进行全表扫描。

高级应用：结合其他函数

INSTR() 函数可以与其他字符串函数结合使用，实现更复杂的功能。

示例 1：提取字符串中所有数字

SELECT
    input_string,
    CASE
        WHEN LENGTH(TRIM(REGEXP_REPLACE(input_string, '[0-9]+', ' '))) = 0 THEN input_string
        ELSE ''
    END AS extracted_numbers
FROM
    (
        SELECT 'abc123def456' AS input_string UNION ALL
        SELECT '789ghi' AS input_string UNION ALL
        SELECT 'no numbers' AS input_string
    ) AS strings;

这个例子使用 REGEXP_REPLACE() 函数去除字符串中的非数字字符，然后使用 LENGTH() 函数判断结果是否为空。如果不为空，则说明字符串中包含数字。如果字符串只包含数字，则返回原始字符串，否则返回空字符串。

示例 2：统计子字符串出现的次数

虽然 INSTR() 本身不能直接统计子字符串出现的次数，但我们可以通过循环和递归的方式实现这个功能。但是MySQL不适合做这种循环和递归，性能会很差。这里仅提供思路。

-- 这段代码仅供思路参考，实际不推荐在MySQL中这样使用
DELIMITER //
CREATE FUNCTION count_substring(str TEXT, substr VARCHAR(255))
RETURNS INT
DETERMINISTIC
BEGIN
    DECLARE count INT DEFAULT 0;
    DECLARE pos INT DEFAULT 1;

    loop_label: LOOP
        SET pos = INSTR(str, substr);
        IF pos = 0 THEN
            LEAVE loop_label;
        END IF;
        SET count = count + 1;
        SET str = SUBSTRING(str, pos + LENGTH(substr));
    END LOOP loop_label;

    RETURN count;
END//
DELIMITER ;

SELECT count_substring('hello world hello', 'hello'); -- 返回 2

这段代码定义了一个名为 count_substring 的自定义函数，用于统计子字符串 substr 在字符串 str 中出现的次数。但是，在实际应用中，应尽量避免在 MySQL 中使用循环和递归，因为这会严重影响性能。可以考虑在应用程序代码中实现这个功能。

总结

INSTR() 函数是 MySQL 中一个强大而实用的字符串查找函数。掌握 INSTR() 的基本语法和应用场景，可以帮助我们更高效地处理字符串数据。了解 INSTR() 和 LOCATE() 的区别，可以根据实际情况选择更合适的函数。通过合理的索引和优化，可以提高字符串搜索的性能。

灵活使用字符串函数，提升数据处理效率

总而言之，INSTR() 函数在字符串处理中扮演着重要角色。熟练掌握其用法，并结合其他字符串函数，可以极大地提升数据处理的效率和灵活性。记住，在实际应用中，性能优化始终是需要考虑的重要因素。