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

It is believed that many inexperienced people have no idea about how to migrate SQLITE to MYSQL. Therefore, this article summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

To meet the needs of colleagues, request to transfer the data from SQLITE to MYSQL, which has never been touched before. Search here, try there, and list the process below.

The main process is divided into three steps:

1. Export the structure of the SQLITE table and make some format adjustments

2. Export SQLITE data for special character processing.

3, export MYSQL (note character set) [@ more@]

The following is the process of my operation, which has two parts: single table operation and batch processing (including script)

# single table operation #

# Open SQLITE DB

Root > sqlite3 mig.db

# check the table and use ".help" to get help

Sqlite > .tables

# Export data table structure

Sqlite > .output / tmp/heyf/createtable.sql

Sqlite > .schema accounts_profile

# adjust the format of the table statement, remove the double quotes, and add the table creation constraint of MYSQL.

Root > sed's / "/ `/ g' createtable.sql > create_onmysql.sql

Root > sed-I-e's / ^); $/) engine=innodb default charset=utf8 collate=utf8_bin; / g 'create_onmysql.sql

Root > sed-I 's/integer.*PRIMARY KEY/int NOT NULL auto_increment PRIMARY KEY/g' create_onmysql.sql

In # sqlite, the fields that are INTEGER PRIMARY KEY will be automatically increased

Root > sed-I's / text/ longtext/g' create_onmysql.sql

In # sqlite, the maximum length of text is 2G. In MYSQL, it needs to be changed to longtext.

# build the table in MYSQL:

Mysql-uroot-h227.0.0.1-- default-character-set=utf8 test

< create_onmysql.sql # 导出数据 sqlite>

.mode insert accounts_profile_starred_review_requests

Sqlite > .output / tmp/heyf/accounts_profile_starred_review_requests.sql

Sqlite > select * from accounts_profile_starred_review_requests

Sqlite > .output stdout

# handling special characters

DOTABLE=accounts_profile_starred_review_requests

Cd / tmp/heyf

DOFILE=$ {DOTABLE} .sql

Sed-e "sGUBG" $DOFILE > ${DOFILE} .new

# Import data into MYSQL

Mysql-uroot-h227.0.0.1-- default-character-set=utf8 test-e "truncate table ${DOTABLE};"

Mysql-uroot-h227.0.0.1-- default-character-set=utf8 test

< ${DOFILE}.new 如果表很多：那我们就需要批量处理一下啦，下面是一些批量脚本 ： ########### 批量处理 #################### # 打开SQLITE DB root>

Sqlite3 mig.db

# View the table

Sqlite > .tables

# put all table names in the TABLE.LIST file

# Export data table structure

Sqlite > .output / tmp/heyf/createtable.sql

Sqlite > .schema accounts_profile

Sqlite > .schema.

# create a table structure in MYSQL

Root > sed's / "/ `/ g' createtable.sql > create_onmysql.sql

Root > sed-I-e's / ^); $/) engine=innodb default charset=utf8 collate=utf8_bin; / g 'create_onmysql.sql

Root > sed-I 's/integer.*PRIMARY KEY/int NOT NULL auto_increment PRIMARY KEY/g' create_onmysql.sql

In # sqlite, the fields that are INTEGER PRIMARY KEY will be automatically increased

Root > sed-I's / text/ longtext/g' create_onmysql.sql

In # sqlite, the maximum length of text is 2G. In MYSQL, it needs to be changed to longtext.

Root > mysql-uroot-h227.0.0.1-- default-character-set=utf8 test

< /tmp/heyf/create_onmysql.sql # SQLITE 中导出数据 ## 将所有的表生成导出语句（SQLITE) ## cat create_exp.sh ------------------------- #!/bin/bash [ $# -ne 1 ] && exit 1 TABFILE=$1 WORKDIR=/tmp/heyf/dmpfile/ OUTFILE=exp.sql while read LINE do echo $LINE echo ".mode insert $LINE" >

> $OUTFILE

Echo ".output $WORKDIR/$ {LINE} .sql" > > $OUTFILE

Echo "select * from $LINE;" > > $OUTFILE

Echo ".output stdout" > > $OUTFILE

Done

< $TABFILE ------------------------- ## 运行后将产生 sqlite 所有表的导出语句 sh create_exp.sh table.list ## 现在进行SQLITE可以将所有表的数据导出来。 sqlite3 /home/admin/foryunfei/heyf.db .read /tmp/heyf/exp.sql # 处理特殊字符 ## 现在需要将所有的SQL文件中的""替换成"",以使SQL能正确执行 ## cat replace.sh ------------------------- #!/bin/bash [ $# -ne 1 ] && exit 1 TABFILE=$1 WORKDIR=/tmp/heyf/dmpfile/ while read DOTABLE do cd $WORKDIR DOFILE=${DOTABLE}.sql sed -e "s///g" $DOFILE >

${DOFILE} .new

Done < $TABFILE

-

# # sh replace.sh table.list

# # importing data into MYSQL

# # cat imp.sh

-

#! / bin/bash

[$#-ne 1] & & exit 1

TABFILE=$1

WORKDIR=/tmp/heyf/dmpfile/

While read DOTABLE

Do

Cd $WORKDIR

Mysql-uroot-h227.0.0.1-- default-character-set=utf8 test-e "truncate table ${DOTABLE};"

Mysql-uroot-h227.0.0.1-- default-character-set=utf8 test < ${DOTABLE} .sql.new

Done < $TABFILE

-

# # sh imp.sh table.list

After reading the above, have you mastered how to migrate SQLITE to MYSQL? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

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.