Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

What's the best way of converting a mysql database to a sqlite one?

Tags:

I currently have a relatively small (4 or 5 tables, 5000 rows) MySQL database that I would like to convert to an sqlite database. As I'd potentially have to do this more than once, I'd be grateful if anyone could recommend any useful tools, or at least any easily-replicated method.

(I have complete admin access to the database/machines involved.)

like image 654
ChrisThomas123 Avatar asked Aug 10 '08 15:08

ChrisThomas123


People also ask

What are the disadvantages of SQLite?

One of the main drawbacks of the SQLite system is its lack of multi-user capabilities which can be found in full-fledged RDBMS systems like MySQL and PostgreSQL. This translates to a lack of granular access control, a friendly user management system, and security capabilities beyond encrypting the database file itself.

Is SQLite faster than MySQL?

MySQL, while also easy to use, provides a more robust set of features, better security, better performance at scale and overall better multi-user management. SQLite lacks these features and optimizations.

Which is better SQLite or MySQL?

MySQL has a well-constructed user management system which can handle multiple users and grant various levels of permission. SQLite is suitable for smaller databases. As the database grows the memory requirement also gets larger while using SQLite. Performance optimization is harder when using SQLite.


1 Answers

I've had to do similar things a few times. The easiest approach for me has been to write a script that pulls from one data source and produces an output for the new data source. Just do a SELECT * query for each table in your current database, and then dump all the rows into an INSERT INTO query for your new database. You can either dump this into a file or pipe it straight into the database frontend.

It's not pretty, but honestly, pretty hardly seems to be a major concern for things like this. This technique is quick to write, and it works. Those are my primary criteria for things like this.

You might want to check out this thread, too. It looks like a couple of people have already put together basically what you need. I didn't look that far into it, though, so no guarantees.

like image 197
Derek Park Avatar answered Oct 13 '22 16:10

Derek Park