Migrate database engine from MySQL to SQLite: Difference between revisions
Jump to navigation
Jump to search
m (Planetoid moved page Migrate PHP script from SQLite to MySQL to Migrate database engine from SQLite to MySQL) |
Tags: Mobile edit Mobile web edit |
||
(10 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 | ||
* 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" | * 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 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 [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=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> | ** 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> | ||
Line 14: | Line 22: | ||
INSTR(string, substring); | INSTR(string, substring); | ||
</pre> | </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 == | ||
Line 21: | Line 31: | ||
[[Category:MySQL]] | [[Category:MySQL]] | ||
[[Category:SQLite]] | [[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