Anyone know a quick easy way to migrate a SQLite3 database to MySQL?
SQLite 2.7. 6 is often faster (sometimes more than twice as fast) than MySQL 3.23. 41 for most common operations. SQLite does not execute CREATE INDEX or DROP TABLE as fast as the other databases.
On the bottom of the screen there is a button called “Start Migration”. Click it to get to the source selection. On the first drop down menu choose “SQLite”. The menu will now change and give you the possibility to load a file.
Everyone seems to starts off with a few greps and perl expressions and you sorta kinda get something that works for your particular dataset but you have no idea if it's imported the data correctly or not. I'm seriously surprised nobody's built a solid library that can convert between the two.
Here a list of ALL the differences in SQL syntax that I know about between the two file formats: The lines starting with:
are not used in MySQL
CREATE TABLE/INSERT INTO "table_name"
and MySQL uses CREATE TABLE/INSERT INTO table_name
INSERT INTO
clausesINSERT INTO
clauses't'
and 'f'
for booleans, MySQL uses 1
and 0
(a simple regex for this can fail when you have a string like: 'I do, you don't' inside your INSERT INTO
)AUTOINCREMENT
, MySQL uses AUTO_INCREMENT
Here is a very basic hacked up perl script which works for my dataset and checks for many more of these conditions that other perl scripts I found on the web. Nu guarantees that it will work for your data but feel free to modify and post back here.
#! /usr/bin/perl while ($line = <>){ if (($line !~ /BEGIN TRANSACTION/) && ($line !~ /COMMIT/) && ($line !~ /sqlite_sequence/) && ($line !~ /CREATE UNIQUE INDEX/)){ if ($line =~ /CREATE TABLE \"([a-z_]*)\"(.*)/i){ $name = $1; $sub = $2; $sub =~ s/\"//g; $line = "DROP TABLE IF EXISTS $name;\nCREATE TABLE IF NOT EXISTS $name$sub\n"; } elsif ($line =~ /INSERT INTO \"([a-z_]*)\"(.*)/i){ $line = "INSERT INTO $1$2\n"; $line =~ s/\"/\\\"/g; $line =~ s/\"/\'/g; }else{ $line =~ s/\'\'/\\\'/g; } $line =~ s/([^\\'])\'t\'(.)/$1THIS_IS_TRUE$2/g; $line =~ s/THIS_IS_TRUE/1/g; $line =~ s/([^\\'])\'f\'(.)/$1THIS_IS_FALSE$2/g; $line =~ s/THIS_IS_FALSE/0/g; $line =~ s/AUTOINCREMENT/AUTO_INCREMENT/g; print $line; } }
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With