An example of how to migrate sqlite to MySQL scripts-Mysql Tutorial-php.cn

An example of how to migrate sqlite to MySQL scripts

黄舟

Release： 2017-08-09 14:21:11

Original

1278 people have browsed it

这篇文章主要介绍了sqlite迁移到mysql脚本的方法,需要的朋友可以参考下

废话不多说了，直接给大家贴代码了，具体代码如下所示：

#! /usr/bin/perl
#
# based on https://stackoverflow.com/a/87531/5742651
# usage: sqlite3 .dump database_name.sqlite3 | perl sqlite2mysql.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 &#39;t&#39; and &#39;f&#39; => 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(&#39;val1&#39;);
#  INSERT INTO table VALUES(&#39;val2&#39;);  => INSERT INTO table VALUES(&#39;val1&#39;), (&#39;val2&#39;), (&#39;val3&#39;);
#  INSERT INTO table VALUES(&#39;val3&#39;);
my $open=0;
my $line_cache = &#39;&#39;;
# 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/\&#39;\&#39;/\\\&#39;/g;
   }
    if ($open == 1) {
       $open = 0;
       $line = $line_cache.";\n".$line;
       $line_cache = &#39;&#39;;
    }
   $line =~ s/\"/`/g;
   $line =~ s/([^\\&#39;])\&#39;t\&#39;(.)/$1THIS_IS_TRUE$2/g;
   $line =~ s/THIS_IS_TRUE/1/g;
   $line =~ s/([^\\&#39;])\&#39;f\&#39;(.)/$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";

Copy after login

总结

The above is the detailed content of An example of how to migrate sqlite to MySQL scripts. For more information, please follow other related articles on the PHP Chinese website!