問題描述
我不確定我是否在 SQLite 中發(fā)現(xiàn)了錯誤，或者我是否只是沒有正確使用它.我將相對文件路徑(正如您從 UNIX 文件系統(tǒng)中知道的那樣)存儲在數(shù)據(jù)庫中.為安全起見，我已將該列標(biāo)記為唯一.
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 約束.我的目標(biāo)是將路徑為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/*';
這篇關(guān)于替換 SQLite DB 中的路徑字符串導(dǎo)致意外違反唯一約束的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！