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)