MySQL JSON INSERT操作中单反斜杠转义异常如何解决

转义报错、存储内容看起来不符合预期的核心原因是写入JSON字段时存在两层独立生效的转义规则，两层规则先后对反斜杠做解析，仅做单次转义无法满足要求，对存储结果的判断方式错误也会误以为存在多余反斜杠，两层规则的具体逻辑如下：

• 第一层：MySQL SQL语法的转义规则
  MySQL默认SQL模式下，字符串中的反斜杠是转义前缀，要表示1个实际的反斜杠，必须在SQL字符串里写成\\。SQL解析器执行语句时，会先把所有转义序列还原，之后才会把值传给JSON类型的校验、解析逻辑。
• 第二层：JSON规范的转义规则
  JSON语法本身不允许字符串中出现无转义的单个反斜杠，仅支持\"、\\、\/、\b、\f、\n、\r、\t、\uXXXX这几组合法转义序列。要在JSON字符串里表示1个实际的反斜杠，必须写成\\。如果JSON解析器收到的内容里出现\d这类不被认可的转义组合，就会抛出Invalid escape character in string错误。

对应操作过程中的两类问题：

1. 第一次写入写为^\\d*[13579]$时，经过SQL层解析后，传给JSON解析器的内容是^\d*[13579]$，其中\d属于非法转义序列，直接触发报错。
2. 第二次写入多加反斜杠后语法校验通过，但看到的“多余反斜杠”并不是实际存储的内容有问题——可视化工具、原始字段查询返回的是JSON序列化后的标准文本，为了符合JSON语法会自动给反斜杠加转义，不是字段里真的存了多个反斜杠。

根据写入方式的不同，转义规则有区别，以下两种常用场景都能保证最终读取到的正则和原表达式^\d*[13579]$完全一致：

• 场景1：直接写JSON字面量执行插入
  这种场景需要同时满足SQL、JSON两层转义要求：正则里的1个反斜杠，最终在SQL插入语句的字符串里要写4个反斜杠，示例：

  INSERT INTO 你的表名 (json字段名) VALUES ('{"your_regex_key": "^\\\\d*[13579]$"}');
  

  解析流程验证：SQL层先把4个反斜杠解析为2个，传给JSON解析器的内容是{"your_regex_key": "^\\d*[13579]$"}，完全符合JSON语法；JSON层再把2个反斜杠解析为1个，最终存储的实际字符串值就是目标正则。
• 场景2：通过MySQL JSON构造函数、预处理语句、ORM框架写入
  这种场景下JSON格式的转义会由函数、程序驱动自动完成，你只需要满足SQL层的转义要求即可：正则里的1个反斜杠，在传入的字符串参数里写2个反斜杠就行，示例：

  -- 用JSON_OBJECT构造JSON值的写法
  INSERT INTO 你的表名 (json字段名) VALUES (JSON_OBJECT('your_regex_key', '^\\d*[13579]$'));
  

校验提示：不要直接通过可视化工具的表数据浏览界面、或者直接查询JSON字段原始值判断存储是否正确，这类场景返回的是JSON序列化后的带转义文本。必须用MySQL的JSON提取操作符->>取出目标字符串值做校验，示例语句：

SELECT json字段名->>'$.your_regex_key' FROM 你的表名;

该语句返回的结果就是无转义的原始正则内容，可直接和原表达式做比对。

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