Import Mysql Dump File To Sqlite

Here is my use case: I have an sql dump file provided by a server application that I don't have access to, neither to the MySQL database that originates the file. The sql dump file

Solution 1:

Do you have shell access? You could just cat file.sql | sqlite3 my.db although as @VMai mentions in the comments, this may not work due to MySQL-specific SQL dialect without preprocessing.

Here is a good blog entry of how to do it in code. If you have the MySQL-specific dialect to worry about, you'd either have to preprocess or modify it in the stream of work in code.

You could use one of the tools listed here to do preprocessing on the SQL if it doesn't import well straight out of MySQL.

Solution 2:

Because I faced this issue I think my findings and solution could be in the interest of others. Here it is:

• MySQL is much richer in terms of data types than SQLite, so there is NO exhaustive transcoding implementation possible.
• for the common data types, what fits both as content representation are 'integer' and 'text'.
• the index key has a different syntax.
• overall, the MySQL dump file would require too much parsing for a small result.

THE SOLUTION however is very simple, even if we don't have access to the originating - MySQL - database, as following:

• load the MySQL dump in another transient MySQL-type database where you have full control.
• use a simple (PHP) script to read then write the content of the MySQL tables into a text file.
• read this text file with another script (Java, in my case, for Android) and feed the SQLite database tables. It is assumed that the SQLite schema has been previously created.

This is basic but, believe me, it is the most appropriate for such functionality. MySQL and SQLite are definitely not alike as construction!