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

From LemonWiki共筆
Jump to: navigation, search
Line 1: Line 1:
 
== Notes ==
 
== Notes ==
 
* 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
* Replace MySQL [https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_char-length CHAR_LENGTH] function with SQLite [https://www.sqlitetutorial.net/sqlite-functions/sqlite-length/#targetText=SQLite%20Length,returns%20the%20number%20of%20bytes. Length] function<ref>[https://github.com/owncloudarchive/mozilla_sync/issues/47 sqlite does not have CHAR_LENGTH function · Issue #47 · owncloudarchive/mozilla_sync]</ref>
+
* 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 [https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_char-length CHAR_LENGTH] function with SQLite [https://www.sqlitetutorial.net/sqlite-functions/sqlite-length/#targetText=SQLite%20Length,returns%20the%20number%20of%20bytes. Length] function<ref>[https://github.com/owncloudarchive/mozilla_sync/issues/47 sqlite does not have CHAR_LENGTH function · Issue #47 · owncloudarchive/mozilla_sync]</ref>
 +
** Replace MySQL {{kbd | key=IF}} function with SQLite {{kbd | key=CASE}} function
 +
** Replace MySQL {{kbd | key=LOCATE}} function<ref>[https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_locate MySQL :: MySQL 8.0 Reference Manual :: 12.5 String Functions and Operators]</ref> with SQLite INSTR {{kbd | key=CASE}} function<ref>[https://www.sqlitetutorial.net/sqlite-functions/sqlite-instr/ SQLite INSTR]</ref>
  
 +
<pre>
 +
-- MySQL
 +
LOCATE(substr,str), LOCATE(substr,str,pos)
 +
 +
-- SQLite
 +
INSTR(string, substring);
 +
</pre>
  
 
== References ==
 
== References ==

Revision as of 14:08, 1 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
    • Replace MySQL LOCATE function[2] with SQLite INSTR CASE function[3]
-- MySQL
LOCATE(substr,str), LOCATE(substr,str,pos)

-- SQLite
INSTR(string, substring);

References