Compiled Chronicles

A software development blog by Angelo Villegas

MySQL to SQLite migration cheat sheet

At work, we needed to migrate from MySQL to SQLite. All of this thanks to this cheatsheet we got the migration an easy task. But it didn’t explain many things, just the major part in a step by step process.

Tools :

  • SQLite Database Browser
  • MySQL with phpMyAdmin
  • Text Editor such as TextEdit (we used Fraise)

Steps:

You can actually follow the step there and just don’t read here but you can also follow this simple steps.

  1. In phpMyAdmin page, go to Export tab and select the tables you want to convert.
  2. Make sure to select the SQL on the radio buttons.
  3. Uncheck all checkboxes besides the Export group but leave the checkboxes besides Structure and Data.
  4. Pick ANSI on SQL compatibility mode and check the save as file then click the Go button. It’s best to put the compression to none. But if you like, you can pick zipped or gzipped (just make sure to extract the file after download).
  5. Open the file with a text editor such as TextEdit or Fraise. Find the CREATE TABLE line inside the text. You have to edit them all if you have more than one.
  6. Edit all the CREATE TABLE codes from:
    CREATE TABLE dictionary ( id int(11) NOT NULL auto_increment, text varchar(100) NOT NULL, int tinyint(1) NOT NULL default '0' COMMENT 'is an adjective', PRIMARY KEY  (id), UNIQUE KEY words (word) );

    to:

    CREATE TABLE dictionary( id INTEGER PRIMARY KEY ASC, text TEXT, int INTEGER );

    If you’re wondering why this changes are needed, then please study SQLite3 data types. Also make sure that you only have 1 primary key. MySQL accepts 2 so maybe, just maybe. We got an error because of this.

  7. Check your file for suspicious characters that might cause the import to choke. Single quotation marks used as apostrophes show up as escaped by themselves; for example you’re becomes you”re.
  8. Check your encoding. We used the Western as default then save the file.
  9. Open SQLite Database Browser and go to File> Import> Database From SQL File and click yes if asked to create new database.

Although this steps may not guarantee 100% successful to the others, I can offer a help by commenting here and/or asking the original poster in his blog linked at the beginning of this post. The post was 2 years old though so I’m not sure if he can still remember or that he can still give a solid support.

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *