替换 SQLite DB 中的路径字符串导致意外违反唯一约束
我不确定我是否在 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/*';
相关文章