SQLite核心API操作控制详解
2021-01-154.1k 阅读
SQLite 概述
SQLite 是一款轻型的数据库，它是遵守 ACID 的关系型数据库管理系统，它的设计目标是嵌入式的，而且目前已经在很多嵌入式产品中使用了它，它占用资源非常的低，在嵌入式设备中，可能只需要几百K 的内存就够了。它能够支持 Windows/Linux/Unix 等等主流的操作系统，同时能够跟很多程序语言相结合，比如 Tcl、C#、PHP、Java 等，还有ODBC 接口，同样比起 Mysql、PostgreSQL 这两款开源的世界著名数据库管理系统来讲，它的处理速度比他们都快。
SQLite 第一个Alpha版本诞生于2000年5月。 至今已经有20多年，SQLite也迎来了多个版本的更新。SQLite 是一款零配置的数据库，这意味着与其他数据库不同，SQLite 不需要独立的服务器进程或操作的系统（OS）。SQLite 直接访问其数据库文件。
SQLite C API 核心函数
SQLite 提供了丰富的 C 语言 API 来操作数据库，这些 API 构成了与 SQLite 数据库交互的基础。下面详细介绍一些核心 API 函数。
sqlite3_open 函数
sqlite3_open 函数用于打开一个 SQLite 数据库。如果数据库文件不存在，该函数会尝试创建一个新的数据库文件。
#include <sqlite3.h>
int main() {
sqlite3 *db;
const char *data_source = "test.db";
int rc = sqlite3_open(data_source, &db);
if(rc) {
fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
return(0);
} else {
fprintf(stdout, "Opened database successfully\n");
}
sqlite3_close(db);
return 0;
}
在上述代码中，通过 sqlite3_open 尝试打开名为 test.db 的数据库文件。如果打开失败，通过 sqlite3_errmsg 获取错误信息并打印。成功打开数据库后，后续可对数据库进行操作，操作完成后使用 sqlite3_close 关闭数据库。
sqlite3_exec 函数
sqlite3_exec 函数用于执行 SQL 语句，比如创建表、插入数据、查询数据等。
#include <sqlite3.h>
#include <stdio.h>
static int callback(void *data, int argc, char **argv, char **azColName) {
int i;
fprintf(stdout, "%s: ", (const char*)data);
for(i = 0; i < argc; i++) {
printf("%s = %s\t", azColName[i], argv[i]? argv[i] : "NULL");
}
printf("\n");
return 0;
}
int main() {
sqlite3 *db;
char *zErrMsg = 0;
int rc;
const char *data = "Callback function called";
const char *sql;
const char *data_source = "test.db";
rc = sqlite3_open(data_source, &db);
if(rc) {
fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
return(0);
} else {
fprintf(stdout, "Opened database successfully\n");
}
sql = "CREATE TABLE COMPANY(" \
"ID INT PRIMARY KEY NOT NULL," \
"NAME TEXT NOT NULL," \
"AGE INT NOT NULL," \
"ADDRESS CHAR(50)," \
"SALARY REAL );";
rc = sqlite3_exec(db, sql, callback, (void*)data, &zErrMsg);
if(rc != SQLITE_OK) {
fprintf(stderr, "SQL error: %s\n", zErrMsg);
sqlite3_free(zErrMsg);
} else {
fprintf(stdout, "Table created successfully\n");
}
sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " \
"VALUES (1, 'Paul', 32, 'California', 20000.00 ); " \
"INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " \
"VALUES (2, 'Allen', 25, 'Texas', 15000.00 ); " \
"INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " \
"VALUES (3, 'Teddy', 23, 'Norway', 20000.00 ); " \
"INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " \
"VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );";
rc = sqlite3_exec(db, sql, callback, (void*)data, &zErrMsg);
if(rc != SQLITE_OK) {
fprintf(stderr, "SQL error: %s\n", zErrMsg);
sqlite3_free(zErrMsg);
} else {
fprintf(stdout, "Records created successfully\n");
}
sql = "SELECT * from COMPANY";
rc = sqlite3_exec(db, sql, callback, (void*)data, &zErrMsg);
if(rc != SQLITE_OK) {
fprintf(stderr, "SQL error: %s\n", zErrMsg);
sqlite3_free(zErrMsg);
} else {
fprintf(stdout, "Operation done successfully\n");
}
sqlite3_close(db);
return 0;
}
上述代码展示了 sqlite3_exec 的多种用途。首先通过它创建了 COMPANY 表，然后插入多条记录，最后查询并打印表中的所有数据。sqlite3_exec 的第三个参数为回调函数指针，在查询操作时，回调函数会被多次调用，每次处理一行查询结果。
sqlite3_prepare_v2 函数
sqlite3_prepare_v2 函数用于准备一个 SQL 语句，以便后续执行。它会解析 SQL 语句，生成一个可执行的对象，适用于需要多次执行同一 SQL 模板但参数不同的场景。
#include <sqlite3.h>
#include <stdio.h>
int main() {
sqlite3 *db;
sqlite3_stmt *stmt;
const char *data_source = "test.db";
const char *sql = "INSERT INTO COMPANY (ID, NAME, AGE, ADDRESS, SALARY) VALUES (?,?,?,?,?)";
int rc = sqlite3_open(data_source, &db);
if(rc) {
fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
return(0);
}
rc = sqlite3_prepare_v2(db, sql, -1, &stmt, 0);
if(rc != SQLITE_OK) {
fprintf(stderr, "Failed to prepare statement: %s\n", sqlite3_errmsg(db));
sqlite3_close(db);
return(0);
}
sqlite3_bind_int(stmt, 1, 5);
sqlite3_bind_text(stmt, 2, "David", -1, SQLITE_STATIC);
sqlite3_bind_int(stmt, 3, 27);
sqlite3_bind_text(stmt, 4, "Florida", -1, SQLITE_STATIC);
sqlite3_bind_double(stmt, 5, 35000.0);
rc = sqlite3_step(stmt);
if(rc != SQLITE_DONE) {
fprintf(stderr, "Failed to execute statement: %s\n", sqlite3_errmsg(db));
}
sqlite3_finalize(stmt);
sqlite3_close(db);
return 0;
}
在此代码中，首先通过 sqlite3_prepare_v2 准备了一条插入数据的 SQL 语句。然后使用 sqlite3_bind_* 系列函数为 SQL 语句中的占位符绑定具体的值。最后通过 sqlite3_step 执行该语句。执行完毕后，使用 sqlite3_finalize 释放 sqlite3_stmt 对象占用的资源。
sqlite3_column_* 函数
当使用 sqlite3_prepare_v2 和 sqlite3_step 执行查询语句后，sqlite3_column_* 函数用于获取查询结果集中当前行的数据。
#include <sqlite3.h>
#include <stdio.h>
int main() {
sqlite3 *db;
sqlite3_stmt *stmt;
const char *data_source = "test.db";
const char *sql = "SELECT * FROM COMPANY WHERE AGE >?";
int rc = sqlite3_open(data_source, &db);
if(rc) {
fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
return(0);
}
rc = sqlite3_prepare_v2(db, sql, -1, &stmt, 0);
if(rc != SQLITE_OK) {
fprintf(stderr, "Failed to prepare statement: %s\n", sqlite3_errmsg(db));
sqlite3_close(db);
return(0);
}
sqlite3_bind_int(stmt, 1, 25);
while((rc = sqlite3_step(stmt)) == SQLITE_ROW) {
int id = sqlite3_column_int(stmt, 0);
const char *name = (const char*)sqlite3_column_text(stmt, 1);
int age = sqlite3_column_int(stmt, 2);
const char *address = (const char*)sqlite3_column_text(stmt, 3);
double salary = sqlite3_column_double(stmt, 4);
printf("ID: %d, Name: %s, Age: %d, Address: %s, Salary: %.2f\n", id, name, age, address, salary);
}
if(rc != SQLITE_DONE) {
fprintf(stderr, "Failed to execute statement: %s\n", sqlite3_errmsg(db));
}
sqlite3_finalize(stmt);
sqlite3_close(db);
return 0;
}
在这个示例中，查询 AGE 大于 25 的记录。在 sqlite3_step 返回 SQLITE_ROW 时，通过 sqlite3_column_* 函数获取每一列的数据并打印。
事务控制 API
在 SQLite 中，事务是确保数据一致性和完整性的重要机制。SQLite 提供了相应的 API 来控制事务。
显式事务控制
#include <sqlite3.h>
#include <stdio.h>
int main() {
sqlite3 *db;
sqlite3_stmt *stmt;
const char *data_source = "test.db";
int rc = sqlite3_open(data_source, &db);
if(rc) {
fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
return(0);
}
const char *begin_transaction = "BEGIN TRANSACTION";
const char *insert_sql = "INSERT INTO COMPANY (ID, NAME, AGE, ADDRESS, SALARY) VALUES (?,?,?,?,?)";
const char *commit_transaction = "COMMIT";
rc = sqlite3_exec(db, begin_transaction, 0, 0, 0);
if(rc != SQLITE_OK) {
fprintf(stderr, "Failed to begin transaction: %s\n", sqlite3_errmsg(db));
sqlite3_close(db);
return(0);
}
rc = sqlite3_prepare_v2(db, insert_sql, -1, &stmt, 0);
if(rc != SQLITE_OK) {
fprintf(stderr, "Failed to prepare statement: %s\n", sqlite3_errmsg(db));
sqlite3_exec(db, "ROLLBACK", 0, 0, 0);
sqlite3_close(db);
return(0);
}
sqlite3_bind_int(stmt, 1, 6);
sqlite3_bind_text(stmt, 2, "Tom", -1, SQLITE_STATIC);
sqlite3_bind_int(stmt, 3, 29);
sqlite3_bind_text(stmt, 4, "New York", -1, SQLITE_STATIC);
sqlite3_bind_double(stmt, 5, 40000.0);
rc = sqlite3_step(stmt);
if(rc != SQLITE_DONE) {
fprintf(stderr, "Failed to execute insert: %s\n", sqlite3_errmsg(db));
sqlite3_exec(db, "ROLLBACK", 0, 0, 0);
} else {
rc = sqlite3_exec(db, commit_transaction, 0, 0, 0);
if(rc != SQLITE_OK) {
fprintf(stderr, "Failed to commit transaction: %s\n", sqlite3_errmsg(db));
}
}
sqlite3_finalize(stmt);
sqlite3_close(db);
return 0;
}
在上述代码中，首先通过 sqlite3_exec 执行 BEGIN TRANSACTION 语句开始一个事务。然后准备并执行插入语句，如果插入成功则执行 COMMIT 提交事务，否则执行 ROLLBACK 回滚事务。
隐式事务
SQLite 也支持隐式事务。默认情况下，每条 SQL 语句本身就是一个事务。例如，使用 sqlite3_exec 执行一条插入语句时，该插入操作是原子的，如果成功则提交，失败则回滚。
#include <sqlite3.h>
#include <stdio.h>
int main() {
sqlite3 *db;
char *zErrMsg = 0;
int rc;
const char *sql;
const char *data_source = "test.db";
rc = sqlite3_open(data_source, &db);
if(rc) {
fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
return(0);
}
sql = "INSERT INTO COMPANY (ID, NAME, AGE, ADDRESS, SALARY) VALUES (7, 'Jerry', 30, 'Los Angeles', 45000.0)";
rc = sqlite3_exec(db, sql, 0, 0, &zErrMsg);
if(rc != SQLITE_OK) {
fprintf(stderr, "SQL error: %s\n", zErrMsg);
sqlite3_free(zErrMsg);
} else {
fprintf(stdout, "Record inserted successfully\n");
}
sqlite3_close(db);
return 0;
}
在此示例中，插入操作是隐式事务，若执行成功则数据插入数据库，若失败则不会对数据库造成影响。
错误处理
在使用 SQLite API 时，错误处理至关重要。SQLite 的每个 API 函数在执行失败时都会返回一个错误码。可以通过 sqlite3_errmsg 函数获取详细的错误信息。
#include <sqlite3.h>
#include <stdio.h>
int main() {
sqlite3 *db;
int rc = sqlite3_open("nonexistent.db", &db);
if(rc) {
const char *errmsg = sqlite3_errmsg(db);
fprintf(stderr, "Failed to open database: %s\n", errmsg);
sqlite3_close(db);
return(0);
}
sqlite3_close(db);
return 0;
}
在上述代码中，尝试打开一个不存在的数据库文件，若打开失败，通过 sqlite3_errmsg 获取错误信息并打印。
内存管理
SQLite API 在使用过程中涉及到一些内存管理问题。例如，sqlite3_exec 函数在执行 SQL 语句出错时，会通过 zErrMsg 参数返回错误信息，调用者需要使用 sqlite3_free 释放该内存。
#include <sqlite3.h>
#include <stdio.h>
int main() {
sqlite3 *db;
char *zErrMsg = 0;
int rc;
const char *sql;
const char *data_source = "test.db";
rc = sqlite3_open(data_source, &db);
if(rc) {
fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
return(0);
}
sql = "CREATE TABLE INVALID_TABLE(" \
"ID INT PRIMARY KEY NOT NULL," \
"NAME TEXT NOT NULL," \
"AGE INT NOT NULL," \
"ADDRESS CHAR(50)," \
"SALARY REAL );";
rc = sqlite3_exec(db, sql, 0, 0, &zErrMsg);
if(rc != SQLITE_OK) {
fprintf(stderr, "SQL error: %s\n", zErrMsg);
sqlite3_free(zErrMsg);
}
sqlite3_close(db);
return 0;
}
在这个例子中，若创建表的 SQL 语句执行出错，sqlite3_exec 会将错误信息赋值给 zErrMsg，程序通过 sqlite3_free 释放该内存。
另外，对于 sqlite3_prepare_v2 创建的 sqlite3_stmt 对象，在使用完毕后，需要调用 sqlite3_finalize 释放相关资源，避免内存泄漏。
多线程与 SQLite
SQLite 支持多线程访问，但需要注意一些事项。SQLite 库本身是线程安全的，但在多线程环境下使用时，需要确保正确地管理数据库连接和事务。
一种常见的做法是为每个线程分配独立的数据库连接。这样可以避免不同线程之间对同一连接的竞争。
#include <pthread.h>
#include <sqlite3.h>
#include <stdio.h>
void* thread_function(void* arg) {
sqlite3 *db;
int rc = sqlite3_open("test.db", &db);
if(rc) {
fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
return NULL;
}
const char *sql = "INSERT INTO COMPANY (ID, NAME, AGE, ADDRESS, SALARY) VALUES (?,?,?,?,?)";
sqlite3_stmt *stmt;
rc = sqlite3_prepare_v2(db, sql, -1, &stmt, 0);
if(rc != SQLITE_OK) {
fprintf(stderr, "Failed to prepare statement: %s\n", sqlite3_errmsg(db));
sqlite3_close(db);
return NULL;
}
// 假设这里通过参数获取不同的插入值
int id = *((int*)arg);
sqlite3_bind_int(stmt, 1, id);
sqlite3_bind_text(stmt, 2, "Thread Insert", -1, SQLITE_STATIC);
sqlite3_bind_int(stmt, 3, 28);
sqlite3_bind_text(stmt, 4, "Thread Location", -1, SQLITE_STATIC);
sqlite3_bind_double(stmt, 5, 30000.0);
rc = sqlite3_step(stmt);
if(rc != SQLITE_DONE) {
fprintf(stderr, "Failed to execute insert: %s\n", sqlite3_errmsg(db));
}
sqlite3_finalize(stmt);
sqlite3_close(db);
return NULL;
}
int main() {
pthread_t threads[5];
int ids[5] = {8, 9, 10, 11, 12};
for(int i = 0; i < 5; i++) {
pthread_create(&threads[i], NULL, thread_function, &ids[i]);
}
for(int i = 0; i < 5; i++) {
pthread_join(threads[i], NULL);
}
return 0;
}
在上述代码中，每个线程都独立打开数据库连接，准备并执行插入语句。这样可以有效避免多线程环境下对数据库连接的竞争问题。
性能优化
在使用 SQLite 时，为了获得更好的性能，可以采取以下一些优化措施。
批量操作
尽量减少数据库的 I/O 操作，例如将多次插入操作合并为一次批量插入。
#include <sqlite3.h>
#include <stdio.h>
int main() {
sqlite3 *db;
sqlite3_stmt *stmt;
const char *data_source = "test.db";
int rc = sqlite3_open(data_source, &db);
if(rc) {
fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
return(0);
}
const char *sql = "INSERT INTO COMPANY (ID, NAME, AGE, ADDRESS, SALARY) VALUES (?,?,?,?,?)";
rc = sqlite3_prepare_v2(db, sql, -1, &stmt, 0);
if(rc != SQLITE_OK) {
fprintf(stderr, "Failed to prepare statement: %s\n", sqlite3_errmsg(db));
sqlite3_close(db);
return(0);
}
int ids[] = {13, 14, 15};
const char *names[] = {"Alice", "Bob", "Charlie"};
int ages[] = {26, 27, 28};
const char *addresses[] = {"City1", "City2", "City3"};
double salaries[] = {32000.0, 33000.0, 34000.0};
for(int i = 0; i < 3; i++) {
sqlite3_bind_int(stmt, 1, ids[i]);
sqlite3_bind_text(stmt, 2, names[i], -1, SQLITE_STATIC);
sqlite3_bind_int(stmt, 3, ages[i]);
sqlite3_bind_text(stmt, 4, addresses[i], -1, SQLITE_STATIC);
sqlite3_bind_double(stmt, 5, salaries[i]);
rc = sqlite3_step(stmt);
if(rc != SQLITE_DONE) {
fprintf(stderr, "Failed to execute insert: %s\n", sqlite3_errmsg(db));
}
sqlite3_reset(stmt);
}
sqlite3_finalize(stmt);
sqlite3_close(db);
return 0;
}
在此示例中，通过循环为同一条插入语句的占位符绑定不同的值，然后执行插入操作，而不是每次插入都准备和执行新的 SQL 语句，从而减少了数据库的 I/O 开销。
索引优化
合理创建索引可以显著提高查询性能。例如，对于经常用于 WHERE 子句的列创建索引。
#include <sqlite3.h>
#include <stdio.h>
int main() {
sqlite3 *db;
char *zErrMsg = 0;
int rc;
const char *sql;
const char *data_source = "test.db";
rc = sqlite3_open(data_source, &db);
if(rc) {
fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
return(0);
}
sql = "CREATE INDEX idx_age ON COMPANY(AGE)";
rc = sqlite3_exec(db, sql, 0, 0, &zErrMsg);
if(rc != SQLITE_OK) {
fprintf(stderr, "SQL error: %s\n", zErrMsg);
sqlite3_free(zErrMsg);
} else {
fprintf(stdout, "Index created successfully\n");
}
sqlite3_close(db);
return 0;
}
上述代码为 COMPANY 表的 AGE 列创建了索引。当执行涉及 AGE 列的查询时，如 SELECT * FROM COMPANY WHERE AGE > 25，SQLite 可以利用该索引快速定位符合条件的数据，提高查询效率。
缓存机制
在应用程序层面实现简单的缓存机制，对于频繁查询且不经常变化的数据，直接从缓存中获取，避免重复查询数据库。
#include <sqlite3.h>
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#define CACHE_SIZE 10
typedef struct {
int id;
char name[50];
int age;
char address[50];
double salary;
} CompanyRecord;
CompanyRecord cache[CACHE_SIZE];
int cache_index = 0;
CompanyRecord* get_from_cache(int id) {
for(int i = 0; i < CACHE_SIZE; i++) {
if(cache[i].id == id) {
return &cache[i];
}
}
return NULL;
}
void add_to_cache(CompanyRecord record) {
cache[cache_index] = record;
cache_index = (cache_index + 1) % CACHE_SIZE;
}
int main() {
sqlite3 *db;
sqlite3_stmt *stmt;
const char *data_source = "test.db";
int rc = sqlite3_open(data_source, &db);
if(rc) {
fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
return(0);
}
const char *sql = "SELECT * FROM COMPANY WHERE ID =?";
rc = sqlite3_prepare_v2(db, sql, -1, &stmt, 0);
if(rc != SQLITE_OK) {
fprintf(stderr, "Failed to prepare statement: %s\n", sqlite3_errmsg(db));
sqlite3_close(db);
return(0);
}
int target_id = 1;
CompanyRecord *cached_record = get_from_cache(target_id);
if(cached_record) {
printf("Retrieved from cache: ID: %d, Name: %s, Age: %d, Address: %s, Salary: %.2f\n",
cached_record->id, cached_record->name, cached_record->age, cached_record->address, cached_record->salary);
} else {
sqlite3_bind_int(stmt, 1, target_id);
rc = sqlite3_step(stmt);
if(rc == SQLITE_ROW) {
CompanyRecord record;
record.id = sqlite3_column_int(stmt, 0);
strcpy(record.name, (const char*)sqlite3_column_text(stmt, 1));
record.age = sqlite3_column_int(stmt, 2);
strcpy(record.address, (const char*)sqlite3_column_text(stmt, 3));
record.salary = sqlite3_column_double(stmt, 4);
add_to_cache(record);
printf("Retrieved from database: ID: %d, Name: %s, Age: %d, Address: %s, Salary: %.2f\n",
record.id, record.name, record.age, record.address, record.salary);
} else {
fprintf(stderr, "Failed to execute query: %s\n", sqlite3_errmsg(db));
}
}
sqlite3_finalize(stmt);
sqlite3_close(db);
return 0;
}
在此示例中，实现了一个简单的缓存机制。在查询数据时，首先尝试从缓存中获取，如果缓存中不存在则查询数据库，并将查询结果添加到缓存中。这样对于相同的查询可以直接从缓存获取数据，减少数据库查询次数，提高性能。
通过对 SQLite 核心 API 的深入理解和合理运用，结合事务控制、错误处理、内存管理、多线程处理以及性能优化等方面的知识，开发者可以在各种应用场景中高效地使用 SQLite 数据库，构建稳定、高性能的应用程序。无论是嵌入式设备开发，还是桌面应用、移动应用等领域，SQLite 都凭借其轻量级、零配置等特性，成为众多开发者的首选数据库之一。