SQL查询仅返回唯一值且顺序颠倒，如何按指定顺序返回全部结果？

问题根源 #

1. IN子句不会保证返回结果的顺序与传入的ID列表一致，数据库默认按自身存储/索引规则排序。
2. 直接拼接ID列表的写法存在SQL注入风险，且无法保留原数组的重复项对应关系。

修改后的查询方案（以PostgreSQL为例，参数化查询） #

const result = await db.manyOrNone(`
  SELECT t.value
  FROM (
    -- 将传入的ID数组拆分为带顺序索引的临时表
    SELECT unnest($1::text[]) AS id, generate_series(1, array_length($1::text[], 1)) AS idx
  ) AS arr
  -- 关联原数据表，保留每个ID的匹配行
  JOIN "Table" t ON t.id = arr.id
  -- 按临时表的索引排序，保证与原ID数组顺序一致
  ORDER BY arr.idx
`, [idArray]);

MySQL适配方案 #

如果使用MySQL，可通过变量生成顺序索引：

const result = await db.manyOrNone(`
  SELECT t.value
  FROM (
    SELECT id, @row := @row + 1 AS idx
    FROM (
      -- 生成包含所有重复ID的临时数据集
      ${idArray.map(() => 'SELECT ? AS id').join(' UNION ALL ')}
    ) AS arr, (SELECT @row := 0) AS init
  ) AS arr
  JOIN \`Table\` t ON t.id = arr.id
  ORDER BY arr.idx
`, idArray);

关键说明 #

• 采用参数化查询替代字符串拼接，彻底避免SQL注入风险。
• 通过临时表绑定每个ID的顺序索引，确保最终结果的顺序与原ID数组完全一致。
• 关联查询会保留原数据表中所有匹配的行，包括重复ID对应的重复VALUE值。

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