本文档风哥主要介绍sqlite数据库架构原理与官方核心特性相关知识，包括sqlite数据库概述、架构原理、核心特性、学习前景、硬件环境要求、操作系统与运行平台、生产环境规划建议等内容，风哥教程参考sqlite官方文档Architecture Of SQLite、About SQLite、Distinctive Features等内容编写，适合DBA人员和开发人员在学习和测试中使用，如果要应用于生产环境则需要自行确认。
Part01-基础概念与理论知识
1.1 sqlite数据库概述与发展历程
sqlite是一个进程内的轻量级关系型数据库管理系统，它不是一个独立的进程，而是作为一个库嵌入到应用程序中。sqlite由D. Richard Hipp于2000年创建，是目前世界上部署最广泛的数据库引擎。更多视频教程www.fgedu.net.cn
- 零配置：无需安装、无需设置、无需管理
- 无服务器：直接读写磁盘文件，无需中间服务进程
- 单文件存储：整个数据库存储在单个跨平台磁盘文件中
- 跨平台：支持Windows、Linux、macOS、Android、iOS等
- 开源免费：源代码完全开放，无版权限制
- ACID事务：完整支持原子性、一致性、隔离性、持久性
1.1.1 sqlite数据库发展历程
sqlite数据库的发展经历了多个重要阶段：
- 2000年：sqlite 1.0发布，最初用于美国海军项目
- 2001年：sqlite 2.0发布，引入新的文件格式
- 2004年：sqlite 3.0发布，支持UTF-8、UTF-16、BLOB等
- 2010年：WAL模式引入，大幅提升并发性能
- 2018年：sqlite 3.25.0引入窗口函数支持
- 2024年：持续更新，支持JSON1扩展、CTE等高级特性
1.2 sqlite数据库架构原理详解
sqlite采用分层架构设计，从上到下依次为接口层、SQL编译器、虚拟机、B-Tree引擎、页面缓存和操作系统接口。学习交流加群风哥微信: itpux-com
1.2.1 sqlite数据库架构组件
┌─────────────────────────────────────────────────────────┐
│ 应用程序接口层 │
│ (sqlite3_open, sqlite3_prepare, sqlite3_step) │
├─────────────────────────────────────────────────────────┤
│ SQL编译器层 │
│ (分词器 Tokenizer → 解析器 Parser → 代码生成器) │
├─────────────────────────────────────────────────────────┤
│ 虚拟机层 (VDBE) │
│ (字节码执行引擎，执行SQL语句生成的字节码) │
├─────────────────────────────────────────────────────────┤
│ B-Tree存储引擎层 │
│ (表和索引使用B-Tree结构存储) │
├─────────────────────────────────────────────────────────┤
│ 页面缓存层 (Pager) │
│ (管理数据库页面读写、事务、锁机制) │
├─────────────────────────────────────────────────────────┤
│ 操作系统接口层 (VFS) │
│ (抽象文件操作，支持跨平台) │
└─────────────────────────────────────────────────────────┘
1.2.2 sqlite数据库核心组件说明
接口层（Interface）：提供C语言API接口，包括sqlite3_open()、sqlite3_prepare_v2()、sqlite3_step()等核心函数。应用程序通过这些接口与sqlite交互。
SQL编译器（SQL Compiler）：包含分词器、解析器和代码生成器三个子组件。分词器将SQL文本分解为标记，解析器根据语法规则生成语法树，代码生成器将语法树转换为虚拟机字节码。
虚拟机（VDBE）：虚拟数据库引擎是sqlite的核心，负责执行字节码程序。每个SQL语句都被编译成字节码程序，由VDBE执行。风哥提示：VDBE是sqlite高效执行SQL的关键组件。
B-Tree引擎：sqlite使用B-Tree结构组织数据，每个表和索引都使用独立的B-Tree存储。B-Tree提供高效的查找、插入、删除操作。
页面缓存（Pager）：负责管理数据库页面的读写缓存、事务管理、锁机制和崩溃恢复。默认页面大小为4096字节。
操作系统接口（VFS）：虚拟文件系统抽象层，使sqlite能够跨平台运行。VFS定义了文件操作的统一接口，不同操作系统实现各自的VFS。
1.3 sqlite数据库核心特性分析
sqlite具有许多独特的核心特性，使其在特定场景下成为最佳选择。学习交流加群风哥QQ113257174
1.3.1 sqlite数据库独特特性
1. 零配置管理
– 无需安装数据库服务
– 无需配置文件
– 无需数据库管理员
2. 单文件数据库
– 整个数据库存储在一个文件中
– 文件格式跨平台兼容
– 便于备份和迁移
3. 动态类型系统
– 支持动态类型（存储类）
– 列可以存储任意类型数据
– 类型亲和性机制
4. 完整的SQL支持
– 支持大部分SQL-92标准
– 支持外键约束
– 支持触发器、视图
– 支持CTE（公用表表达式）
– 支持窗口函数
5. 事务支持
– 完整ACID事务
– 支持WAL模式
– 支持嵌套事务
6. 扩展能力
– 支持自定义函数
– 支持自定义聚合
– 支持虚拟表
– 支持全文检索（FTS）
– 支持JSON操作
1.3.2 sqlite数据库与其他数据库对比
特性 sqlite MySQL/Oracle/PostgreSQL
────────────────────────────────────────────────────────────
架构模式 嵌入式 客户端/服务器
安装配置 零配置 需要安装配置
管理维护 无需管理 需要DBA管理
并发写入 单写入者 多写入者并发
网络访问 不支持 支持
数据规模 适合中小规模 适合大规模
资源占用 极低 较高
适用场景 本地存储 企业级应用
1.4 sqlite数据库学习前景与职业发展
sqlite作为全球部署最广泛的数据库，学习sqlite具有重要的职业价值。更多学习教程公众号风哥教程itpux_com
1.4.1 sqlite数据库应用领域
- 移动应用开发：Android、iOS应用本地数据存储
- 嵌入式系统：物联网设备、智能硬件数据存储
- 桌面应用：浏览器、邮件客户端、办公软件
- Web应用：中小型网站、开发测试环境
- 数据分析：数据清洗、离线分析工具
- 游戏开发：游戏存档、配置数据管理
1.4.2 sqlite数据库学习路线
阶段一：基础入门
├── sqlite基本概念与特点
├── 安装与命令行工具使用
├── SQL基础语法
└── 数据类型与表设计
阶段二：进阶应用
├── 事务与并发控制
├── 索引与查询优化
├── PRAGMA参数调优
└── 备份与恢复
阶段三：高级特性
├── FTS全文检索
├── JSON数据处理
├── 触发器与视图
└── 虚拟表扩展
阶段四：开发集成
├── C/C++ API编程
├── Python集成开发
├── Java集成开发
└── 性能优化实践
Part02-生产环境规划与建议
2.1 sqlite数据库硬件环境要求
sqlite对硬件资源要求极低，可以在各种硬件环境下运行。但在生产环境中，仍需合理规划硬件资源。
2.1.1 sqlite数据库最低硬件要求
CPU要求：
– 最低：任意32位或64位处理器
– 推荐：多核处理器（提升并发读取性能）
内存要求：
– 最低：8MB可用内存
– 推荐：256MB以上（用于页面缓存）
– 生产环境建议：1GB以上
存储要求：
– 最低：足够存储数据库文件的空间
– 推荐：SSD固态硬盘（提升IO性能）
– 数据库文件最大支持281TB
网络要求：
– sqlite为嵌入式数据库，无需网络支持
– 如需远程访问，需通过应用程序实现
2.1.2 sqlite数据库生产环境硬件建议
小型应用（数据量 < 10GB）：
├── CPU：2核
├── 内存：2GB
├── 存储：50GB SSD
└── 适用：个人项目、小型应用
中型应用（数据量 10GB – 100GB）：
├── CPU：4核
├── 内存：8GB
├── 存储：500GB SSD
└── 适用：中型网站、企业应用
大型应用（数据量 > 100GB）：
├── CPU：8核以上
├── 内存：16GB以上
├── 存储：1TB以上 SSD
└── 适用：数据分析、数据仓库
2.2 sqlite数据库操作系统与运行平台
sqlite具有优秀的跨平台特性，支持几乎所有主流操作系统和运行平台。更多视频教程www.fgedu.net.cn
2.2.1 sqlite数据库支持的操作系统
Linux系列：
├── Oracle Linux 7.x / 8.x / 9.x
├── RHEL 7.x / 8.x / 9.x
├── CentOS 7.x / 8.x / Stream 9
├── Ubuntu 18.04 / 20.04 / 22.04 / 24.04
├── Debian 10 / 11 / 12
├── 国产麒麟 Kylin V10 SP3
├── 欧拉 openEuler 20.03 / 22.03
└── 其他Linux发行版
Windows系列：
├── Windows 10 / 11
├── Windows Server 2016 / 2019 / 2022
└── Windows 7 / 8.1（部分支持）
macOS系列：
├── macOS 10.15 (Catalina)
├── macOS 11 (Big Sur)
├── macOS 12 (Monterey)
├── macOS 13 (Ventura)
├── macOS 14 (Sonoma)
└── macOS 15 (Sequoia)
移动平台：
├── Android 4.0+（内置sqlite）
├── iOS 5.0+（内置sqlite）
└── 其他移动操作系统
嵌入式平台：
├── ARM架构处理器
├── MIPS架构处理器
├── RISC-V架构处理器
└── 各种物联网设备
2.2.2 sqlite数据库运行平台特性
平台 文件系统 默认路径 特殊配置
────────────────────────────────────────────────────────────────
Linux ext4/xfs /sqlite/fgdata 支持WAL模式
Windows NTFS C:\sqlite\fgdata 需注意路径分隔符
macOS APFS /sqlite/fgdata 类Unix特性
Android ext4/f2fs /data/data/包名/ 内置sqlite
iOS APFS 应用沙盒目录 内置sqlite
2.3 sqlite数据库生产环境规划建议
在生产环境中部署sqlite数据库，需要合理规划目录结构、文件权限和配置参数。学习交流加群风哥微信: itpux-com
2.3.1 sqlite数据库目录规划
安装目录：/sqlite/app
├── bin/ # 可执行文件目录
│ └── sqlite3 # 命令行工具
├── lib/ # 库文件目录
│ └── libsqlite3.so
├── include/ # 头文件目录
│ └── sqlite3.h
└── doc/ # 文档目录
数据目录：/sqlite/fgdata
├── fgedudb.db # 主数据库文件
├── fgedudb.db-wal # WAL日志文件
├── fgedudb.db-shm # 共享内存文件
└── backup/ # 备份目录
└── fgedudb_backup.db
日志目录：/sqlite/logs
├── sqlite.log # 应用日志
└── error.log # 错误日志
脚本目录：/sqlite/scripts
├── backup.sh # 备份脚本
├── check.sh # 检查脚本
└── maintenance.sh # 维护脚本
2.3.2 sqlite数据库文件权限规划
创建sqlite用户和组：
$ groupadd -g 2000 sqlite
$ groupadd: group ‘sqlite’ already exists
$ useradd -u 2000 -g sqlite -d /sqlite -s /bin/bash sqlite
$ id sqlite
uid=2000(sqlite) gid=2000(sqlite) groups=2000(sqlite)
创建目录并设置权限：
$ mkdir -p /sqlite/app /sqlite/fgdata /sqlite/logs /sqlite/scripts
$ chown -R sqlite:sqlite /sqlite
$ chmod -R 755 /sqlite
$ chmod 700 /sqlite/fgdata
数据库文件权限设置：
$ chmod 644 /sqlite/fgdata/fgedudb.db
$ ls -l /sqlite/fgdata/fgedudb.db
-rw-r–r– 1 sqlite sqlite 5242880 Apr 8 10:00 /sqlite/fgdata/fgedudb.db
Part03-生产环境项目实施方案
3.1 sqlite数据库环境检查与准备
在部署sqlite数据库之前，需要对系统环境进行检查和准备。
3.1.1 检查操作系统信息
$ cat /etc/os-release
NAME=”Oracle Linux Server”
VERSION=”9.3″
ID=”ol”
ID_LIKE=”fedora”
VERSION_ID=”9.3″
PLATFORM_ID=”platform:el9″
PRETTY_NAME=”Oracle Linux Server 9.3″
检查系统架构：
$ uname -m
x86_64
检查内核版本：
$ uname -r
5.15.0-200.131.27.el9uek.x86_64
检查内存信息：
$ free -h
total used free shared buff/cache available
Mem: 15Gi 2.0Gi 10Gi 200Mi 3.0Gi 12Gi
Swap: 8.0Gi 0B 8.0Gi
检查磁盘空间：
$ df -h /sqlite
Filesystem Size Used Avail Use% Mounted on
/dev/sdb1 100G 5.0G 95G 5% /sqlite
3.1.2 检查系统依赖
$ rpm -qa | grep -E “gcc|make|readline”
gcc-11.3.1-4.3.0.1.el9.x86_64
make-4.3-7.el9.x86_64
readline-devel-8.1-4.el9.x86_64
检查系统库：
$ ldconfig -p | grep sqlite
libsqlite3.so.0 (libc6,x86-64) => /lib64/libsqlite3.so.0
检查系统参数：
$ cat /proc/sys/fs/file-max
6815744
$ ulimit -n
65535
3.2 sqlite数据库版本选择与获取
选择合适的sqlite版本对于生产环境至关重要。更多学习教程公众号风哥教程itpux_com
3.2.1 sqlite数据库版本说明
版本号格式：3.XX.YY
– 3：主版本号（长期保持为3）
– XX：次版本号（偶数为稳定版，奇数为开发版）
– YY：发布号
示例：
– 3.45.0：稳定版本
– 3.45.1：稳定版本（bug修复）
– 3.46.0：稳定版本（新功能）
当前最新稳定版本查询：
$ curl -s https://www.sqlite.org/download.html | grep -o “sqlite-autoconf-[0-9]*” | head -1
sqlite-autoconf-3450200
3.2.2 sqlite数据库下载与安装
$ cd /backup
$ wget https://www.sqlite.org/2024/sqlite-autoconf-3450200.tar.gz
–2024-04-08 10:00:00– https://www.sqlite.org/2024/sqlite-autoconf-3450200.tar.gz
Resolving www.sqlite.org… 45.33.254.241
Connecting to www.sqlite.org|45.33.254.241|:443… connected.
HTTP request sent, awaiting response… 200 OK
Length: 3091961 (2.9M) [application/x-gzip]
Saving to: ‘sqlite-autoconf-3450200.tar.gz’
sqlite-autoconf-3450200.tar.gz 100%[===================================================================>] 2.95M 1.02MB/s in 2.9s
2024-04-08 10:00:03 (1.02 MB/s) – ‘sqlite-autoconf-3450200.tar.gz’ saved [3091961/3091961]
解压源码包：
$ tar -xzf sqlite-autoconf-3450200.tar.gz
$ cd sqlite-autoconf-3450200
$ ls -l
total 3200
-rw-r–r– 1 1000 1000 11357 Jan 1 2024 INSTALL
-rw-r–r– 1 1000 1000 35147 Jan 1 2024 Makefile.am
-rw-r–r– 1 1000 1000 41820 Jan 1 2024 Makefile.in
-rw-r–r– 1 1000 1000 1086 Jan 1 2024 README.txt
-rw-r–r– 1 1000 1000 69993 Jan 1 2024 aclocal.m4
-rwxr-xr-x 1 1000 1000 147944 Jan 1 2024 configure
-rw-r–r– 1 1000 1000 4181 Jan 1 2024 configure.ac
-rw-r–r– 1 1000 1000 266979 Jan 1 2024 shell.c
-rw-r–r– 1 1000 1000 2519392 Jan 1 2024 sqlite3.c
-rw-r–r– 1 1000 1000 629977 Jan 1 2024 sqlite3.h
-rw-r–r– 1 1000 1000 31281 Jan 1 2024 sqlite3ext.h
3.2.3 sqlite数据库编译安装
$ ./configure –prefix=/sqlite/app –enable-static –enable-shared
checking build system type… x86_64-pc-linux-gnu
checking host system type… x86_64-pc-linux-gnu
checking for gcc… gcc
checking whether the C compiler works… yes
checking for C compiler default output file name… a.out
checking for suffix of executables…
checking whether we are cross compiling… no
checking for suffix of object files… o
checking whether the compiler supports GNU C… yes
checking whether gcc accepts -g… yes
checking for gcc option to enable C11 features… none needed
checking for a sed that does not truncate output… /usr/bin/sed
checking for grep that handles long lines and -e… /usr/bin/grep
checking for egrep… /usr/bin/grep -E
…
config.status: creating Makefile
config.status: creating sqlite3.pc
编译sqlite：
$ make -j$(nproc)
gcc -DPACKAGE_NAME=\”sqlite\” -DPACKAGE_TARNAME=\”sqlite\” -DPACKAGE_VERSION=\”3.45.2\” \
-DPACKAGE_STRING=\”sqlite\ 3.45.2\” -DPACKAGE_BUGREPORT=\”\” -DPACKAGE_URL=\”\” \
-DPACKAGE=\”sqlite\” -DVERSION=\”3.45.2\” -DSTDC_HEADERS=1 -DHAVE_SYS_TYPES_H=1 \
-DHAVE_SYS_STAT_H=1 -DHAVE_STDLIB_H=1 -DHAVE_STRING_H=1 -DHAVE_MEMORY_H=1 \
…
gcc -g -O2 -o sqlite3 shell.c sqlite3.c -lpthread -ldl -lm
安装sqlite：
$ make install
make[1]: Entering directory ‘/backup/sqlite-autoconf-3450200’
/usr/bin/mkdir -p ‘/sqlite/app/bin’
/usr/bin/install -c sqlite3 ‘/sqlite/app/bin’
/usr/bin/mkdir -p ‘/sqlite/app/include’
/usr/bin/install -c -m 644 sqlite3.h sqlite3ext.h ‘/sqlite/app/include’
/usr/bin/mkdir -p ‘/sqlite/app/lib’
/usr/bin/install -c -m 644 libsqlite3.a libsqlite3.la ‘/sqlite/app/lib’
/usr/bin/mkdir -p ‘/sqlite/app/lib/pkgconfig’
/usr/bin/install -c -m 644 sqlite3.pc ‘/sqlite/app/lib/pkgconfig’
make[1]: Leaving directory ‘/backup/sqlite-autoconf-3450200’
3.2.4 sqlite数据库安装验证
$ /sqlite/app/bin/sqlite3 –version
sqlite 3.45.2 2024-03-12 11:06:23 d8cd6d49b46a395b13955387d05e9e1a8a87e8ed7996bbd593f5c8b1cde0bd9a
设置环境变量：
$ echo ‘export PATH=/sqlite/app/bin:$PATH’ >> ~/.bashrc
$ echo ‘export LD_LIBRARY_PATH=/sqlite/app/lib:$LD_LIBRARY_PATH’ >> ~/.bashrc
$ source ~/.bashrc
验证环境变量：
$ which sqlite3
/sqlite/app/bin/sqlite3
$ sqlite3 –version
sqlite 3.45.2 2024-03-12 11:06:23 d8cd6d49b46a395b13955387d05e9e1a8a87e8ed7996bbd593f5c8b1cde0bd9a
3.3 sqlite数据库架构验证实战
安装完成后，通过实际操作验证sqlite数据库的架构和特性。from sqlite视频:www.itpux.com
3.3.1 创建测试数据库
$ mkdir -p /sqlite/fgdata
$ cd /sqlite/fgdata
创建测试数据库：
$ sqlite3 fgedudb.db
SQLite version 3.45.2 2024-03-12 11:06:23
Enter “.help” for usage hints.
sqlite>
查看数据库信息：
sqlite> .databases
main: /sqlite/fgdata/fgedudb.db r/w
查看sqlite版本：
sqlite> SELECT sqlite_version();
sqlite_version()
—————-
3.45.2
查看编译选项：
sqlite> SELECT * FROM pragma_compile_options() LIMIT 10;
compile_options
—————-
ATOMIC_INTRINSICS=1
BINDATA=1
BUG_COMPATIBLE_20160819=1
CASE_SENSITIVE_LIKE
COMPILER=gcc-11.3.1
DEFAULT_AUTOVACUUM=0
DEFAULT_CACHE_SIZE=-2000
DEFAULT_FILE_FORMAT=4
DEFAULT_JOURNAL_SIZE_LIMIT=-1
DEFAULT_MEMSTATUS=1
3.3.2 验证sqlite架构组件
sqlite> CREATE TABLE fgedu_users (
…> id INTEGER PRIMARY KEY AUTOINCREMENT,
…> username TEXT NOT NULL,
…> email TEXT UNIQUE,
…> created_at DATETIME DEFAULT CURRENT_TIMESTAMP
…> );
插入测试数据：
sqlite> INSERT INTO fgedu_users (username, email) VALUES (‘fgedu01’, ‘fgedu01@fgedu.net.cn’);
sqlite> INSERT INTO fgedu_users (username, email) VALUES (‘fgedu02’, ‘fgedu02@fgedu.net.cn’);
sqlite> INSERT INTO fgedu_users (username, email) VALUES (‘fgedu03’, ‘fgedu03@fgedu.net.cn’);
查询数据：
sqlite> SELECT * FROM fgedu_users;
id username email created_at
— ——– ——————- ——————-
1 fgedu01 fgedu01@fgedu.net.cn 2024-04-08 10:05:00
2 fgedu02 fgedu02@fgedu.net.cn 2024-04-08 10:05:01
3 fgedu03 fgedu03@fgedu.net.cn 2024-04-08 10:05:02
查看表结构：
sqlite> .schema fgedu_users
CREATE TABLE fgedu_users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
username TEXT NOT NULL,
email TEXT UNIQUE,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
查看执行计划：
sqlite> EXPLAIN QUERY PLAN SELECT * FROM fgedu_users WHERE id = 1;
QUERY PLAN
`–SEARCH fgedu_users USING INTEGER PRIMARY KEY (rowid=?)
3.3.3 验证sqlite核心特性
sqlite> BEGIN TRANSACTION;
sqlite> INSERT INTO fgedu_users (username, email) VALUES (‘fgedu04’, ‘fgedu04@fgedu.net.cn’);
sqlite> ROLLBACK;
sqlite> SELECT COUNT(*) FROM fgedu_users;
COUNT(*)
——–
3
验证ACID特性：
sqlite> PRAGMA journal_mode;
journal_mode
————
delete
sqlite> PRAGMA synchronous;
synchronous
———–
2
验证WAL模式：
sqlite> PRAGMA journal_mode=WAL;
journal_mode
————
wal
sqlite> .exit
查看数据库文件：
$ ls -la /sqlite/fgdata/
total 2048
drwxr-xr-x 2 sqlite sqlite 4096 Apr 8 10:06 .
drwxr-xr-x 5 sqlite sqlite 4096 Apr 8 10:00 ..
-rw-r–r– 1 sqlite sqlite 5242880 Apr 8 10:06 fgedudb.db
-rw-r–r– 1 sqlite sqlite 32768 Apr 8 10:06 fgedudb.db-wal
-rw-r–r– 1 sqlite sqlite 32768 Apr 8 10:06 fgedudb.db-shm
Part04-生产案例与实战讲解
4.1 sqlite数据库嵌入式应用案例
sqlite在嵌入式系统中应用广泛，以下是典型的嵌入式应用案例。
4.1.1 物联网设备数据采集案例
sqlite> CREATE TABLE fgedu_sensor_data (
…> id INTEGER PRIMARY KEY AUTOINCREMENT,
…> device_id TEXT NOT NULL,
…> sensor_type TEXT NOT NULL,
…> sensor_value REAL,
…> unit TEXT,
…> collected_at DATETIME DEFAULT CURRENT_TIMESTAMP,
…> uploaded INTEGER DEFAULT 0
…> );
创建索引优化查询：
sqlite> CREATE INDEX idx_device_time ON fgedu_sensor_data(device_id, collected_at);
sqlite> CREATE INDEX idx_uploaded ON fgedu_sensor_data(uploaded);
插入模拟数据：
sqlite> INSERT INTO fgedu_sensor_data (device_id, sensor_type, sensor_value, unit)
…> VALUES (‘DEV001’, ‘temperature’, 25.5, ‘C’);
sqlite> INSERT INTO fgedu_sensor_data (device_id, sensor_type, sensor_value, unit)
…> VALUES (‘DEV001’, ‘humidity’, 65.0, ‘%’);
sqlite> INSERT INTO fgedu_sensor_data (device_id, sensor_type, sensor_value, unit)
…> VALUES (‘DEV002’, ‘temperature’, 23.8, ‘C’);
查询未上传数据：
sqlite> SELECT * FROM fgedu_sensor_data WHERE uploaded = 0;
id device_id sensor_type sensor_value unit collected_at uploaded
— ——— ———– ———— —- ——————- ——–
1 DEV001 temperature 25.5 C 2024-04-08 10:10:00 0
2 DEV001 humidity 65.0 % 2024-04-08 10:10:01 0
3 DEV002 temperature 23.8 C 2024-04-08 10:10:02 0
统计设备数据量：
sqlite> SELECT device_id, COUNT(*) as count FROM fgedu_sensor_data GROUP BY device_id;
device_id count
——— —–
DEV001 2
DEV002 1
4.2 sqlite数据库Web应用案例
sqlite适合中小型Web应用的数据存储，以下是Web应用案例。更多视频教程www.fgedu.net.cn
4.2.1 网站用户管理案例
sqlite> CREATE TABLE fgedu_web_users (
…> id INTEGER PRIMARY KEY AUTOINCREMENT,
…> username TEXT UNIQUE NOT NULL,
…> password_hash TEXT NOT NULL,
…> email TEXT UNIQUE,
…> status INTEGER DEFAULT 1,
…> created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
…> last_login DATETIME
…> );
创建会话表：
sqlite> CREATE TABLE fgedu_sessions (
…> session_id TEXT PRIMARY KEY,
…> user_id INTEGER NOT NULL,
…> ip_address TEXT,
…> created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
…> expires_at DATETIME,
…> FOREIGN KEY (user_id) REFERENCES fgedu_web_users(id)
…> );
创建操作日志表：
sqlite> CREATE TABLE fgedu_audit_log (
…> id INTEGER PRIMARY KEY AUTOINCREMENT,
…> user_id INTEGER,
…> action TEXT NOT NULL,
…> details TEXT,
…> ip_address TEXT,
…> created_at DATETIME DEFAULT CURRENT_TIMESTAMP
…> );
插入用户数据：
sqlite> INSERT INTO fgedu_web_users (username, password_hash, email)
…> VALUES (‘admin’, ‘hash_admin_123’, ‘admin@fgedu.net.cn’);
sqlite> INSERT INTO fgedu_web_users (username, password_hash, email)
…> VALUES (‘user01’, ‘hash_user01_456’, ‘user01@fgedu.net.cn’);
查询活跃用户：
sqlite> SELECT id, username, email, last_login FROM fgedu_web_users WHERE status = 1;
id username email last_login
— ——– —————— ———-
1 admin admin@fgedu.net.cn
2 user01 user01@fgedu.net.cn
4.3 sqlite数据库移动端应用案例
sqlite是Android和iOS平台的内置数据库，以下是移动端应用案例。学习交流加群风哥微信: itpux-com
4.3.1 移动应用本地缓存案例
sqlite> CREATE TABLE fgedu_cache_data (
…> cache_key TEXT PRIMARY KEY,
…> cache_value TEXT,
…> content_type TEXT,
…> created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
…> expires_at DATETIME
…> );
创建离线数据表：
sqlite> CREATE TABLE fgedu_offline_data (
…> id INTEGER PRIMARY KEY AUTOINCREMENT,
…> data_type TEXT NOT NULL,
…> data_content TEXT,
…> sync_status INTEGER DEFAULT 0,
…> created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
…> synced_at DATETIME
…> );
插入缓存数据：
sqlite> INSERT INTO fgedu_cache_data (cache_key, cache_value, content_type, expires_at)
…> VALUES (‘user_profile_001’, ‘{“name”:”fgedu”,”age”:30}’, ‘json’,
…> datetime(‘now’, ‘+1 hour’));
查询有效缓存：
sqlite> SELECT cache_key, cache_value FROM fgedu_cache_data
…> WHERE expires_at > datetime(‘now’);
cache_key cache_value content_type
————— ————————- ————
user_profile_001 {“name”:”fgedu”,”age”:30} json
清理过期缓存：
sqlite> DELETE FROM fgedu_cache_data WHERE expires_at < datetime('now');
sqlite> SELECT changes();
changes()
——–
0
Part05-风哥经验总结与分享
5.1 sqlite数据库最佳实践建议
基于多年的数据库管理经验，以下是sqlite数据库的最佳实践建议。更多学习教程公众号风哥教程itpux_com
5.1.1 生产环境配置建议
1. 启用WAL模式
PRAGMA journal_mode=WAL;
2. 设置同步级别
PRAGMA synchronous=NORMAL;
3. 配置缓存大小
PRAGMA cache_size=-64000; # 64MB
4. 设置忙等待超时
PRAGMA busy_timeout=30000; # 30秒
5. 启用外键约束
PRAGMA foreign_keys=ON;
6. 设置临时文件位置
PRAGMA temp_store=MEMORY;
7. 配置页面大小（创建数据库时）
PRAGMA page_size=4096;
5.1.2 性能优化建议
1. 合理使用索引
– 为常用查询条件创建索引
– 避免过度索引
– 使用EXPLAIN QUERY PLAN分析查询
2. 批量操作优化
– 使用事务包装批量操作
– 使用预编译语句
– 避免频繁的磁盘写入
3. 数据库文件优化
– 定期执行VACUUM整理碎片
– 使用ANALYZE更新统计信息
– 合理设置页面大小
4. 并发控制优化
– 使用WAL模式提升并发
– 设置合理的busy_timeout
– 控制事务持续时间
5.2 sqlite数据库架构设计要点
sqlite数据库架构设计需要考虑数据规模、访问模式和扩展需求。from sqlite视频:www.itpux.com
5.2.1 数据库文件规划
单数据库文件适用场景：
├── 数据量 < 100GB
├── 单一应用访问
├── 简单数据模型
└── 便于备份迁移
多数据库文件适用场景：
├── 数据量 > 100GB
├── 多租户架构
├── 数据分片需求
└── 独立备份需求
示例：多租户数据库架构
/sqlite/fgdata/
├── tenant_001.db
├── tenant_002.db
├── tenant_003.db
└── common.db
5.2.2 表结构设计规范
1. 主键设计
– 使用INTEGER PRIMARY KEY
– 利用ROWID机制
– 避免使用UUID作为主键
2. 字段类型选择
– TEXT：字符串、日期时间
– INTEGER：整数、布尔值
– REAL：浮点数
– BLOB：二进制数据
3. 约束设计
– 使用NOT NULL约束
– 合理使用UNIQUE约束
– 启用外键约束
4. 索引设计
– 主键自动创建索引
– 为外键创建索引
– 为常用查询条件创建索引
5.3 sqlite数据库学习路线总结
sqlite数据库学习需要循序渐进，以下是完整的学习路线总结。学习交流加群风哥QQ113257174
5.3.1 学习阶段划分
第一阶段：基础入门（1-2周）
├── sqlite概述与特点
├── 安装与命令行工具
├── SQL基础语法
├── 数据类型与表设计
└── 基础CRUD操作
第二阶段：进阶应用（2-3周）
├── 事务与并发控制
├── 索引与查询优化
├── PRAGMA参数调优
├── 备份与恢复
└── 数据导入导出
第三阶段：高级特性（3-4周）
├── FTS全文检索
├── JSON数据处理
├── 触发器与视图
├── CTE与窗口函数
└── 虚拟表扩展
第四阶段：开发集成（2-3周）
├── C/C++ API编程
├── Python集成开发
├── Java集成开发
└── 性能优化实践
第五阶段：生产实战（持续）
├── 架构设计实践
├── 性能调优实战
├── 故障处理经验
└── 最佳实践总结
5.3.2 推荐学习资源
官方资源：
├── 官方文档：https://www.sqlite.org/docs.html
├── 官方教程：https://www.sqlite.org/lang.html
└── 官方FAQ：https://www.sqlite.org/faq.html
视频教程：
├── 风哥sqlite教程系列
├── 更多视频教程www.fgedu.net.cn
└── 公众号：风哥教程itpux_com
交流学习：
├── 风哥微信：itpux-com
├── 风哥QQ：113257174
└── 技术交流群
本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html
联系我们
在线咨询：
微信号：itpux-com
工作日：9:30-18:30，节假日休息
