Created
December 26, 2008 20:37
-
-
Save cannikin/40119 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
--- | |
layout: post | |
title: Convert a MySQL database to a SQLite3 database | |
--- | |
<span class="intro">I wanted to convert a</span> <a href="http://mysql.org">MySQL</a> database to a <a href="http://sqlite.org">SQLite3</a> database the other day. I did some searching and found a <a href="http://www.sqlite.org/cvstrac/wiki?p=ConverterTools">good script on the SQLite3 site</a> . It didn’t quite work for me, but it was close (left a bunch of random MySQL “set” statements everywhere and used MySQL’s default multiple insert syntax). After some tweaking I got it to create the file without errors. Here’s my version for anyone that needs to do the same thing (requires <em>mysqldump</em> and <em>perl</em> be installed on your system): | |
{% highlight bash %} | |
#!/bin/sh | |
if [ "x$1" == "x" ]; then | |
echo "Usage: $0 <dbname>" | |
exit | |
fi | |
if [ -e "$1.db" ]; then | |
echo "$1.db already exists. I will overwrite it in 15 seconds if you do not press CTRL-C." | |
COUNT=15 | |
while [ $COUNT -gt 0 ]; do | |
echo "$COUNT" | |
sleep 1 | |
COUNT=$((COUNT - 1)) | |
done | |
rm $1.db | |
fi | |
/usr/local/mysql/bin/mysqldump -u root --compact --compatible=ansi --default-character-set=binary --extended-insert=false $1 | | |
grep -v ' KEY "' | | |
grep -v ' UNIQUE KEY "' | | |
grep -v ' PRIMARY KEY ' | | |
sed 's/^SET.*;//g' | | |
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/ enum([^)]*) / varchar(255) /g' | | |
sed 's/ on update [^,]*//g' | | |
sed "s/\\\'/''/g" | # convert MySQL escaped apostrophes to SQLite \' => '' | |
sed 's/\\\"/"/g' | # convert escaped double quotes into regular quotes | |
sed "s/’/''/g" | # MySQL was outputting this weird character as a single quote? | |
sed 's/\\\n/\n/g' | | |
sed 's/\\r//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.sqlite3 > $1.err | |
ERRORS=`cat $1.err | wc -l` | |
if [ $ERRORS == 0 ]; then | |
echo "Conversion completed without error. Output file: $1.sqlite3" | |
rm $1.sql | |
rm $1.err | |
else | |
echo "There were errors during conversion. Please review $1.err and $1.sql for details." | |
fi | |
{% endhighlight %} | |
<strong>Update, 11/3/08</strong> Updated the script above. Fixed a couple issues with newlines and lowercasing everything also lowercased the actual values in the tables! For some reason I had convinced myself it was only lowercasing the table and column names… There is still an issue where apostrophes are turned into weird characters, seemingly <span class="caps">UTF</span>-8. This might just be a simple matter of telling <em>mysqldump</em> to use latin instead of utf-8 encoding? I haven’t played around with it, but if anyone figures it out please let me know. |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment