Shulou(Shulou.com)05/31 Report--

Editor to share with you what problems sqlite3 migration mysql may encounter, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

Question list

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

2. Sqlite database data cannot export hidden field rowid

3. The data export format of sqlite database is not compatible with other databases, such as single and double quotation marks.

4. The exported sqlite data does not have a column name, as follows INSERT INTO protocol VALUES ('New pages for large categories','). What we may need is INSERT INTO protocol ('text1','text2') VALUES (' large page (new), 'refresh page')

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

6. The limit of table field length is different.

7. The efficiency of data writing

Problem solving

First of all, you can't use sqliteStudio, Navicat and other tools. Here, we use the shell command to avoid content generation directly.

BEGIN TRANSACTION

COMMIT

CREATE UNIQUE INDEX

PRAGMA foreign_keys=OFF

At the same time, the use of shell can be achieved with a small amount of code.

1. Solve the problem that the field column name has no output.

Run the sqlite3 command "pragma table_info;" to get the following output

0 | name | TEXT | 0 | | 0

1 | description | TEXT | 0 | | 0

Then get the column name by string substitution, as follows:

Suppose the variable is COLS = name,description

2. Solve the problems of single quotation marks and double quotes

Run the sqlite3 command ".mode insert & & .dump table name" to get the following output

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

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

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

The problem of single quotation marks and double quotation marks can be solved in this way, where single quotation marks are output directly.

3. Solve the problem that the default field rowid of sqlite3 cannot be displayed. Change rowid to id directly here.

Sets the current mode to .dump insert mode

Run the sqlite3 command "select rowid as id,$COLS from table name" to get the following output

The name of the INSERT INTO table is VALUES (1 test')

The name of the INSERT INTO table is VALUES (2)

The name of the INSERT INTO table is VALUES (3)

4. Add the previously obtained column name to the following sql statement through the shell string command

After modification, it is as follows:

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

The name of the INSERT INTO table ('name',' description') VALUES (2)

The name of the INSERT INTO table ('name',' description') VALUES (3MagneTestOne Test`)

5. Escape character processing

If there are escape characters in the data in the database, such as: {\ "lastname\":\ "Tianjin\"}. If this kind of data is not processed, it will become {"lastname":\ "Tianjin\"} when insert the data to the database. Therefore, you need to deal with the escape character. It is very simple to use the shell command, such as the command: sed's #\ # g'

Scheme realization

This script mainly solves the above 1 / 5 problems, and can be modified 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-dwindlers'- 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 is all the contents of the article "what are the problems that sqlite3 may encounter in migrating mysql". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.