StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Everyone seems to starts off with a few greps and perl expressions and you sorta kinda get something that works for your particular dataset but you have no idea if it's imported the data correctly or not. I'm seriously surprised nobody's built a solid library that can convert between the two.</p> <p>Here a list of ALL the differences in SQL syntax that I know about between the two file formats: The lines starting with:</p> <ul> <li>BEGIN TRANSACTION</li> <li>COMMIT</li> <li>sqlite_sequence</li> <li>CREATE UNIQUE INDEX</li> </ul> <p>are not used in MySQL</p> <ul> <li>SQLlite uses CREATE TABLE/INSERT INTO "table_name" and MySQL uses CREATE TABLE/INSERT INTO table_name</li> <li>MySQL doesn't use quotes inside the schema definition</li> <li>MySQL uses single quotes for strings inside the INSERT INTO clauses</li> <li>SQLlite and MySQL have different ways of escaping strings inside INSERT INTO clauses</li> <li>SQLlite uses 't' and 'f' for booleans, MySQL uses 1 and 0 (a simple regex for this can fail when you have a string like: 'I do, you don\'t' inside your INSERT INTO)</li> <li>SQLLite uses AUTOINCREMENT, MySQL uses AUTO_INCREMENT</li> </ul> <p>Here is a very basic hacked up perl script which works for <em>my</em> dataset and checks for many more of these conditions that other perl scripts I found on the web. Nu guarentees that it will work for your data but feel free to modify and post back here.</p> <pre><code>#! /usr/bin/perl while ($line = <>){ if (($line !~ /BEGIN TRANSACTION/) && ($line !~ /COMMIT/) && ($line !~ /sqlite_sequence/) && ($line !~ /CREATE UNIQUE INDEX/)){ if ($line =~ /CREATE TABLE \"([a-z_]*)\"(.*)/){ $name = $1; $sub = $2; $sub =~ s/\"//g; $line = "DROP TABLE IF EXISTS $name;\nCREATE TABLE IF NOT EXISTS $name$sub\n"; } elsif ($line =~ /INSERT INTO \"([a-z_]*)\"(.*)/){ $line = "INSERT INTO $1$2\n"; $line =~ s/\"/\\\"/g; $line =~ s/\"/\'/g; }else{ $line =~ s/\'\'/\\\'/g; } $line =~ s/([^\\'])\'t\'(.)/$1THIS_IS_TRUE$2/g; $line =~ s/THIS_IS_TRUE/1/g; $line =~ s/([^\\'])\'f\'(.)/$1THIS_IS_FALSE$2/g; $line =~ s/THIS_IS_FALSE/0/g; $line =~ s/AUTOINCREMENT/AUTO_INCREMENT/g; print $line; } } </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload