sqlite3 外键作用原理和使用方法

转自http://zjx2988.blog.163.com/blog/static/9791033120150554232742/
1. sqlite3 默认为关闭外键限制的,如要打开,请执行如下命令:

PRAGMA FOREIGN_KEYS=ON;
  1. sqlite3 外键的创建和使用方法一:
    例如,我们创建如下两个表,Persons 和 Orders。
create table persons

(
id_p integer not null,
lastname varchar(20),
firstname varchar(20),
address varchar(100),
city varchar(100), 
primary key(id_p)
);

create table orders
(
id_o integer not null,
orderno not null,
id_p integer,
primary key(id_o),
foreign key(id_p) references persons(id_p)
);

假如在 persons 已插入如下数据:

id_p  lastname   firstname     address             city
  1      Adams    John       Oxford Street       London
  2      Bush     George     Fifth Avenue        New York
  3      Carter   Thomas     Changan Street      Beijing

外键插入限制:
当执行如下命令往 orders 插入记录时:

insert into orders values(1, 22456, 4);

发生错误 Error: foreign key constraint failed
由于 persons 中没有 id_p 为4记录而导致插入失败。

外键删除限制:
假设 orders 已插入如下数据:

id_o  orderno  id_p
  1      22456     1

当执行如下命令从 persons 删除记录时:

delete from persons where id_p=1;

发生错误 Error: foreign key constraint failed
由于在 orders 中有记录其 id_p=1 导致不能直接从 persons 中删除,必需首先删除 orders 中所有 id_p=1 的记录,才能从 persons 中删除 id_p=1 的记录。
执行 drop table persons; 删除表格时,也必需确保 orders 中所有相关的记录全部删除时,删除表格才能成功。

外键更新限制:
假设 orders 已插入如下数据:

id_o  orderno  id_p
  1      22456     1

当执行如下命令更新 persons 的记录时:
update persons set id_p=4 where id_p=1;
发生错误 Error: foreign key constraint failed
因为 orders 存在 id_p=1 的记录,所有不能在 persons 中直接更新 id_p=1 的记录,必需从 orders 中删除所有 id_p=1 的记录才能在 persons 中任相应的更新。

  1. sqlite3 外键的创建和使用方法二:
    如果想从 persons 中删除或更新相应的记录后,会在 orders 中有相应的体现而不是提示错误。则可按如下方式创建 orders。
create table persons
(
id_p integer not null,
lastname varchar(20),
firstname varchar(20),
address varchar(100),
city varchar(100),
primary key(id_p)
);

create table orders
(
id_o integer not null,
orderno integer not null,
id_p integer,
primary key(id_o),
foreign key(id_p) references persons(id_p) on delete cascade on update cascade
);

外键插入限制:
与方法一保持一致。

外键删除限制:
假设 persons 中已插入方法一中的数据。且 orders 中插入了如下数据:

id_o  orderno  id_p
  1      22456     1

当执行如下命令从 persons 中删除记录时:

delete from persons where id_p=1;

会成功删除 id_p=1 的记录,且连带着 orders 中所有 id_p=1 的记录会自动被删除。
执行 drop table persons; 删除表格时,会成功删除 persons,连带着 orders 中所有相关的记录也会被删除。

外键更新限制:
假设 persons 中已插入方法一中的数据。且 orders 中插入了如下数据:

id_o  orderno  id_p
  1      22456     1
  2      22457     1

当执行如下命令更新 persons 中的记录时:
update persons set id_p=4 where id_p=1;
会成功更新 id_p=1 的记录,且 orders 中的记录会自动更改为如下所示:

id_o  orderno  id_p
  1      22456     4
  2      22457     4
<think>好的,用户现在想了解SQLite数据库的内部实现机制原理。首先,我需要回顾之前关于SQLite的讨论,用户之前遇到了参数绑定错误的问题,现在转向了实现原理。根据用户提供的引用内容,引用[1]提到SQLite是轻量级的关系数据库,具有高性能可读的源代码,而引用[2]则涉及绕过加密层的技术,说明用户可能对底层结构感兴趣。 用户的问题集中在实现机制原理,因此需要详细解释SQLite的架构组件。首先,我应该分模块介绍,比如前端(SQL编译器)、后端(B-tree页面缓存)、操作系统接口等。根据引用[1],需要强调SQLite的轻量级高性能,以及源代码的可读性,所以可能需要提到其模块化设计。 接下来,需要考虑用户可能的背景。用户之前处理过参数绑定错误,可能具备一定的编程数据库使用经验,但对底层原理不熟悉。因此,解释时需要兼顾技术深度易懂性,比如用B-tree结构、锁机制、事务处理等概念,但避免过于晦涩的术语。 用户提供的引用中提到逆向工程加密绕过,这可能暗示用户对SQLite的文件结构数据存储方式感兴趣。因此,在回答中需要详细说明数据库文件的格式,如页的结构、B-tree在存储中的具体应用,以及事务日志(WAL)的作用。 另,用户可能想知道SQLite如何保证ACID特性,特别是原子性一致性,这与事务处理机制相关。需要解释回滚日志WAL的区别,以及它们如何协作确保数据完整性。 最后,用户可能有后续问题,比如如何优化性能或处理大型数据库,因此相关问题部分需要涵盖这些方面。同时,引用之前的错误讨论,可能可以链接到事务或锁机制的问题,但当前问题集中在原理,所以保持相关问题的针对性。 总结,回答结构应分为核心架构、存储机制、事务处理、锁机制等部分,每个部分详细但简明,使用示例比喻帮助理解,并确保引用提供的资料,如源代码结构文档的重要性。最后的问题部分应引导用户深入可能的应用场景优化策略。</think>#### SQLite数据库实现原理深度解析 SQLite作为**单文件嵌入式关系型数据库**,其设计哲学是"零配置、无服务器、单文件存储",核心实现机制包含以下关模块: --- ### 一、核心架构分层(模块化设计) SQLite采用分层架构,主要分为: 1. **前端(SQL编译器)** - 词法分析器(Tokenizer):将SQL语句拆分为标记流,例如识别`SELECT`为关字 - 语法分析器(Parser):构建抽象语法树(AST),使用Lemon解析器生成器实现[^1] - 代码生成器:将AST转换为**字节码程序**(Bytecode Program) ```c /* 示例:SQLite字节码结构 */ struct VdbeOp { u8 opcode; // 操作码如OP_OpenRead int p1, p2, p3; // 操作数 char *zComment; // 调试注释 }; ``` 2. **后端(B-tree引擎)** - B-tree实现表索引与数据存储,每个表索引对应独立B-tree - 页面管理器(Pager)处理磁盘I/O,管理缓存事务日志(WAL/journal) ```sql -- B-tree页面结构示例 | Page Header (12B) | Cell Pointer Array | Unallocated Space | Cell Content Area | ``` 3. **操作系统接口(VFS层)** 通过虚拟文件系统抽象层支持跨平台,例如实现文件锁、内存映射等操作系统差异的适配[^1]。 --- ### 二、存储引擎核心机制 #### 1. 单文件结构(*.db) 数据库文件由**固定大小页面(默认4KB)**构成,结构如下: ``` | 文件头(100B) | 页1 | 页2 | ... | 页N | ``` 文件头包含魔数`0x53 0x51 0x4c 0x69 0x74 0x65 0x20 0x66`、页面大小、版本号等元数据。 #### 2. B-tree与数据组织 - **表B-tree(B+tree)**:叶子节点直接存储记录数据 记录格式:`| Header | Column1 | Column2 | ... |`,Header包含数据类型长度信息 - **索引B-tree(B-tree)**:叶子节点存储值+ROWID指针 - 平衡算法:通过节点分裂/合并维持树高平衡,确保$O(\log n)$查询复杂度 #### 3. 锁机制(并发控制) 采用**文件锁层级模型**: ``` UNLOCKED → SHARED → RESERVED → PENDING → EXCLUSIVE ``` 通过`fcntl()`或`flock()`实现,支持多读单写(MVCC在WAL模式下启用)。 --- ### 三、事务与ACID实现 #### 1. 原子性(Atomicity) - **回滚日志模式**:修改前先写日志到`-journal`文件,事务提交后删除日志 - **WAL模式**:写前日志(Write-Ahead Logging)将修改写入`-wal`文件,提交时更新元数据 #### 2. 一致性(Consistency) - 约束检查:在字节码执行阶段验证`NOT NULL`、`UNIQUE`等约束 - 级联:通过触发器模拟实现(需启用`PRAGMA foreign_keys=ON`) #### 3. 隔离性(Isolation) - 默认**SERIALIZABLE隔离级别**,通过锁机制实现 - WAL模式下支持读写并发,读操作访问快照,写操作追加WAL文件 --- ### 四、关性能优化 1. **页面缓存(Page Cache)** 使用LRU算法管理内存中的数据库页面,减少磁盘I/O。缓存大小由`PRAGMA cache_size`控制。 2. **预写日志(WAL)优势** - 写操作无需覆盖原数据页,只需追加到WAL文件 - 读操作可并行执行,不受写锁阻塞 - 公式推导WAL提交速度: $$T_{commit} = T_{wal\_write} + T_{checkpoint}$$ 其中检查点(Checkpoint)将WAL内容合并回主数据库 3. **内存数据库模式** 通过`:memory:`打开数据库时,所有数据存储在堆内存中,适合临时数据处理。 --- ### 五、与其他数据库的对比 | 特性 | SQLite | MySQL | |---------------------|-----------------|-----------------| | 架构 | 嵌入式 | 客户端-服务器 | | 并发写入 | 单写器 | 多写器 | | 存储方式 | 单文件 | 多文件/目录 | | 适用场景 | 移动端/嵌入式 | 高并发Web应用 | --- ### 六、实现原理相关实践 1. **直接解析数据库文件** 可通过二进制工具分析页面结构: ```bash hexdump -C test.db | head -n 20 # 查看文件头 ``` 2. **调试B-tree结构** 使用SQLite Shell命令: ```sql PRAGMA index_info(&#39;索引名&#39;); -- 查看索引结构 .dump --data-only -- 导出数据页内容 ``` --- ### 七、扩展阅读方向 1. **B-tree变种优化**:如COW B-tree在WAL模式中的应用 2. **VFS层定制**:实现加密文件系统或网络存储 3. **WAL文件恢复机制**:通过`-wal``-shm`文件实现崩溃恢复 通过理解这些机制,可以更好地优化SQLite性能或开发定制存储引擎[^1][^2]。 --相关问题--: 1. SQLite的WAL模式如何实现读写并发? 2. 如何手动恢复损坏的SQLite数据库文件? 3. SQLite在嵌入式设备中的内存管理策略有哪些? 4. SQLite的锁机制如何避免死锁?
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值