替换 SQLite DB 中的路径字符串导致意外违反唯一约

Replace path string in SQLite DB causes unexpected violated unique constraint(替换 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/*';

这篇关于替换 SQLite DB 中的路径字符串导致意外违反唯一约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！