SQLite3 C API实战：INSERT与SELECT读写数据完整指南

SQLite3学习笔记5INSERT写 SELECT读数据C API)一直用命令行敲SQLite3的SQL语句总觉得不过瘾。这周把C API的读写流程完整跑了一遍从裸的sqlite3_exec到参数绑定再到事务批量提交踩了几个坑也绕了几个弯。这篇笔记就把INSERT和SELECT这两条最基础的路径从头到尾拆开每一步都配上代码和运行结果给正准备用C操作SQLite3的朋友做个参考。先说清楚这篇能解决什么问题看完之后你应该能手写一个完整的C程序把结构化数据写进SQLite3文件再把它读出来处理。覆盖的场景包括单条写入、批量写入、条件查询、结果集遍历以及几个容易踩的坑——比如中文编码、SQL注入、stmt忘记释放这类问题。1. 整体设计思路为什么从C API入手SQLite3的C API是它的原生接口其他语言绑定Python的sqlite3、Node的better-sqlite3底层都是同一套。把这层吃透至少有三个好处一是性能可控。C这边没有解释器层也没有ORM的对象映射开销对于批量写入和高频点查能精确控制每一条指令的执行路径。二是嵌入式场景离不开它。很多跑在Linux工控板、路由器、网关上的程序数据存储就靠一个SQLite3文件如果只会调Python接口到那些只有C交叉编译工具链的环境就抓瞎了。三是理解底层逻辑。比如参数绑定为什么比字符串拼接安全事务为什么能大幅提升批量写入速度搞清楚这些原理再回头用其他语言基本不用看文档也能猜个八九不离十。1.1 核心技术选型这次笔记围绕三个核心API展开sqlite3_exec、sqlite3_prepare_v2和sqlite3_step。sqlite3_exec适合执行没有返回结果的语句比如建表、删除、更新。sqlite3_prepare_v2则用于处理需要返回数据的语句它会把SQL文本编译成字节码VDBE指令之后用sqlite3_step逐步执行并取出行数据。我实际测试下来养成一个习惯很重要凡是涉及外部输入条件的SQL一律走prepare bind绝不用字符串拼接。后面会专门讲原因这里先记住结论。1.2 开发环境说明我这次是在Ubuntu 22.04上做的测试编译器gcc 11.4SQLite3版本3.37.2。系统里没有自带开发库的话需要先安装sudo apt-get install libsqlite3-dev编译的时候记得加链接参数gcc -o sqlite_demo sqlite_demo.c -lsqlite3注意链接库的位置在系统/usr/lib/x86_64-linux-gnu/下如果编译报cannot find -lsqlite3先执行sudo ldconfig刷新库缓存再检查/usr/include下有没有sqlite3.h头文件。2. 数据库连接与基础环境初始化2.1 打开和关闭数据库第一步永远是打开数据库。SQLite3提供两个函数sqlite3_open和sqlite3_open_v2。前者参数少适合快速测试后者能指定打开标志比如只读、创建等等更精细。#include stdio.h #include stdlib.h #include sqlite3.h int main(void) { sqlite3 *db NULL; int rc sqlite3_open(test.db, db); if (rc ! SQLITE_OK) { fprintf(stderr, 无法打开数据库: %s\n, sqlite3_errmsg(db)); return 1; } printf(数据库打开成功\n); // ... 后续操作 sqlite3_close(db); return 0; }这段代码的执行逻辑很简单sqlite3_open如果发现test.db文件不存在会在当前目录创建一个新文件。打开成功后db指针指向一个连接对象后续所有操作都通过这个指针进行。这里有一个容易忽视的细节sqlite3_open的第二个参数是sqlite3 **很多初学者容易传错成sqlite3 *编译时候可能不报错但运行会段错误。检查一下自己的代码确保传的是指针的地址。2.2 创建表结构数据库是空的时候需要用sqlite3_exec创建表。这个函数接受一个SQL字符串执行完成后返回结果码const char *sql_create CREATE TABLE IF NOT EXISTS user ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, age INTEGER, score REAL DEFAULT 0.0 );; char *err_msg NULL; rc sqlite3_exec(db, sql_create, 0, 0, err_msg); if (rc ! SQLITE_OK) { fprintf(stderr, 建表失败: %s\n, err_msg); sqlite3_free(err_msg); return 1; } printf(表创建成功\n);IF NOT EXISTS一定要加否则第二次运行程序会报table user already exists。AUTOINCREMENT能让主键自动分配但是它有额外开销如果不需要严格按照“最大ID1”的方式来分配直接用INTEGER PRIMARY KEY就够底层是rowid的别名速度更快。表结构里面我特意加了TEXT、INTEGER、REAL三种类型后面读写样例会覆盖这三类数据的处理方式。3. INSERT写入三种境界3.1 直接用sqlite3_exec写单条记录最直观的方式就是拼SQL字符串然后用sqlite3_exec执行char sql[256]; snprintf(sql, sizeof(sql), INSERT INTO user (name, age, score) VALUES (%s, %d, %.2f);, 张三, 25, 88.5); rc sqlite3_exec(db, sql, 0, 0, err_msg); if (rc ! SQLITE_OK) { fprintf(stderr, 插入失败: %s\n, err_msg); sqlite3_free(err_msg); }这种方式确实能跑通但问题很大name字段如果包含单引号SQL会直接断裂报语法错误如果拼接的是用户输入的内容就是标准的SQL注入姿势每条记录都需要先分配缓冲区、格式化字符串效率低且容易出错所以我只用它来做初始化或者测试数据真正写业务数据坚决不用这个方式。如果你负责的项目里出现了类似这种字符串拼SQL的代码建议尽快改成下面的参数绑定方式。3.2 参数绑定正确且安全的写法sqlite3_prepare_v2sqlite3_bind_*sqlite3_step是官方推荐路径。先上完整代码sqlite3_stmt *stmt NULL; const char *sql_insert INSERT INTO user (name, age, score) VALUES (?, ?, ?);; rc sqlite3_prepare_v2(db, sql_insert, -1, stmt, NULL); if (rc ! SQLITE_OK) { fprintf(stderr, 准备语句失败: %s\n, sqlite3_errmsg(db)); return 1; } // 绑定参数注意索引从1开始不是0 sqlite3_bind_text(stmt, 1, 李四, -1, SQLITE_TRANSIENT); sqlite3_bind_int(stmt, 2, 30); sqlite3_bind_double(stmt, 3, 92.5); // 执行 rc sqlite3_step(stmt); if (rc ! SQLITE_DONE) { fprintf(stderr, 执行失败: %s\n, sqlite3_errmsg(db)); } else { printf(插入成功最后ID: %lld\n, sqlite3_last_insert_rowid(db)); } // 释放语句对象 sqlite3_finalize(stmt);这段代码有几个关键点问号占位符。SQL语句里的?就是参数位对应后续的bind顺序。也可以用?1、?2这种带编号的写法同一个参数可以在语句里引用多次适用更复杂的场景。bind索引从1开始。这是C API最容易踩的坑。sqlite3_bind_text(stmt, 1, ...)绑定的是第一个问号很多人习惯性从0开始结果就是第一个参数永远没绑上最后执行报错或者写入非法值。SQLITE_TRANSIENT的含义。这个宏告诉SQLite3“我的字符串缓冲区可能会变你内部复制一份。”如果传SQLITE_STATICSQLite3会认为这个指针指向的内存会一直有效不会复制这会导致悬空指针问题。除非你的字符串确实是全局的生命周期否则都用SQLITE_TRANSIENT。sqlite3_last_insert_rowid确保拿到自增主键。在多线程场景下这个函数返回的是当前连接的插入操作的rowid不要误以为它是全局的。如果用连接池拿到的ID可能不是你期望的那条务必确认当前执行线程用的是同一个连接对象。3.3 批量写入与事务控场制如果你要一口气插入几千条数据逐条提交会慢到怀疑人生。原因很简单每条INSERT在默认模式下都是一个独立事务涉及一次磁盘fsync。解决思路是手动控制事务边界批量插入前BEGIN全部插完COMMIT中间出错ROLLBACK。sqlite3_exec(db, BEGIN TRANSACTION;, 0, 0, 0); sqlite3_stmt *stmt NULL; sqlite3_prepare_v2(db, INSERT INTO user (name, age, score) VALUES (?, ?, ?);, -1, stmt, NULL); for (int i 0; i 10000; i) { char name[32]; snprintf(name, sizeof(name), user_%d, i); sqlite3_bind_text(stmt, 1, name, -1, SQLITE_TRANSIENT); sqlite3_bind_int(stmt, 2, 20 (i % 20)); sqlite3_bind_double(stmt, 3, 60.0 i * 0.1); rc sqlite3_step(stmt); if (rc ! SQLITE_DONE) { fprintf(stderr, 第%d条插入失败: %s\n, i, sqlite3_errmsg(db)); sqlite3_finalize(stmt); sqlite3_exec(db, ROLLBACK;, 0, 0, 0); return 1; } // 重置语句以便重新绑定 sqlite3_reset(stmt); } sqlite3_finalize(stmt); sqlite3_exec(db, COMMIT;, 0, 0, 0);我自己用10000条数据做了简单测试方式耗时逐条提交约 850ms事务内批量提交约 180ms性能差距接近5倍。注意sqlite3_reset和sqlite3_clear_bindings的区别reset让语句状态回到初始位置可以重新执行但它不会清除之前绑定的值。如果循环里每次都重新调用bind系列函数旧值会被覆盖没问题如果某次循环少绑了一个参数旧值会被复用这是个隐患。建议每次都全量绑定所有参数别偷懒。3.4 一个INSERT的完整封装实际工程里我会把INSERT封装成一个函数方便复用int insert_user(sqlite3 *db, const char *name, int age, double score, sqlite3_int64 *out_id) { const char *sql INSERT INTO user (name, age, score) VALUES (?, ?, ?);; sqlite3_stmt *stmt NULL; int rc sqlite3_prepare_v2(db, sql, -1, stmt, NULL); if (rc ! SQLITE_OK) { fprintf(stderr, prepare失败: %s\n, sqlite3_errmsg(db)); return rc; } sqlite3_bind_text(stmt, 1, name, -1, SQLITE_TRANSIENT); sqlite3_bind_int(stmt, 2, age); sqlite3_bind_double(stmt, 3, score); rc sqlite3_step(stmt); if (rc SQLITE_DONE out_id) { *out_id sqlite3_last_insert_rowid(db); } sqlite3_finalize(stmt); return rc; }封装的思路就是把prepare → bind → step → finalize的固定流程塞进函数调用方只管传参数拿返回值或rowid。这套模式适用于所有INSERT/UPDATE/DELETE建议照抄。4. SELECT读取从结果集里捞数据4.1 查询的基础流程SELECT比INSERT多了一个读取结果集的步骤。流程是sqlite3_prepare_v2准备SQLsqlite3_bind_*绑定查询条件如果有循环调用sqlite3_step直到返回SQLITE_DONE每次返回SQLITE_ROW时用sqlite3_column_*取出列值sqlite3_finalize释放语句直接上代码sqlite3_stmt *stmt NULL; const char *sql_query SELECT id, name, age, score FROM user WHERE age ? ORDER BY score DESC;; rc sqlite3_prepare_v2(db, sql_query, -1, stmt, NULL); if (rc ! SQLITE_OK) { fprintf(stderr, 查询准备失败: %s\n, sqlite3_errmsg(db)); return 1; } sqlite3_bind_int(stmt, 1, 20); printf(id\tname\tage\tscore\n); while ((rc sqlite3_step(stmt)) SQLITE_ROW) { int id sqlite3_column_int(stmt, 0); const unsigned char *name sqlite3_column_text(stmt, 1); int age sqlite3_column_int(stmt, 2); double score sqlite3_column_double(stmt, 3); printf(%d\t%s\t%d\t%.2f\n, id, name, age, score); } if (rc ! SQLITE_DONE) { fprintf(stderr, 查询执行异常: %s\n, sqlite3_errmsg(db)); } sqlite3_finalize(stmt);4.2 sqlite3_step的三个关键返回值这是新手最容易搞混的地方单独拿出来说返回值含义下一步动作SQLITE_ROW成功取到一行数据用column函数读取SQLITE_DONE数据全部取完循环结束SQLITE_BUSY数据库被其他连接锁住重试或等待SQLITE_ERRORSQL执行错误查看sqlite3_errmsg大多数查询循环的结构都是while (sqlite3_step(stmt) SQLITE_ROW)退出后判断是不是SQLITE_DONE如果不是就说明中途出了异常。有个细节我很早以前忽略过sqlite3_prepare_v2成功之后SQL语句编译成了VDBE字节码这期间如果数据库schema发生变更比如另一个线程执行了ALTER TABLEsqlite3_step会返回SQLITE_SCHEMA。新版SQLite3虽然会自动重新prepare但认识这个返回值能让你排查问题时少走弯路。4.3 按索引还是按列名取数据sqlite3_column_*系列函数第一眼看起来只能按索引取——就是SELECT语句里面列的顺序从0开始编号。但它也支持按列名取sqlite3_column_index(stmt, name)拿到索引再去取值。int name_col_idx sqlite3_column_index(stmt, name); const unsigned char *name sqlite3_column_text(stmt, name_col_idx);这种方式的好处是如果SELECT语句的列顺序调整了只要列名不变代码就不用改。代价是每次都要在列名和索引之间做一次字符串查找性能略低。我的建议是程序里固定查询语句的话用索引动态拼接查询条件的话用列名稳妥第一。4.4 处理NULL值与类型转换数据库里的字段可能是NULL。C API取NULL值的方式是sqlite3_column_type(stmt, col_idx)返回SQLITE_NULL然后做对应处理。for (int i 0; i sqlite3_column_count(stmt); i) { int col_type sqlite3_column_type(stmt, i); switch (col_type) { case SQLITE_INTEGER: printf(%lld, sqlite3_column_int64(stmt, i)); break; case SQLITE_FLOAT: printf(%f, sqlite3_column_double(stmt, i)); break; case SQLITE_TEXT: printf(%s, sqlite3_column_text(stmt, i)); break; case SQLITE_NULL: printf(NULL); break; default: printf(未知类型); } }还有一个实用技巧sqlite3_column_text返回的是unsigned char*直接用%s打印没问题但如果要做字符串操作比如snprintf、strcmp记得先强制转成const char*编译器才不会给你告警打扰。4.5 用sqlite3_exec 回调函数查询如果你的查询只需要一次性拿到全部结果也可以用sqlite3_exec配合回调函数。回调会在每一行数据返回时被调用int callback(void *data, int argc, char **argv, char **col_name) { for (int i 0; i argc; i) { printf(%s %s\n, col_name[i], argv[i] ? argv[i] : NULL); } printf(---\n); return 0; } char *err_msg NULL; rc sqlite3_exec(db, SELECT * FROM user;, callback, NULL, err_msg); if (rc ! SQLITE_OK) { fprintf(stderr, 查询错误: %s\n, err_msg); sqlite3_free(err_msg); }回调方案代码量少适合快速测试和临时脚本。缺点是状态不集中业务逻辑分散在回调里面代码复杂度上来以后不好维护。我一般只在命令行工具或者一次性数据检查的时候用它。5. 踩坑实录与性能优化5.1 编译/链接错误速查报错信息原因解决方案fatal error: sqlite3.h: No such file or directory开发库没装apt install libsqlite3-devundefined reference to sqlite3_open链接库没加编译加-lsqlite3database or disk is full磁盘空间不足检查分区可用空间attempt to write a readonly database文件权限不够chmod w test.dbunsupported file format数据库文件损坏或版本不兼容备份后用sqlite3恢复5.2 中文写入乱码有朋友遇到过用C API写入中文字符串然后命令行工具读出来就变成乱码。多半原因就是终端、程序的字符编码和数据库内部存储格式不匹配。SQLite3内部以UTF-8存储文本。如果你的C源文件是GBK编码运行时的const char*也是GBK写进去当然就乱了。解决办法源码文件统一存成UTF-8编译器加-finput-charsetUTF-8运行时确保字符串是UTF-8编码查询前可以执行PRAGMA encoding UTF-8;确认存储编码另外SQLite3 API本身不做编码转换传什么字节序列就存什么。中文这条坑本质是编码问题不是SQLite3的问题。5.3 每次SELECT都慢看看有没有索引没有索引的情况下WHERE age 20这类查询是全表扫描数据量上千就有感知了。建索引的方法CREATE INDEX idx_user_age ON user(age);C API里建索引和建表的写法一样用sqlite3_exec执行上面SQL就行。实测100万条数据不带索引查WHERE age 30大约耗时450ms建索引后降到1ms以内差距非常明显。但索引不是越多越好每个索引都会拖慢INSERT/UPDATE/DELETE的速度因为每次写入都要维护索引结构。读多写少的表就多建索引写频繁的表只给高频查询字段建。5.4 防止资源泄漏这是C程序写SQLite3最容易被忽视的问题。每次sqlite3_prepare_v2成功都会分配内存保存编译后的语句不调sqlite3_finalize就真的泄漏。循环里准备上千次而忘记释放内存蹭蹭涨。我给自己定了几条规矩prepare之后所有return路径上都要finalize如果函数里有提前return的逻辑先finalize再return每写一个函数都要数一下prepare和finalize是不是成对出现的可以用专门的工具比如Valgrind跑一遍检查内存泄漏valgrind --leak-checkfull ./sqlite_demo看到definitely lost: 0 bytes才算过关。5.5 关于线程与连接SQLite3默认编译模式下一个连接对象同一时间只能被一个线程使用。多线程要并发读写通常有两种做法每个线程独立打开连接SQLite3内部通过文件锁处理并发用sqlite3_config启用串行模式让底层直接接管线程安全第一种做法简单直观但要注意同一个数据库文件被多个连接同时写可能出现SQLITE_BUSY解决办法是设置busy_timeoutsqlite3_busy_timeout(db, 5000); // 5秒等待这比在代码里写while (rc SQLITE_BUSY)要优雅得多它由SQLite3内部阻塞等待锁释放后自动重试。6. 写在最后的实践心得把这套C API的读写流程完整跑过一遍之后我最大的体会是SQLite3的文档和API设计其实非常直接踩坑基本都集中在参数绑定、资源释放和编码这三个点上。参数绑定这件事养成“凡是外部输入一律走bind”的习惯之后真的可以减少一半以上的调试时间。字符串拼接SQL的写法第一次跑通很容易但等到出现引号、特殊字符、并发写入问题的时候返工代价远大于一开始就用standard API。资源释放方面C程序员其实都有肌肉记忆——malloc要和free配对open要和close配对。SQLite3用prepare和finalize也是一样的道理。只要每条return路径上都确认语句被finalize了内存问题基本能杜绝。编码问题属于“不遇到不重视遇到了就抓瞎”的类型。建议所有涉及中文的C项目从第一天起就统一UTF-8编码标准源文件、数据库、终端三个环节保持一致可以省掉后期大量排查乱码的时间。后续我准备把UPDATE、DELETE以及SQLite3的WAL模式、VACUUM维护命令也整理成笔记。每个主题争取都保持“先原理、再代码、后经验”的节奏遇到有价值的坑也会继续记下来。如果你用C写过SQLite3的项目欢迎交流各自遇到过的奇葩问题某些报错信息不看根本想不到还有这种用法。