换行:从 MySQL 迁移到 SQLite [英] Newlines: Migration from MySQL to SQLite

问题描述

目前我面临的问题是，当我将数据放入 SQLite 数据库时，来自 MySQL 转储的换行符将被忽略.MySQL 转储的\r\n"将 SQLite 解释为字符串，所以我在应用程序中有类似nomnomnom\r\n nomnomnom"而不是nomnomnom

Currently I am facing the issue that newlines from a MySQL dump will be ignored when I put the data in a SQLite database. The "\r\n", which MySQL dumps, interprets SQLite as a string, so I have sentences like "nomnomnom\r\n nomnomnom" in the application instead of "nomnomnom

nomnomnom".

nomnomnom".

所以我的问题是我该怎么做才能让 SQLite 正确读取换行符?我已经在谷歌上搜索了很多，以找出 SQLite 需要什么样的换行符语法，但令我惊讶的是我没有找到任何明确的答案.

So my question is what have I to do that SQLite reads the linebreaks correctly? I have already googled a lot to find out what kind of newline syntax SQLite needs but to my surprise I haven't found any clear answer.

推荐答案

SQL 没有反斜杠转义(这是 MySQL 的一个怪癖).

SQL has no backslash escapes (this is a MySQL quirk).

要将 MySQL 转储转换为真正的 SQL，请使用编辑器或某些脚本来转换这些转义序列:

To convert the MySQL dump into real SQL, use an editor or some script to convert these escape sequences:

\n -> new line
\r -> carriage return
\" -> "
\' -> '' (inside strings)

(在 SQL 字符串中，除了 ' 之外的每个字符都按字面解释.)

(In SQL strings, every character except ' is interpreted literally.)

例如这个命令:

INSERT INTO MyTable VALUES('nomnomnom\r\n nomnomnom');

会变成这样:

INSERT INTO MyTable VALUES('nomnomnom
 nomnomnom');

或者，您可以使用 SQL 函数插入控制字符:

Alternatively, you could insert control characters using SQL functions:

INSERT INTO MyTable VALUES('nomnomnom' || char(13) || char(10) || ' nomnomnom');

这篇关于换行:从 MySQL 迁移到 SQLite的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！