如何通过Knex.js在PostgreSQL中转义login列的特殊字符

现有数据表：

需要实现类似JavaScript方法strstr.replace(/[.*+?^${}()|[\]\\]/g, "\\$&")的效果，对login列中的特殊字符进行转义（给每个特殊字符前添加反斜杠）。

尝试过的方法及错误 #

1. Knex.js写法：

knex('orders')
      .update({
        login: knex.raw(`REPLACE(login, '/[-[\]{}()*+?.,\\^$|#\s]/g', '\\$&')`)
        // 另一种写法
        login: knex.raw(`REPLACE(login, regexp_match(login, '/[-[\]{}()*+?.,\\^$|#\s]/g'), '\\$&')`)
      })

报错：replace(character varying, text[], unknown) does not exist，原因是REPLACE仅支持普通字符串替换，不支持正则匹配；而regexp_match返回数组类型，和REPLACE的参数类型不兼容。

2. 直接SQL尝试：

update "orders" set "login" = REGEXP_REPLACE(login,'[-[]{}()*+$1.,\\^$|#s]', '\\$&', 'g');

报错正则无效，去掉+后语句可运行，但无法覆盖所有需要转义的字符。

可以通过PostgreSQL的REGEXP_REPLACE函数实现需求，核心是适配PostgreSQL的正则语法，正确处理特殊字符转义规则：

1. 正确的SQL更新语句 #

UPDATE "orders" 
SET "login" = REGEXP_REPLACE(login, '[.*+?^${}()|\\[\\]\\\\]', '\\\\$&', 'g');

• 正则模式[.*+?^${}()|\\[\\]\\\\]：精准匹配所有需要转义的特殊字符（.、*、+、?、^、$、{、}、(、)、|、[、]、\），注意在SQL中\需双重转义，[和]在方括号内也需转义。
• 替换字符串\\\\$&：$&代表正则匹配到的内容，\\\\在SQL中最终解析为单个\，实现给匹配字符添加反斜杠转义的效果。
• 参数'g'表示全局替换，处理所有符合条件的字符。

2. 适配Knex.js的写法 #

由于JS模板字符串中反斜杠需要再次转义，调整后的代码如下：

knex('orders')
  .update({
    login: knex.raw(`REGEXP_REPLACE(login, '[.*+?^${}()|\\\\[\\\\]\\\\\\\\]', '\\\\\\\\$&', 'g')`)
  })

3. 原错误原因说明 #

你之前的正则报错是因为包含无意义的$1（未定义捕获组却引用），同时未正确转义[、]、\等特殊字符，导致正则语法无效。修正正则模式后，+可正常被匹配和转义。

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