MySQL中使用substring_index未找到值时返回NULL的实现方案

嘿，这个问题我之前也帮人处理过！substring_index确实有个小坑——当要找的分隔符或者目标内容不存在时，它不会返回NULL，而是直接返回整个原字段或者最后一段有效内容，这确实不符合你的需求。下面分两种常见场景给你对应的解决方案：

场景1：按固定分隔符的位置提取值（比如按|取第2、第3个元素） #

如果你的需求是从用某个分隔符（比如|）拼接的大字段里提取指定位置的元素，那可以先统计字段里分隔符的数量，判断是否足够提取目标位置的元素，不够就返回NULL：

SELECT 
  id,
  -- 提取第2个元素，当分隔符数量>=1时才有效（元素数=分隔符数+1）
  CASE 
    WHEN (LENGTH(big_field) - LENGTH(REPLACE(big_field, '|', ''))) >= 1 
    THEN SUBSTRING_INDEX(SUBSTRING_INDEX(big_field, '|', 2), '|', -1)
    ELSE NULL 
  END AS value_2,
  -- 提取第3个元素，需要分隔符数量>=2
  CASE 
    WHEN (LENGTH(big_field) - LENGTH(REPLACE(big_field, '|', ''))) >= 2 
    THEN SUBSTRING_INDEX(SUBSTRING_INDEX(big_field, '|', 3), '|', -1)
    ELSE NULL 
  END AS value_3
FROM your_table;

核心逻辑：用LENGTH(big_field) - LENGTH(REPLACE(big_field, '|', ''))计算字段里|的总个数，只有当个数满足目标元素的位置要求时，才调用substring_index提取，否则直接返回NULL。

场景2：提取特定标识后的值（比如从key1=xxx;key2=yyy中取key1、key2的值） #

如果你的大字段是类似键值对的格式，需要提取某个特定键对应的值，那可以先用LOCATE()判断这个键是否存在，再决定是否提取：

SELECT 
  id,
  -- 提取key1对应的值
  CASE 
    WHEN LOCATE('key1=', big_field) > 0 
    THEN SUBSTRING_INDEX(SUBSTRING_INDEX(big_field, 'key1=', -1), ';', 1)
    ELSE NULL 
  END AS key1_value,
  -- 提取key2对应的值
  CASE 
    WHEN LOCATE('key2=', big_field) > 0 
    THEN SUBSTRING_INDEX(SUBSTRING_INDEX(big_field, 'key2=', -1), ';', 1)
    ELSE NULL 
  END AS key2_value
FROM your_table;

LOCATE('key1=', big_field)会返回key1=第一次出现的位置，如果字段里没有这个标识，就返回0，这时我们直接返回NULL；如果存在，就用substring_index先截取key1=之后的内容，再截取到下一个分隔符（这里用;举例）之前的部分，就是对应的值。

额外提示（MySQL 8.0+适用） #

如果你用的是MySQL 8.0及以上版本，还可以用REGEXP_SUBSTR()函数实现更灵活的提取，同样能结合判断返回NULL：

SELECT 
  id,
  CASE 
    WHEN big_field REGEXP 'key1=[^;]+'
    THEN REGEXP_SUBSTR(big_field, 'key1=([^;]+)', 1, 1, 'c', 1)
    ELSE NULL 
  END AS key1_value
FROM your_table;

这个正则表达式会匹配key1=后面到;之前的内容，REGEXP_SUBSTR的最后一个参数1表示返回第一个捕获组的内容，用起来更简洁。

内容的提问来源于stack exchange，提问作者PJD