0

I have a MySQL database that I would like in SQLite for an app I'm planning to develop. How would I go about this? Thanks.

I saw the previous question Export a MySQL Database to SQLite Database

However, there was no accepted or definitive solution. So what would be the best approach to doing this?

OK, my solution: (hope somebody finds this useful)

  1. Installed DB Browser for SQLite from http://sqlitebrowser.org/
  2. I exported the MySQL structure (without data) as SQL.
  3. Then cleaned up the SQL according to this link http://livecode.byu.edu/database/mysql-sqliteExport.php
  4. Then in DB Browser for SQLite imported the cleaned up SQL to create the database and all the tables. I found that DB Browser for SQLite didn't allow importing of CSV data into the already created tables. So I went to the Apple App Store on my Mac and bought and installed SQLPro for SQLite app ($12 Australian...).
  5. In SQLPro for SQLite I opened the SQLite database created in DB Browser for SQLite.Then exported each MySQL table as CSV and imported those into each existing table in SQLPro for SQLite.
  6. Done
Community
  • 1
  • 1
IlludiumPu36
  • 4,196
  • 10
  • 61
  • 100
  • You may or may not be able to easily migrate it. MySQL has a lot of functionality which SQLite does not have. – Tim Biegeleisen Sep 08 '15 at 08:41
  • Sounds like it may be best to to recreate from scratch using something like SQLite Database Browser. – IlludiumPu36 Sep 08 '15 at 08:50
  • 1
    If you have a lot of data already in the MySQL database I'd go for generating an json or XML file and importing that file in the app. Take into consideration that sqlite already has an autoincremented id for each row, as specified here: https://www.sqlite.org/autoinc.html – George Sep 08 '15 at 08:58

1 Answers1

1

Although I've only used sqlite in an Android environment, I would recommend looking over the following:

Keep in mind that sqlite is a stripped-down version of mysql and has a lot of features missing. For example, while altering an table you may only rename or add new columns, but you may not delete the existing columns. Have a look at the documentation for more specific details that may apply to your specific implementation.

You may find all the limitations here: Sqlite limitations

George
  • 628
  • 6
  • 13