第五章 SQLite数据库：4、SQLite 进阶用法：常见的约束、PRAGMA 配置、数据操作

本文链接：https://blog.youkuaiyun.com/qq_62848032/article/details/147323339

📘 SQLite PRAGMA 与约束机制全解

一、PRAGMA 指令概念与原理

❓ 什么是 PRAGMA？

PRAGMA 是 SQLite 提供的特殊命令，用于查询或设置数据库运行参数和行为选项，可理解为 SQLite 的“配置接口”。

⚙️ 特点：

非标准 SQL，仅适用于 SQLite
可动态控制数据库的缓存、事务、日志、校验等功能
可提升性能、增强控制能力

🔤 基本语法

-- 查询某个 PRAGMA 项
PRAGMA pragma_name;

-- 设置某个 PRAGMA 项
PRAGMA pragma_name = value;

二、常用 PRAGMA 指令详解

1️⃣ `auto_vacuum`：自动清理垃圾空间

PRAGMA auto_vacuum;         -- 查看当前模式（NONE/FULL/INCREMENTAL）
PRAGMA auto_vacuum = FULL;  -- 设置为自动回收模式

NONE：默认，不清理空间
FULL：自动整理数据文件
INCREMENTAL：手动回收已释放页面

2️⃣ `cache_size`：设置缓存页数

PRAGMA cache_size;
PRAGMA cache_size = 2000;

页面数量 × 页面大小（默认 4096 字节）= 缓存内存大小
增大此值有助于减少磁盘 IO

3️⃣ `case_sensitive_like`：LIKE 区分大小写

PRAGMA case_sensitive_like = true;

默认 LIKE 不区分大小写
启用后：LIKE 'A%' 不会匹配 'apple'

4️⃣ `journal_mode`：事务日志模式

PRAGMA journal_mode;
PRAGMA journal_mode = WAL;

模式	含义
DELETE	默认模式，提交后删除日志
WAL	写前日志，推荐模式
MEMORY	使用内存日志，不落盘

5️⃣ `max_page_count`：最大页面数量限制

PRAGMA max_page_count;
PRAGMA max_page_count = 5000000;

控制数据库最大物理大小
限制 = 页面大小 × 页数上限

三、SQLite 约束机制概念与应用

什么是约束（Constraint）？

约束用于强制字段值必须满足条件，从而保障数据的完整性和一致性。

常见约束类型对比

约束类型	功能说明
`NOT NULL`	字段不能为空
`DEFAULT`	未赋值时使用默认值
`UNIQUE`	字段值必须唯一
`PRIMARY KEY`	主键，隐含 `NOT NULL` + `UNIQUE`
`CHECK`	指定字段值必须满足的逻辑条件
`FOREIGN KEY`	引用其他表主键，默认关闭

四、约束使用详解与示例

1️⃣ `NOT NULL`

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

确保 name 字段不能为 NULL。

2️⃣ `DEFAULT`

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    salary REAL DEFAULT 3000.0
);

salary 默认值为 3000。

3️⃣ `UNIQUE`

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    code TEXT UNIQUE
);

保证 product 编码唯一。

4️⃣ `CHECK`

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    amount REAL CHECK(amount > 0)
);

限制金额不能为负数或 0。

5️⃣ `PRIMARY KEY`

CREATE TABLE books (
    isbn TEXT PRIMARY KEY,
    title TEXT NOT NULL
);

isbn 是全表唯一主键。

五、综合案例：PRAGMA + 约束联合使用

-- 启用 WAL 日志模式
PRAGMA journal_mode = WAL;

-- 设置缓存大小
PRAGMA cache_size = 1000;

-- 创建商品表
CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    category TEXT NOT NULL,
    price REAL CHECK(price > 0),
    quantity INTEGER DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 插入样本数据
INSERT INTO products (name, category, price, quantity)
VALUES 
    ('Laptop', 'Electronics', 1200.00, 10),
    ('Smartphone', 'Electronics', 800.00, 25),
    ('Coffee Maker', 'Appliances', 150.00, 5);

-- 更新
UPDATE products SET price = 1100.00 WHERE name = 'Laptop';

-- 查询
SELECT * FROM products WHERE price > 500;

-- 删除库存不足产品
DELETE FROM products WHERE quantity < 10;

-- 查看数据库页数
PRAGMA page_count;