一、Sqlite3 数据类型

二、Sqlite3 常用指令操作

三、Sqlite3 API

1. sqlite3_open ( )

2. sqlite3_errmsg ( )

3. sqlite3_close ( )

4. sqlite3_exec ( )

5. sqlite3_get_table ( )

6. sqlite3_prepare_v2 ( )

7. sqlite3_bind ( )

8. sqlite3_step ( )

9. sqlite3_finalize ( )

四、Sqlite3 事务机制 (性能优化)

1. 事务

2. 锁状态

3. SQLITE_BUSY

五、Sqlite3 磁盘同步（性能优化）

六、功能示例（增删改查、性能优化）

1.打开 / 创建数据库

2.检查表 / 创建表 / 删除表

3. 判断数据是否存在（某字段重复）

4.添加数据

5. 更新数据

6. 删除数据

7. 查询数据

8. 管理数据库容量

一、Sqlite3 数据类型

  • NULL ：空值

  • INTEGER：整型

  • REAL：浮点型

  • TEXT：字符串

  • BLOB：二进制

  • DATETIME：日期

二、Sqlite3 常用指令操作

  • 打开数据库 【sqlite3 dbname】
  • 查看数据库版本 【.version】
  • 查看已打开的数据库 【.database】
  • 显示表 【.table】
  • 显示表信息 【.schema】
  • 格式化输出数据 【.header on】【.modecolumn】
  • 导出数据库 【.dump】
  • 清理压缩数据库 【vacuum】

三、Sqlite3 API

1. sqlite3_open ( )

int sqlite3_open( const char* dbname，sqlite3* *db)

功能打开或创建数据库文件，返回句柄
参数const char* dbname ：数据库文件名， 为NULL时在RAM创建内存数据库
sqlite3* *db ：数据库指针句柄
返回值成功 ：SQLITE_OK
失败 ：ERROR_msg
sqlite3 *sqlite3_open_database(){sqlite3 *db;int ret;ret = sqlite3_open("MyDB", &db);if (ret == SQLITE_OK) {/* 打开/创建 成功*/} else { /* 打开/创建失败, 打印错误信息*/sqlite3_errmsg(db);return NULL;}return db;}

2. sqlite3_errmsg ( )

char *sqlite3_errmsg ( sqlite3* db )

功能显示错误信息
参数sqlite3* db ：数据库文件句柄
返回值const char* err_msg
printf ("Error message is: %s", sqlite3_errmsg(db));

3. sqlite3_close ( )

int sqlite3_close ( sqlite3* db )

功能关闭数据库文件连接
参数sqlite3* db ：数据库文件句柄
返回值成功：SQLITE_OK
阻塞失败：SQLITE_BUSY
int sqlite3_close_database (sqlite3 *db){if (db != NULL) {sqlite3_close(db);return 0;} else {return -1;}}

4. sqlite3_exec ( )

intsqlite3_exec（sqlite3* db，char* sql，exec_callback，

void* data，char* *err_msg）

功能回调执行SQL，包含预编译—执行
参数sqlite3* db ：数据库文件句柄
char* sql ：执行的sql语句字符串
exec_callback ：回调函数 （无回调函数则默认0）
void* data ：传入回调函数的参数 （无回调函数则默认0）
char* *err_msg ：错误信息
返回值成功 ：SQLITE_OK
失败 ：Error_msg

int exec_handle (void* data，int argc，char* *argv，char* *colname)

功能sqlite_exec回调函数，每次只返回单条结果
参数void* data ：sqlite3_exec的传入参数
int argc ：SQL功能返回结果的字段数量
char* *argv ：SQL功能返回结果的内容，为一维char *数组
char* *cloname ：SQL功能返回结果的字段名称，为一维char *数组
返回值

注意：必须return 0(FALSE)，负责报errmsg【query aborted】

5. sqlite3_get_table ( )

int sqlite3_get_table (sqlite3* db，char* sql，char* **dbResult，

int* nRow，int* nColnm，char* *errmsg)

功能非回调执行SQL
参数sqlite3* db：数据库文件句柄
char* sql：执行的sql语句
char* **dbResult：执行sql返回的结果集，一维char *数组的指针
int* nRow：返回结果集的总行数
int* nColnm：返回结果集的字段数
返回值SQLITE_OK：执行成功
Error_code：执行失败
注意事项**dbResult使用完必须用sqlite3_free_table( )释放，否则会内存泄漏
访问**dbResult要注意越界问题，否则程序会崩

6. sqlite3_prepare_v2 ( )

int sqlite3_prepare_v2 (sqlite3 *db，const char *zSql，int nByte，

sqlite3_stmt **ppStmt，const char **pzTail )

功能将SQL语句文本转化为申明对象，返回对象指针
参数sqlite3* db ：数据库文件句柄
const char* zSQL：SQL语句
int nByte ：SQL字节数，< 0表示取到第一个\0终止符
sqlite3_stmt* *ppStmt：预编译语句句柄
const char* *pzTail ：指向SQL语句中未使用的部分
返回值成功：SQLITE_OK
失败：Error_code，stmt =NULL

7. sqlite3_bind ( )

int sqlite3_bind_int (sqlite3_stmt* pStmt，int index，int data)

功能给预编译语句绑定整型参数
参数sqlite3_stmt *pStmt ：预编译语句句柄
int index：序号 ，从1开始
int data：整型数值
返回值成功：SQLITE_OK
失败：Error_code

int sqlite3_bind_doubule (sqlite3_stmt* pStmt，int index，double data)

功能给预编译语句绑定双精度参数
参数sqlite3_stmt *pStmt ：预编译语句句柄
int index：序号 ，从1开始
double data：双精度数值
返回值成功：SQLITE_OK
失败：Error_code

int sqlite3_bind_text (sqlite3_stmt *pStmt，int index，const char *str，

int strlen，void(*)(void*))

功能给预编译语句绑定参数
参数sqlite3_stmt *pStmt ：预编译语句句柄
int index：序号 ，从1开始
const char *str ：绑定值
int strlen：str长度
void(*)(void*) ：NULL
返回值成功：SQLITE_OK
失败：Error_code

8. sqlite3_step ( )

int sqlite3_step (sqlite3_stmt *pStmt)

功能执行预编译后的SQL对象
参数sqlite3_stmt *pStmt ：预编译语句句柄
返回值成功： SQLITE_OK | SQLITE_DONE
阻塞： SQLITE_BUSY
运行错误：SQLITE_ERROR
查询成功：SQLITE_ROW

9. sqlite3_finalize ( )

int sqlite3_finalize (sqlite3_stmt *pStmt)

功能销毁预编译对象
参数sqlite3_stmt *pStmt ：预编译语句对象
返回值成功：SQLITE_OK
失败：Error_code

四、Sqlite3 事务机制 (性能优化)

1. 事务

BEGIN TRANSACTION：启动事务，启动事务在内存中操作。

COMMIT：提交事务，将在内存中的操作提交到库中，并无法回滚。

ROLLBACK：回滚事务，对启动事务后的操作进行回滚

注意事项：

  • 隐式事务：每次DML都为一次事务
  • 显式事务：当需要插入、更新、删除大量数据时，可优化处理时间
  • 效率：事务对查询操作效率无影响，对表操作无影响

2. 锁状态

SHARED：数据库处于读状态，多连接可同时持有，阻塞提交。

RESERVED：数据库处于写状态，多连接仅持有一个，不阻塞读

PENDING：数据库写完成，即将提交。其他连接不可再获取SHARED锁，并等待所有SHARED释放，不阻塞读

EXCLUSIVE：数据库提交事务，阻塞读

锁类型状态共享互斥性级别
SHARED读n阻塞提交1
RESERVED写1不阻塞读2
PENDING预备提交1不阻塞读3
EXCLUSIVE提交1阻塞读4

3. SQLITE_BUSY

原因：连接中存在锁状态为PENDING或者EXCLUSIVE时，其他线程申请锁失败。

解决方式：

int sqlite3_busy_timeout (sqlite3* db,int ms) ：等待 ms后申请锁

int sqlite3_busy_handler (sqlite3* db, intcallback_db(void*, int), void* db) ：循环次数等待锁释放

五、Sqlite3 磁盘同步（性能优化）

PRAGMA synchronous = FULL ：每个关键磁盘操作后同步（sqlite3默认）

PRAGMA synchronous = NORMAL：每个关键磁盘操作的序列后同步 （sqlite2默认）

PRAGMA synchronous = OFF：不进行同步，传递给操作系统完成

同步机制安全性效率性
PRAGMA synchronous = FULL高低
PRAGMA synchronous = NORMAL中中
PRAGMA synchronous = OFF低高

六、功能示例（增删改查、性能优化）

1.打开 / 创建 / 关闭 数据库

sqlite3* sqlite3_open_database(char* db_name){int rc = 0;sqlite3* db;if (db_name == NULL || strlen(db_name) == 0 ) {return NULL;} rc = sqlite3_open(db_name, &db);if (rc != SQLITE_OK) {printf("Open db error :%s", sqlite3_errmsg(db));return NULL;}return db;}
void sqlite3_close_database(sqlite3 *db){if (db != NULL) {sqlite3_close(db);}}

2.检查表 / 创建表 / 删除表

int sqlite3_IsExist_table(sqlite3* db, char* table_name){int rc = 0;int ret = -1;char sql_str[128] = {0}；char* err_msg = NULL;sprintf(sql_str, "SELECT * FROM %s", table_name);rc = sqlite3_exec(db, sql_str, 0, 0, &err_msg);if (rc != SQLITE_OK) {ret = -1;/* 表不存在*/sqlite_free(err_msg);} else {ret = 0; /* 表存在*/}return ret;}
int sqlite3_Create_table(sqlite3* db, char* table_name){int rc = 0;int ret = -1;char sql_str[128] = {0};char* err_msg = NULL;/* 字段：ID Data RealDate*/sprinft(sql_str, "CREATE TABLE IF NOT EXISTS %s (""ID INTEGER PRIMARY KEY AUTOINCREMENT, ""Data TEXT, ""RealDate DATETIME)", table_name);rc = sqlite3_exec(db, sql_str, 0, 0, err_msg);if (rc != SQLITE_OK) {printf("Create table %s error :%s", table_name, err_msg);sqlite3_free(err_msg);ret = -1;} else {ret = 0;}return ret;}
int sqlite3_Delete_table(sqlite3* db, char* table_name){int rc = 0;int ret = -1;char sql_str[128] = {0};char* err_msg = NULL;sprinft(sql_str, "DROP TABLE %s", table_name);rc = sqlite3_exec(db, sql_str, 0, 0, err_msg);if (rc != SQLITE_OK) {printf("Delete table %s error :%s", table_name, err_msg);sqlite3_free(err_msg);ret = -1;} else {ret = 0;}return ret;}

3. 判断数据是否存在（某字段重复）

int IsExist_cal_handle(void *flag, int argc, char** argv, char **colname){int i = *(int*)(flag);*(int *)(flag) = i + 1;return 0;}int sqlite3_IsExist_field(sqlite3* db, char* table_name, char* data_buf){int ret = -1;int rc = 0;char sql_str[128] = {0};char* err_msg = NULL;int flag = 0;/* 用于回调函数计数*/sprintf(sql_str, "Select * FROM %s WHERE Data = '%s'", table_name, data_buf); rc = sqlite3_exec(db, sql_str, IsExist_cal_handle, &flag, &err_msg);if (rc != SQLITE_OK) {/* 查询失败*/printf("Check data exist error :%s", err_msg);return ret;} else {if (flag == 0 ) { /* 结果不存在*/printf("Check data Not exist");return flag;} else {/* 结果存在*/printf("Check data exist, num is %d", flag);return flag;}}}

4.添加数据

/* 添加单条数据*/int sqlite3_insert_single(sqlite3* db, char* table_name, char* data_buf){int rc = 0;int ret = -1;char sql_str[128] = {0};char* err_msg = NULL;sprintf(sql_str, "INSERT INTO %s (ID, Data, RealDate) values(NULL, '%s', DATETIME('now', 'localtime'))", table_name, data_buf);sqlite3_busy_timeout(db, 30*1000);rc = sqlite3_exec(db, sql_str, 0, 0, &err_msg);if (rc != SQLITE_OK) {printf("Insert error :%s", err_msg);sqlite3_free(err_msg);ret = -1;} else {ret = 0;}return ret;}
/* 添加多条数据, 利用显示事务机制*//* 依次插入数据,若出现插入失败,则回滚事务;若全部成功,则提交事务*/int sqlite3_Insert_multidata(sqlite3* db, const char* table_name, char* *buff, int len){int rc = 0；int ret = -1;int i = 0;char sql_str[128] = {0};char* err_msg = NULL;rc = sqlite3_exec(db, "BEGIN;", 0, 0, 0);if (rc != SQLITE_OK) {return ret;}while (buff[i] != NULL && i < len) {memset(sql_str, 0, sizeof(sql_str));sprintf(sql_str, "INSERT INTO %s (ID, Data, RealDate) values(NULL, '%s', DATETIME('now', 'localtime'))", table_name, buff[i]);sqlite3_busy_timeout(db, 30*1000);rc = sqlite3_exec(db, sql_str, 0, 0, &err_msg);if (rc != SQLITE_OK) {printf("Insert No%d data error: %s", i + 1, err_msg);sqlite3_free(err_msg);break;} else {i++;}}if (i < len) {sqlite3_exec(db, "ROLLBACK;", 0, 0, 0);ret = -1;} if (i == len) {sqlite3_exec(db, "COMMIT;", 0, 0, 0);ret = 0;}return ret;}
int sqlite3_Insert_multidata_v2 (sqlite3* db, const char* table_name, char* *buff, int len){int rc = 0；int ret = -1;int i = 0;char sql_str[128] = {0};char* err_msg = NULL;sqlite3_stmt* stmt;sprintf(sql_str, "INSERT INTO %s (ID, Data, RealDate) values (?, ?, DATETIME('now', 'localtime'))");sqlite3_exec(db, "BEGIN;", 0, 0, 0);rc = sqlite3_prepare(db, sql_str, strlen(sql_str), &stmt, 0);if (rc != SQLITE_OK) {return ret;}while(buff[i] != NULL && i < len) {sqlite3_bind_int(stmt, 1, NULL);sqlite3_bind_text(stmt, 2, buff[i], strlen(buff), NULL);rc = sqlite3_step(stmt);if ((rc != SQLITE_OK) && (rc != SQLITE_DONE)) {break;}sqlite3_reset(stmt);i++;}sqlite3_finalize(stmt);if (i < len) {sqlite3_exec(db, "ROLLBACK;", 0, 0, 0);ret = -1;} if (i == len) {sqlite3_exec(db, "COMMIT;", 0, 0, 0);ret = 0;}return ret;}

5. 更新数据

/* 更新单条数据*/int sqlite3_Update_single(sqlite3* db, char* table_name, char* data_buf){int rc = 0;int ret = -1;char sql_str[128] = {0};char* err_msg = NULL;sprintf(sql_str, "UPDATE %s SET RealDate = DATETIME('now', 'localtime') WHERE Data = '%s'", table_name, data_buf);sqlite3_busy_timeout(db, 30*1000);rc = sqlite3_exec(db, sql_str, 0, 0, &err_msg);if (rc != SQLITE_OK) {printf("Update error :%s", err_msg);sqlite3_free(err_msg);ret = -1;} else {ret = 0;}return ret;}
/* 更新多条数据----事务法*/int sqlite3_Update_multidata(sqlite3* db, const char* table_name, char* *buff, int len){int rc = 0；int ret = -1;int i = 0;char sql_str[128] = {0};char* err_msg = NULL;rc = sqlite3_exec(db, "BEGIN;", 0, 0, 0);if (rc != SQLITE_OK) {return ret;}while (buff[i] != NULL && i < len) {memset(sql_str, 0, sizeof(sql_str));sprintf(sql_str, "UPDATE %s SET RealDate = DATETIME('now', 'localtime') WHERE Data = '%s'", table_name, buff[i]);sqlite3_busy_timeout(db, 30*1000);rc = sqlite3_exec(db, sql_str, 0, 0, &err_msg);if (rc != SQLITE_OK) {printf("Update No%d data error: %s", i + 1, err_msg);sqlite3_free(err_msg);break;} else {i++;}}if (i < len) {sqlite3_exec(db, "ROLLBACK;", 0, 0, 0);ret = -1;} if (i == len) {sqlite3_exec(db, "COMMIT;", 0, 0, 0);ret = 0;}return ret;}

6. 删除数据

与添加和更新逻辑一样，参考上。

7. 查询数据

/* 回调函数, 返回单次执行结果*/int exec_handle(void *data, int argc, char** argv, char **colname){/* 计数器*/int i = *(int *)(data);*(int *)(data) = i + 1;/* 取出结果*/printf("NO.%d message: [%s] is [%s], [%s] is [%s]...", *(int*)(data), colname[0], colname[1], argv[0], argv[1]);return 0;}/* exec查找完成无论是否有结果返回的都是SQLITE_OK, 所以判断查询结果得用计数器判断*/int sqlite3_select_exec (sqlite *db, char *table_name){char sql[MAX_INPUT] = {0};char *err_msg = NULL;int data = 0;int ret;sprintf(sql, "select * from %s Where ID > 10", table_name);sqlite3_busy_timeout(db, 30*1000);ret = sqlite3_exec(db, sql, exec_handle, &data, &err_msg);if (ret != SQLITE_OK) {/* 查询失败*/printf("exec error is:%s\n", err_msg);return -1;} else {if (data == 0) {/* 查询成功，无查询结果*/return 0;} else {/* 查询成功，有查询结果*/return 1;}}}
/* 执行sql, 将结果集的某一字段进行存储*/int sqlite3_select_table(sqlite3* db, char* table_name){int ret = 0;char sql_str[MAX_INPUT] = {0};char **res;char **data;int* Idata;int row, col;char* err_msg = NULL;int i, j;/* 执行sql, 判断执行结果*/sprintf(sql_str, "select * from %s", table_name);sqlite3_busy_timeout(db, 30*1000);ret = sqlite3_get_table(db, sql_str, &res, &row, &col, &err_msg);if (ret != SQLITE_OK) {printf("error msg is %s", err_msg);sqlite3_close_database(db);sqlite3_free(err_msg);return -1;} else if (row == 0 || col == 0){sqlite3_close_database(db);return 0;} /* 初始化动态数组*/Idata = (int*)malloc(sizeof(int) * (row + 1));data = (char **)malloc(sizeof(char *) * (row + 1));if (data == NULL) {/* fails to malloc */sqlite3_close_database(db);return -1;} else {for (i = 0; i <= row; i++) {data[i] = (char *)malloc(1024);if (data[i] == NULL) {/* fails to malloc*/sqlite3_close_database(db);return -1;} else {memset(data[i], 0, 1024);}}}/* 接收返回数据*/for (j = 1; j <= row; j++) {/* 接收字符数据*/strcpy(data[j], res[col * (row + 1) - 1]);/* 接收整型数据*/Idata[j] = atoi(res[col * (row + 1) - 1]);}/* 释放空间*/sqlite3_free_table(res);for (i = 0; i<= row; i++) {memset(data[i], 0, 1024);if (data[i] != NULL) {free(data[i]);}}free(data);free(Idata);sqlite3_close_database(db);return 1;}
int sqlite3_select_prepare(sqlite3* db){int ret, rc, i, j;int send_flag = 0;int record_num;char package_id[16] = {0};int msg_len;char db_name[32] = "/data/device_property";char db_table[32] = "device";char sql_str[128] = {0};char* err_msg = NULL;char* *res_data;struct deviceNumber* *device;sqlite3* db;sqlite3_stmt *stmt;record_num = sqlite3_get_record_num(db_name, db_table);if (record_num == 0) {return -1;}db = sqlite3_open_database(db_name);if (db == NULL) {return -1;}memset(sql_str, 0, sizeof(sql_str));sprintf(sql_str, "select * from %s", db_table);rc = sqlite3_prepare_v2(db, sql_str, sizeof(sql_str), &stmt, NULL);if (rc != SQLITE_OK) {DEBUG_INFO("[read sqlite3 device] Select error");sqlite3_close_database(db);return -1;}/* 申请内存*//* 字符串数组*/res_data = (char**)malloc(sizeof(char*) * record_num);if (res_data == NULL) {DEBUG_INFO("[read sqlite3 device] malloc error");sqlite3_close_database(db);return -1;}for (i = 0; i < record_num; i++) {res_data[i] = (char*)malloc(MAX_DEVICE_PROPERTY_SIZE);if (res_data[i] == NULL) {sqlite3_close_database(db);DEBUG_INFO("[read sqlite3 device] malloc2 error");return -1;}}/* 结构体内存*/device = (struct deviceNumber**)malloc(sizeof(struct deviceNumber) * record_num);if (device == NULL) {DEBUG_INFO("[read sqlite3 device malloc error]");sqlite3_close_database(db);return -1;}for (i = 0; i product_key, sqlite3_column_text(stmt,3));strcpy(device[j]->device_number, sqlite3_column_text(stmt,4));strcpy(device[j]->device_secret, sqlite3_column_text(stmt,5));device[j]->reporting_center = sqlite3_column_int(stmt, 1);if (res_data[j] != NULL) {//printf("%s\n", res_data[j]);} else {printf("get history text error\n");}j ++;}/* 删除数据*/if (record_num > 0 && send_flag > 0) {ret = sqlite3_delete_device_property(db_table, record_num);if (ret < 0) {DEBUG_INFO("sqlite3_delete device property error");}}/* 释放内存*/for (i = 0; i < record_num; i++) {if (res_data[i] != NULL) {free(res_data[i]);}if (device[i] != NULL) {free(device[i]);}}if (res_data != NULL) {free(res_data);}if (device != NULL) {free(device);}sqlite3_finalize(stmt);sqlite3_close_database(db);return 0;}

8. 管理数据库容量

/* 按照容量进行管理*/void sqlite3_check_database(char* db_name, char* table_name, int size){int ret;int rc;int record_num;struct stat fstat;char sql_str[128] = {0};char* err_msg = NULL;sqlite3* db;ret = stat(db_name, &fstat);if (ret == 0) { /*数据库文件存在 */if (fstat.st_size >= size)/* st_size 单位为byte*/{record_num = sqlite3_get_record_num(db_name, table_name);db = sqlite3_open_database(db_name);if (db != NULL && record_num > 0) {memset(sql_str, 0, sizeof(sql_str));sprintf(sql_str, "DELETE FROM realtime WHERE ID in(SELECT ID FROM realtime LIMIT %d);", record_num*0.4);sqlite3_busy_timeout(db, 30*1000);rc = sqlite3_exec(db, sql_str, &err_msg);if (rc != SQLITE_OK) {printf("Delete error :%s", err_msg);sqlite3_free(err_msg);}sqlite3_busy_timeout(db, 50*1000);rc = sqlite3_exec(db, "Vacuum", 0, 0, &err_msg);if (rc != SQLITE_OK) {printf("error msg is %s", err_msg);sqlite3_free(err_msg);}sqlite3_close_database(db);}} else {/* 未达到清理容量*/}}}

总结于：

https://sqlite.org/cli.html

https://blog.csdn.net/aohun0743/article/details/101702277