Home >Database >Mysql Tutorial >How to Convert a MySQL Dump SQL File to a SQLite3 Database?

How to Convert a MySQL Dump SQL File to a SQLite3 Database?

DDD
DDDOriginal
2024-11-03 01:27:29661browse

How to Convert a MySQL Dump SQL File to a SQLite3 Database?

Converting MySQL Dump SQL to Sqlite3 DB

Importing a MySQL dump SQL file into a SQLite3 database requires conversion to ensure compatibility.

The provided shell script offers an automated way to convert the dump file:

<code class="shell">#!/bin/sh
# Usage:  <dumpname>

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</code>

The script performs the following transformations:

  • Removes redundant key definitions.
  • Sets fields to unsigned if applicable.
  • Sets auto_increment fields to primary key autoincrement.
  • Converts integer types to their equivalents in SQLite (smallint, tinyint, and int become integer).
  • Removes character sets.
  • Converts enum types to varchar(255).
  • Removes set references to update triggers.
  • Converts multiple row inserts to individual inserts for SQLite compatibility.
  • Wraps the converted SQL in a transaction for safety.

The above is the detailed content of How to Convert a MySQL Dump SQL File to a SQLite3 Database?. For more information, please follow other related articles on the PHP Chinese website!

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn