問題描述

我不確定我是否在 SQLite 中發現了錯誤，或者我是否只是沒有正確使用它.我將相對文件路徑(正如您從 UNIX 文件系統中知道的那樣)存儲在數據庫中.為安全起見，我已將該列標記為唯一.

I'm not sure whether I've found a bug in SQLite or whether I'm simply not using it correctly. I'm storing relative file paths (as you know them from UNIX file systems) in a DB. For safety I've marked the column to be unique.

下面是一個不言自明的示例，其中最后一個命令意外失敗并違反了 UNIQUE 約束.我的目標是將路徑為a"的目錄重命名為d"

Below is a self-explanatory example where the last command unexpectedly fails with a violated UNIQUE constraint. My goal is to rename the directory with path "a" to "d"

CREATE TABLE test (db_id INTEGER PRIMARY KEY, path TEXT UNIQUE);
INSERT INTO test (path) VALUES ('a');
INSERT INTO test (path) VALUES ('a/d/a');
INSERT INTO test (path) VALUES ('a/d');
INSERT INTO test (path) VALUES ('a/d/c');
INSERT INTO test (path) VALUES ('a/a');
INSERT INTO test (path) VALUES ('a/c');
INSERT INTO test (path) VALUES ('a/a/a');
UPDATE test SET path = 'd' WHERE db_id = 1;
UPDATE test SET path = replace(path, 'a/', 'd/') WHERE path GLOB 'a/*'

歡迎提出任何想法.我使用的是 SQLite v2.6.0.

Any ideas are welcome. I'm using SQlite v2.6.0.

推薦答案

INSERT INTO test (path) VALUES ('a/d/a');
INSERT INTO test (path) VALUES ('a/a/a');

用d/替換a/后，兩個值都是d/d/a.

After replacing a/ with d/, both values are d/d/a.

如果只想更改字符串開頭的a/，則不能使用replace():

If you want to change only an a/ at the start of the string, you cannot use replace():

UPDATE test
SET path = 'd/' || substr(path, 3)
WHERE path GLOB 'a/*';

這篇關于替換 SQLite DB 中的路徑字符串導致意外違反唯一約束的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！