Newlines: Migration from MySQL to SQLite

Question

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".

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.

How do you create the dump in MySQL and how do you import the dump into SQLite? — VMai, Jul 08 '14 at 07:48
For dumping I use mysqldump and to import into the SQLite database SQLite Manager. — HamuSumo, Jul 08 '14 at 09:21

score 0 · Accepted Answer · answered Jul 08 '14 at 08:31

0

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

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)

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

For example, this command:

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

would be converted into this:

INSERT INTO MyTable VALUES('nomnomnom
 nomnomnom');

Alternatively, you could insert control characters using SQL functions:

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

answered Jul 08 '14 at 08:31

CL.

173,858
17
217
259

Thank you for your answer. This is clearly one step forward. I have already wrote a php script to convert \' to '' but how do I convert \n and \r to a real line break in php? – HamuSumo Jul 08 '14 at 09:28
This question is not about PHP string handling. To ask a new question, use the "Ask Question" button. – CL. Jul 08 '14 at 14:54

Newlines: Migration from MySQL to SQLite

1 Answers1

Linked