mysql - 正确转义 JSON 特殊字符以在 CSV 文件中使用

Question

从 Twitter 检索推文时，这里是收到的原始 JSON 的片段（通过 Fiddler 捕获：

[{"text":"\"California GOP Files FEC Complaint Over Obama Visit to Dying Grandmother\"\r\nhttp:\/\/url.com\/6jd5j5"}]

在对它进行一些操作后，包括反序列化，然后重新序列化它（通过JSON.NET），它最终在数据库中是这样的：

{"text": "\"California GOP Files FEC Complaint Over Obama Visit to Dying Grandmother\"\r\nhttp://url.com/6jd5j5"}

唯一的区别是 URL 在正斜杠周围没有反斜杠转义。（我不确定这是否有大问题，如果有，请加入）

我的困惑实际上是如何处理这些转义的控制字符。在 MySQL 客户端中对我的表运行 SELECT 查询，使用 MySQL 的JSON_UNQUOTE 函数，它将取消转义字符。被\r\n正确转义，但它保留了有趣的文本周围的双引号......

+----------+-------------------------------------------------------------------------------------------------------+
| user_id  | JSON_UNQUOTE(JSON_EXTRACT(tw.tweet_json, '$.text'))                                                   |
+----------+-------------------------------------------------------------------------------------------------------+
| 12844052 | "California GOP Files FEC Complaint Over Obama Visit to Dying Grandmother"
http://url.com/6jd5j5 |
+----------+-------------------------------------------------------------------------------------------------------+

这是我不使用 JSON_UNQUOTE unescape 函数时的样子：

+-------------------------------------------------------------------------------------------------------------+
| JSON_EXTRACT(tw.tweet_json, '$.text')                                                                       |
+-------------------------------------------------------------------------------------------------------------+
| "\"California GOP Files FEC Complaint Over Obama Visit to Dying Grandmother\"\r\nhttp://url.com/6jd5j5" |
+-------------------------------------------------------------------------------------------------------------+

我需要将这些推文导出为 CSV 文件，供 Excel 或 Google 表格使用。

我在查询后使用以下说明符：

INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/so.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

使用 Excel 打开 CSV 文件显示以下内容：（第二行/条目使用 JSON_UNESCAPE 功能）

请注意第二个条目在使用 JSON_UNESCAPE 功能时如何显示过多的斜线。

这是在记事本中打开的 CSV 文件：

  "\"\\\"California GOP Files FEC Complaint Over Obama Visit to Dying Grandmother\\\"\\r\\nhttp://url.com/6jd5j5\""
"\"California GOP Files FEC Complaint Over Obama Visit to Dying Grandmother\"
\
http://url.com/6jd5j5"

问题：我怎样才能在此处正确地转义推文，以便可以按原意阅读？原始推文链接

编辑ESCAPED BY '"'来自@Michael 的建议使用-sqlbot让我更接近-但是现在打开CSV 时，推文的第二部分（URL）在一个新的单元格中。我已经验证这在 Excel 和 Google 表格中都会发生：

渲染的 CSV 图像（复制和粘贴文本效果不佳）

标签： mysqljsoncsvjson.netescaping

Answer 1

经过一番挖掘，@Michael - sqlbot 的一些有用的评论和这个答案，我得到了它在谷歌表格和 Excel 中的正常工作，并带有以下语句：

    SELECT REPLACE(JSON_UNQUOTE(JSON_EXTRACT({JSON_COL}, {JSON_PROP_TO_RETRIEVE})), '\r\n', '\n')
      ...
    INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/{FILE_NAME}.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '"' LINES TERMINATED BY '\r\n';