如何使用SQL的POSITION函数查找子字符串-SQL-PHP中文网

position函数返回子串首次出现的1-based位置，未找到返回0；postgresql用position('sub' in str)，mysql用locate('sub', str)，sql server用charindex('sub', str)，语法不通用，跨库需适配。

如何使用sql的position函数查找子字符串

POSITION函数在不同数据库里的写法差异

POSITION不是SQL标准统一函数，各数据库实现方式不同：PostgreSQL和标准SQL用POSITION('substr' IN 'string')，MySQL用LOCATE('substr', 'string')，SQL Server得用CHARINDEX('substr', 'string')。直接照搬语法会报错，比如在MySQL里写POSITION会提示Unknown function 'POSITION'。

常见错误是把其他语言的indexOf习惯带进来，以为所有数据库都支持POSITION('a' IN col)这种写法——只在PostgreSQL、Oracle（部分版本）、标准SQL中有效。

PostgreSQL：支持POSITION('x' IN col)，返回从1开始的位置；找不到返回0
MySQL：必须用LOCATE('x', col)，同样从1开始，找不到返回0
SQL Server：用CHARINDEX('x', col)，也是从1开始，找不到返回0
SQLite：支持INSTR(col, 'x')，但注意参数顺序是INSTR(haystack, needle)，和MySQL相反

为什么POSITION返回的是1-based索引而不是0-based

SQL标准规定字符串位置从1开始计数，这和大多数编程语言（如Python、JavaScript）的0-based索引不同。如果用POSITION('ab' IN 'abcde')，结果是1，不是0。误当成0-based去截取就会偏移一位，比如配合SUBSTRING时写成SUBSTRING(col, POSITION('x' IN col), 2)是对的，但若写成SUBSTRING(col, POSITION('x' IN col) - 1, 2)就会漏掉第一个字符。

实际场景中，这个特性常被用来做条件过滤：WHERE POSITION('admin' IN username) > 0，等价于username LIKE '%admin%'，但前者在某些引擎上无法走索引，性能更差。

POSITION和LIKE、INSTR混用时的性能陷阱

单纯查是否存在子串，POSITION(x IN col) > 0比col LIKE '%x%'在语义上更清晰，但执行计划往往一样——都是全表扫描。真正影响性能的是是否能走索引：只有前缀匹配（如col LIKE 'x%'）才可能用上B-tree索引，而POSITION无论怎么写都无法触发索引查找。

MySQL

编写正确的MySQL查询，避免字符集、索引和锁方面的常见陷阱。

下载

如果字段很长（比如TEXT类型），且频繁查子串位置，考虑加生成列+索引（PostgreSQL支持表达式索引，MySQL 5.7+支持生成列）：

ALTER TABLE logs ADD COLUMN first_dot_pos INT GENERATED ALWAYS AS (POSITION('.' IN host)) STORED;

但注意：生成列值依赖函数确定性，POSITION是确定性函数，没问题；而像NOW()这类就不行。

嵌套使用POSITION提取中间段时的边界问题

想从'user@domain.com'里提取'domain'，容易这么写：SUBSTRING(email, POSITION('@' IN email) + 1, POSITION('.' IN email) - POSITION('@' IN email) - 1)。但只要邮箱不含点号（比如'test@local'），第二个POSITION就返回0，导致长度算出负数，结果为空或报错（取决于数据库）。

安全做法是先判断存在性，或用COALESCE兜底：

SELECT SUBSTRING(email,
  POSITION('@' IN email) + 1,
  COALESCE(NULLIF(POSITION('.' IN email), 0), LENGTH(email) + 1)
    - POSITION('@' IN email) - 1)
FROM users;

真正麻烦的不是语法，而是子串重复出现时该取哪一次——POSITION只返回第一次匹配位置，没有类似REGEXP_INSTR的occurrence参数。需要第二次出现时，得先用SUBSTRING切掉第一段再查。