Migrate database engine from MySQL to SQLite: Difference between revisions

Revision as of 14:41, 9 November 2019

Steps of migration database engine from MySQL to SQLite

Remove database name from SQL query: SQLite will consider the database name as table name
In MySQL, string are able to quoted with single quote symbol ' or double quote symbol ". In SQLite the string quoted with double quote symbol will be considered as column name! It will cause the problem: "no such column: xxx"
Replace the following functions
- Replace MySQL CHAR_LENGTH function with SQLite Length function^[1]
- Replace MySQL IF function with SQLite CASE function^[2]
- Replace MySQL LOCATE function^[3] with SQLite INSTR CASE function^[4]

-- MySQL
LOCATE(substr,str), LOCATE(substr,str,pos)

-- SQLite
INSTR(string, substring);

Comparison of SQLite management softwares

Import: (1) database from SQL file (2) table from CSV file
Export: (1) database to SQL file (2) table(s) as CSV file (3) table(s) as JSON file
Export the query result: ok. Steps (1) execute SQL (2) click "Save the results" as CSV file

@@ Line 1: / Line 1: @@
-== Notes ==
+Steps of migration database engine from MySQL to SQLite
+== Steps of migration database engine from MySQL to SQLite ==
+# Migrate database data from MySQL to SQLite using the SQLite management software
+# Modify the scripts of database access
+# Testing the scripts
+== Notes of modification of scripts ==
 * Remove database name from SQL query: SQLite will consider the database name as table name
 * In MySQL, string are able to quoted with single quote symbol {{kbd | key=<nowiki>'</nowiki>}} or double quote symbol {{kbd | key=<nowiki>"</nowiki>}}. In SQLite the string quoted with double quote symbol will be considered as '''column name'''! It will cause the problem: "no such column: xxx"