Sqlite
SQLite 是一个开源软件库，它实现了独立、无服务器、零配置、事务性 SQL 数据库引擎。SQLite 是世界上部署最广泛的 SQL 数据库引擎。
查看更多相关内容
SQLite 的连接池如何实现？SQLite 的连接池对于提高应用性能很重要：
1. **连接池的概念**
- SQLite 本身不提供内置的连接池
- 连接池需要在应用层实现
- 目的是重用数据库连接，减少创建和销毁连接的开销
2. **连接池的优势**
- **提高性能**：避免频繁创建和销毁连接
- **减少资源消耗**：降低内存和 CPU 使用
- **提高并发能力**：管理有限数量的连接
- **统一配置**：集中管理连接参数
3. **实现连接池的要点**
- 维护一个连接队列
- 提供获取和释放连接的方法
- 处理连接超时和错误
- 管理连接的最大数量
4. **Python 连接池示例**
```python
import sqlite3
from queue import Queue
class SQLiteConnectionPool:
def __init__(self, db_path, max_connections=5):
self.db_path = db_path
self.max_connections = max_connections
self.pool = Queue(max_connections)
for _ in range(max_connections):
self.pool.put(sqlite3.connect(db_path, check_same_thread=False))
def get_connection(self):
return self.pool.get()
def return_connection(self, conn):
self.pool.put(conn)
def close_all(self):
while not self.pool.empty():
conn = self.pool.get()
conn.close()
```
5. **连接池配置参数**
- 最大连接数：根据应用并发需求设置
- 最小空闲连接数：保持一定数量的空闲连接
- 连接超时时间：获取连接的最大等待时间
- 连接最大生命周期：定期刷新连接
6. **使用连接池的最佳实践**
- 使用后及时释放连接
- 使用 try-finally 确保连接释放
- 处理连接异常情况
- 定期检查连接有效性
- 监控连接池使用情况
7. **连接池的挑战**
- 线程安全：确保多线程环境下的安全访问
- 连接泄漏：防止连接未正确释放
- 连接过期：处理长时间未使用的连接
- 资源限制：避免创建过多连接
8. **第三方库支持**
- 各语言都有成熟的连接池实现
- Python：SQLAlchemy、DBUtils
- Java：HikariCP、Apache DBCP
- Node.js：generic-pool
连接池是提高 SQLite 应用性能的重要技术手段。
服务端 · 2月18日 22:01
SQLite 支持哪些约束类型？SQLite 的约束机制确保数据的完整性和一致性：
1. **NOT NULL 约束**
- 确保列不接受 NULL 值
- 插入或更新时如果违反约束会报错
```sql
CREATE TABLE users (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL
);
```
2. **UNIQUE 约束**
- 确保列中的所有值都是唯一的
- 可以应用于单列或多列组合
- 自动创建索引以提高性能
```sql
CREATE TABLE users (
id INTEGER PRIMARY KEY,
email TEXT UNIQUE
);
```
3. **PRIMARY KEY 约束**
- 唯一标识表中的每一行
- 自动创建 NOT NULL 和 UNIQUE 约束
- 可以是单列或复合主键
```sql
CREATE TABLE users (
id INTEGER PRIMARY KEY,
username TEXT
);
```
4. **FOREIGN KEY 约束**
- 建立表之间的关系
- 确保引用的完整性
- 需要启用外键约束：`PRAGMA foreign_keys = ON;`
```sql
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
user_id INTEGER,
FOREIGN KEY (user_id) REFERENCES users(id)
);
```
5. **CHECK 约束**
- 定义列值必须满足的条件
- 可以包含复杂的表达式
```sql
CREATE TABLE products (
id INTEGER PRIMARY KEY,
price REAL CHECK(price > 0),
quantity INTEGER CHECK(quantity >= 0)
);
```
6. **DEFAULT 约束**
- 为列指定默认值
- 当插入时未提供值时使用默认值
```sql
CREATE TABLE users (
id INTEGER PRIMARY KEY,
status TEXT DEFAULT 'active',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
```
7. **约束冲突处理**
- 使用 ON CONFLICT 子句处理约束冲突
- 可选值：ROLLBACK、ABORT、FAIL、IGNORE、REPLACE
```sql
INSERT OR REPLACE INTO users (id, name) VALUES (1, 'John');
```
合理使用约束可以确保数据质量，防止无效数据的插入和更新。
服务端 · 2月18日 21:57
SQLite 如何处理并发访问？SQLite 的并发控制机制是其核心特性之一：
1. **锁级别**
SQLite 使用不同级别的锁来管理并发访问：
- **UNLOCKED**：未锁定状态
- **SHARED**：共享锁，允许多个读操作同时进行
- **RESERVED**：预留锁，表示准备写入，但仍允许读操作
- **PENDING**：等待锁，阻止新的读操作，等待现有读操作完成
- **EXCLUSIVE**：排他锁，独占访问，阻止所有其他操作
2. **读写并发模型**
- **多读单写**：SQLite 支持多个读操作同时进行，但同一时间只允许一个写操作
- 读操作不会阻塞其他读操作
- 写操作需要获得排他锁，会阻塞所有其他操作
3. **事务隔离模式**
```sql
-- DEFERRED（默认）：第一次读操作时获取共享锁，第一次写操作时升级为排他锁
BEGIN DEFERRED TRANSACTION;
-- IMMEDIATE：立即获取预留锁，防止其他写操作
BEGIN IMMEDIATE TRANSACTION;
-- EXCLUSIVE：立即获取排他锁，阻止所有其他操作
BEGIN EXCLUSIVE TRANSACTION;
```
4. **WAL 模式（Write-Ahead Logging）**
- WAL 模式显著提高了并发性能
- 读操作不会阻塞写操作，写操作不会阻塞读操作
- 写操作将更改写入 WAL 文件，而不是直接修改数据库文件
- 检查点（Checkpoint）时将 WAL 文件的内容合并到主数据库文件
5. **死锁处理**
- SQLite 会自动检测死锁并回滚其中一个事务
- 应用程序应捕获 `SQLITE_BUSY` 错误并重试
- 使用 `sqlite3_busy_timeout()` 设置忙等待超时
6. **并发优化建议**
- 使用 WAL 模式提高并发性能
- 保持事务简短，减少锁持有时间
- 将读操作和写操作分离到不同的事务中
- 使用适当的隔离模式避免不必要的阻塞
SQLite 的并发控制机制在保证数据一致性的同时，提供了合理的并发性能，适合中小型应用的并发需求。
服务端 · 2月18日 21:51
SQLite 的窗口函数（Window Functions）如何使用？SQLite 的窗口函数（Window Functions）提供了强大的数据分析能力：
1. **窗口函数的概念**
- 窗口函数对一组行执行计算，但不会将多行合并为一行
- 类似于聚合函数，但保留了原始行的详细信息
- SQLite 3.25.0+ 支持窗口函数
2. **窗口函数语法**
```sql
function_name(expression) OVER (
[PARTITION BY partition_expression]
[ORDER BY sort_expression]
[frame_clause]
)
```
3. **常用窗口函数**
- **聚合函数**：SUM()、AVG()、COUNT()、MAX()、MIN()
- **排名函数**：ROW_NUMBER()、RANK()、DENSE_RANK()
- **偏移函数**：LAG()、LEAD()
- **分析函数**：FIRST_VALUE()、LAST_VALUE()
4. **排名函数示例**
```sql
-- ROW_NUMBER：连续排名
SELECT
name,
score,
ROW_NUMBER() OVER (ORDER BY score DESC) as rank_num
FROM students;
-- RANK：相同分数相同排名，跳过后续排名
SELECT
name,
score,
RANK() OVER (ORDER BY score DESC) as rank_num
FROM students;
-- DENSE_RANK：相同分数相同排名，不跳过后续排名
SELECT
name,
score,
DENSE_RANK() OVER (ORDER BY score DESC) as rank_num
FROM students;
```
5. **偏移函数示例**
```sql
-- LAG：获取前一行的值
SELECT
date,
sales,
LAG(sales) OVER (ORDER BY date) as prev_sales,
sales - LAG(sales) OVER (ORDER BY date) as growth
FROM sales_data;
-- LEAD：获取后一行的值
SELECT
date,
sales,
LEAD(sales) OVER (ORDER BY date) as next_sales
FROM sales_data;
```
6. **分区示例**
```sql
-- 按部门分区，计算每个部门内的排名
SELECT
department,
name,
salary,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) as dept_rank
FROM employees;
-- 计算每个部门的平均工资
SELECT
department,
name,
salary,
AVG(salary) OVER (PARTITION BY department) as dept_avg_salary
FROM employees;
```
7. **窗口帧（Frame）示例**
```sql
-- 计算移动平均（3天）
SELECT
date,
sales,
AVG(sales) OVER (
ORDER BY date
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) as moving_avg
FROM sales_data;
-- 计算累计总和
SELECT
date,
sales,
SUM(sales) OVER (
ORDER BY date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) as running_total
FROM sales_data;
```
8. **实际应用场景**
```sql
-- 计算销售额排名
SELECT
product_name,
sales_amount,
RANK() OVER (ORDER BY sales_amount DESC) as sales_rank
FROM sales;
-- 计算同比增长
SELECT
year,
month,
revenue,
LAG(revenue) OVER (ORDER BY year, month) as prev_revenue,
(revenue - LAG(revenue) OVER (ORDER BY year, month)) /
LAG(revenue) OVER (ORDER BY year, month) * 100 as growth_rate
FROM monthly_revenue;
-- 找出每个类别的前3名产品
WITH ranked_products AS (
SELECT
category,
product_name,
sales,
RANK() OVER (PARTITION BY category ORDER BY sales DESC) as rank_num
FROM products
)
SELECT * FROM ranked_products WHERE rank_num <= 3;
```
9. **窗口帧类型**
- **ROWS**：基于物理行数
- **RANGE**：基于值范围
- **GROUPS**：基于分组
10. **性能优化**
- 为窗口函数的 ORDER BY 和 PARTITION BY 列创建索引
- 避免在窗口函数中使用复杂表达式
- 考虑使用子查询或 CTE 优化复杂窗口函数
窗口函数是 SQLite 进行复杂数据分析的重要工具。
服务端 · 2月18日 21:51
SQLite 的扩展机制如何使用？SQLite 的扩展机制允许开发者添加自定义功能：
1. **扩展概述**
- SQLite 支持通过扩展添加自定义函数、聚合函数、虚拟表等
- 扩展可以动态加载或静态链接
- 扩展使用 C/C++ 编写，遵循 SQLite 的 API 规范
2. **加载扩展**
```sql
-- 启用扩展加载
PRAGMA load_extension = ON;
-- 加载扩展
SELECT load_extension('extension_path');
-- 加载内置扩展（如 FTS5）
SELECT load_extension('sqlite3ext');
```
3. **创建标量函数**
```c
// C 代码示例：创建自定义函数
#include <sqlite3ext.h>
SQLITE_EXTENSION_INIT1
static void my_function(sqlite3_context *context, int argc, sqlite3_value **argv) {
if (argc != 1) {
sqlite3_result_error(context, "Invalid argument count", -1);
return;
}
const char *text = (const char *)sqlite3_value_text(argv[0]);
// 处理逻辑...
sqlite3_result_text(context, "result", -1, SQLITE_TRANSIENT);
}
#ifdef _WIN32
__declspec(dllexport)
#endif
int sqlite3_myextension_init(sqlite3 *db, char **pzErrMsg, const sqlite3_api_routines *pApi) {
SQLITE_EXTENSION_INIT2(pApi);
sqlite3_create_function(db, "my_function", 1, SQLITE_UTF8, NULL, my_function, NULL, NULL);
return SQLITE_OK;
}
```
4. **创建聚合函数**
```c
// C 代码示例：创建自定义聚合函数
typedef struct {
double sum;
int count;
} AvgCtx;
static void avg_step(sqlite3_context *context, int argc, sqlite3_value **argv) {
AvgCtx *p = (AvgCtx *)sqlite3_aggregate_context(context, sizeof(AvgCtx));
if (p->count == 0) {
p->sum = 0;
}
p->sum += sqlite3_value_double(argv[0]);
p->count++;
}
static void avg_final(sqlite3_context *context) {
AvgCtx *p = (AvgCtx *)sqlite3_aggregate_context(context, sizeof(AvgCtx));
if (p->count > 0) {
sqlite3_result_double(context, p->sum / p->count);
} else {
sqlite3_result_null(context);
}
}
int sqlite3_myextension_init(sqlite3 *db, char **pzErrMsg, const sqlite3_api_routines *pApi) {
SQLITE_EXTENSION_INIT2(pApi);
sqlite3_create_function(db, "my_avg", 1, SQLITE_UTF8, NULL, NULL, avg_step, avg_final);
return SQLITE_OK;
}
```
5. **创建虚拟表**
```c
// C 代码示例：创建虚拟表模块
typedef struct MyVtab MyVtab;
struct MyVtab {
sqlite3_vtab base;
// 自定义数据...
};
static int myCreate(sqlite3 *db, void *pAux, int argc, const char *const *argv, sqlite3_vtab **ppVtab, char **pzErr) {
MyVtab *pVtab = (MyVtab *)sqlite3_malloc(sizeof(MyVtab));
// 初始化虚拟表...
*ppVtab = (sqlite3_vtab *)pVtab;
return SQLITE_OK;
}
static int myConnect(sqlite3 *db, void *pAux, int argc, const char *const *argv, sqlite3_vtab **ppVtab, char **pzErr) {
// 连接虚拟表...
return SQLITE_OK;
}
static int myBestIndex(sqlite3_vtab *pVtab, sqlite3_index_info *pIdxInfo) {
// 优化查询计划...
return SQLITE_OK;
}
static int myFilter(sqlite3_vtab_cursor *pCursor, int idxNum, const char *idxStr, int argc, sqlite3_value **argv) {
// 过滤数据...
return SQLITE_OK;
}
static int myNext(sqlite3_vtab_cursor *pCursor) {
// 移动到下一行...
return SQLITE_OK;
}
static int myEof(sqlite3_vtab_cursor *pCursor) {
// 检查是否到达末尾...
return 0;
}
static int myColumn(sqlite3_vtab_cursor *pCursor, sqlite3_context *context, int i) {
// 返回列值...
return SQLITE_OK;
}
static int myRowid(sqlite3_vtab_cursor *pCursor, sqlite3_int64 *pRowid) {
// 返回行 ID...
return SQLITE_OK;
}
static sqlite3_module myModule = {
.iVersion = 0,
.xCreate = myCreate,
.xConnect = myConnect,
.xBestIndex = myBestIndex,
.xDisconnect = myDisconnect,
.xDestroy = myDestroy,
.xOpen = myOpen,
.xClose = myClose,
.xFilter = myFilter,
.xNext = myNext,
.xEof = myEof,
.xColumn = myColumn,
.xRowid = myRowid
};
int sqlite3_myextension_init(sqlite3 *db, char **pzErrMsg, const sqlite3_api_routines *pApi) {
SQLITE_EXTENSION_INIT2(pApi);
sqlite3_create_module(db, "my_module", &myModule, 0);
return SQLITE_OK;
}
```
6. **常用内置扩展**
- **FTS3/FTS4/FTS5**：全文搜索
- **RTREE**：空间索引
- **STAT4**：查询优化统计
- **JSON1**：JSON 支持
- **GEOPOLY**：地理多边形
7. **扩展的应用场景**
- 自定义业务逻辑函数
- 集成第三方库
- 实现特殊数据类型
- 优化特定查询模式
- 添加加密支持
8. **扩展开发注意事项**
- 遵循 SQLite 的内存管理规则
- 正确处理错误情况
- 确保线程安全
- 提供完整的文档
- 进行充分的测试
9. **跨语言扩展**
- Python：使用 sqlite3.create_function()
- JavaScript：使用 Database.createFunction()
- Java：使用 Function.create()
- Go：使用 RegisterFunc()
SQLite 的扩展机制提供了强大的扩展能力，使开发者能够根据需求定制数据库功能。
服务端 · 2月18日 21:51
SQLite 的生成列（Generated Columns）如何使用？SQLite 的生成列（Generated Columns）提供了一种自动计算列值的方式：
1. **生成列的概念**
- 生成列的值由表达式自动计算得出
- 不能直接插入或更新生成列的值
- 分为 STORED（存储）和 VIRTUAL（虚拟）两种类型
2. **生成列类型**
- **STORED**：计算结果存储在磁盘上，占用空间但查询快
- **VIRTUAL**：每次查询时计算，不占用空间但查询慢
3. **创建生成列**
```sql
-- STORED 生成列
CREATE TABLE products (
id INTEGER PRIMARY KEY,
name TEXT,
price REAL,
quantity INTEGER,
total REAL GENERATED ALWAYS AS (price * quantity) STORED
);
-- VIRTUAL 生成列
CREATE TABLE users (
id INTEGER PRIMARY KEY,
first_name TEXT,
last_name TEXT,
full_name TEXT GENERATED ALWAYS AS (first_name || ' ' || last_name) VIRTUAL
);
```
4. **生成列的约束**
- 表达式必须确定，不能包含随机函数
- 不能引用其他生成列
- 不能引用同一表的其他行
- 不能包含子查询
- 不能使用非确定性函数（如 random()）
5. **使用生成列的优势**
- **数据一致性**：自动保持数据同步
- **减少冗余**：避免手动维护计算字段
- **性能优化**：STORED 列可以建立索引
- **代码简化**：减少应用层计算逻辑
6. **为生成列创建索引**
```sql
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
order_date TEXT,
status TEXT,
year INTEGER GENERATED ALWAYS AS (CAST(strftime('%Y', order_date) AS INTEGER)) STORED
);
-- 为生成列创建索引
CREATE INDEX idx_year ON orders(year);
```
7. **修改生成列**
SQLite 不支持直接修改生成列，需要：
```sql
-- 创建新表
CREATE TABLE products_new (
id INTEGER PRIMARY KEY,
name TEXT,
price REAL,
quantity INTEGER,
total REAL GENERATED ALWAYS AS (price * quantity * 1.1) STORED
);
-- 迁移数据
INSERT INTO products_new (id, name, price, quantity)
SELECT id, name, price, quantity FROM products;
-- 删除旧表，重命名新表
DROP TABLE products;
ALTER TABLE products_new RENAME TO products;
```
8. **生成列的使用场景**
- 计算字段（总价、平均值等）
- 数据转换（日期格式转换、类型转换）
- 数据提取（从 JSON 中提取字段）
- 数据验证（检查条件）
- 派生数据（基于其他列计算）
9. **性能考虑**
- STORED 列：占用存储空间，查询快，写入慢
- VIRTUAL 列：不占用存储空间，查询慢，写入快
- 根据查询频率选择合适的类型
- 为频繁查询的 STORED 列创建索引
10. **与其他特性结合**
- 与 JSON 扩展结合：提取 JSON 字段
- 与全文搜索结合：生成搜索文本
- 与约束结合：验证数据完整性
生成列是 SQLite 提供自动化数据维护的强大工具。
服务端 · 2月18日 21:51
SQLite 的触发器（Trigger）如何使用？SQLite 的触发器（Trigger）机制允许在特定数据库事件发生时自动执行操作：
1. **触发器类型**
- **BEFORE 触发器**：在操作执行前触发
- **AFTER 触发器**：在操作执行后触发
- **INSTEAD OF 触发器**：替代原始操作执行（主要用于视图）
2. **触发事件**
- INSERT：插入数据时触发
- UPDATE：更新数据时触发
- DELETE：删除数据时触发
3. **创建触发器**
```sql
-- BEFORE INSERT 触发器
CREATE TRIGGER update_timestamp
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
SET NEW.created_at = CURRENT_TIMESTAMP;
END;
-- AFTER UPDATE 触发器
CREATE TRIGGER log_update
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
INSERT INTO audit_log (table_name, operation, user_id)
VALUES ('users', 'UPDATE', NEW.id);
END;
```
4. **触发器中的特殊引用**
- **NEW.column**：引用 INSERT 或 UPDATE 操作后的新值
- **OLD.column**：引用 UPDATE 或 DELETE 操作前的旧值
- NEW 只在 INSERT 和 UPDATE 中可用
- OLD 只在 UPDATE 和 DELETE 中可用
5. **触发器应用场景**
- 自动维护时间戳字段
- 实现审计日志
- 数据验证和约束
- 级联操作
- 数据同步
6. **管理触发器**
```sql
-- 查看触发器
SELECT * FROM sqlite_master WHERE type = 'trigger';
-- 删除触发器
DROP TRIGGER trigger_name;
-- 列出表的触发器
PRAGMA trigger_list(table_name);
```
7. **限制和注意事项**
- 触发器不能直接调用其他触发器（递归）
- 触发器执行会影响性能
- 复杂的触发器逻辑难以调试
- 避免在触发器中执行耗时操作
触发器是 SQLite 实现自动化数据维护和业务逻辑的重要工具。
服务端 · 2月18日 21:51
SQLite 的视图（View）如何使用？SQLite 的视图（View）提供了一种虚拟表的概念：
1. **视图的概念**
- 视图是基于 SQL 查询结果的虚拟表
- 不存储实际数据，只存储查询定义
- 每次查询视图时都执行底层的 SQL 语句
2. **创建视图**
```sql
-- 创建简单视图
CREATE VIEW user_orders AS
SELECT u.id, u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;
-- 创建带过滤条件的视图
CREATE VIEW active_users AS
SELECT * FROM users WHERE status = 'active';
```
3. **视图的优势**
- **简化复杂查询**：将复杂的 JOIN 和聚合操作封装在视图中
- **数据安全性**：限制用户访问特定列或行
- **逻辑抽象**：隐藏底层表结构的变化
- **代码重用**：避免重复编写相同的查询
4. **查询视图**
```sql
-- 查询视图就像查询普通表一样
SELECT * FROM user_orders WHERE order_count > 5;
```
5. **管理视图**
```sql
-- 查看视图定义
SELECT sql FROM sqlite_master WHERE type = 'view' AND name = 'user_orders';
-- 删除视图
DROP VIEW view_name;
-- 修改视图（需要先删除再创建）
DROP VIEW IF EXISTS user_orders;
CREATE VIEW user_orders AS ...;
```
6. **视图的限制**
- SQLite 的视图是只读的（默认情况下）
- 不能直接对视图执行 INSERT、UPDATE、DELETE 操作
- 可以使用 INSTEAD OF 触发器实现可更新视图
```sql
CREATE TRIGGER update_user_orders
INSTEAD OF UPDATE ON user_orders
BEGIN
UPDATE users SET name = NEW.name WHERE id = NEW.id;
END;
```
7. **视图的使用场景**
- 报表和数据分析
- 数据权限控制
- 简化应用层的查询逻辑
- 跨表查询的抽象
8. **性能考虑**
- 视图本身不存储数据，每次查询都执行底层 SQL
- 复杂视图可能影响查询性能
- 可以考虑使用物化视图（通过表定期刷新）
视图是 SQLite 提供数据抽象和安全控制的重要机制。
服务端 · 2月18日 21:51
什么是 SQLite 的预编译语句？如何使用？SQLite 的预编译语句（Prepared Statements）是提高性能和安全性的重要机制：
1. **预编译语句的概念**
- 预编译语句是 SQL 模板，包含参数占位符
- SQL 语句只编译一次，可以多次执行
- 参数值在执行时绑定，而不是编译时
2. **预编译语句的优势**
- **性能提升**：避免重复解析和编译 SQL
- **防止 SQL 注入**：参数化查询自动转义特殊字符
- **代码可读性**：SQL 语句更清晰易读
- **类型安全**：参数类型自动处理
3. **使用预编译语句**
```python
# Python 示例
import sqlite3
conn = sqlite3.connect('database.db')
cursor = conn.cursor()
# 创建预编译语句
stmt = "INSERT INTO users (name, email) VALUES (?, ?)"
# 多次执行
users = [('Alice', 'alice@example.com'), ('Bob', 'bob@example.com')]
cursor.executemany(stmt, users)
conn.commit()
```
4. **参数占位符**
- **问号占位符（?）**：SQLite 标准占位符
- **命名占位符（:name）**：使用名称引用参数
```sql
-- 问号占位符
SELECT * FROM users WHERE id = ? AND status = ?
-- 命名占位符
SELECT * FROM users WHERE id = :id AND status = :status
```
5. **预编译语句的生命周期**
- 准备（Prepare）：编译 SQL 语句
- 绑定（Bind）：设置参数值
- 执行（Execute）：执行语句
- 重置（Reset）：重置语句以便再次执行
- 释放（Finalize）：释放语句资源
6. **性能优化技巧**
- 在循环外准备语句，循环内执行
- 使用 executemany 批量操作
- 重用预编译语句而不是每次重新创建
- 及时释放不再使用的语句
7. **安全最佳实践**
- 始终使用参数化查询，避免字符串拼接
- 不要将用户输入直接拼接到 SQL 中
- 验证参数类型和范围
- 使用最小权限原则
8. **不同语言的实现**
- Python：sqlite3 模块的 execute() 和 executemany()
- Java：PreparedStatement 接口
- C/C++：sqlite3_prepare_v2() 和 sqlite3_bind_*()
- Node.js：db.prepare() 和 stmt.run()
预编译语句是 SQLite 开发中不可或缺的技术，既提高了性能又增强了安全性。
服务端 · 2月18日 21:51
SQLite 的索引机制如何影响查询性能？SQLite 的索引机制对查询性能有重要影响：
1. **索引类型**
- **B-Tree 索引**：默认索引类型，适合范围查询和排序
- **哈希索引**：仅在内存数据库中可用，适合等值查询
- **R-Tree 索引**：用于空间数据，支持地理信息查询
- **全文索引（FTS）**：用于全文搜索功能
2. **索引创建**
```sql
-- 创建单列索引
CREATE INDEX idx_name ON table_name(column_name);
-- 创建复合索引
CREATE INDEX idx_composite ON table_name(col1, col2);
-- 创建唯一索引
CREATE UNIQUE INDEX idx_unique ON table_name(column_name);
```
3. **索引使用原则**
- 在 WHERE 子句、JOIN 条件、ORDER BY、GROUP BY 中频繁使用的列上创建索引
- 选择性高的列（唯一值多）更适合建索引
- 避免在小表上创建索引
- 复合索引遵循最左前缀原则
4. **索引优化技巧**
- 使用 `EXPLAIN QUERY PLAN` 分析查询执行计划
- 避免在索引列上使用函数或表达式
- 使用 `LIKE 'prefix%'` 可以利用索引，`LIKE '%suffix'` 不能
- 合理使用覆盖索引（Covering Index）避免回表
5. **索引维护**
- 索引会增加 INSERT、UPDATE、DELETE 操作的开销
- 定期使用 `ANALYZE` 命令更新统计信息
- 使用 `REINDEX` 重建碎片化的索引
- 删除不再使用的索引以节省空间和提高写入性能
6. **主键和自动索引**
- 创建表时声明 PRIMARY KEY 会自动创建唯一索引
- UNIQUE 约束也会自动创建索引
- WITHOUT ROWID 表可以避免创建隐藏的 rowid 索引
合理使用索引可以显著提高查询性能，但需要在查询性能和写入性能之间找到平衡。
服务端 · 2月18日 21:50