Sqlite的一些高级使用
创建一个演示表fs
1
2
3
4
5
6
7
|
CREATE TABLE fs (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
full_path TEXT NOT NULL UNIQUE,
parent_id INTEGER,
type TEXT CHECK(type IN ('file', 'directory')),
)
|
insert or replace
使用条件：
如果插入的数据包含主键或唯一约束字段，并且这些字段的值已经存在于表中，那么 INSERT OR REPLACE 语句会将新数据替换掉旧数据(如果使用自增主键，那么新数据的主键值会覆盖旧数据的主键值)。
1
2
|
INSERT OR REPLACE INTO fs (id, name, full_path, parent_id, type)
VALUES (1, 'file1', '/path/to/file1', 1, 'file');
|
insert or ignore
使用条件：
如果插入的数据包含主键或唯一约束字段，并且这些字段的值已经存在于表中，那么 INSERT OR IGNORE 语句将不会插入新数据，也不会报错。
1
2
|
INSERT OR IGNORE INTO fs (id, name, full_path, parent_id, type)
VALUES (1, 'file1', '/path/to/file1', 1, 'file');
|
insert or update
使用条件：
- sqlite 3.24.0 版本后才支持。
- 插入的数据必须包含主键或唯一约束字段。
1
2
|
INSERT INTO fs (id, name, full_path, parent_id, type)
VALUES (1, 'file1', '/path/to/file1', 1, 'file') ON CONFLICT(id) DO UPDATE SET name = 'new_name';
|
递归删除
使用场景：
- 当你需要删除一个目录及其所有子目录和文件时，可以使用递归删除。
方法一：
1
2
3
4
5
6
7
8
|
-- 假设要删除目录 id=1 及其所有子项
WITH RECURSIVE to_delete(id) AS (
SELECT id FROM fs WHERE id = 1 -- 初始目标目录
UNION ALL
SELECT f.id FROM files f
JOIN to_delete d ON f.parent_id = d.id -- 递归查找子项
)
DELETE FROM files WHERE id IN (SELECT id FROM to_delete);
|
关键解释：
- WITH RECURSIVE 构建一个临时表 to_delete，递归收集所有需要删除的 id。
- UNION ALL 分两步：
- 第一步选择目标目录（如 id=1）。
- 第二步递归选择所有 parent_id 匹配已选记录的 id 的子项。
- DELETE 语句删除所有 id 在 to_delete 中的记录。
方法二：
先决条件：
- 确保你的 SQLite 数据库支持递归查询（通常需要启用递归查询功能）。
- 确保你的数据库支持外键约束（FOREIGN KEY）。
1
2
3
4
5
6
7
8
9
10
11
|
CREATE TABLE fs (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
full_path TEXT NOT NULL UNIQUE,
parent_id INTEGER, -- 父级目录的 id（NULL 表示根目录）
type TEXT CHECK(type IN ('file', 'directory')),
FOREIGN KEY (parent_id) REFERENCES files(id) ON DELETE CASCADE
);
-- 启用外键约束（需在每次连接时设置）
PRAGMA foreign_keys = ON;
|
关键说明：
ON DELETE CASCADE：外键约束，当父级记录被删除时，自动删除子记录（需启用 foreign_keys）