【数据库】sqlite3数据库备份、导出方法汇总

【数据库】sqlite3常用命令及SQL语句

1、直接拷贝数据库

这是最直接、最简单的方法：在linux下用cp直接拷贝数据库，win下直接复制粘贴。

2、使用.backup .clone

1）交互式

在终端上执行[ sqlite3 数据库文件名]，进入sqlite环境，然后可以使用.backup .clone备份或克隆数据库。（注：如果新数据库已经存在，克隆时会报错，而备份不会）

$ sqlite3
sqlite> .help
.backup ?DB? FILE        Backup DB (default "main") to FILE
...
.clone NEWDB             Clone data into NEWDB from the existing database
...

2）脚本

可以使用shell脚本执行，这样就能在程序中调用脚本，自动执行。

$ cat sqlte3Backup.sh 
#!/bin/bash
oldDB=$1
newDB=$2
echo ".backup $newDB" | sqlite3 $oldDB

3、导出到csv文件中（其它格式类似）

可以使用select筛选出指定信息，保存到csv中

1）交互式

$ sqlite3 数据库文件名
sqlite> .headers on	// 显示列表头，否则在csv中没有表头
sqlite> .mode csv	// 选择csv（逗号分隔值类型）
sqlite> .once test.csv	// 将下面的SQL语句输出保存到文件中，只保存一次，
						// 第二个SQL会恢复输出到终端。
sqlite> SELECT * FROM 表;
sqlite> .exit;

2）脚本

$ cat sqlte3ToCSV.sh 
#!/bin/bash
fileName=$1
dbName=$2
echo ".headers on
.mode csv
.once $fileName
select * from test;
.exit
" | sqlite3 $dbName

3）导出成其它格式汇总

a> .mode ascii

id^_name^_age^^1^_XiaoHong^_18^^2^_XiaoMing^_19^^

b> .mode column

id          name        age       
----------  ----------  ----------
1           XiaoHong    18        
2           XiaoMing    19 

c> .mode html

<TR><TH>id</TH>
<TH>name</TH>
<TH>age</TH>
</TR>
<TR><TD>1</TD>
<TD>XiaoHong</TD>
<TD>18</TD>
</TR>
<TR><TD>2</TD>
<TD>XiaoMing</TD>
<TD>19</TD>
</TR>

d> .mode insert

INSERT INTO "table"(id,name,age) VALUES(1,'XiaoHong',18);
INSERT INTO "table"(id,name,age) VALUES(2,'XiaoMing',19);

e> .mode line

   id = 1
 name = XiaoHong
  age = 18

   id = 2
 name = XiaoMing
  age = 19

f> .mode list

id|name|age
1|XiaoHong|18
2|XiaoMing|19

g> .mode tabs

id	name	age
1	XiaoHong	18
2	XiaoMing	19

h> .mode tcl

"id" "name" "age"
"1" "XiaoHong" "18"
"2" "XiaoMing" "19"

4、使用SQL语句：VACUUM INTO

注意：这种方法对SQLite版本有要求，SQLite 版本至少3.27.0 (2019-02-07)

1）交互式

$ sqlite3 数据库文件名
sqlite> VACUUM INTO 新数据库文件名 // VACUUM 后面省略了数据库名，默认是main， 使用.database查看
sqlite> .exit;

2）脚本

$ cat sqlte3Vacuum.sh
#!/bin/bash
oldDB=$1
newDB=$2
echo "vacuum into '$newDB'" | sqlite3 $oldDB

3）在SQLite提供的C接口中使用SQL语句：vacuum into

注意：如果新数据库存在，使用VACUUM INTO会报错

error: output file already exists!

测试程序如下：

int main()
{
	sqlite3 *pDB;

	int rc = sqlite3_open("test.db", &pDB);
	if( rc==SQLITE_OK ){
		printf("open test.db OK!\n");
		char *sqlVacuum = "VACUUM INTO 'new.db';";
		char *errMsg = 0;
		rc = sqlite3_exec(pDB, sqlVacuum, NULL, 0, &errMsg);
		if( rc!=SQLITE_OK ){
			printf("VACUUM INTO new.db error: %s!\n", errMsg);
			sqlite3_free(errMsg);
		} else {
			printf("VACUUM INTO new.db OK!\n");
		}
	} else {
		printf("open test.db error!\n");
	}

	sqlite3_close(pDB);
	return 0;
}

5、使用SQLite C接口sqlite3_backup*

涉及的接口如下：

sqlite3_backup_init()
sqlite3_backup_step()
sqlite3_backup_remaining()
sqlite3_backup_pagecount()
sqlite3_backup_finish()

测试程序如下：

#include <stdio.h>
#include <sqlite3.h>

void progress(int left, int total){
	printf("total = %d; left = %d\n", total, left);
}

int backupDb(sqlite3 *pDb, const char *zFilename, void(*xProgress)(int, int) ){
  int rc;
  sqlite3 *pFile;
  sqlite3_backup *pBackup;

  rc = sqlite3_open(zFilename, &pFile);
  if( rc==SQLITE_OK ){

	pBackup = sqlite3_backup_init(pFile, "main", pDb, "main");
	if( pBackup ){

	  do {
		rc = sqlite3_backup_step(pBackup, 5);
		xProgress(
			sqlite3_backup_remaining(pBackup),
			sqlite3_backup_pagecount(pBackup)
		);
//		if( rc==SQLITE_OK || rc==SQLITE_BUSY || rc==SQLITE_LOCKED ){
//		  sqlite3_sleep(250);
//		}
	  } while( rc==SQLITE_OK || rc==SQLITE_BUSY || rc==SQLITE_LOCKED );

	  (void)sqlite3_backup_finish(pBackup);
	}
	rc = sqlite3_errcode(pFile);
  }

  (void)sqlite3_close(pFile);
  return rc;
}
int main(){
	sqlite3 *pDB;

	int rc = sqlite3_open("test.db", &pDB);
	if( rc==SQLITE_OK ){
		printf("open test.db OK!\n");

		rc = backupDb(pDB, "new.db", progress);
		if( rc==SQLITE_OK ){
			printf("backupDb new.db OK!\n");
		} else {
			printf("backupDb new.db error!\n");
		}
	} else {
		printf("open test.db error!\n");
	}

	sqlite3_close(pDB);
	return 0;
}