I have a small project at hand. The online database is SQLite, and the local development environment uses MySQL database for management convenience. Sometimes in order to verify some data problems on the line, you want to synchronize the data on the line to the local, and then import it into MySQL. What was supposed to be simple actually took some time.

The complete process is divided into three steps:

1. Download the online SQLite database to local, and export it as SQL file

This step is very simple, using the SCP command can solve the file download. To export to SQL files, just use the following command:

sqlite3 database.sqlite .dump > sqlite_dump.sql

Database.sqlite is the name of the downloaded SQLite database, and sqlite_dump.sql is the exported SQL format data file.

2. Convert the contents of the SQL file

SQLite exported SQL files cannot be used directly in MySQL, and some syntax differences need to be converted. The main differences are as follows:

  • SQLite has some statements that MySQL does not support, such asBEGIN TRANSACTION.COMMIT.sqlite_sequence.
  • Some SQLite table building statements are not compatible with MySQL. Such asvarchar not null, this will return an error in MySQL.
  • SQLite creates tables using double quotes:"", but MySQL uses the top quotation marks:``.
  • SQLite index creation statements use double quotation marks for table names and fields. Similar to the above, you need to replace them with upper quotation marks.
  • SQLite uset 和 fTo express a Boolean value, MySQL uses1 和 0.
  • SQLite increment attribute keyword isAUTOINCREMENTAnd MySQL isAUTO_INCREMENT.

The conversion step is the most time-consuming, online search several ready-made conversion code, the results can not be directly used after conversion. You have to take it one by one.

3. Import MySQL

After the second step of the transformation, you can import SQL data directly using the MySQL command. The command format is as follows:

mysql -uroot -p123456 db_name < sqlite_dump.sql

DB_NAME is the name of the data table to be imported in MySQL.