PostgreSQL中使用Dapper通过列表过滤/排除数据的类型问题

问题核心 #

使用<> ANY(:bookIds)无法实现排除列表中所有ID的需求（该语法是OR逻辑，只要不等于列表中任意一个就匹配）；直接用NOT IN会因UUID类型匹配报错；字符串拼接会导致类型转换错误（uuid <> text）。

正确解决方案 #

方案1：使用NOT (book_id = ANY(:bookIds))

这是最直观且语义清晰的写法，等价于NOT IN，同时能正确适配UUID类型：

await using var conn = new NpgsqlConnection(_dbContext.Database.GetConnectionString());
await conn.ExecuteAsync(@"
    UPDATE books
    SET discount = :discount
    WHERE author_id = :authorId
    AND NOT (book_id = ANY(:bookIds));
", new { discount = discountVal, authorId = author, bookIds = BookIdList });

方案2：使用<> ALL(:bookIds)

该语法表示book_id不等于列表中的所有元素，效果和NOT IN一致，但需注意：当传入的列表为空时，<> ALL会返回true（匹配所有数据），而NOT IN空列表会返回false（无匹配），根据业务场景选择：

await using var conn = new NpgsqlConnection(_dbContext.Database.GetConnectionString());
await conn.ExecuteAsync(@"
    UPDATE books
    SET discount = :discount
    WHERE author_id = :authorId
    AND book_id <> ALL(:bookIds);
", new { discount = discountVal, authorId = author, bookIds = BookIdList });

错误写法分析 #

• <> ANY(:bookIds)：逻辑为book_id不等于列表中任意一个元素即匹配，与“排除所有列表内ID”的需求相反。
• NOT IN (:bookIds)：Dapper对PostgreSQL的NOT IN数组参数支持不如ANY友好，容易出现类型匹配问题。
• 字符串拼接：强制将UUID转为文本，与数据库的uuid类型无法直接比较，同时存在SQL注入风险，严禁使用。

内容的提问来源于stack exchange，提问作者A. Ramirez