A collection of possible problems when migrating sqlite3 to mysql

Brief description

Suitable for readers: Mobile development

There are many syntax problems when migrating sqlite3 data to mysql. It is impossible to list all the problems at once. This article will try to list as many problems as possible, because everyone encounters different problems. Readers can use this article to predict in advance whether they will face any problems. Knowing what to expect can help avoid or reduce unexpected problems that may occur after migrating data. According to the list of problems, you can understand what problems you need to solve. I will also provide a solution for your reference. There is no one-size-fits-all solution to the migration problem, because everyone encounters different problems. If some conflicting problems exist in the solution at the same time, it will become a problem.

Okay, let’s get straight to the point!

Question List

1. Various variables dumped by sqlite3 are not recognized in mysql, such as (BEGIN TRANSACTION, COMMIT, etc.)

2. The sqlite database data cannot export the hidden field rowid

3. The data export format of sqlite database is incompatible with other databases, such as single quotes and double quotes

4. The exported sqlite data does not have column names, as shown in INSERT INTO protocol VALUES('大類頁（新）', ' ') . What we may need is INSERT INTO protocol（'text1','text2'） VALUES('大類頁（新）', '刷新頁')

5. Special symbol processing, such as escape character "\"

6. Table field length limits are different

7. Data writing efficiency

Problem Solving

First of all, you cannot use tools such as sqliteStudio and Navicat. Here, you can use shell commands to directly avoid content generation.

BEGIN TRANSACTION

COMMIT

CREATE UNIQUE INDEX

PRAGMA foreign_keys=OFF

At the same time, using the shell method can be implemented with very little code.

1. Solve the problem that the field column name is not output

Running the sqlite3 command " pragma table_info(表名); " yields the following output

0|name|TEXT|0||0

1|description|TEXT|0||0

Then get the column name through string replacement, as follows:

Assume the variables are COLS = name, description

2. Solve the problem of single quotes and double quotes

Running the sqlite3 command " .mode insert && .dump 表名" yields the following output

INSERT INTO table name VALUES ('test',' test');

INSERT INTO table name VALUES ('test','test');

INSERT INTO table name VALUES ('test','test');

This method can solve the problem of single quotes and double quotes. Here we directly output single quotes uniformly.

3. Solve the problem that the default field rowid of sqlite3 cannot be displayed. Here, directly change rowid to id

Set the current mode to .dump insert mode

Running the sqlite3 command " select rowid as id,$COLS from 表名" yields the following output

INSERT INTO table name VALUES (1,'test',' test');

INSERT INTO table name VALUES (2,'test','test');

INSERT INTO table name VALUES (3,'test','test');

4. Use the shell string command to add the previously obtained column names to the following SQL statement

After modification, it is as follows:

INSERT INTO table name ('name',' description') VALUES (1,'test',' test');

INSERT INTO table name('name',' description') VALUES(2,'test','test');

INSERT INTO table name('name',' description') VALUES(3,'test','test');

5. Escape character processing

If there are escape characters in the data in the database, such as: {\"lastname\":\\"天津\\"} . If this type of data is not processed, it will become {"lastname":\"天津\"} when inserted into the database. So we need to process the escape character. It is very simple to process it with shell command, such as command: sed 's#\\#\\\\#g'
Solution Implementation

This script mainly solves the above 1~5 problems. You can modify the script as needed.

 #!/bin/sh
 SQLITE=sqlite3
 if [ -z "$1" ] ; then
   echo usage: $0 sqlite3.db
   exit
 fi
 DB="$1"
 TABLES=`"$SQLITE" "$DB" .tables`
 for TABLE in $TABLES ; do
   CREATE=`"$SQLITE" "$DB" "SELECT sql FROM sqlite_master WHERE type=\"table\" AND name = \"$TABLE\";"`
   echo $CREATE";" |
   cut -d'=' -f2 |
   sed "s/^CREATE TABLE $TABLE (/CREATE TABLE $TABLE (id int auto_increment primary key ,/g"
   COLS=`"$SQLITE" "$DB" "pragma table_info($TABLE)" | cut -d'|' -f2 `
   COLS_CS=`echo $COLS | sed 's/ /,/g'`
   echo ".mode insert \n.header on \n select rowid as id,$COLS_CS from $TABLE;\n" |
   "$SQLITE" "$DB" |
   sed "s/^INSERT INTO \"table\"/INSERT INTO $TABLE /g" |
   sed 's#\\#\\\\#g'
 done

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM.