我有一个包含来自MySQL的表和数据的导出SQL文件,我想将其导入Sqlite 3 DB.
最好的方法是什么？
只通过sqlite3工具导入文件不起作用.
这个shell脚本可以帮助你
#!/bin/sh if [ "x$1" == "x" ]; then echo "Usage: $0" exit fi cat $1 | grep -v ' KEY "' | grep -v ' UNIQUE KEY "' | grep -v ' PRIMARY KEY ' | sed '/^SET/d' | sed 's/ unsigned / /g' | sed 's/ auto_increment/ primary key autoincrement/g' | sed 's/ smallint([0-9]*) / integer /g' | sed 's/ tinyint([0-9]*) / integer /g' | sed 's/ int([0-9]*) / integer /g' | sed 's/ character set [^ ]* / /g' | sed 's/ enum([^)]*) / varchar(255) /g' | sed 's/ on update [^,]*//g' | perl -e 'local $/;$_=<>;s/,\n\)/\n\)/gs;print "begin;\n";print;print "commit;\n"' | perl -pe ' if (/^(INSERT.+?)\(/) { $a=$1; s/\\'\''/'\'\''/g; s/\\n/\n/g; s/\),\(/\);\n$a\(/g; } ' > $1.sql cat $1.sql | sqlite3 $1.db > $1.err ERRORS=`cat $1.err | wc -l` if [ $ERRORS == 0 ]; then echo "Conversion completed without error. Output file: $1.db" rm $1.sql rm $1.err rm tmp else echo "There were errors during conversion. Please review $1.err and $1.sql for details." fi
为了使上述脚本起作用,我做了以下更改:
用#!/ bin/bash运行它
将两个seds添加到管道化seds列表中:
sed's/\\ r \\n/\\n/g'
sed's/\\"/"/ g'
'rm tmp'行是一个no-op(除非你有一个名为'tmp'的文件:O)
我的mysqldump命令看起来像这样:
$ mysqldump -u usernmae -h host --compatible = ansi --skip-opt -p database_name> dump_file
然后它工作得很好...感谢脚本.
我尝试了一些使用sed或awk的脚本,但总是发生错误,可能是由于我的MySQL数据库的索引和外键以及所需的mysqldump选项.
然后我发现Perl模块SQL :: Translator "将特定于供应商的SQL表定义转换为其他格式......"
此模块创建所有外键并更正索引,如果需要更改名称.
所以,我重写了shell脚本,包括MySQL db的转储.有两个转储,因为脚本"sqlt"仅生成结构,并且如果转储没有数据,则可以快速运行.请注意,它可以适应SQL :: Translator支持的其他转换.
在我发布这个shell脚本后,我意识到问题是要转换MySQL转储文件,所以我使用模块SQL :: Translator做了一个Perl脚本.在我的测试中,我使用了一个没有选项生成的转储文件(mysqldump -u user --password database> dumpfile).我对字符集没有任何问题.
在其他测试中我遇到了mysql触发器的问题,因此我修改了脚本以跳过它.
#!/bin/sh #=============================================================================== # USAGE: ./mysql2sqlite.sh# DESCRIPTION: Converts MySQL databases to SQLite # Triggers are not converted # REQUIREMENTS: mysqldump, Perl and module SQL::Translator, SQLite #=============================================================================== if [ "$#" = 2 ]; then USER="$2" else echo "Usage: $0 " exit fi if [ -s $1.db ]; then read -p "File <$1.db> exists. Overwrite? [y|n] " ANS if [ "$ANS" = "y" ] || [ "$ANS" = "Y" ] ; then rm $1.db else echo "*** Aborting..." exit fi fi # extracts the necessary structure for SQLite: mysqldump --skip-triggers --skip-add-locks --routines --no-data --compatible=ansi \ --compact -u $USER --password $1 > /tmp/$1_$$_str.sql # verify if [ ! -s /tmp/$1_$$_str.sql ]; then echo "*** There are some problem with the dump. Exiting." exit fi # translates MySQL syntax structure to SQLite using the script "sqlt" of the # perl module SQL::Translator (that corrects the foreign keys, indexes, etc.) sqlt -f MySQL -t SQLite --show-warnings /tmp/$1_$$_str.sql \ 1> /tmp/$1_$$.sqlite 2> /tmp/$1_$$_sqlt.log # verify if [ ! -s /tmp/$1_$$.sqlite ]; then echo "*** There are some problem with the sql translation. Exiting." exit fi # adds statements to allow to load tables with foreign keys: echo "PRAGMA foreign_keys=OFF;" >> /tmp/$1_$$.sqlite echo "BEGIN TRANSACTION;" >> /tmp/$1_$$.sqlite # extracts the data (simple inserts) without locks/disable keys, # to be read in versions of SQLite that do not support multiples inserts: mysqldump --skip-triggers --no-create-db --no-create-info --skip-add-locks \ --skip-extended-insert --compatible=ansi --compact -u $USER \ --password $1 >> /tmp/$1_$$.sqlite # adds statements to finish the transaction: echo "COMMIT;" >> /tmp/$1_$$.sqlite echo "PRAGMA foreign_keys=ON;" >> /tmp/$1_$$.sqlite # correct single quotes in inserts perl -pi -e ' if (/^INSERT INTO/) { s/\\'\''/'\'\''/g; } ' /tmp/$1_$$.sqlite # load the sql file and generate the SQLite db with the same name # of the MySQL database sqlite3 $1.db < /tmp/$1_$$.sqlite 2> /tmp/$1_$$sqlite.errlog # verify ERRORS=`cat /tmp/$1_$$sqlite.errlog | wc -l` if [ $ERRORS = 0 ]; then echo "* Conversion complete. Verify the file < $1.db >" rm /tmp/$1_$$* else echo "*** There are some problem. Verify the files < /tmp/$1_$$* >" fi
这里是Perl脚本,用于转换SQLite数据库文件中的转储文件.
#!/usr/bin/perl #=============================================================================== # USAGE: ./mysql2sqlite.pl# DESCRIPTION: Converts MySQL dumpfile to SQLite database # Triggers are not converted # The dump must be done with # > mysqldump --skip-triggers -u [user] --p [database] > dumpfile # REQUIREMENTS: Perl and module SQL::Translator, SQLite #=============================================================================== use strict; use warnings; use Carp; use English qw( -no_match_vars ); use SQL::Translator; use 5.012; my $file = $ARGV[0]; my $filedb = $file; $filedb =~ s/\.*[^.]*$/.db/; if ( -s $filedb ) { say "*** Ja existe o arquivo < $filedb >. Abandonando..."; exit; } my @stru; my @data; open( my $SQLFILE, "<", $file ) or croak "Can't open $file: $OS_ERROR"; while (<$SQLFILE>) { # nao considera linhas com comentarios e lock/unlock/drop next if ( /^--/ || /^\/\*/ || /^lock/i || /^unlock/i || /^drop/i ); # processa os inserts if (/^(INSERT.+?)[(]/) { my $ins = $1; # captura o nome da tabela s/\\[']/''/g; # substitue aspas simples - \' s/[)],[(]/);\n$ins(/g; # divide multiplos inserts push( @data, $_ ); } # processa a estrutura else { push( @stru, $_ ); } } close($SQLFILE); my $strusql = join( '', @stru ); my $datasql = join( '', @data ); #open( my $STRU, ">", "stru.sql" ); # to verify the results #open( my $DATA, ">", "data.sql" ); #print $STRU $strusql; #print $DATA $datasql; # here the conversion my $translator = SQL::Translator->new( no_comments => 0, show_warnings => 0, quote_table_names => 1, quote_field_names => 1, validate => 1, ); my $struout = $translator->translate( from => 'MySQL', to => 'SQLite', data => \$strusql, # filename => $file, ) or croak "Error: " . $translator->error; # define inicio e final da transacao de inserts my $prgini = "PRAGMA foreign_keys=OFF;\n"; my $traini = "BEGIN TRANSACTION;\n"; my $trafin = "COMMIT;\n"; my $prgfin = "PRAGMA foreign_keys=ON;\n"; #gera o arquivo final sqlite my $sqlout = join( "\n", $struout, $prgini, $traini, $datasql, $trafin, $prgfin); open( my $FINAL, ">", "/tmp/final.sql" ); print $FINAL $sqlout; # Monta o SQLite database my $log = "/tmp/sqlite.errlog"; my $command = "sqlite3 $filedb < /tmp/final.sql 2> $log"; system($command) == 0 or die "system $command failed: $?"; if ( -s $log ) { say "*** Houve algum problema. Verifique o arquivo < /tmp/sqlite.errlog > "; } else { say "*** Conversao completa. Verifique o arquivo < $filedb > "; }
京公网安备 11010802040832号 | 京ICP备19059560号-6 