- SQLite Database Browser
- MySQL with phpMyAdmin
- Text Editor such as TextEdit (we used Fraise)
- In phpMyAdmin page, go to Export tab and select the tables you want to convert.
- Make sure to select the SQL on the radio buttons.
- Uncheck all checkboxes besides the Export group but leave the checkboxes besides Structure and Data.
- 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).
- 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.
- 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) );
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.
CREATE TABLE dictionary( id INTEGER PRIMARY KEY ASC, text TEXT, int INTEGER );
- 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.
- Check your encoding. We used the Western as default then save the file.
- Open SQLite Database Browser and go to File> Import> Database From SQL File and click yes if asked to create new database.
Angelo Villegas is a software developer building native iOS apps using Objective-C and Swift. I enjoy thinking that the apps I built make the users’ life easier.