Sqlite的一些高级使用

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);

关键解释:

  1. WITH RECURSIVE 构建一个临时表 to_delete,递归收集所有需要删除的 id。
  2. UNION ALL 分两步:
  • 第一步选择目标目录(如 id=1)。
  • 第二步递归选择所有 parent_id 匹配已选记录的 id 的子项。
  1. 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)

0%