sqlite迁移到mysql脚本的方法

#! /usr/bin/perl

#

# based on https://stackoverflow.com/a/87531/5742651

# usage: sqlite 3 .dump database_name.sqlite3 | perl sqlite2 mysql .pl | mysql -u root -p $import_database_name

#

# ignore follow lines:

#  BEGIN TRANSACTION

#  COMMIT

#  sqlite_sequence

#  CREATE UNIQUE INDEX

#  PRAGMA foreign_keys= OFF

# "tablename/field" => `tablename/field`

# booleans 't' and 'f' => 1 and 0

# AUTOINCREMENT => AUTO_INCREMENT

# varchar => varchar (255)

# CREATE TABLE table ... => DROP TABLE table ; CREATE TABLE table ...

# Merge insert sqls into multiple insert to speed up

#  INSERT INTO table VALUES ( 'val1' );

#  INSERT INTO table VALUES ( 'val2' );  => INSERT INTO table VALUES ( 'val1' ), ( 'val2' ), ( 'val3' );

#  INSERT INTO table VALUES ( 'val3' );

my $ open =0;

my $line_cache = '' ;

# For speed up

print "SET GLOBAL max_allowed_packet=209715200;\n" ;

#print "SET AUTOCOMMIT=0;\n" ;

while ($line = <>){

   if (($line !~ /PRAGMA foreign_keys= OFF /) && ($line !~ / BEGIN TRANSACTION /) && ($line !~ / COMMIT /) && ($line !~ /sqlite_sequence/) && ($line !~ / CREATE UNIQUE INDEX /)){

    if ($line =~ / CREATE TABLE \ "([a-z_0-9]*)\"(.*)/){

    $name = " \`$1\` ";

    $sub = $2;

    $sub =~ s/varchar([^(])/varchar(255)$1/g;

    $line = " DROP TABLE IF EXISTS $ name ;\nCREATE TABLE $ name $sub\n ";

    }

    elsif ($line =~ /CREATE VIEW ([a-z_0-9]*)(.*)/){

    $name = " \`$1\` ";

    $sub = $2;

    $line = " DROP VIEW IF EXISTS $ name ;\nCREATE VIEW $ name $sub\n ";

    }

    elsif ($line =~ /INSERT INTO \"([a-z_]*)\" VALUES(.*);/){

         if ($open == 0) {

           $open = 1;

        $line_cache .= " INSERT INTO \`$1\` VALUES $2 ";

         } else {

           $line_cache .= " , $2 ";

         }

         next;

    }else{

    $line =~ s/\'\'/\\\'/g;

    }

     if ($open == 1) {

        $open = 0;

        $line = $line_cache." ;\n ".$line;

        $line_cache = '';

     }

    $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;

   }

}

#print " SET AUTOCOMMIT=1;\n";