How to Convert MySQL Dump Files into SQLite-Importable Format?

Converting MySQL Dump Files into SQLite Importable Format

Problem:

Importing an exported MySQL dump SQL file into a SQLite3 database directly using the sqlite3 tool isn't successful. How can this be resolved?

Solution:

The following shell script can convert the MySQL dump file into a format that can be imported into SQLite3:

#!/bin/sh
if [ "x" == "x" ]; then
   echo "Usage:  <dumpname>"
   exit
fi
cat  |
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=;
     s/\'\''/'\'\''/g;
     s/\n/\n/g;
     s/\),\(/\);\n$a\(/g;
  }
  ' > .sql
cat .sql | sqlite3 .db > .err
ERRORS=`cat .err | wc -l`
if [ $ERRORS == 0 ]; then
  echo "Conversion completed without error. Output file: .db"
  rm .sql
  rm .err
    rm tmp
else
   echo "There were errors during conversion.  Please review .err and .sql for details."
fi

Explanation:

• The script first filters out certain lines from the MySQL dump file that can cause issues when importing into SQLite3, such as key constraints and character set definitions.
• It then converts specific data types, such as smallint and int, to integer and removes any unsigned modifiers.
• The script converts enum data types to varchar(255) and removes any on update clauses.
• It wraps the SQL statements in a begin and commit transaction block.
• The script corrects any escaped newline characters and splits INSERT statements with multiple rows into individual statements.
• Finally, it imports the modified SQL file into the SQLite3 database.

The above is the detailed content of How to Convert MySQL Dump Files into SQLite-Importable Format?. For more information, please follow other related articles on the PHP Chinese website!