插入時字元串列的重複數據刪除/規範化

我有一個文本元組數據庫。想像一下，例如完整的文件路徑+標準註釋。然後你轉儲大文件樹並為文件生成註釋。一個文件可以有很多註釋，但它們的文本完全重複。數據庫達到幾 GB 的大小，所以我認為它對於 sqlite 標準來說相當大，不過我在這裡是個菜鳥。

無論如何，由於各個列中的字元串確實重複了很多，但是組合是原始的，我想我可以有不同原始字元串的表，並且組合就像外鍵的元組（我相信這稱為規範化），然後 a VIEW，保留所有API，用於閱讀。

問題是，我可以在數據庫端實現重複數據刪除機制進行插入嗎？

我想到了類似INSERTon(text_column1, text_column2, text_column3)然後編寫某種INSTEAD OF INSERT觸發器，將其拆分為 3 個INSERT IF NOT EXISTS命令 + 1 個插入到關係表中。但我認為甚至不可能有不同的“介面”和“儲存”模式。我確實寫不出來。

我有一些緊密相關的補充問題（因此我相信不值得單獨輸入）：

1. 也許 sqlite3 已經在幕後進行了字元串重複數據刪除？（我對此表示懷疑，因為可變字元串會使事情變得有點複雜。但沒有什麼是不可撤銷的。）
2. 如果這很難，那麼出於某種原因，這可能是一個壞主意？

如果它有幫助，我的數據在插入後是只讀的。

我讀了：

1. PostgreSQL 是否有可變字元儲存層通過自動重複數據刪除來優化儲存空間？
2. 在 Oracle 中實現重複數據刪除觸發器
3. MySQL中字元串的自動重複數據刪除（規範化）

但他們考慮不同的 SQL 系統，並沒有真正回答一般問題。我認為這不是一個流行的問題和解決方案。

這是一個粗略的草圖。我只能使用SQLite 3.8進行測試，它似乎UPSERT是在2018-06-04 - Release 3.24.0中引入的。即以下內容未經測試，但希望您無論如何都能從中有所收穫

create table Texts
( tid integer not null primary key AUTOINCREMENT
, textval varchar(20) not null unique);

create index x1 on texts (textval);

create table T
( x int not null 
, tid int not null references texts (tid)
, primary key (x, tid) );

create view v as 
  select t.x, texts.textval
  from t
  join texts
      on t.tid = texts.tid
;

CREATE TRIGGER trig1 
INSTEAD OF INSERT ON V 
BEGIN
       -- insert unless textval is already in place
       INSERT INTO texts (textval)
       VALUES (NEW.textval) ON CONFLICT (textval) DO NOTHING;

       -- lookup tid for textval
       insert into t (x, tid)
       select NEW.x, texts.tid
       from texts where texts.textval = NEW.textval;

END;

rowid 似乎被推薦超過autoincrement，但這有點離題，所以我無論如何都使用它。

對於 3.8，我使用了這個醜陋的解決方法：

CREATE TRIGGER trig1 
INSTEAD OF INSERT ON V 
BEGIN
       -- insert unless textval is already in place
       INSERT INTO texts (textval)
       SELECT NEW.textval FROM (VALUES(1))
       WHERE NOT EXISTS (
           SELECT 1 FROM texts WHERE textval = NEW.textval 
       );

       --lookup tid for textval
       insert into t (x, tid)
       select NEW.x, texts.tid
       from texts where texts.textval = NEW.textval;
END;

你可以在DB<>Fiddle試試。測試：

insert into V (x,textval) values (1,'a'); 
insert into V (x,textval) values (5,'bb');  
insert into V (x,textval) values (15,'a');

select * from v;
x   textval
1   a
5   bb
15  a

select * from texts;
tid textval
1   a
2   bb

select * from t;
x   tid
1   1
5   2
15  1

引用自：https://dba.stackexchange.com/questions/240767