[Sqlite]-->数据迁移备份--从低版本3.6.2到高版本3.8.6

erew2

数据迁移

一， 使用.dump命令

命令帮助提示

.dump ?TABLE? ...      Dump the database in an SQL text format

                         If TABLE specified, only dump tables matching

                         LIKE pattern TABLE.

理解分析：
       使用.dump命令可以将数据库对象导出成SQL格式。不带任何参数时，.dump将整个数据库导出为数据库定义语言(DDL)和数据库操作语言(DML)命令，适合重新创建数据库对象和其中的数据。如果提供了参数，Shell将参数解析作为表名或视图，导出任何匹配给定参数的表或视图，那些不匹配的将被忽略。

默认情况下.dump 命令的输出定向到屏幕。如：.dump

如果要将输出重定向到文件，请使用.dump[filename]命令，此命令将所有的输出重定向到指定的文件中。若要恢复到屏幕的输出，只需要执行.output stdout命令就OK了。

sqlite>.output file.sql

sqlite>.dump

sqlite>.output stdout

注：如果file.sql不存在，将在当前工作目录中创建该文件。如果文件存在，它将被覆盖。

二，准备测试数据：

CREATE TABLE COMPANY(ID INT NOT NULL, NAME VARCHAR(20),AGE INT,ADDRESS VARCHAR(20),SALARY DECIMAL(7,2));

INSERT INTO COMPANY

SELECT 1,           'Paul',        32,          'California'  ,20000.0 UNION ALL

SELECT 2,           'Allen',       25,          'Texas'       ,15000.0 UNION ALL

SELECT 3,           'Teddy',       23,         'Norway'      ,20000.0 UNION ALL

SELECT 4,           'Mark',        25,          'Rich-Mond'   ,65000.0 UNION ALL

SELECT 5,           'David',       27,          'Texas'       ,85000.0 UNION ALL

SELECT 6,           'Kim',         22,          'South-Hall'  ,45000.0 UNION ALL

SELECT 7,           'James',       24,          'Houston'     ,10000.0 ;

SELECT * FROM COMPANY;

CREATE TABLE t1(id INT,NAME VARCHAR(20));

INSERT INTO t1 SELECT 1,'a' UNION ALL SELECT 2,'b' UNION ALL SELECT 3,'c';

将低版本的表修改表名为临时表COMPANY_TMP。

ALTER TABLE COMPANY RENAME TO COMPANY_TMP;

三，开始备份操作，在低版本3.6.2上面做备份：

[iyunv@name01 ~]# /usr/bin/sqlite3.bak.3.6.2 tim

SQLite version 3.6.20

Enter ".help" for instructions

Enter SQL statements terminated with a ";"

sqlite>

sqlite> .headers on

sqlite> .mode columns

sqlite> .output alltables.sql

sqlite> .dump

sqlite>.exit

四，查看生成的备份文件：

[iyunv@name01 ~]# more alltables.sql

PRAGMA foreign_keys=OFF;

BEGIN TRANSACTION;

CREATE TABLE "COMPANY_TMP"(ID INT NOT NULL, NAME VARCHAR(20),AGE INT,ADDRESS VARCHAR(20),SALARY DECIMAL(7,2));

INSERT INTO "COMPANY_TMP" VALUES(1,'Paul',32,'California',20000);

INSERT INTO "COMPANY_TMP" VALUES(2,'Allen',25,'Texas',15000);

INSERT INTO "COMPANY_TMP" VALUES(3,'Teddy',23,'Norway',20000);

INSERT INTO "COMPANY_TMP" VALUES(4,'Mark',25,'Rich-Mond',65000);

INSERT INTO "COMPANY_TMP" VALUES(5,'David',27,'Texas',85000);

INSERT INTO "COMPANY_TMP" VALUES(6,'Kim',22,'South-Hall',45000);

INSERT INTO "COMPANY_TMP" VALUES(7,'James',24,'Houston',10000);

CREATE TABLE t1(id INT,NAME VARCHAR(20));

INSERT INTO "t1" VALUES(1,'a');

INSERT INTO "t1" VALUES(2,'b');

INSERT INTO "t1" VALUES(3,'c');

COMMIT;

[iyunv@name01 ~]#

看到都是一条条备份成的dml、ddl的sql语句。

五，在高版本3.8.6上面恢复数据

1，在3.8.6上面建立相同的COMPANY表，不过多添加了2个字段IPHONE和LOGIN_DATE，如下所示：

DROP TABLE IF EXISTS COMPANY;

CREATE TABLE COMPANY(

ID INT NOT NULL,

NAME VARCHAR(20),

AGE INT,

ADDRESS VARCHAR(20),

SALARY DECIMAL(7,2),

IPHONE VARCHAR(16)                             

NOT NULL,LOGIN_DATE DATETIME

);

INSERT INTO COMPANY

SELECT 1,           'Paul',        32,          'California'  ,20000.0,'14782121412',DATETIME('NOW') UNION ALL

SELECT 2,           'Allen',       25,          'Texas'       ,15000.0,'13982121412',DATETIME('NOW') ;

SELECT * FROM COMPANY;

            

2， 开始恢复导入原来备份的数据sql脚本

[iyunv@name01 ~]# sqlite3 ti

SQLite VERSION 3.8.6 2014-08-15 11:46:33

Enter ".help" FOR USAGE hints.

sqlite> .headers ON

sqlite> .MODE COLUMNS

sqlite> .READ alltables.sql

sqlite> .TABLE

COMPANY      COMPANY_TMP  t1        

sqlite>

3，在数据迁移从旧临时表迁移到新表之前，查下现有表的数据

sqlite>SELECT * FROM COMPANY;

ID          NAME        AGE         ADDRESS     SALARY      IPHONE       LOGIN_DATE        

----------  ----------  ----------  ----------  ----------  -----------  -------------------

1           Paul        32          California  20000       14782121412  2014-08-29 11:14:07

2           Allen       25          Texas       15000       13982121412  2014-08-29 11:14:07

sqlite>

4，开始使用INSERT迁移数据到新添加字段的表COMPANY

sqlite> INSERT INTO COMPANY(ID,NAME,AGE,ADDRESS,SALARY,IPHONE) SELECT ID,NAME,AGE,ADDRESS,SALARY,'' FROM COMPANY_TMP ;

sqlite> SELECT * FROM COMPANY;

ID          NAME        AGE         ADDRESS     SALARY      IPHONE       LOGIN_DATE        

----------  ----------  ----------  ----------  ----------  -----------  -------------------

1           Paul        32          California  20000       14782121412  2014-08-29 11:14:07

2           Allen       25          Texas       15000       13982121412  2014-08-29 11:14:07

1           Paul        32          California  20000                                      

2           Allen       25          Texas       15000                                      

3           Teddy       23          Norway      20000                                      

4           Mark        25          Rich-Mond   65000                                      

5           David       27          Texas       85000                                      

6           Kim         22          South-Hall  45000                                      

7           James       24          Houston     10000                                      

sqlite>

看到迁移数据之后的显示，旧表数据已经迁移到新表了。

六，最后删除旧的临时表

sqlite> DROP TABLE IF EXISTS COMPANY_TMP;

sqlite>

至此，整个数据迁移工作顺利结束。