如何在MySQL JOIN中使用字符串且不使用IN语句

嘿，我完全理解你的顾虑——当订单ID数量上千的时候，IN子句确实会拖慢查询速度，而且从可读性和性能优化的角度来说，确实不是最优选择。这里有几个实用的方案，能让你在JOIN操作里高效使用这批订单ID：

方案1：使用临时表（推荐大数量场景） #

临时表是处理这类批量ID最稳妥的方式，因为你可以给它加索引，让JOIN的性能大幅提升：

1. 首先创建临时表：

CREATE TEMPORARY TABLE temp_order_ids (
    order_id INT PRIMARY KEY
);

2. 将逗号分隔的ID字符串拆分后插入临时表：
   • 如果你用的是MySQL 8.0+，可以直接用内置的STRING_SPLIT函数：

   INSERT INTO temp_order_ids (order_id)
   SELECT value FROM STRING_SPLIT('1,2,3,4,5,6', ',');
   

   • 如果是在应用层（比如PHP），可以先把字符串拆成数组，再用批量插入语句，效率会更高：

   $OrderIds = "1,2,3,4,5,6";
   $ids = explode(',', $OrderIds);
   // 批量插入的SQL语句，这里用预处理语句避免SQL注入
   $stmt = $pdo->prepare("INSERT INTO temp_order_ids (order_id) VALUES (?)");
   foreach ($ids as $id) {
       $stmt->execute([$id]);
   }
   

3. 最后和目标表JOIN查询：

SELECT o.*
FROM orders o
JOIN temp_order_ids toi ON o.id = toi.order_id;

方案2：用CTE（公共表表达式）拆分字符串（适合一次性查询） #

如果不想创建临时表，MySQL 8.0+支持的CTE可以直接把字符串拆分成虚拟表，然后JOIN：

WITH split_order_ids AS (
    SELECT value AS order_id
    FROM STRING_SPLIT('1,2,3,4,5,6', ',')
)
SELECT o.*
FROM orders o
JOIN split_order_ids soi ON o.id = soi.order_id;

这个方案不用额外维护临时表，适合单次查询，但如果ID数量特别大，性能可能略逊于带索引的临时表。

方案3：兼容老版本MySQL的递归CTE拆分 #

如果你的MySQL版本低于8.0，没有STRING_SPLIT函数，可以用递归CTE来拆分字符串：

SET @order_ids = '1,2,3,4,5,6';
WITH RECURSIVE split_ids AS (
    SELECT 
        SUBSTRING_INDEX(@order_ids, ',', 1) AS order_id,
        SUBSTRING(@order_ids, LOCATE(',', @order_ids) + 1) AS remaining
    UNION ALL
    SELECT 
        SUBSTRING_INDEX(remaining, ',', 1) AS order_id,
        SUBSTRING(remaining, LOCATE(',', remaining) + 1) AS remaining
    FROM split_ids
    WHERE remaining != ''
)
SELECT o.*
FROM orders o
JOIN split_ids si ON o.id = si.order_id;

注意：如果ID数量极多，可能需要调整MySQL的递归深度限制（max_recursion_depth参数），但整体性能还是比IN子句好。

额外注意事项 #

• 确保订单ID的类型和目标表的ID类型一致（比如都是INT），避免隐式类型转换导致索引失效；
• 批量插入时一定要用预处理语句，防止SQL注入问题；
• 如果是频繁执行这类查询，临时表的方案会更高效，因为临时表的索引可以重复利用。

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