本文共 3727 字,大约阅读时间需要 12 分钟。
这是最直接、最简单的方法:在linux下用cp直接拷贝数据库,win下直接复制粘贴。
在终端上执行[ sqlite3 数据库文件名],进入sqlite环境,然后可以使用.backup .clone备份或克隆数据库。(注:如果新数据库已经存在,克隆时会报错,而备份不会)
$ sqlite3sqlite> .help.backup ?DB? FILE Backup DB (default "main") to FILE....clone NEWDB Clone data into NEWDB from the existing database...
可以使用shell脚本执行,这样就能在程序中调用脚本,自动执行。
$ cat sqlte3Backup.sh #!/bin/basholdDB=$1newDB=$2echo ".backup $newDB" | sqlite3 $oldDB
可以使用select筛选出指定信息,保存到csv中
$ sqlite3 数据库文件名sqlite> .headers on // 显示列表头,否则在csv中没有表头sqlite> .mode csv // 选择csv(逗号分隔值类型)sqlite> .once test.csv // 将下面的SQL语句输出保存到文件中,只保存一次, // 第二个SQL会恢复输出到终端。sqlite> SELECT * FROM 表;sqlite> .exit;
$ cat sqlte3ToCSV.sh #!/bin/bashfileName=$1dbName=$2echo ".headers on.mode csv.once $fileNameselect * from test;.exit" | sqlite3 $dbName
id^_name^_age^^1^_XiaoHong^_18^^2^_XiaoMing^_19^^
id name age ---------- ---------- ----------1 XiaoHong 18 2 XiaoMing 19
idnameage1XiaoHong182XiaoMing19
INSERT INTO "table"(id,name,age) VALUES(1,'XiaoHong',18);INSERT INTO "table"(id,name,age) VALUES(2,'XiaoMing',19);
id = 1 name = XiaoHong age = 18 id = 2 name = XiaoMing age = 19
id|name|age1|XiaoHong|182|XiaoMing|19
id name age1 XiaoHong 182 XiaoMing 19
"id" "name" "age""1" "XiaoHong" "18""2" "XiaoMing" "19"
注意:这种方法对SQLite版本有要求,SQLite 版本至少3.27.0 (2019-02-07)
$ sqlite3 数据库文件名sqlite> VACUUM INTO 新数据库文件名 // VACUUM 后面省略了数据库名,默认是main, 使用.database查看sqlite> .exit;
$ cat sqlte3Vacuum.sh#!/bin/basholdDB=$1newDB=$2echo "vacuum into '$newDB'" | sqlite3 $oldDB
注意:如果新数据库存在,使用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;}
涉及的接口如下:
sqlite3_backup_init()sqlite3_backup_step()sqlite3_backup_remaining()sqlite3_backup_pagecount()sqlite3_backup_finish()
测试程序如下:
#include#include 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;}
转载地址:http://bdmei.baihongyu.com/