sqlite 使用: 01-源码编译与使用

原创已于 2025-11-11 18:45:53 修改 · 284 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#sqlite #c++ #linux

于 2025-10-10 18:54:54 首次发布

sqlite 专栏收录该内容

3 篇文章

订阅专栏

本文是对SQLite C/C++ 接口学习使用的说明。

参考文档：

1.An Introduction To The SQLite C/C++ Interface
2.SQLite – C/C++ | 菜鸟教程
3.The SQLite C/C++ Interface 有了基础，当手册看

环境：

SQLite
Ubuntu 20.04
gcc 9.4.0

1.安装

到下载页面SQLite Download Page 进行下载源码包：

$ wget https://www.sqlite.org/2025/sqlite-autoconf-3500400.tar.gz

解压编译安装：

$ tar xzvf sqlite-autoconf-3500400.tar.gz
$ mkdir build
#若要安装到系统路径，使用如下配置
#$ ./configure --prefix=/usr/local
$ ./configure --prefix=build/
$ make
$ make install

查看编译结果,验证：

$ ll -h build/
total 0
drwxrwxrwx 1 guo guo 4.0K Oct 10 17:00 ./
drwxrwxrwx 1 guo guo 4.0K Oct 10 17:00 ../
drwxrwxrwx 1 guo guo 4.0K Oct 10 17:00 bin/
drwxrwxrwx 1 guo guo 4.0K Oct 10 17:00 include/
drwxrwxrwx 1 guo guo 4.0K Oct 10 17:00 lib/
drwxrwxrwx 1 guo guo 4.0K Oct 10 17:00 share/
$ cd build/bin/
$ ./sqlite3 
SQLite version 3.50.4 2025-07-30 19:33:53
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite>.exit  （回车退出）

sqlite3程序的使用可以参考这里： SQLite 命令，这里专注编程相关的内容。

2.使用

2.1 简单示例

先来个小例子试一下：

#include <iostream>
#include <string>
#include "sqlite3.h"

// 回调函数，用于处理查询结果
static int callback(void *data, int argc, char **argv, char **azColName)
{
    std::string *output = (std::string *)data;
    for (int i = 0; i < argc; i++)
    {
        *output += azColName[i];
        *output += " = ";
        *output += (argv[i] ? argv[i] : "NULL");
        *output += "\n";
    }
    return 0;
}

int main()
{
    sqlite3 *db;
    char *errMsg = 0;
    int rc;
    std::string result;

    // 打开数据库连接
    rc = sqlite3_open("test.db", &db);

    if (rc)
    {
        std::cerr << "无法打开数据库: " << sqlite3_errmsg(db) << std::endl;
        return 1;
    }
    else
    {
        std::cout << "成功打开数据库" << std::endl;
    }

    // 创建表
    const char *createTableSQL = "CREATE TABLE IF NOT EXISTS PERSON("
                                 "ID INTEGER PRIMARY KEY AUTOINCREMENT, "
                                 "NAME TEXT NOT NULL, "
                                 "AGE INT NOT NULL);";

    rc = sqlite3_exec(db, createTableSQL, 0, 0, &errMsg);

    if (rc != SQLITE_OK)
    {
        std::cerr << "SQL错误: " << errMsg << std::endl;
        sqlite3_free(errMsg);
    }
    else
    {
        std::cout << "表创建成功" << std::endl;
    }

    // 插入数据
    const char *insertSQL = "INSERT INTO PERSON (NAME, AGE) VALUES ('张三', 25);";
    rc = sqlite3_exec(db, insertSQL, 0, 0, &errMsg);

    if (rc != SQLITE_OK)
    {
        std::cerr << "插入错误: " << errMsg << std::endl;
        sqlite3_free(errMsg);
    }
    else
    {
        std::cout << "记录插入成功" << std::endl;
    }

    // 查询数据
    const char *selectSQL = "SELECT * FROM PERSON;";
    std::cout << "查询结果:" << std::endl;

    rc = sqlite3_exec(db, selectSQL, callback, &result, &errMsg);

    if (rc != SQLITE_OK)
    {
        std::cerr << "查询错误: " << errMsg << std::endl;
        sqlite3_free(errMsg);
    }
    else
    {
        std::cout << result << std::endl;
    }

    // 更新数据
    const char *updateSQL = "UPDATE PERSON SET AGE = 26 WHERE NAME = '张三';";
    rc = sqlite3_exec(db, updateSQL, 0, 0, &errMsg);

    if (rc != SQLITE_OK)
    {
        std::cerr << "更新错误: " << errMsg << std::endl;
        sqlite3_free(errMsg);
    }
    else
    {
        std::cout << "记录更新成功" << std::endl;
    }

    // 再次查询以验证更新
    result.clear();
    std::cout << "更新后的查询结果:" << std::endl;
    rc = sqlite3_exec(db, selectSQL, callback, &result, &errMsg);

    if (rc != SQLITE_OK)
    {
        std::cerr << "查询错误: " << errMsg << std::endl;
        sqlite3_free(errMsg);
    }
    else
    {
        std::cout << result << std::endl;
    }

    // 删除数据
    const char *deleteSQL = "DELETE FROM PERSON WHERE NAME = '张三';";
    rc = sqlite3_exec(db, deleteSQL, 0, 0, &errMsg);

    if (rc != SQLITE_OK)
    {
        std::cerr << "删除错误: " << errMsg << std::endl;
        sqlite3_free(errMsg);
    }
    else
    {
        std::cout << "记录删除成功" << std::endl;
    }

    // 关闭数据库连接
    sqlite3_close(db);
    std::cout << "数据库连接已关闭" << std::endl;

    return 0;
}

对应的执行结果：

$ ./sqlite_demo 
成功打开数据库
表创建成功
记录插入成功
查询结果:
ID = 2
NAME = 张三
AGE = 25

记录更新成功
更新后的查询结果:
ID = 2
NAME = 张三
AGE = 26

记录删除成功
数据库连接已关闭

配套的makefile

# SQLite C++ 示例程序 Makefile

# 编译器和标志
CXX = g++
CXXFLAGS = -std=c++11 -Wall -Wextra
INCLUDES = -I./sqlite-autoconf-3500400/build/include

# 目标可执行文件名
TARGET = sqlite_demo

# 源文件
SRCDIR = .
SRC = $(SRCDIR)/main.cpp

# SQLite 静态库路径
SQLITE_LIB = ./sqlite-autoconf-3500400/build/lib/libsqlite3.a
LIBS = -lpthread -ldl

# SQLite 动态库路径，需要通过rpath 设置库路径，并将库拷贝到对应路径下。或者拷贝到系统路径下
#LIBDIR = -L./sqlite-autoconf-3500400/build/lib
#LIBS = -lsqlite3 $(LIBS)

# 对象文件
OBJ = main.o

# 默认目标
all: $(TARGET)

# 链接对象文件生成可执行文件（使用静态链接）
$(TARGET): $(OBJ)
	$(CXX) $(CXXFLAGS) -o $@ $^ $(SQLITE_LIB) $(LIBS)

# 编译主程序
main.o: $(SRC)
	$(CXX) $(CXXFLAGS) $(INCLUDES) -c $< -o $@

# 清理生成的文件
clean:
	rm -f $(OBJ) $(TARGET)

# 重新构建
rebuild: clean all

# 运行程序
run: $(TARGET)
	./$(TARGET)

.PHONY: all clean rebuild run