python - 具有重复值的 Sqlite 列

Question

假设列a SQLite DB 是 非常重复，总是相同的 4 个值 .其他值可能会在稍后出现，但不会有 1000 个不同的值。

VALUES = ["hello world", "it's a shame to store this str many times", "bye bye", "abc"]

import sqlite3, random
db = sqlite3.connect('repetitive1.db')
db.execute("CREATE TABLE IF NOT EXISTS data(id INTEGER PRIMARY KEY, a TEXT);")
for i in range(1000 * 1000):
    db.execute("INSERT INTO data (a) VALUES (?)", (random.choice(VALUES),))
db.commit()

此处，对于 100 万个项目，DB 大小为 24 MB，即平均 24 个字节。
多次重新存储所有字符串有点遗憾，因为它总是相同的值 一次又一次 .当然，一个解决方案是使用 ID = 0, 1, 2, 3(以后最多 1000)作为重复值，并且只存储整数 ID:

db = sqlite3.connect('repetitive2.db')
db.execute("CREATE TABLE IF NOT EXISTS data(id INTEGER PRIMARY KEY, a INT);")
for i in range(1000*1000):
    db.execute("INSERT INTO data (a) VALUES (?)", (random.randint(0, 3),))
db.commit()

增益:数据库只有 9 MB，即平均每行 9 个字节，这要好得多。
但缺点是我们必须手动执行此操作:

维护 另一 table 具有 ID 和字符串之间的对应关系，

检测何时出现新值(以前从未见过)，给它一个新的 ID，等等

如果行被删除并且最后一个字符串不再出现在任何地方，我们可能需要做一些清理并从第二个表中删除它的 ID

等

这是可能的并且不是很困难，但多年来我注意到 SQLite 通常对类似的事情有巧妙的优化/好的技巧。
问题:有没有办法让 SQLite 自动完成所有操作？即设置一种模式，在该模式下，SQLite 将在内部尽最大努力对列中的数据进行重复数据删除，例如通过为此列使用 ID 而不是一次又一次地存储相同的字符串？ (无需自己维护任何东西？)

Answer 1

问题与Add data to many-to-many relation with one SQL command非常相似，但它还讨论了进一步的方面 - 未使用实体的自动清理。

Is there a way to let SQLite do everything automatically? ... (without having to maintain anything ourselves)

不。您基本上希望在基表和引用表中插入行(如果尚不存在)，同时按值指定引用而不是其 surrogate key .事实上，这在其他 RDBMS 中也不是一项简单的任务。其中一些支持:

存储过程

Multitable inserts

OUTPUT clause

RETURNING clause

可写(可更新) View

INSTEAD OF View 触发

从上面的列表中 SQLite 只支持 INSTEAD OF触发器。以下是它如何适用于您的用例(我从问题下的评论中提到的 db<>fiddle 中采用了表 words 并将其列 a 重命名为 value ):

PRAGMA foreign_keys = ON;

CREATE TABLE words(
  id INTEGER PRIMARY KEY,
  value TEXT
);

CREATE UNIQUE INDEX unique_words_value ON words(value);

CREATE TABLE data(
  id INTEGER PRIMARY KEY,
  word_id INTEGER NOT NULL,
  FOREIGN KEY (word_id) REFERENCES words(id)
);

CREATE VIEW data_view AS
SELECT d.id, w.value FROM data AS d INNER JOIN words AS w on w.id = d.word_id;

CREATE TRIGGER data_view_insert INSTEAD OF INSERT ON data_view
BEGIN
  INSERT OR IGNORE INTO words(value) VALUES (NEW.value);
  INSERT OR IGNORE INTO data(word_id) VALUES(
    (SELECT id FROM words WHERE value = NEW.value)
  );
END;

INSERT INTO data_view (value) VALUES
  ('random1'),
  ('random2'),
  ('random3'),
  ('random1'),
  ('random3'),
  ('random4');

INSERT语句产生了表 words 的内容:


ID
值(value)


1
随机 1

2
随机2

3
随机3

4
随机4


和 data_view :


ID
值(value)


1
随机 1

2
随机2

3
随机3

4
随机 1

5
随机3

6
随机4


支持从 data 中删除行自动清理 words 中未使用的值您可以添加 INSTEAD OF DELETE触发 data_view :

CREATE TRIGGER data_view_delete INSTEAD OF DELETE ON data_view
BEGIN
  DELETE FROM data
    WHERE id = OLD.id;
  DELETE FROM words
    WHERE value = OLD.value AND NOT EXISTS(SELECT 1 FROM data_view WHERE value = OLD.value);
END;

它的第一条语句删除表 data 中的一行第二个从 words 中删除引用的值，如果它没有被表 data 中的其他行引用.类似于 INSERT ，您从表 data 中删除行间接通过 data_view :

-- delete one 'random2' and 'random4' value data
DELETE FROM data_view WHERE id IN (2, 4);

结果是 words :


ID
值(value)


1
随机 1

3
随机3

4
随机4


如您所见 'random2'值被删除，因为它在 data 中只被引用过一次, 和 'random4'值被保留，因为表 data 中有另一个引用( data_view ):


ID
值(value)


1
随机 1

3
随机3

5
随机3

6
随机4


这是 db<>fiddle和玩。