Quick easy way to migrate SQLite3 to MySQL?

Question

Anyone know a quick easy way to migrate a SQLite3 database to MySQL?

Answer 1

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:

• BEGIN TRANSACTION
• COMMIT
• sqlite_sequence
• CREATE UNIQUE INDEX

are not used in MySQL

• SQLite uses CREATE TABLE/INSERT INTO "table_name" and MySQL uses CREATE TABLE/INSERT INTO table_name
• MySQL doesn't use quotes inside the schema definition
• MySQL uses single quotes for strings inside the INSERT INTO clauses
• SQLite and MySQL have different ways of escaping strings inside INSERT INTO clauses
• SQLite uses '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)
• SQLLite uses 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;
    }
}