如何在Postgres中通过Node.js查询并更新表内匹配特定关键词的记录

核心匹配逻辑 #

Postgres实现不区分大小写的字段内容匹配有两种常用方案：

• 使用ILIKE运算符（不区分大小写的LIKE匹配），配合通配符%匹配任意字符，animal ILIKE '%dog%'即可匹配所有animal字段包含dog、Dog、DOG等任意大小写组合的记录
• 使用正则匹配运算符~*（不区分大小写的正则匹配），animal ~* 'dog'可以实现和上述ILIKE完全相同的效果

更新操作SQL语句 #

要将匹配到的记录的scary字段设为yes，使用UPDATE语句即可：

UPDATE petlist
SET scary = 'yes'
WHERE animal ILIKE '%dog%';
-- 如果仅需要修改原来scary为no的记录，增加条件即可
-- UPDATE petlist SET scary = 'yes' WHERE animal ILIKE '%dog%' AND scary = 'no';

对应Node.js实现代码 #

参数化查询时注意把通配符%放在参数值中，避免SQL注入风险：

app.get('/update_dog_scary', (req, res) => {
  pool.query(
    "UPDATE petlist SET scary = 'yes' WHERE animal ILIKE $1",
    ['%dog%'],
    (error, results) => {
      if (error) {
        console.log("更新失败")
        throw error
      }
      // results.rowCount返回被修改的记录条数
      res.status(200).json({
        updatedCount: results.rowCount
      })
    }
  )
});

测试匹配结果 #

如果需要先验证匹配的记录是否正确，可以先执行查询语句确认：

SELECT * FROM petlist WHERE animal ILIKE '%dog%';

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