sql - 将 blob 字段移动到新表(id，blob)中，并将其替换为新表中的 id

Question

我有一个 BLOB 字段，其中包含 1-2 MB 的图像。我想创建一个只有 2 个字段的新表 - 一个具有引用 ID 和 BLOB 的主键，然后将保存 BLOB 的字段替换为保存新表中相同 BLOB 的引用 ID 的字段。

我不太了解SQL，我什至不确定这是否可以仅使用SQL。还是我需要制作一个 C++/Python 程序才能做到这一点？

注意:我在数据库中使用 SQLite，由于它们不强制字段的类型，我什至不需要创建新字段，我只需将 BLOB 替换为 ID。

Answer 1

您必须将所有 blob 复制到另一个表，并使用新行的 ID 更新旧字段。
后者可以使用 last_insert_rowid() ，但仅适用于单行，因此您必须使用一种逐步执行插入和更新的机制。

这可以通过触发器(和一个虚拟 View ，以便触发复制的 UPDATE 不会实际更新表)来完成:

CREATE TABLE NewTable (ID INTEGER PRIMARY KEY, Blob);

CREATE TEMPORARY VIEW OldTable_view AS
SELECT * FROM OldTable;

CREATE TEMPORARY TRIGGER move_blob
INSTEAD OF UPDATE ON OldTable_view
BEGIN
    INSERT INTO NewTable(Blob)
    SELECT Blob FROM OldTable WHERE ID = OLD.ID;

    UPDATE OldTable
    SET Blob = last_insert_rowid()
    WHERE ID = OLD.ID;
END;

UPDATE OldTable_view SET ID = ID;

DROP VIEW OldTable_view;