Difference between revisions of "Migrate database engine from MySQL to SQLite"

From LemonWiki共筆
Jump to: navigation, search
m (SQLite management software)
m
(One intermediate revision by the same user not shown)
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
 
* 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"
 
* 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"
Line 16: Line 24:
  
 
* Other functions are not supported such as: SET
 
* Other functions are not supported such as: SET
 
== SQLite management software ==
 
Comparison of SQLite management softwares
 
 
[https://sqlitebrowser.org/ DB Browser for SQLite] v. 3.11.2
 
* 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
 
 
''$'' [https://www.navicat.com/cht/products/navicat-premium Navicat Premium]
 
* Import: (1) database from SQL file (2) table from CSV/Excel file
 
* Export: (1) database to SQL file (2) table as CSV/Excel file
 
* Export the query result: ok
 
  
 
== References ==
 
== References ==

Revision as of 14:57, 9 November 2019

Steps of migration database engine from MySQL to SQLite

Steps of migration database engine from MySQL to SQLite

  1. Migrate database data from MySQL to SQLite using the SQLite management software
  2. Modify the scripts of database access
  3. 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 ' 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);
  • Other functions are not supported such as: SET

References