JavaScript 正则表达式在 SQL 插入中获取单引号内的多个单引号

Question

问题

我正在进行数据库迁移，遇到以下情况，插入的单引号内有单引号，经过多次尝试修复后，我得出的结论是我需要使用正则表达式，然后我到达了以至于我可以替换文本中心的单引号，现在当它出现在开头时我遇到了问题。我的正则表达式有前两个单引号，但我的目标是最后两个。

SQL 插入:

(1, '22', '2006-11-10', '3', 'field1', 'field2', '2006-12-12', '11:20:06', '''field3', '

Lorem ipsum dolor sit amet, consectetur adipiscing elit. Vivamus ex ipsum, luctus id dignissim tempor, volutpat eget sapien. Cras accumsan molestie iaculis. Mauris vehicula a nunc vel tincidunt. 
 Class aptent taciti sociosqu ad litora torquent per conubia nostra, per inceptos himenaeos. 

    - Lorem ipsum dolor '' sit amet - ''''

 Aliquam vestibulum tincidunt odio, id varius sapien lacinia ac. Phasellus ullamcorper ante ut lacus dictum commodo.
  Integer sit amet lacus ornare massa feugiat tempus. 

', null, null);

我的正则表达式(js):

((["'])(?:(?=(\'\'\?))\2)*?)\1(?!,)

在正则表达式并进行查找和替换之后，我的目标是得到以下结果:

(1, '22', '2006-11-10', '3', 'field1', 'field2', '2006-12-12', '11:20:06', '\'\'field3', '

Lorem ipsum dolor sit amet, consectetur adipiscing elit. Vivamus ex ipsum, luctus id dignissim tempor, volutpat eget sapien. Cras accumsan molestie iaculis. Mauris vehicula a nunc vel tincidunt. 
 Class aptent taciti sociosqu ad litora torquent per conubia nostra, per inceptos himenaeos. 

    - Lorem ipsum dolor \'\' sit amet - \'\'\'\'

 Aliquam vestibulum tincidunt odio, id varius sapien lacinia ac. Phasellus ullamcorper ante ut lacus dictum commodo.
  Integer sit amet lacus ornare massa feugiat tempus. 

', null, null);

我正在使用此网站进行测试:https://regex101.com/ (选择了 JavaScript)

Answer 1

一种选择是匹配 ' 或 " 分隔符，然后延迟重复，直到再次匹配该分隔符后跟逗号 (或 )，如果该项目是列表中的最后一项)。然后，使用替换函数将分隔符内的所有内容替换为转义的 ':

const input = `(1, '22', '2006-11-10', '3', 'field1', 'field2', '2006-12-12', '11:20:06', '''field3', '

Lorem ipsum dolor sit amet, consectetur adipiscing elit. Vivamus ex ipsum, luctus id dignissim tempor, volutpat eget sapien. Cras accumsan molestie iaculis. Mauris vehicula a nunc vel tincidunt. 
 Class aptent taciti sociosqu ad litora torquent per conubia nostra, per inceptos himenaeos. 

    - Lorem ipsum dolor '' sit amet - ''''

 Aliquam vestibulum tincidunt odio, id varius sapien lacinia ac. Phasellus ullamcorper ante ut lacus dictum commodo.
  Integer sit amet lacus ornare massa feugiat tempus. 

', null, null);`;


const output = input.replace(
  /(["'])([\s\S]*?)\1(?=[,)])/g,
  (_, delim, content) => delim + content.replace(/'/g, "\\'") + delim
);
console.log(output);

https://regex101.com/r/RRmq8g/1

如果您想在分隔符为 " 时转义 "，请从 delim 构造替换正则表达式:

const output = input.replace(
  /(["'])([\s\S]*?)\1(?=[,)])/g,
  (_, delim, content) => {
    const pattern = new RegExp(delim, 'g')
    return delim + content.replace(pattern, '\\' + delim) + delim
  }
);

const input = `(1, "22 foo bar "double quotes" here ", '2006-11-10', '3', 'field1', 'field2', '2006-12-12', '11:20:06', '''field3', '

Lorem ipsum dolor sit amet, consectetur adipiscing elit. Vivamus ex ipsum, luctus id dignissim tempor, volutpat eget sapien. Cras accumsan molestie iaculis. Mauris vehicula a nunc vel tincidunt. 
 Class aptent taciti sociosqu ad litora torquent per conubia nostra, per inceptos himenaeos. 

    - Lorem ipsum dolor '' sit amet - ''''

 Aliquam vestibulum tincidunt odio, id varius sapien lacinia ac. Phasellus ullamcorper ante ut lacus dictum commodo.
  Integer sit amet lacus ornare massa feugiat tempus. 

', null, null);`;


const output = input.replace(
  /(["'])([\s\S]*?)\1(?=[,)])/g,
  (_, delim, content) => {
    const pattern = new RegExp(delim, 'g')
    return delim + content.replace(pattern, '\\' + delim) + delim
  }
);
console.log(output);

/(["'])([\s\S]*?)\1(?=[,)])/g 表示:

• (["']) 匹配并捕获分隔符
• ([\s\S]*?) - 重复任意字符，直到:
• \1 - 分隔符，后跟
• (?=[,)]) - 逗号或 )