MySQL instr函数参数如何正确使用？

INSTR() 函数用于返回一个子字符串在另一个字符串中第一次出现的位置。

基本语法：

INSTR(str, substr)

参数详解

INSTR 函数接受两个主要参数,这两个参数都是必需的。

参数 1: str (被搜索的字符串)

• 类型: String (字符串)
• 描述: 这是您要在其中进行搜索的原始字符串或字符串表达式，它可以是字面量、表中的列名,或者任何可以解析为字符串的表达式。
• 示例:
  • 'Hello, World!'
  • user_email (假设 user_email 是一个包含电子邮件地址的列)
  • CONCAT(first_name, ' ', last_name) (一个由多列拼接而成的字符串)

参数 2: substr (要查找的子字符串)

• 类型: String (字符串)
• 描述: 这是您要在 str 中查找的目标子字符串，它同样可以是字面量、列名或字符串表达式。
• 示例:
  • 'World'
  • domain_name (假设 domain_name 是一个包含域名后缀的列)

返回值

INSTR 函数的返回值是一个整数,具体含义如下：

• 成功找到: 返回 substr 在 str 中第一次出现的起始位置。位置计数从 1 开始，而不是从 0 开始。
• 未找到: str 中不包含 substr，则返回 0。
• 参数为 NULL: 如果任一参数 (str 或 substr) 为 NULL，则整个函数的返回值也为 NULL。

使用示例

让我们通过一些实际的例子来更好地理解这个函数。

示例 1: 基本用法

SELECT INSTR('Hello, World!', 'World');

结果:

8

解释: 子字符串 'World' 在 'Hello, World!' 中从第 8 个字符开始。

示例 2: 未找到的情况

SELECT INSTR('Hello, World!', 'MySQL');

结果:

0

解释: 因为 'Hello, World!' 中不包含 'MySQL'，所以返回 0。

示例 3: 查找单个字符

SELECT INSTR('example@email.com', '@');

结果:

8

解释: 符号出现在第 8 个位置。

示例 4: 在表数据中使用

假设我们有一个 users 表,结构如下：

场景: 找出所有 符号出现在域名部分（ 之后）的第一个字符是 'o' 的用户。

SELECT
    username,
    email,
    INSTR(email, '@') AS at_symbol_position
FROM
    users
WHERE
    INSTR(SUBSTRING(email, INSTR(email, '@') + 1), 'o') > 0;

查询分析:

1. INSTR(email, '@'): 找到 符号的位置。
2. SUBSTRING(email, INSTR(email, '@') + 1): 从 符号的下一个字符开始截取字符串，得到域名部分（'example.com', 'another.org'）。
3. INSTR(SUBSTRING(...), 'o'): 在截取出的域名部分查找字母 'o'。
4. ... > 0: 如果找到，返回值会大于 0，WHERE 条件成立。

结果: | username | email | at_symbol_position | |----------|--------------------|---------------------| | john_doe | john@example.com | 5 | | admin | admin@localhost | 6 |

与 LOCATE 和 POSITION 的对比

在 MySQL 中，有多个函数可以实现类似的功能,了解它们的区别很重要。

示例对比: 这三个查询的结果是完全一样的。

SELECT INSTR('Hello World', 'World');       -- 返回 7
SELECT LOCATE('World', 'Hello World');      -- 返回 7
SELECT POSITION('World' IN 'Hello World');   -- 返回 7

推荐:

• LOCATE 和 POSITION 的参数顺序 (find in) 在逻辑上更直观,因此更受推荐。
• INSTR 也很常见，特别是在从其他数据库（如 Oracle）迁移过来的代码中。

性能考虑

对于简单的字符串查找，INSTR、LOCATE 和 POSITION 的性能差异可以忽略不计，但在处理非常大的字符串或进行大量数据查询时,需要注意：

• 索引: 如果您需要在 WHERE 子句中频繁使用 INSTR 函数来搜索文本（WHERE INSTR(description, 'keyword') > 0），那么普通索引（B-Tree）通常无法被有效利用,数据库无法通过索引直接定位包含某个关键词的行。
• 全文索引: 对于在文本内容中搜索关键词的需求，MySQL 提供了全文索引（FULLTEXT Index），它在性能上远超 INSTR，并且支持更复杂的搜索逻辑（如布尔搜索、相关性评分等），如果您有全文搜索的需求，应优先考虑使用 FULLTEXT 索引和 MATCH() ... AGAINST() 语法。