尧图网站建设 
1. 先搞清楚 SQLite 的定位后面能少走一半弯路SQLite 这东西第一次真正落到我手上是一个给车间做的桌面统计工具。客户那边的电脑不让装服务、不让开端口、IT 部门连 SQL Server 都不批最后我把整个数据库压缩成一个后缀.db的文件丢进安装目录程序双击就能跑。从那以后凡是遇到单机、单用户、数据量不大、部署环境恶劣的需求我第一反应都是先掂量掂量 SQLite 够不够用。先把结论摆在前头SQLite 是一个嵌入式的关系型数据库引擎它没有服务器进程没有端口没有账号密码体系整个数据库就是一个或几个普通文件。你写的 SQL 由链接进程序的库直接解析执行读写的就是磁盘上的那个文件。它解决的典型问题是我需要 SQL 的查询能力但我不需要一个数据库服务。适合谁来学桌面软件开发者、小型工具/内部系统开发者、数据分析做临时加工的人、移动端和后端做本地缓存的工程师以及任何需要把结构化数据存进一个能随手拷贝的文件里的人。网上关于 SQLite 的资料有个很别扭的地方要么是官网那种干巴巴的文档翻译要么是几行CREATE TABLE就收尾的五分钟教程。真要上项目你会发现卡住你的从来不是语法而是这些问题——Windows 上到底装哪个包为什么INSERT完拿不到自增 ID.NET 4.8 的老项目该引哪个驱动为什么偶尔报database is locked为什么我用 DB Browser 打开好好的库程序一读就报错这篇就把这些实际问题从头到尾捋一遍语法部分只讲够用的重点放在那些文档里不写、但一定会踩的地方。1.1 一个文件就是一座库单 db 文件的本质很多人第一次看到 SQLite 的单文件模式会本能地怀疑一个文件怎么可能同时装下表结构、数据、索引、事务日志答案在于 SQLite 自己实现了一套完整的页式存储管理文件被切成固定大小的页page默认 4096 字节第一页叫页 1放着数据库头和一个叫sqlite_master新版本里也叫sqlite_schema的系统表你执行.tables看到的所有表名本质上就是从这张表里SELECT出来的。表的数据以 B 树的形式分散在若干页上索引是另一棵 B 树空闲页由 freelist 链起来复用。这个设计带来几个非常实际的好处。第一是可移植——把.db文件从 Windows 拷到 Linux、从电脑拷到手机模拟器格式完全一致字节序问题 SQLite 自己处理了。第二是可备份——停止写入后直接复制文件就是一份完整备份不需要mysqldump那一套。第三是事务是真的——SQLite 在默认的journal模式下会先写一个-journal回滚日志提交时删除开了 WAL 模式则写-wal和-shm两个附属文件这也是为什么你复制数据库的时候要确认没有别的连接在写。不过有个细节必须提前说清楚你看到的一个文件在运行期可能不止一个。开了 WAL 之后目录里会多出xxx.db-wal和xxx.db-shm如果只拷了主文件、正好又没做 checkpoint那拷走的副本可能缺最近的提交。想干净地拿单文件要么程序里执行一次PRAGMA wal_checkpoint(TRUNCATE)并断开所有连接要么直接用VACUUM INTO 备份.db生成一份新文件。1.2 它适合谁不适合谁我把 SQLite 的适用边界总结成一张表你对着自己的场景勾一下基本就有答案了场景特征建议理由单机桌面程序、工具软件存配置和业务数据首选零部署一个文件带着走手机 App / 客户端本地缓存首选系统内置无需额外依赖网站或服务的读多写少小型库QPS 几十级别可以用加 WAL busy_timeout够撑住数据分析的中间结果集加工推荐直接对 CSV/Parquet 导入后写 SQL比 pandas 写链式表达式清爽多台机器同时写同一个库文件放共享盘别用网络文件系统的锁语义不可靠极易损坏高并发写入、每秒上百次事务别用写操作全局串行只能有一个写者需要行级权限、账号体系、审计日志别用它压根没有用户概念权限就是文件系统权限单表上亿行 复杂分析查询谨慎没有并行查询大表全扫会很慢考虑 DuckDB 之类注意把.db文件放在 OneDrive、坚果云、企业网盘这类自动同步目录里是我见过最隐蔽的数据库损坏来源。同步客户端在文件句柄还没释放时就上传、下载覆盖产生的半截文件能让整个库打不开。要同步就用VACUUM INTO导出的快照去同步主库永远放在本地磁盘。2. Windows 上把 SQLite 跑起来命令行加图形化工具两条腿新手在这个环节最容易迷路因为 sqlite.org 下载页上一堆压缩包名字又长得差不多。我按先用命令行跑通、再配可视化工具的顺序讲这个顺序很重要——命令行能让你看清每一步到底发生了什么等出问题时你才知道是驱动的问题还是工具的问题。2.1 命令行版下载、解压、配环境变量与版本验证打开官网下载页Windows 下你会看到几类文件sqlite-tools-win-x64-3xxxxxx.zip命令行工具里面是sqlite3.exe、sqlite-dll-win-x64-3xxxxxx.zip动态库给 C/C 或某些嵌入式场景用、还有源码包sqlite-amrs-*.zip。做日常开发你只需要tools 那个包。具体步骤下载sqlite-tools-win-x64-*.zip解压到一个不带空格、不带中文的路径比如D:\tools\sqlite3\。路径带中文在某些老版本控制台里会出乱码没必要给自己找麻烦。按Win R输入sysdm.cpl进高级 → 环境变量在系统变量的Path里追加D:\tools\sqlite3。关掉所有已开的命令行窗口Path 是启动时读取的重新打开cmd执行sqlite3 -version正常的话会输出类似3.46.0 2024-05-23 ...的版本号。这一步如果提示不是内部或外部命令九成是 Path 没生效而不是文件没下对。建库并进入交互模式sqlite3 D:\data\demo.db注意这个文件不存在时 SQLite 会自动创建不会报错也不会提示你。这是很多人第一次用时的困惑来源我建了个空库怎么什么都没有因为一个空数据库本来就没有任何表。进到sqlite3提示符之后常用的点命令dot commands你得记住几个它们不是 SQL是命令行工具的语法.help # 查看所有点命令 .tables # 列出所有表 .schema user # 查看某张表的建表语句 .headers on # 查询结果带列名 .mode box # 用表格框线显示结果比默认的竖线好看 .timer on # 显示每条语句耗时 .read init.sql # 执行一个 sql 脚本文件 .dump # 导出整个库为 SQL 语句 .backup D:\data\bak.db # 在线备份 .quit # 退出实操心得在 Windows 的cmd里直接查询含中文的数据可能会显示成乱码因为控制台默认走的是 GBK 代码页。先执行一次chcp 65001切到 UTF-8 再进 sqlite3中文就正常了。这个问题跟数据库本身的编码无关纯粹是控制台显示层的锅别去改PRAGMA encoding改坏了自己难受。2.2 可视化工具三选一DB Browser、SQLiteStudio、DBeaver命令行查数据终究不顺手日常还是得配个 GUI。热词里提到的三个工具我都长期用过各自的脾气差别挺大工具定位优点明显短板我什么时候用它DB Browser for SQLite专一型 SQLite 客户端体积小、启动快、不依赖 Java建表界面化改字段类型很方便CSV 导入导出顺手大表几十万行以上浏览会卡编辑数据时必须先保存更改再提交新人容易误以为已经写进去了快速看数据结构、临时改两行数据、给学生演示SQLiteStudio多库多窗口客户端绿色版单文件可同时开多个库和多个 SQL 窗口支持导出 SQL/CSV/JSON/HTML 等一堆格式SQL 编辑器有补全界面风格偏老高 DPI 屏上字有点小对大字段BLOB展示不够友好需要对照两个库做数据比对、批量导出报表时DBeaver Community通用数据库 IDE一个工具管 MySQL、PostgreSQL、SQLite 全部SQL 编辑、执行计划可视化、数据迁移能力都强首次连接 SQLite 要自动下载 JDBC 驱动离线环境要手动配启动吃内存对嵌入式库来说有点杀鸡用牛刀项目里同时有 SQLite 和服务端库不想装两个工具选哪个其实不重要重要的是别同时开着两个工具去改同一个.db文件。这两个工具各自会持有自己的连接一个开着未提交的事务另一个就会撞上database is locked。我见过有人抱怨数据改了没生效最后查出来是 DB Browser 里点了修改记录但没点写入更改。2.3 第一次建库建表把最小闭环走通别急着上业务表先拿一张最简单的表把增删改查走一遍感知一下 SQLite 的手感-- 建一张用户表 CREATE TABLE user ( id INTEGER PRIMARY KEY, -- 自增主键等价于 rowid 的别名 name TEXT NOT NULL, email TEXT UNIQUE, age INTEGER CHECK (age 0 AND age 200), created TEXT NOT NULL DEFAULT (datetime(now)) ); -- 插入 INSERT INTO user (name, email, age) VALUES (张三, zhangsanexample.com, 28); INSERT INTO user (name, email, age) VALUES (李四, lisiexample.com, 35); -- 查询 SELECT id, name, age FROM user WHERE age 30; -- 更新 UPDATE user SET age 29 WHERE name 张三; -- 删除 DELETE FROM user WHERE email IS NULL;跑完之后执行.schema user看看 SQLite 帮你补全了什么再执行.dump看看数据是怎么被导出成 SQL 的。这个来回很值得做一遍你会对SQLite 到底怎么存东西建立直觉。有一点必须提醒DELETE FROM user;和DROP TABLE user;之后文件大小通常不会变小。SQLite 只是把这些页标记为空闲放回 freelist等后续插入复用。想让文件瘦回去得执行VACUUM;它会重建整个数据库文件。对一个大库来说VACUUM可能是秒级到分钟级的操作别在高频写入的路径上随手调用。3. 建表与增删改查几个和 MySQL 不一样的坑SQL 基础我不铺开讲重点讲 SQLite 和别的数据库行为不一致、容易让人踩坑的地方。这些差异平时看不出来一出问题就是代码在 MySQL 上跑得好好的换 SQLite 就炸。3.1 类型系统五个存储类与动态类型的真相SQLite 对类型的处理方式和绝大多数数据库都不同。它内部只有五个存储类NULL、INTEGER、REAL、TEXT、BLOB。你在建表时写的VARCHAR(50)、DATETIME、BOOLEAN、DECIMAL(10,2)这些名字SQLite 会按一套类型亲和性type affinity规则归到上述某一类里而不是强制约束。说得直白一点你声明了age INTEGER往里插字符串abcSQLite 不会报错它会原样把abc存成 TEXT。真正约束数值的是CHECK约束或者新版本提供的STRICT表-- 3.37.0 起支持严格表类型写错直接报错 CREATE TABLE account ( id INTEGER PRIMARY KEY, balance INTEGER NOT NULL, memo TEXT ) STRICT;类型亲和性的归类规则可以简单记成这几条名字里含INT的归 INTEGER含CHAR、CLOB、TEXT的归 TEXT含BLOB或没写类型的归 BLOB含REAL、FLOA、DOUB的归 REAL其余全归 NUMERIC。所以VARCHAR(50)会被归为 TEXTBOOLEAN会被归为 NUMERIC——你存进去的true实际是整数 1。关于日期时间我的建议很明确统一用 TEXT 存 ISO8601 的 UTC 字符串或者统一用 INTEGER 存 Unix 时间戳别混着来。用DEFAULT (datetime(now))存的是 UTC 时间如果你想存本地时间得写datetime(now,localtime)但我不推荐这么做本地时区一旦跨机器就乱套。注意SQLite 没有真正的BOOLEAN类型TRUE/FALSE关键字是 3.23 之后才作为1/0的字面量支持的。老项目里你可能会看到有人存Y/N那就是历史遗留的产物。3.2 建表语句里的约束怎么写才不给自己挖坑几个我踩过坑、后来固定成习惯的写法主键一律写INTEGER PRIMARY KEY不要写INT PRIMARY KEY。注意这里的INTEGER一个字母都不能差写INT就会变成普通的整数列加唯一索引拿不到 rowid 的行为。外键要显式开启。SQLite 出于兼容历史的原因外键约束默认是关闭的必须在每个连接上执行PRAGMA foreign_keys ON;否则你写的ON DELETE CASCADE就是一行注释。能用NOT NULL就用。SQLite 允许列里存 NULL而 NULL 在比较运算里是个大坑WHERE memo x会漏掉memo IS NULL的行很多人第一次统计对不上数就是栽在这。WITHOUT ROWID只在特定场景用。当你的主键是复合的文本键、且表非常窄、查询几乎都是主键定位时它可以省掉一层索引开销但一旦表里有BLOB或长文本收益基本为负别盲目套用。一个带完整约束的建表模板长这样PRAGMA foreign_keys ON; CREATE TABLE category ( id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE ); CREATE TABLE product ( id INTEGER PRIMARY KEY, cat_id INTEGER NOT NULL REFERENCES category(id) ON DELETE CASCADE, name TEXT NOT NULL, price REAL NOT NULL CHECK (price 0), stock INTEGER NOT NULL DEFAULT 0, created_at TEXT NOT NULL DEFAULT (strftime(%Y-%m-%dT%H:%M:%fZ,now)) ); CREATE INDEX idx_product_cat ON product(cat_id); CREATE INDEX idx_product_name ON product(name);3.3 增删改查的实战写法和 UPSERT基础语法你会我讲几个实际项目里高频、但教程里少见的写法。**UPSERT存在则更新不存在则插入**是 3.24 之后的能力处理同步数据时特别好用INSERT INTO product (id, cat_id, name, price, stock) VALUES (1001, 1, 扳手, 39.9, 20) ON CONFLICT(id) DO UPDATE SET price excluded.price, stock excluded.stock;excluded是关键字代表本次本来想插进去的那行。相比SELECT判断再INSERT/UPDATE的两步写法它在并发下更可靠也不用担心中间被别的写入插队。查询里几个值得记住的点LIMIT 10 OFFSET 20做分页在深分页时性能很差因为 OFFSET 要求它先扫过前 20 行有条件的话用上一页最后一个 id做游标分页。GROUP BY后面跟着的字段如果没在SELECT里SQLite 会随便挑一行返回不报错这跟 MySQL 早期非严格模式类似别依赖这个行为。窗口函数在 3.25 之后是支持的ROW_NUMBER() OVER (PARTITION BY cat_id ORDER BY price DESC)可以一行搞定每组取前 N 条。批量写入一定要包事务这是 SQLite 上性能差距最大的一个点BEGIN; INSERT INTO product (cat_id,name,price,stock) VALUES (1,螺丝,0.5,1000); INSERT INTO product (cat_id,name,price,stock) VALUES (1,螺母,0.4,1000); -- ... 再写几百上千条 COMMIT;不开事务时每条INSERT都要单独提交一次每次都涉及日志写入和 fsync机械硬盘上每秒可能只有几十条包在事务里以后几千条也能在毫秒级完成。差距能到两个数量级这不是玄学是磁盘同步的物理代价。3.4 索引与查询计划什么时候该加什么时候别加想知道一条 SQL 到底走没走索引用EXPLAIN QUERY PLANEXPLAIN QUERY PLAN SELECT * FROM product WHERE cat_id 1 AND price 10;输出里如果出现SCAN TABLE product就是全表扫描出现SEARCH TABLE product USING INDEX idx_product_cat (cat_id?)说明用上了索引。我的习惯是写完一条稍复杂的查询先跑一遍EXPLAIN QUERY PLAN再决定要不要加索引而不是凭感觉一通乱加。关于索引有几条经验值得说。联合索引的顺序很重要CREATE INDEX idx_a_b ON t(a, b)能加速WHERE a?和WHERE a? AND b?但对单独WHERE b?基本没用这是最左前缀原则SQLite 和 MySQL 一致。索引不是越多越好每个索引都意味着插入和更新时要多维护一棵 B 树一张写多读少的表挂五六个索引写入性能会明显下滑。部分索引在特定场景很划算比如你只查未删除的记录CREATE INDEX idx_active ON product(cat_id) WHERE stock 0;最后别忘了定期跑一下ANALYZE;它收集统计信息让查询规划器做出更靠谱的选择。数据量变化很大比如一次性导入了几十万行之后跑一次效果立竿见影。4. 最容易被问爆的一个点INSERT 之后怎么拿到自增序号这个问题在社区里的出现频率高得离谱而且答案版本五花八门有的还互相矛盾。我把它的来龙去脉彻底讲清楚你看完就知道那些说法为什么对、为什么错。4.1 INTEGER PRIMARY KEY 与 AUTOINCREMENT 的真实差别先说结论INTEGER PRIMARY KEY本身就已经是自增的不需要加AUTOINCREMENT。原因是这样的列会成为rowid的别名而rowid的分配规则是取当前表中最大的 rowid 加一。删掉最后一行再插入新行的 id 会复用被删掉的那个值。加上AUTOINCREMENT之后行为变成取历史最大 rowid 加一永不复用。实现方式是 SQLite 内部多维护一张sqlite_sequence表记录每张表分配过的最大值。代价是每次插入都要额外读写这张表开销比不带时大一点。写法是否自增删除后是否复用 id额外开销建议id INTEGER PRIMARY KEY是会复用无绝大多数场景就用它id INTEGER PRIMARY KEY AUTOINCREMENT是永不复用有维护 sqlite_sequence需要 id 全局单调、不允许重复使用时id INT PRIMARY KEY否只是唯一索引不适用无别这么写什么情况下值得加AUTOINCREMENT我遇到过两种一是 id 会对外暴露比如做单据号业务上不允许出现删了 5 号单又冒出新的 5 号单二是某些同步逻辑靠 id 递增判断增量。其他情况下不加更省。4.2 last_insert_rowid() 的正确姿势与多线程陷阱拿到刚插入那行的 id标准做法是在同一个连接上执行SELECT last_insert_rowid();这个函数返回的是当前连接上最近一次成功 INSERT 产生的 rowid注意两个关键词当前连接、最近一次。理解这两点很多诡异 bug就解释得通了。第一个陷阱是跨连接。你在连接 A 上插入跑到连接 B 上查last_insert_rowid()拿到的要么是 0要么是 B 上一次插入的值绝对不是你要的。所以取 id 必须紧跟着插入在同一连接里做。第二个陷阱是多线程共享连接。如果两个线程共用一个连接线程 1 刚插入完还没执行SELECT last_insert_rowid()线程 2 插入了另一行线程 1 拿到的就是线程 2 的 id。SQLite 的连接对象本身也不是线程安全的正确做法是每个线程用自己的连接或者取 id 的操作和插入放在同一个锁里。在 .NET 里的正确写法以 System.Data.SQLite 为例using (var conn new SQLiteConnection(Data Sourcedata.db;Version3;)) { conn.Open(); using (var cmd conn.CreateCommand()) { cmd.CommandText INSERT INTO product(cat_id,name,price,stock) VALUES(c,n,p,s);; cmd.Parameters.AddWithValue(c, 1); cmd.Parameters.AddWithValue(n, 钳子); cmd.Parameters.AddWithValue(p, 25.5); cmd.Parameters.AddWithValue(s, 10); cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); cmd.CommandText SELECT last_insert_rowid();; long newId (long)cmd.ExecuteScalar(); Console.WriteLine($新插入的行 id {newId}); } }实操心得注意我在取 id 之前调用了cmd.Parameters.Clear()。同一个 Command 对象复用是没问题的但参数不清干净下一次执行时参数名对不上很容易出SQLiteException。这个坑我在早期代码里犯过好几次后来干脆养成一个 SQL 一个 Command的习惯反而更清爽。4.3 批量插入、RETURNING 与 id 映射如果你需要批量插入并拿到每一行的 idlast_insert_rowid()就帮不上忙了——它只给你最后一条。有三个方案方案一逐条插入 每条后取 id包在一个事务里。简单直接几千行以内性能完全可接受。缺点是代码啰嗦。方案二自己算 id。在事务开始前SELECT IFNULL(MAX(id),0) FROM product;拿到基准值然后按插入顺序递增推算。前提是这段时间没有别的写入且不能用 AUTOINCREMENT 之外的并发插入风险较高我不太推荐。方案三用RETURNINGSQLite 3.35.0 起支持这是最优雅的做法INSERT INTO product(cat_id,name,price,stock) VALUES (1,锤子,45,5), (1,改锥,12,30) RETURNING id, name;它会返回每一行插入后的 id用 DataReader 读就行。不过有两点要注意一是必须用ExecuteReader而不是ExecuteNonQuery用错了什么都拿不到二是老版本驱动可能不认这条语句得确认服务端版本和驱动版本都够新。方案四我个人最喜欢压根不依赖自增序号主键直接用 GUID 或 ULID插入前在代码里生成。这样批量插入后 id 全都已知天然适合做离线同步、多端合并。代价是字符串主键比整数占空间、索引效率略低。数据量在几十万以内这点差异可以忽略换来的是代码逻辑简单一大截。5. .NET 4.8 连接 SQLite驱动选型与连接字符串逐项拆解这块是热词里反复出现的问题也是桌面开发老项目最常卡的地方。.NET Framework 4.8 属于老框架很多新包对它的支持情况需要单独确认选错包会带来一堆运行时加载失败。5.1 两个主流驱动的取舍对比项System.Data.SQLiteMicrosoft.Data.Sqlite包名System.Data.SQLite.CoreMicrosoft.Data.SqliteSQLitePCLRaw.bundle_e_sqlite3.NET Framework 4.8 支持原生支持最稳走 netstandard2.0 兼容可以跑但要留意初始化命名空间System.Data.SQLiteMicrosoft.Data.SqliteAPI 风格ADO.NETSQLiteConnection等ADO.NET接口更贴近现代 .NET内置 SQLite 版本随包发布可用SQLiteConnection.SQLiteVersion查由 SQLitePCLRaw 提供可用SELECT sqlite_version()查加密支持支持Password关键字部分版本需专门构建默认不支持加密我的建议老项目、需要加密、想少折腾选它新项目或未来要迁移到 .NET 6选它在 .NET Framework 4.8 上我一般直接上System.Data.SQLite.Core装完就能用。如果用Microsoft.Data.Sqlite在 .NET Framework 环境下有个地方要特别注意某些情况下需要在程序启动入口调用一次SQLitePCL.Batteries_V2.Init()否则运行时会报找不到本地库的错误。这个初始化在 .NET Core 项目里通常由模块初始化器自动完成但在 .NET Framework 下不一定保险起见显式加一行成本为零。5.2 连接字符串关键参数逐项说明System.Data.SQLite的连接字符串关键字挺多常用的这几个你一定要清楚Data SourceD:\app\data.db;Version3;PoolingTrue;Max Pool Size20; FailIfMissingFalse;Journal ModeWAL;SynchronousNormal; BusyTimeout5000;Foreign KeysTrue;DateTimeFormatISO8601;DateTimeKindUtc; Cache Size-8000;Data Source数据库文件路径。可以是相对路径但相对的是当前工作目录桌面程序双击启动和命令行启动的工作目录可能不同所以我强烈建议用绝对路径或者用AppDomain.CurrentDomain.BaseDirectory拼。Version3指定 SQLite 3 的方言加上比较保险。Pooling连接池开关默认是开的。桌面程序里频繁开关连接时保持默认即可能省掉重复打开文件的成本。FailIfMissing默认False文件不存在就自动创建。如果你的程序希望库不存在就报错把它设成True可以避免误连到一个空库上还不知道。Journal Mode可以在这里直接指定WAL省得程序里再执行一次 PRAGMA。等价的 PRAGMA 是PRAGMA journal_modeWAL;。SynchronousWAL 模式下推荐Normal兼顾安全和速度设为Off在断电时可能损坏数据库别图快。BusyTimeout单位毫秒遇到锁时等待多久再报错。默认值偏小甚至为 0设成 5000 能挡掉绝大部分偶发的database is locked。Foreign KeysTrue等价于PRAGMA foreign_keysON写在连接字符串里最省事不用每次开连接都执行一遍。DateTimeFormat / DateTimeKind控制DateTime类型怎么存。老项目的常见坑是有人存本地时间、有人存 UTC最后统计报表对不上。新项目统一ISO8601Utc。Cache Size负值表示 KB 数-8000就是约 8MB 的页缓存。桌面程序适当调大对读性能有帮助。注意Microsoft.Data.Sqlite的连接字符串关键字跟上面不完全一样比如它用ModeReadWriteCreate、CacheShared、Default Timeout30写成Journal ModeWAL这种老关键字它可能不认识。切驱动的时候连接字符串要一起改别直接复制粘贴。5.3 参数化、事务与一个可复用的代码骨架最后给一个我在多个 .NET 4.8 工具项目里反复用过的骨架涵盖了初始化、参数化查询、事务和异常处理public static class Db { private static string BuildConnStr(string dbPath) $Data Source{dbPath};Version3;PoolingTrue; Journal ModeWAL;SynchronousNormal;BusyTimeout5000;Foreign KeysTrue; DateTimeFormatISO8601;DateTimeKindUtc;Cache Size-8000;; public static void InitSchema(string dbPath) { using (var conn new SQLiteConnection(BuildConnStr(dbPath))) { conn.Open(); using (var cmd conn.CreateCommand()) { cmd.CommandText CREATE TABLE IF NOT EXISTS product( id INTEGER PRIMARY KEY, name TEXT NOT NULL, price REAL NOT NULL CHECK(price 0), stock INTEGER NOT NULL DEFAULT 0, created_at TEXT NOT NULL DEFAULT (strftime(%Y-%m-%dT%H:%M:%fZ,now)) ); CREATE INDEX IF NOT EXISTS idx_product_name ON product(name);; cmd.ExecuteNonQuery(); } } } public static void BatchInsert(string dbPath, IEnumerable(string Name, double Price, int Stock) rows) { using (var conn new SQLiteConnection(BuildConnStr(dbPath))) { conn.Open(); using (var tran conn.BeginTransaction()) using (var cmd conn.CreateCommand()) { cmd.Transaction tran; cmd.CommandText INSERT INTO product(name,price,stock) VALUES(n,p,s);; var pn cmd.Parameters.Add(n, DbType.String); var pp cmd.Parameters.Add(p, DbType.Double); var ps cmd.Parameters.Add(s, DbType.Int32); foreach (var r in rows) { pn.Value r.Name; pp.Value r.Price; ps.Value r.Stock; cmd.ExecuteNonQuery(); } tran.Commit(); } } } }几个细节值得强调BEGIN和COMMIT在驱动层用BeginTransaction()/Commit()就够了没必要手写 SQLCreateCommand每次新建比复用更安全参数用Add并显式指定DbType比AddWithValue少一些类型推断的意外。另外CREATE TABLE IF NOT EXISTS是保证程序多次启动不出错的关键写法。6. 性能与稳定性调优几条 PRAGMA 顶十行代码SQLite 的默认配置偏保守是为了兼容各种极端环境。在本地桌面程序或单机服务里改几个 PRAGMA 就能拿到明显的性能提升。6.1 WAL、synchronous、busy_timeout 三件套PRAGMA journal_mode WAL; -- 写前日志读写不互相阻塞 PRAGMA synchronous NORMAL; -- WAL 下推荐值兼顾安全与速度 PRAGMA busy_timeout 5000; -- 遇锁等待 5 秒 PRAGMA foreign_keys ON; -- 开启外键约束 PRAGMA temp_store MEMORY; -- 临时表放内存排序聚合更快 PRAGMA cache_size -20000; -- 约 20MB 页缓存这三个里面WAL 是收益最大的。默认的DELETE日志模式下写操作会阻塞读操作WAL 模式下读写可以同时进行只有一个写者但仍允许并发读对桌面程序里后台写数据 界面查列表这种场景特别友好。但 WAL 有两个必须记住的约束不能用在网络共享盘上WAL 依赖共享内存文件-shm而网络文件系统不保证共享内存语义用了会出各种奇怪的错误会产生额外的两个文件前面提过打包分发时要留意。6.2 批量写入与事务边界的取舍前面说过批量插入要包事务但事务也不是越大越好。事务过大有两个问题一是-wal文件会持续增长一个插入 50 万行的大事务能撑出几百 MB 的 WAL二是中途失败要全部回滚白干一场。我通常按500 到 2000 行一批切分事务具体数值看单行大小。切分之后既能拿到大部分性能收益失败时也只损失一批。如果是一次性导入百万级数据还可以临时关几个约束来提速PRAGMA synchronous OFF; PRAGMA journal_mode OFF; BEGIN; -- 大批量导入 COMMIT; PRAGMA journal_mode WAL; PRAGMA synchronous NORMAL;这么做的前提是数据能从源头重新导入因为一旦中途断电库是有可能损坏的。千万别在用户日常使用的库上这么干。6.3 备份、维护与完整性检查备份我最推荐VACUUM INTO它会生成一份紧凑的、不含空闲页的完整副本而且是在线操作不阻塞读VACUUM INTO D:\backup\data_20240601.db;.NET 里System.Data.SQLite的SQLiteConnection还提供了BackupDatabase方法可以在两个连接之间做在线备份适合程序里做定时备份功能。命令行下的.backup也是一个选择。日常维护方面PRAGMA integrity_check;会做一次完整校验返回ok说明结构没问题数据量大的库上它会比较慢可以先用PRAGMA quick_check;快速过一遍。如果你怀疑文件损坏比如程序被强制结束过、磁盘出过问题先跑quick_check真有问题再用.recover命令尝试把数据抢救到新库。还有一条容易被忽略的定期跑PRAGMA optimize;。它会根据统计信息决定是否需要重新ANALYZE开销小可以在程序退出前顺手执行一次长期下来查询计划会稳定不少。实操心得我习惯在程序启动时做三件事——检查文件是否存在、执行PRAGMA quick_check、把连接字符串里的 PRAGMA 一次性设好。这三步加起来通常不超过 100 毫秒但能挡掉相当一部分用户数据打不开的求助比事后救火划算太多。7. 常见问题排查实录与速查表这一节是我这些年被问得最多的问题集合。每个问题我都给出成因分析和具体处理办法你可以当成一份排障手册。7.1 database is locked 的成因与处理这个报错几乎每个 SQLite 使用者都会遇到成因有七八种我按出现频率排一下第一种连接没关。最常见。SQLiteConnection忘了Dispose或者事务开着没提交连接一直挂着。桌面程序里一个长时间跑的后台任务持有着写事务界面上的查询就会撞锁。处理办法是用using包住所有连接事务一定要在try/finally里保证提交或回滚。第二种busy_timeout 太小。默认值很低偶发的锁竞争会立刻抛错而不是等待。设置PRAGMA busy_timeout5000或连接字符串里写BusyTimeout5000能消掉大部分偶发报错。第三种图形工具开着事务。DB Browser 里如果修改了数据没点写入更改它会一直持有一个事务。这时候你的程序去写就会锁住。排查时先把 GUI 工具关掉试试。第四种多进程同时写。SQLite 允许多进程读、单进程写。程序在写的时候另一个程序也在写必然冲突。设计上要避免这种模式让写操作集中到一个进程。第五种防病毒或同步软件占用文件。杀毒软件实时扫描会短暂持有文件句柄同步客户端更麻烦。把数据库目录加到杀毒软件白名单别放同步目录。第六种WAL 用在网络盘上。前面说过这是硬性限制换成本地盘就好了。第七种事务里做了耗时操作。在事务中间调用 HTTP 接口、弹对话框等待用户输入事务就一直开着。原则是事务里只做数据库操作业务逻辑放外面。7.2 中文乱码、日期偏差与文件损坏中文乱码分三种情况。控制台显示乱码是代码页问题chcp 65001解决。数据写进去就是乱码通常是拼接 SQL 时字符串编码不对或者用sbyte[]/byte[]存了字符串正确做法是全程用参数化查询 System.Text.Encoding.UTF8。用 CSV 导入出现乱码多半是文件本身是 GBK 编码导入前用编辑器转成 UTF-8。日期偏差八小时几乎都是本地时间和 UTC 混用导致的。检查两处一是建表时的默认值写的是datetime(now)还是datetime(now,localtime)二是驱动连接字符串里的DateTimeKind设的是Utc还是Local。统一到一套上就不会出问题我倾向全用 UTC显示时在界面上做转换。文件损坏的表现通常是打开时报file is not a database或者查询报database disk image is malformed。先别慌用.recover抢救sqlite3 broken.db .recover | sqlite3 new.db这条命令会把能读出来的数据导进一个新库。如果数据重要第二步是立刻停掉所有写入把原文件整个复制一份留底再在副本上做各种尝试。7.3 问题速查表与我的避坑清单现象最可能原因处理办法database is locked连接未释放 / 未设 busy_timeout / GUI 占锁用 using 管连接设 5000ms 超时关掉 GUIno such table连到了另一个文件 / 相对路径工作目录不同用绝对路径打印实际连接的文件路径自增 id 拿不到跨连接调用 last_insert_rowid同一连接同一 Command 顺序执行插入中文变问号拼接 SQL 或非 UTF-8 编码全部改参数化统一 UTF-8时间差 8 小时UTC 与本地时间混用统一存 UTC展示层转换删除数据后文件没变小SQLite 复用空闲页需要瘦身时执行 VACUUM批量插入极慢未包事务BEGIN/COMMIT 包起来按千行分批外键约束不生效PRAGMA 没开连接字符串加 Foreign KeysTrue拷到别的机器打不开漏了 -wal 文件或用了加密用 VACUUM INTO 生成快照再拷查询突然变慢统计信息过期执行 ANALYZE 或 PRAGMA optimize最后再说几句我个人的习惯可能比上面的表更有用。第一永远开STRICT表前提是版本够新让类型错误在写入时就暴露而不是等到三个月后发现某列里混着字符串。第二所有时间字段带_at后缀、所有布尔字段带is_前缀命名上把类型信息传递出来。第三不要把 SQLite 当成临时方案而省略建索引等到数据涨到十万行再补索引用户已经骂过一轮了。第四桌面程序里给数据库文件加一道版本号机制比如建一张meta表存schema_version程序启动时读出来做升级迁移这个习惯能省掉后期无数次手动改表结构的麻烦。 SQLite 实战：单文件数据库、自增ID与 .NET 4.8 连接调优
1. 先搞清楚 SQLite 的定位后面能少走一半弯路SQLite 这东西第一次真正落到我手上是一个给车间做的桌面统计工具。客户那边的电脑不让装服务、不让开端口、IT 部门连 SQL Server 都不批最后我把整个数据库压缩成一个后缀.db的文件丢进安装目录程序双击就能跑。从那以后凡是遇到单机、单用户、数据量不大、部署环境恶劣的需求我第一反应都是先掂量掂量 SQLite 够不够用。先把结论摆在前头SQLite 是一个嵌入式的关系型数据库引擎它没有服务器进程没有端口没有账号密码体系整个数据库就是一个或几个普通文件。你写的 SQL 由链接进程序的库直接解析执行读写的就是磁盘上的那个文件。它解决的典型问题是我需要 SQL 的查询能力但我不需要一个数据库服务。适合谁来学桌面软件开发者、小型工具/内部系统开发者、数据分析做临时加工的人、移动端和后端做本地缓存的工程师以及任何需要把结构化数据存进一个能随手拷贝的文件里的人。网上关于 SQLite 的资料有个很别扭的地方要么是官网那种干巴巴的文档翻译要么是几行CREATE TABLE就收尾的五分钟教程。真要上项目你会发现卡住你的从来不是语法而是这些问题——Windows 上到底装哪个包为什么INSERT完拿不到自增 ID.NET 4.8 的老项目该引哪个驱动为什么偶尔报database is locked为什么我用 DB Browser 打开好好的库程序一读就报错这篇就把这些实际问题从头到尾捋一遍语法部分只讲够用的重点放在那些文档里不写、但一定会踩的地方。1.1 一个文件就是一座库单 db 文件的本质很多人第一次看到 SQLite 的单文件模式会本能地怀疑一个文件怎么可能同时装下表结构、数据、索引、事务日志答案在于 SQLite 自己实现了一套完整的页式存储管理文件被切成固定大小的页page默认 4096 字节第一页叫页 1放着数据库头和一个叫sqlite_master新版本里也叫sqlite_schema的系统表你执行.tables看到的所有表名本质上就是从这张表里SELECT出来的。表的数据以 B 树的形式分散在若干页上索引是另一棵 B 树空闲页由 freelist 链起来复用。这个设计带来几个非常实际的好处。第一是可移植——把.db文件从 Windows 拷到 Linux、从电脑拷到手机模拟器格式完全一致字节序问题 SQLite 自己处理了。第二是可备份——停止写入后直接复制文件就是一份完整备份不需要mysqldump那一套。第三是事务是真的——SQLite 在默认的journal模式下会先写一个-journal回滚日志提交时删除开了 WAL 模式则写-wal和-shm两个附属文件这也是为什么你复制数据库的时候要确认没有别的连接在写。不过有个细节必须提前说清楚你看到的一个文件在运行期可能不止一个。开了 WAL 之后目录里会多出xxx.db-wal和xxx.db-shm如果只拷了主文件、正好又没做 checkpoint那拷走的副本可能缺最近的提交。想干净地拿单文件要么程序里执行一次PRAGMA wal_checkpoint(TRUNCATE)并断开所有连接要么直接用VACUUM INTO 备份.db生成一份新文件。1.2 它适合谁不适合谁我把 SQLite 的适用边界总结成一张表你对着自己的场景勾一下基本就有答案了场景特征建议理由单机桌面程序、工具软件存配置和业务数据首选零部署一个文件带着走手机 App / 客户端本地缓存首选系统内置无需额外依赖网站或服务的读多写少小型库QPS 几十级别可以用加 WAL busy_timeout够撑住数据分析的中间结果集加工推荐直接对 CSV/Parquet 导入后写 SQL比 pandas 写链式表达式清爽多台机器同时写同一个库文件放共享盘别用网络文件系统的锁语义不可靠极易损坏高并发写入、每秒上百次事务别用写操作全局串行只能有一个写者需要行级权限、账号体系、审计日志别用它压根没有用户概念权限就是文件系统权限单表上亿行 复杂分析查询谨慎没有并行查询大表全扫会很慢考虑 DuckDB 之类注意把.db文件放在 OneDrive、坚果云、企业网盘这类自动同步目录里是我见过最隐蔽的数据库损坏来源。同步客户端在文件句柄还没释放时就上传、下载覆盖产生的半截文件能让整个库打不开。要同步就用VACUUM INTO导出的快照去同步主库永远放在本地磁盘。2. Windows 上把 SQLite 跑起来命令行加图形化工具两条腿新手在这个环节最容易迷路因为 sqlite.org 下载页上一堆压缩包名字又长得差不多。我按先用命令行跑通、再配可视化工具的顺序讲这个顺序很重要——命令行能让你看清每一步到底发生了什么等出问题时你才知道是驱动的问题还是工具的问题。2.1 命令行版下载、解压、配环境变量与版本验证打开官网下载页Windows 下你会看到几类文件sqlite-tools-win-x64-3xxxxxx.zip命令行工具里面是sqlite3.exe、sqlite-dll-win-x64-3xxxxxx.zip动态库给 C/C 或某些嵌入式场景用、还有源码包sqlite-amrs-*.zip。做日常开发你只需要tools 那个包。具体步骤下载sqlite-tools-win-x64-*.zip解压到一个不带空格、不带中文的路径比如D:\tools\sqlite3\。路径带中文在某些老版本控制台里会出乱码没必要给自己找麻烦。按Win R输入sysdm.cpl进高级 → 环境变量在系统变量的Path里追加D:\tools\sqlite3。关掉所有已开的命令行窗口Path 是启动时读取的重新打开cmd执行sqlite3 -version正常的话会输出类似3.46.0 2024-05-23 ...的版本号。这一步如果提示不是内部或外部命令九成是 Path 没生效而不是文件没下对。建库并进入交互模式sqlite3 D:\data\demo.db注意这个文件不存在时 SQLite 会自动创建不会报错也不会提示你。这是很多人第一次用时的困惑来源我建了个空库怎么什么都没有因为一个空数据库本来就没有任何表。进到sqlite3提示符之后常用的点命令dot commands你得记住几个它们不是 SQL是命令行工具的语法.help # 查看所有点命令 .tables # 列出所有表 .schema user # 查看某张表的建表语句 .headers on # 查询结果带列名 .mode box # 用表格框线显示结果比默认的竖线好看 .timer on # 显示每条语句耗时 .read init.sql # 执行一个 sql 脚本文件 .dump # 导出整个库为 SQL 语句 .backup D:\data\bak.db # 在线备份 .quit # 退出实操心得在 Windows 的cmd里直接查询含中文的数据可能会显示成乱码因为控制台默认走的是 GBK 代码页。先执行一次chcp 65001切到 UTF-8 再进 sqlite3中文就正常了。这个问题跟数据库本身的编码无关纯粹是控制台显示层的锅别去改PRAGMA encoding改坏了自己难受。2.2 可视化工具三选一DB Browser、SQLiteStudio、DBeaver命令行查数据终究不顺手日常还是得配个 GUI。热词里提到的三个工具我都长期用过各自的脾气差别挺大工具定位优点明显短板我什么时候用它DB Browser for SQLite专一型 SQLite 客户端体积小、启动快、不依赖 Java建表界面化改字段类型很方便CSV 导入导出顺手大表几十万行以上浏览会卡编辑数据时必须先保存更改再提交新人容易误以为已经写进去了快速看数据结构、临时改两行数据、给学生演示SQLiteStudio多库多窗口客户端绿色版单文件可同时开多个库和多个 SQL 窗口支持导出 SQL/CSV/JSON/HTML 等一堆格式SQL 编辑器有补全界面风格偏老高 DPI 屏上字有点小对大字段BLOB展示不够友好需要对照两个库做数据比对、批量导出报表时DBeaver Community通用数据库 IDE一个工具管 MySQL、PostgreSQL、SQLite 全部SQL 编辑、执行计划可视化、数据迁移能力都强首次连接 SQLite 要自动下载 JDBC 驱动离线环境要手动配启动吃内存对嵌入式库来说有点杀鸡用牛刀项目里同时有 SQLite 和服务端库不想装两个工具选哪个其实不重要重要的是别同时开着两个工具去改同一个.db文件。这两个工具各自会持有自己的连接一个开着未提交的事务另一个就会撞上database is locked。我见过有人抱怨数据改了没生效最后查出来是 DB Browser 里点了修改记录但没点写入更改。2.3 第一次建库建表把最小闭环走通别急着上业务表先拿一张最简单的表把增删改查走一遍感知一下 SQLite 的手感-- 建一张用户表 CREATE TABLE user ( id INTEGER PRIMARY KEY, -- 自增主键等价于 rowid 的别名 name TEXT NOT NULL, email TEXT UNIQUE, age INTEGER CHECK (age 0 AND age 200), created TEXT NOT NULL DEFAULT (datetime(now)) ); -- 插入 INSERT INTO user (name, email, age) VALUES (张三, zhangsanexample.com, 28); INSERT INTO user (name, email, age) VALUES (李四, lisiexample.com, 35); -- 查询 SELECT id, name, age FROM user WHERE age 30; -- 更新 UPDATE user SET age 29 WHERE name 张三; -- 删除 DELETE FROM user WHERE email IS NULL;跑完之后执行.schema user看看 SQLite 帮你补全了什么再执行.dump看看数据是怎么被导出成 SQL 的。这个来回很值得做一遍你会对SQLite 到底怎么存东西建立直觉。有一点必须提醒DELETE FROM user;和DROP TABLE user;之后文件大小通常不会变小。SQLite 只是把这些页标记为空闲放回 freelist等后续插入复用。想让文件瘦回去得执行VACUUM;它会重建整个数据库文件。对一个大库来说VACUUM可能是秒级到分钟级的操作别在高频写入的路径上随手调用。3. 建表与增删改查几个和 MySQL 不一样的坑SQL 基础我不铺开讲重点讲 SQLite 和别的数据库行为不一致、容易让人踩坑的地方。这些差异平时看不出来一出问题就是代码在 MySQL 上跑得好好的换 SQLite 就炸。3.1 类型系统五个存储类与动态类型的真相SQLite 对类型的处理方式和绝大多数数据库都不同。它内部只有五个存储类NULL、INTEGER、REAL、TEXT、BLOB。你在建表时写的VARCHAR(50)、DATETIME、BOOLEAN、DECIMAL(10,2)这些名字SQLite 会按一套类型亲和性type affinity规则归到上述某一类里而不是强制约束。说得直白一点你声明了age INTEGER往里插字符串abcSQLite 不会报错它会原样把abc存成 TEXT。真正约束数值的是CHECK约束或者新版本提供的STRICT表-- 3.37.0 起支持严格表类型写错直接报错 CREATE TABLE account ( id INTEGER PRIMARY KEY, balance INTEGER NOT NULL, memo TEXT ) STRICT;类型亲和性的归类规则可以简单记成这几条名字里含INT的归 INTEGER含CHAR、CLOB、TEXT的归 TEXT含BLOB或没写类型的归 BLOB含REAL、FLOA、DOUB的归 REAL其余全归 NUMERIC。所以VARCHAR(50)会被归为 TEXTBOOLEAN会被归为 NUMERIC——你存进去的true实际是整数 1。关于日期时间我的建议很明确统一用 TEXT 存 ISO8601 的 UTC 字符串或者统一用 INTEGER 存 Unix 时间戳别混着来。用DEFAULT (datetime(now))存的是 UTC 时间如果你想存本地时间得写datetime(now,localtime)但我不推荐这么做本地时区一旦跨机器就乱套。注意SQLite 没有真正的BOOLEAN类型TRUE/FALSE关键字是 3.23 之后才作为1/0的字面量支持的。老项目里你可能会看到有人存Y/N那就是历史遗留的产物。3.2 建表语句里的约束怎么写才不给自己挖坑几个我踩过坑、后来固定成习惯的写法主键一律写INTEGER PRIMARY KEY不要写INT PRIMARY KEY。注意这里的INTEGER一个字母都不能差写INT就会变成普通的整数列加唯一索引拿不到 rowid 的行为。外键要显式开启。SQLite 出于兼容历史的原因外键约束默认是关闭的必须在每个连接上执行PRAGMA foreign_keys ON;否则你写的ON DELETE CASCADE就是一行注释。能用NOT NULL就用。SQLite 允许列里存 NULL而 NULL 在比较运算里是个大坑WHERE memo x会漏掉memo IS NULL的行很多人第一次统计对不上数就是栽在这。WITHOUT ROWID只在特定场景用。当你的主键是复合的文本键、且表非常窄、查询几乎都是主键定位时它可以省掉一层索引开销但一旦表里有BLOB或长文本收益基本为负别盲目套用。一个带完整约束的建表模板长这样PRAGMA foreign_keys ON; CREATE TABLE category ( id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE ); CREATE TABLE product ( id INTEGER PRIMARY KEY, cat_id INTEGER NOT NULL REFERENCES category(id) ON DELETE CASCADE, name TEXT NOT NULL, price REAL NOT NULL CHECK (price 0), stock INTEGER NOT NULL DEFAULT 0, created_at TEXT NOT NULL DEFAULT (strftime(%Y-%m-%dT%H:%M:%fZ,now)) ); CREATE INDEX idx_product_cat ON product(cat_id); CREATE INDEX idx_product_name ON product(name);3.3 增删改查的实战写法和 UPSERT基础语法你会我讲几个实际项目里高频、但教程里少见的写法。**UPSERT存在则更新不存在则插入**是 3.24 之后的能力处理同步数据时特别好用INSERT INTO product (id, cat_id, name, price, stock) VALUES (1001, 1, 扳手, 39.9, 20) ON CONFLICT(id) DO UPDATE SET price excluded.price, stock excluded.stock;excluded是关键字代表本次本来想插进去的那行。相比SELECT判断再INSERT/UPDATE的两步写法它在并发下更可靠也不用担心中间被别的写入插队。查询里几个值得记住的点LIMIT 10 OFFSET 20做分页在深分页时性能很差因为 OFFSET 要求它先扫过前 20 行有条件的话用上一页最后一个 id做游标分页。GROUP BY后面跟着的字段如果没在SELECT里SQLite 会随便挑一行返回不报错这跟 MySQL 早期非严格模式类似别依赖这个行为。窗口函数在 3.25 之后是支持的ROW_NUMBER() OVER (PARTITION BY cat_id ORDER BY price DESC)可以一行搞定每组取前 N 条。批量写入一定要包事务这是 SQLite 上性能差距最大的一个点BEGIN; INSERT INTO product (cat_id,name,price,stock) VALUES (1,螺丝,0.5,1000); INSERT INTO product (cat_id,name,price,stock) VALUES (1,螺母,0.4,1000); -- ... 再写几百上千条 COMMIT;不开事务时每条INSERT都要单独提交一次每次都涉及日志写入和 fsync机械硬盘上每秒可能只有几十条包在事务里以后几千条也能在毫秒级完成。差距能到两个数量级这不是玄学是磁盘同步的物理代价。3.4 索引与查询计划什么时候该加什么时候别加想知道一条 SQL 到底走没走索引用EXPLAIN QUERY PLANEXPLAIN QUERY PLAN SELECT * FROM product WHERE cat_id 1 AND price 10;输出里如果出现SCAN TABLE product就是全表扫描出现SEARCH TABLE product USING INDEX idx_product_cat (cat_id?)说明用上了索引。我的习惯是写完一条稍复杂的查询先跑一遍EXPLAIN QUERY PLAN再决定要不要加索引而不是凭感觉一通乱加。关于索引有几条经验值得说。联合索引的顺序很重要CREATE INDEX idx_a_b ON t(a, b)能加速WHERE a?和WHERE a? AND b?但对单独WHERE b?基本没用这是最左前缀原则SQLite 和 MySQL 一致。索引不是越多越好每个索引都意味着插入和更新时要多维护一棵 B 树一张写多读少的表挂五六个索引写入性能会明显下滑。部分索引在特定场景很划算比如你只查未删除的记录CREATE INDEX idx_active ON product(cat_id) WHERE stock 0;最后别忘了定期跑一下ANALYZE;它收集统计信息让查询规划器做出更靠谱的选择。数据量变化很大比如一次性导入了几十万行之后跑一次效果立竿见影。4. 最容易被问爆的一个点INSERT 之后怎么拿到自增序号这个问题在社区里的出现频率高得离谱而且答案版本五花八门有的还互相矛盾。我把它的来龙去脉彻底讲清楚你看完就知道那些说法为什么对、为什么错。4.1 INTEGER PRIMARY KEY 与 AUTOINCREMENT 的真实差别先说结论INTEGER PRIMARY KEY本身就已经是自增的不需要加AUTOINCREMENT。原因是这样的列会成为rowid的别名而rowid的分配规则是取当前表中最大的 rowid 加一。删掉最后一行再插入新行的 id 会复用被删掉的那个值。加上AUTOINCREMENT之后行为变成取历史最大 rowid 加一永不复用。实现方式是 SQLite 内部多维护一张sqlite_sequence表记录每张表分配过的最大值。代价是每次插入都要额外读写这张表开销比不带时大一点。写法是否自增删除后是否复用 id额外开销建议id INTEGER PRIMARY KEY是会复用无绝大多数场景就用它id INTEGER PRIMARY KEY AUTOINCREMENT是永不复用有维护 sqlite_sequence需要 id 全局单调、不允许重复使用时id INT PRIMARY KEY否只是唯一索引不适用无别这么写什么情况下值得加AUTOINCREMENT我遇到过两种一是 id 会对外暴露比如做单据号业务上不允许出现删了 5 号单又冒出新的 5 号单二是某些同步逻辑靠 id 递增判断增量。其他情况下不加更省。4.2 last_insert_rowid() 的正确姿势与多线程陷阱拿到刚插入那行的 id标准做法是在同一个连接上执行SELECT last_insert_rowid();这个函数返回的是当前连接上最近一次成功 INSERT 产生的 rowid注意两个关键词当前连接、最近一次。理解这两点很多诡异 bug就解释得通了。第一个陷阱是跨连接。你在连接 A 上插入跑到连接 B 上查last_insert_rowid()拿到的要么是 0要么是 B 上一次插入的值绝对不是你要的。所以取 id 必须紧跟着插入在同一连接里做。第二个陷阱是多线程共享连接。如果两个线程共用一个连接线程 1 刚插入完还没执行SELECT last_insert_rowid()线程 2 插入了另一行线程 1 拿到的就是线程 2 的 id。SQLite 的连接对象本身也不是线程安全的正确做法是每个线程用自己的连接或者取 id 的操作和插入放在同一个锁里。在 .NET 里的正确写法以 System.Data.SQLite 为例using (var conn new SQLiteConnection(Data Sourcedata.db;Version3;)) { conn.Open(); using (var cmd conn.CreateCommand()) { cmd.CommandText INSERT INTO product(cat_id,name,price,stock) VALUES(c,n,p,s);; cmd.Parameters.AddWithValue(c, 1); cmd.Parameters.AddWithValue(n, 钳子); cmd.Parameters.AddWithValue(p, 25.5); cmd.Parameters.AddWithValue(s, 10); cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); cmd.CommandText SELECT last_insert_rowid();; long newId (long)cmd.ExecuteScalar(); Console.WriteLine($新插入的行 id {newId}); } }实操心得注意我在取 id 之前调用了cmd.Parameters.Clear()。同一个 Command 对象复用是没问题的但参数不清干净下一次执行时参数名对不上很容易出SQLiteException。这个坑我在早期代码里犯过好几次后来干脆养成一个 SQL 一个 Command的习惯反而更清爽。4.3 批量插入、RETURNING 与 id 映射如果你需要批量插入并拿到每一行的 idlast_insert_rowid()就帮不上忙了——它只给你最后一条。有三个方案方案一逐条插入 每条后取 id包在一个事务里。简单直接几千行以内性能完全可接受。缺点是代码啰嗦。方案二自己算 id。在事务开始前SELECT IFNULL(MAX(id),0) FROM product;拿到基准值然后按插入顺序递增推算。前提是这段时间没有别的写入且不能用 AUTOINCREMENT 之外的并发插入风险较高我不太推荐。方案三用RETURNINGSQLite 3.35.0 起支持这是最优雅的做法INSERT INTO product(cat_id,name,price,stock) VALUES (1,锤子,45,5), (1,改锥,12,30) RETURNING id, name;它会返回每一行插入后的 id用 DataReader 读就行。不过有两点要注意一是必须用ExecuteReader而不是ExecuteNonQuery用错了什么都拿不到二是老版本驱动可能不认这条语句得确认服务端版本和驱动版本都够新。方案四我个人最喜欢压根不依赖自增序号主键直接用 GUID 或 ULID插入前在代码里生成。这样批量插入后 id 全都已知天然适合做离线同步、多端合并。代价是字符串主键比整数占空间、索引效率略低。数据量在几十万以内这点差异可以忽略换来的是代码逻辑简单一大截。5. .NET 4.8 连接 SQLite驱动选型与连接字符串逐项拆解这块是热词里反复出现的问题也是桌面开发老项目最常卡的地方。.NET Framework 4.8 属于老框架很多新包对它的支持情况需要单独确认选错包会带来一堆运行时加载失败。5.1 两个主流驱动的取舍对比项System.Data.SQLiteMicrosoft.Data.Sqlite包名System.Data.SQLite.CoreMicrosoft.Data.SqliteSQLitePCLRaw.bundle_e_sqlite3.NET Framework 4.8 支持原生支持最稳走 netstandard2.0 兼容可以跑但要留意初始化命名空间System.Data.SQLiteMicrosoft.Data.SqliteAPI 风格ADO.NETSQLiteConnection等ADO.NET接口更贴近现代 .NET内置 SQLite 版本随包发布可用SQLiteConnection.SQLiteVersion查由 SQLitePCLRaw 提供可用SELECT sqlite_version()查加密支持支持Password关键字部分版本需专门构建默认不支持加密我的建议老项目、需要加密、想少折腾选它新项目或未来要迁移到 .NET 6选它在 .NET Framework 4.8 上我一般直接上System.Data.SQLite.Core装完就能用。如果用Microsoft.Data.Sqlite在 .NET Framework 环境下有个地方要特别注意某些情况下需要在程序启动入口调用一次SQLitePCL.Batteries_V2.Init()否则运行时会报找不到本地库的错误。这个初始化在 .NET Core 项目里通常由模块初始化器自动完成但在 .NET Framework 下不一定保险起见显式加一行成本为零。5.2 连接字符串关键参数逐项说明System.Data.SQLite的连接字符串关键字挺多常用的这几个你一定要清楚Data SourceD:\app\data.db;Version3;PoolingTrue;Max Pool Size20; FailIfMissingFalse;Journal ModeWAL;SynchronousNormal; BusyTimeout5000;Foreign KeysTrue;DateTimeFormatISO8601;DateTimeKindUtc; Cache Size-8000;Data Source数据库文件路径。可以是相对路径但相对的是当前工作目录桌面程序双击启动和命令行启动的工作目录可能不同所以我强烈建议用绝对路径或者用AppDomain.CurrentDomain.BaseDirectory拼。Version3指定 SQLite 3 的方言加上比较保险。Pooling连接池开关默认是开的。桌面程序里频繁开关连接时保持默认即可能省掉重复打开文件的成本。FailIfMissing默认False文件不存在就自动创建。如果你的程序希望库不存在就报错把它设成True可以避免误连到一个空库上还不知道。Journal Mode可以在这里直接指定WAL省得程序里再执行一次 PRAGMA。等价的 PRAGMA 是PRAGMA journal_modeWAL;。SynchronousWAL 模式下推荐Normal兼顾安全和速度设为Off在断电时可能损坏数据库别图快。BusyTimeout单位毫秒遇到锁时等待多久再报错。默认值偏小甚至为 0设成 5000 能挡掉绝大部分偶发的database is locked。Foreign KeysTrue等价于PRAGMA foreign_keysON写在连接字符串里最省事不用每次开连接都执行一遍。DateTimeFormat / DateTimeKind控制DateTime类型怎么存。老项目的常见坑是有人存本地时间、有人存 UTC最后统计报表对不上。新项目统一ISO8601Utc。Cache Size负值表示 KB 数-8000就是约 8MB 的页缓存。桌面程序适当调大对读性能有帮助。注意Microsoft.Data.Sqlite的连接字符串关键字跟上面不完全一样比如它用ModeReadWriteCreate、CacheShared、Default Timeout30写成Journal ModeWAL这种老关键字它可能不认识。切驱动的时候连接字符串要一起改别直接复制粘贴。5.3 参数化、事务与一个可复用的代码骨架最后给一个我在多个 .NET 4.8 工具项目里反复用过的骨架涵盖了初始化、参数化查询、事务和异常处理public static class Db { private static string BuildConnStr(string dbPath) $Data Source{dbPath};Version3;PoolingTrue; Journal ModeWAL;SynchronousNormal;BusyTimeout5000;Foreign KeysTrue; DateTimeFormatISO8601;DateTimeKindUtc;Cache Size-8000;; public static void InitSchema(string dbPath) { using (var conn new SQLiteConnection(BuildConnStr(dbPath))) { conn.Open(); using (var cmd conn.CreateCommand()) { cmd.CommandText CREATE TABLE IF NOT EXISTS product( id INTEGER PRIMARY KEY, name TEXT NOT NULL, price REAL NOT NULL CHECK(price 0), stock INTEGER NOT NULL DEFAULT 0, created_at TEXT NOT NULL DEFAULT (strftime(%Y-%m-%dT%H:%M:%fZ,now)) ); CREATE INDEX IF NOT EXISTS idx_product_name ON product(name);; cmd.ExecuteNonQuery(); } } } public static void BatchInsert(string dbPath, IEnumerable(string Name, double Price, int Stock) rows) { using (var conn new SQLiteConnection(BuildConnStr(dbPath))) { conn.Open(); using (var tran conn.BeginTransaction()) using (var cmd conn.CreateCommand()) { cmd.Transaction tran; cmd.CommandText INSERT INTO product(name,price,stock) VALUES(n,p,s);; var pn cmd.Parameters.Add(n, DbType.String); var pp cmd.Parameters.Add(p, DbType.Double); var ps cmd.Parameters.Add(s, DbType.Int32); foreach (var r in rows) { pn.Value r.Name; pp.Value r.Price; ps.Value r.Stock; cmd.ExecuteNonQuery(); } tran.Commit(); } } } }几个细节值得强调BEGIN和COMMIT在驱动层用BeginTransaction()/Commit()就够了没必要手写 SQLCreateCommand每次新建比复用更安全参数用Add并显式指定DbType比AddWithValue少一些类型推断的意外。另外CREATE TABLE IF NOT EXISTS是保证程序多次启动不出错的关键写法。6. 性能与稳定性调优几条 PRAGMA 顶十行代码SQLite 的默认配置偏保守是为了兼容各种极端环境。在本地桌面程序或单机服务里改几个 PRAGMA 就能拿到明显的性能提升。6.1 WAL、synchronous、busy_timeout 三件套PRAGMA journal_mode WAL; -- 写前日志读写不互相阻塞 PRAGMA synchronous NORMAL; -- WAL 下推荐值兼顾安全与速度 PRAGMA busy_timeout 5000; -- 遇锁等待 5 秒 PRAGMA foreign_keys ON; -- 开启外键约束 PRAGMA temp_store MEMORY; -- 临时表放内存排序聚合更快 PRAGMA cache_size -20000; -- 约 20MB 页缓存这三个里面WAL 是收益最大的。默认的DELETE日志模式下写操作会阻塞读操作WAL 模式下读写可以同时进行只有一个写者但仍允许并发读对桌面程序里后台写数据 界面查列表这种场景特别友好。但 WAL 有两个必须记住的约束不能用在网络共享盘上WAL 依赖共享内存文件-shm而网络文件系统不保证共享内存语义用了会出各种奇怪的错误会产生额外的两个文件前面提过打包分发时要留意。6.2 批量写入与事务边界的取舍前面说过批量插入要包事务但事务也不是越大越好。事务过大有两个问题一是-wal文件会持续增长一个插入 50 万行的大事务能撑出几百 MB 的 WAL二是中途失败要全部回滚白干一场。我通常按500 到 2000 行一批切分事务具体数值看单行大小。切分之后既能拿到大部分性能收益失败时也只损失一批。如果是一次性导入百万级数据还可以临时关几个约束来提速PRAGMA synchronous OFF; PRAGMA journal_mode OFF; BEGIN; -- 大批量导入 COMMIT; PRAGMA journal_mode WAL; PRAGMA synchronous NORMAL;这么做的前提是数据能从源头重新导入因为一旦中途断电库是有可能损坏的。千万别在用户日常使用的库上这么干。6.3 备份、维护与完整性检查备份我最推荐VACUUM INTO它会生成一份紧凑的、不含空闲页的完整副本而且是在线操作不阻塞读VACUUM INTO D:\backup\data_20240601.db;.NET 里System.Data.SQLite的SQLiteConnection还提供了BackupDatabase方法可以在两个连接之间做在线备份适合程序里做定时备份功能。命令行下的.backup也是一个选择。日常维护方面PRAGMA integrity_check;会做一次完整校验返回ok说明结构没问题数据量大的库上它会比较慢可以先用PRAGMA quick_check;快速过一遍。如果你怀疑文件损坏比如程序被强制结束过、磁盘出过问题先跑quick_check真有问题再用.recover命令尝试把数据抢救到新库。还有一条容易被忽略的定期跑PRAGMA optimize;。它会根据统计信息决定是否需要重新ANALYZE开销小可以在程序退出前顺手执行一次长期下来查询计划会稳定不少。实操心得我习惯在程序启动时做三件事——检查文件是否存在、执行PRAGMA quick_check、把连接字符串里的 PRAGMA 一次性设好。这三步加起来通常不超过 100 毫秒但能挡掉相当一部分用户数据打不开的求助比事后救火划算太多。7. 常见问题排查实录与速查表这一节是我这些年被问得最多的问题集合。每个问题我都给出成因分析和具体处理办法你可以当成一份排障手册。7.1 database is locked 的成因与处理这个报错几乎每个 SQLite 使用者都会遇到成因有七八种我按出现频率排一下第一种连接没关。最常见。SQLiteConnection忘了Dispose或者事务开着没提交连接一直挂着。桌面程序里一个长时间跑的后台任务持有着写事务界面上的查询就会撞锁。处理办法是用using包住所有连接事务一定要在try/finally里保证提交或回滚。第二种busy_timeout 太小。默认值很低偶发的锁竞争会立刻抛错而不是等待。设置PRAGMA busy_timeout5000或连接字符串里写BusyTimeout5000能消掉大部分偶发报错。第三种图形工具开着事务。DB Browser 里如果修改了数据没点写入更改它会一直持有一个事务。这时候你的程序去写就会锁住。排查时先把 GUI 工具关掉试试。第四种多进程同时写。SQLite 允许多进程读、单进程写。程序在写的时候另一个程序也在写必然冲突。设计上要避免这种模式让写操作集中到一个进程。第五种防病毒或同步软件占用文件。杀毒软件实时扫描会短暂持有文件句柄同步客户端更麻烦。把数据库目录加到杀毒软件白名单别放同步目录。第六种WAL 用在网络盘上。前面说过这是硬性限制换成本地盘就好了。第七种事务里做了耗时操作。在事务中间调用 HTTP 接口、弹对话框等待用户输入事务就一直开着。原则是事务里只做数据库操作业务逻辑放外面。7.2 中文乱码、日期偏差与文件损坏中文乱码分三种情况。控制台显示乱码是代码页问题chcp 65001解决。数据写进去就是乱码通常是拼接 SQL 时字符串编码不对或者用sbyte[]/byte[]存了字符串正确做法是全程用参数化查询 System.Text.Encoding.UTF8。用 CSV 导入出现乱码多半是文件本身是 GBK 编码导入前用编辑器转成 UTF-8。日期偏差八小时几乎都是本地时间和 UTC 混用导致的。检查两处一是建表时的默认值写的是datetime(now)还是datetime(now,localtime)二是驱动连接字符串里的DateTimeKind设的是Utc还是Local。统一到一套上就不会出问题我倾向全用 UTC显示时在界面上做转换。文件损坏的表现通常是打开时报file is not a database或者查询报database disk image is malformed。先别慌用.recover抢救sqlite3 broken.db .recover | sqlite3 new.db这条命令会把能读出来的数据导进一个新库。如果数据重要第二步是立刻停掉所有写入把原文件整个复制一份留底再在副本上做各种尝试。7.3 问题速查表与我的避坑清单现象最可能原因处理办法database is locked连接未释放 / 未设 busy_timeout / GUI 占锁用 using 管连接设 5000ms 超时关掉 GUIno such table连到了另一个文件 / 相对路径工作目录不同用绝对路径打印实际连接的文件路径自增 id 拿不到跨连接调用 last_insert_rowid同一连接同一 Command 顺序执行插入中文变问号拼接 SQL 或非 UTF-8 编码全部改参数化统一 UTF-8时间差 8 小时UTC 与本地时间混用统一存 UTC展示层转换删除数据后文件没变小SQLite 复用空闲页需要瘦身时执行 VACUUM批量插入极慢未包事务BEGIN/COMMIT 包起来按千行分批外键约束不生效PRAGMA 没开连接字符串加 Foreign KeysTrue拷到别的机器打不开漏了 -wal 文件或用了加密用 VACUUM INTO 生成快照再拷查询突然变慢统计信息过期执行 ANALYZE 或 PRAGMA optimize最后再说几句我个人的习惯可能比上面的表更有用。第一永远开STRICT表前提是版本够新让类型错误在写入时就暴露而不是等到三个月后发现某列里混着字符串。第二所有时间字段带_at后缀、所有布尔字段带is_前缀命名上把类型信息传递出来。第三不要把 SQLite 当成临时方案而省略建索引等到数据涨到十万行再补索引用户已经骂过一轮了。第四桌面程序里给数据库文件加一道版本号机制比如建一张meta表存schema_version程序启动时读出来做升级迁移这个习惯能省掉后期无数次手动改表结构的麻烦。