Migrate database engine from MySQL to SQLite: Difference between revisions
Jump to navigation
Jump to search
(Created page with "== Notes == * 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-functio...") |
Tags: Mobile edit Mobile web edit |
||
(14 intermediate revisions 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 connection and SQL query syntax because some MySQL functions are not compatible in SQLite | |||
# 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 | ||
* 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 {{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" | ||
* 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<ref>[https://www.sqlitetutorial.net/sqlite-case/ SQLite CASE Expression: Simple CASE & Search CASE]</ref> | |||
** 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> | |||
* Other functions are not supported such as: SET, Truncate<ref>[https://www.runoob.com/sqlite/sqlite-truncate-table.html SQLite Truncate Table | 菜鸟教程]</ref>, INGORE | |||
== References == | == References == | ||
<references /> | <references /> | ||
[[Category:Data Science]] | |||
[[Category:MySQL]] | [[Category:MySQL]] | ||
[[Category:SQLite]] | |||
[[Category:Tool]] |
Latest revision as of 07:42, 13 November 2019
Steps of migration database engine from MySQL to SQLite
Steps of migration database engine from MySQL to SQLite[edit]
- Migrate database data from MySQL to SQLite using the SQLite management software
- Modify the scripts of database connection and SQL query syntax because some MySQL functions are not compatible in SQLite
- Testing the scripts
Notes of modification of scripts[edit]
- 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
-- MySQL LOCATE(substr,str), LOCATE(substr,str,pos) -- SQLite INSTR(string, substring);
- Other functions are not supported such as: SET, Truncate[5], INGORE