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

From LemonWiki共筆
Jump to: navigation, search
m
Line 16: Line 16:
  
 
* 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(s) as CSV/Excel file
 +
* Export the query result: ok
 +
  
 
== References ==
 
== References ==
Line 23: Line 37:
 
[[Category:MySQL]]
 
[[Category:MySQL]]
 
[[Category:SQLite]]
 
[[Category:SQLite]]
 +
[[Category:Tool]]

Revision as of 17:51, 7 November 2019

Notes

  • 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

SQLite management software

Comparison of SQLite management softwares

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

$ Navicat Premium

  • Import: (1) database from SQL file (2) table from CSV/Excel file
  • Export: (1) database to SQL file (2) table(s) as CSV/Excel file
  • Export the query result: ok


References