The migration of MySQL and SQLite

Emily
Links: http://zhuanlan.zhihu.com/p/20778415
Source: Know
Copyright belongs to the author. Commercial reprint please contact the author for authorization, non-commercial reprint please specify the source.

Import from SQLite database to MySQL DB instance

Export the files from the Sqllite data file library Xx.sql. Import into the MySQL database.

Type the command: Source/smb/works/mysql.sql

Many of the following errors occur:

You have a error in your SQL syntax; Check the manual thatcorresponds to your MariaDB server version for the right syntax to use near xxxxxxxxx

The reason is that there is a big difference between the Sqllite and MySQL statements: MySQL on the left and SQLite on the right

1. Start the transaction

Mysql for start transaction

Sqlite to begin TRANSACTION

2. Commit a transaction

Mysql for Commit

Sqlite for COMMIT Transaction

3 Creating a Table

MySQL library name, table name, list, etc. do not need to use [],sqlite all the names need to increase [].

4. Data type

SQLite data types are supported by MySQL. However, the definition of sqlite in the scope of MySQL import will be an error. Only varchar needs to define the scope.

5. Conflict resolution

SQLite's ONCONFLICT clause is not a standalone SQL command. This is a non-standard clause that can appear in many other SQL commands. Not supported in MySQL.

Import from MySQL database to SQLite db instance

First step: MySQL export. sql file

Enter in the terminal
Mysqldump-h localhost-u root-p Mydb>e:\mysql\mydb.sql
Then enter the password, wait for an export to succeed, you can check the target file for success.

Step two: Open the database db file with a command.

The database file test.db is created under the current directory, and if the database file exists, the database file is opened. If you want to modify it from someone else's db file, you can open its database file in this way and then modify it. After the import succeeds, the following appears:

Step three: Use the DABATABSE command

The database file that the library main corresponds to saves the path to the file opened in the second step.

Fourth step: Modify the Database

Use an SQL statement to manipulate the database. If migrating from another library, using the SQL file exported by the library, open the SQL file with Notepad, there are some differences between MySQL and SQLite SQL statements, and there are bids in the document "Import from SQLite database to MySQL database".

You can use the Read command to read into the SQL file when you need to create a table for the database, adjust the data structure, and then modify it in the. sql file. If there is a mistake, an error message is returned. If it is correct, it returns directly.

Fifth step: Confirm whether the modification is successful

Using the headers on command, open the table switch, and then the SQL statement Select Lookup table to see if the contents of the table have been modified successfully.

The sqlite3 command needs to be called through the form ". XXX". You need to add "." Before invoking the command.

The SQL statement requires "XXXXXXXXXX;" In the form of a call. Add ";" after statement completion

The command set for SQLite:

MySQL Command collection

The migration of MySQL and SQLite