資料遷移

一， 使用.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上面做備份：

[root@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

 

四，檢視生成的備份檔案：

[root@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;

[root@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指令碼

[root@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>

 

至此，整個資料遷移工作順利結束。