SQL查询：利用FIND_IN_SET()函数高效匹配逗号分隔字符串中的值

在数据库操作中，我们经常会遇到需要根据一个动态的、由逗号分隔的字符串来筛选数据的情况。例如，给定一个字符串"a0007,a0008,a0009"，我们希望从data表中选出col1列的值等于a0007、a0008或a0009的任何一行。传统的处理方式往往效率低下且难以维护。

传统方法的局限性

在尝试解决这类问题时，开发者通常会考虑以下两种方法，但它们都存在明显的不足：

1. 使用多个OR条件

当匹配值数量固定且较少时，直接在WHERE子句中使用OR逻辑似乎可行。

SELECT col1, col2, col3
FROM data
WHERE col1 = 'A0001' OR col1 = 'A0002';

问题： 这种方法的主要缺点是其不可扩展性。如果逗号分隔字符串中的值数量是动态变化的，我们就无法预先构建这种SQL语句。每次值列表改变，都需要重新拼接SQL字符串，这不仅繁琐，而且容易出错，更重要的是，它无法应对值数量过大时SQL语句长度的限制。

2. 循环执行单条查询

另一种尝试是将逗号分隔字符串拆分成数组，然后在一个循环中为每个值单独执行一次SQL查询。

<?php
$comaSeperatedString = "A0007,A0008,A0009,A0010,A0011,A0012";
$col1_arr = explode(",", $comaSeperatedString);

foreach ($col1_arr as $dataItem) {
    $sqlData = $this->con->prepare("SELECT col1, col2, col3 FROM data WHERE col1 = :item");
    $sqlData->bindParam(':item', $dataItem);
    $sqlData->execute();
    // 处理查询结果
}
?>

问题： 这种方法虽然解决了动态值的问题，但其性能极差。每次循环都意味着一次数据库连接、查询解析和执行的开销。对于包含大量值的字符串，这将导致成百上千次的数据库往返，极大地增加了服务器负担和响应时间。这显然是一种“笨拙且浪费”的做法。

使用FIND_IN_SET()函数进行高效匹配

为了克服上述传统方法的局限性，MySQL提供了一个非常实用的字符串函数：FIND_IN_SET(str, strlist)。

FIND_IN_SET()函数简介

FIND_IN_SET(str, strlist)函数用于在由逗号分隔的字符串列表strlist中查找字符串str。

• 如果str在strlist中找到，它将返回str在strlist中的位置（从1开始）。
• 如果未找到，则返回0。
• 如果任一参数为NULL，则返回NULL。

利用这个特性，我们可以将整个逗号分隔的字符串作为strlist参数，并将要匹配的列（例如col1）作为str参数，从而在一个SQL查询中完成多值匹配。

示例代码：结合预处理语句和参数绑定

下面是使用FIND_IN_SET()函数结合PHP的PDO预处理语句实现高效查询的示例：

<?php
// 假设这是从前端或业务逻辑中获取的逗号分隔字符串
$comaSeperatedString = "A0007,A0008,A0009,A0010,A0011,A0012";

// 准备SQL查询语句
// FIND_IN_SET(col1, :values) 会检查 col1 的值是否在 :values 字符串中
$query = $this->con->prepare('SELECT col1, col2, col3 FROM data WHERE FIND_IN_SET(col1, :values)');

// 绑定参数
// 使用参数绑定是防止SQL注入的关键措施
$query->bindParam(':values', $comaSeperatedString);

// 执行查询
$query->execute();

// 获取并处理查询结果
$results = $query->fetchAll(PDO::FETCH_ASSOC);

foreach ($results as $row) {
    echo "Col1: " . $row['col1'] . ", Col2: " . $row['col2'] . ", Col3: " . $row['col3'] . PHP_EOL;
}
?>

代码解析：

怪兽AI数字人

数字人短视频创作，数字人直播，实时驱动数字人

44

查看详情

1. 我们将整个逗号分隔的字符串$comaSeperatedString作为单个参数传递给SQL查询。
2. FIND_IN_SET(col1, :values)在WHERE子句中执行匹配逻辑。如果col1的值在$comaSeperatedString中，FIND_IN_SET将返回一个大于0的整数，条件为真，该行将被选中。
3. 通过使用预处理语句（prepare）和参数绑定（bindParam），我们不仅提高了查询的安全性，有效防止SQL注入，还确保了查询的效率，因为数据库可以缓存查询计划。

注意事项与最佳实践

在使用FIND_IN_SET()函数时，需要考虑以下几点以确保代码的健壮性和性能：

1. 安全性：SQL注入防护

始终使用预处理语句和参数绑定来处理用户输入或动态生成的查询参数。如上述示例所示，将整个逗号分隔字符串作为绑定参数传递给FIND_IN_SET()函数是安全的，因为它将字符串视为一个整体值，而不是SQL代码的一部分。直接将字符串拼接到SQL语句中（即使是FIND_IN_SET内部）都可能导致SQL注入风险。

2. 性能考量

• 索引： FIND_IN_SET()函数通常不能有效利用col1列上的索引。这意味着，对于包含大量数据的表，FIND_IN_SET()可能会导致全表扫描，从而影响查询性能。如果性能是关键考量，并且值列表是固定的或数量较少，可以考虑将逗号分隔字符串拆分成数组，然后使用IN操作符，因为它通常能更好地利用索引。

  <?php
  // 当值列表可以拆分并作为离散值传递时，IN 操作符通常性能更优
  $comaSeperatedString = "A0007,A0008,A0009";
  $col1_arr = explode(",", $comaSeperatedString);
  $placeholders = implode(',', array_fill(0, count($col1_arr), '?'));
  
  $query = $this->con->prepare("SELECT col1, col2, col3 FROM data WHERE col1 IN ($placeholders)");
  $query->execute($col1_arr); // 直接传递数组作为execute的参数
  ?>

  登录后复制

  然而，当输入是一个必须作为单个字符串处理的逗号分隔列表时，FIND_IN_SET()是更直接的解决方案。

• 数据量： 避免在非常大的逗号分隔字符串中使用FIND_IN_SET()，因为字符串长度限制和内部处理复杂性可能导致性能下降。MySQL的group_concat_max_len配置项会影响FIND_IN_SET处理的字符串最大长度。

3. 数据库兼容性

FIND_IN_SET()是MySQL特有的函数。如果您使用的是其他数据库系统，如PostgreSQL、SQL Server或Oracle，则需要寻找其对应的功能：

• PostgreSQL： 可以使用string_to_array()将字符串转换为数组，然后使用ANY或IN操作符。

  SELECT col1, col2, col3 FROM data WHERE col1 = ANY(string_to_array('A0007,A0008,A0009', ','));

  登录后复制
• SQL Server： 可以使用STRING_SPLIT()函数（SQL Server 2016及更高版本）将字符串拆分为表，然后进行JOIN或IN操作。

  SELECT d.col1, d.col2, d.col3
  FROM data d
  JOIN STRING_SPLIT('A0007,A0008,A0009', ',') s ON d.col1 = s.value;

  登录后复制
• Oracle： 可以通过正则表达式或XMLTable等方式解析字符串。

因此，在跨数据库平台开发时，请务必注意函数的可移植性。

总结

当需要在SQL查询中根据一个动态的逗号分隔字符串进行多值匹配时，MySQL的FIND_IN_SET()函数提供了一个简洁且高效的解决方案。它避免了多OR条件带来的不可扩展性，以及循环执行单条查询所导致的性能瓶颈。结合预处理语句和参数绑定，FIND_IN_SET()能够构建出安全、高效的查询。然而，开发者也应了解其在索引利用和跨数据库兼容性方面的局限性，并根据具体的业务场景和性能要求，灵活选择最合适的查询策略。在大多数MySQL场景下，FIND_IN_SET()是处理此类动态多值匹配问题的优选方案。

以上就是SQL查询：利用FIND_IN_SET()函数高效匹配逗号分隔字符串中的值的详细内容，更多请关注php中文网其它相关文章！