SQLite changes() 计算不变的 UPDATEs [英] SQLite changes() counts non-changing UPDATEs

问题描述

我对 SQLite 的 changes() 函数有疑问，根据 文档，返回由最近完成的 INSERT、DELETE 或 UPDATE 语句更改、插入或删除的数据库行数"(另请参阅 底层 C/C++ 函数的文档).

I have question regarding SQLite's changes() function, which, according to the documentation, "returns the number of database rows that were changed or inserted or deleted by the most recently completed INSERT, DELETE, or UPDATE statement" (also see the documentation of the underlying C/C++ function).

我希望使用此函数来检查与单行有关的 UPDATE 语句的执行是否真的导致该行被更改.我所说的 changed 不仅仅是指该行与语句的 WHERE 子句匹配.不，我的意思是，对于有问题的行，与之前相比，执行后至少 1 列的值实际上不同.如果你问我这是在这种情况下对变化的唯一正确定义.

I was hoping to use this function to check whether the execution of an UPDATE statement pertaining to a single row has really caused that row to be changed or not. By changed I do not just mean that the row matched the statement's WHERE clause. No, instead what I mean is that, for the row in question, the value of at least 1 column is actually different after the execution compared to before. If you ask me this is the only proper definition of a change in this context.

所以我希望通过检查 changes() 是否返回 1(行已更改)或 0<来检测此类更改/code>(行不变)在 UPDATE 语句执行后立即调用.但令我绝望的是，这似乎并没有像预期的那样奏效.

So I was hoping to detect such changes by checking whether changes() returns 1 (row changed) or 0 (row unchanged) when called right after the execution of the UPDATE statement. But much to my despair this does not seem to work as expected.

请允许我举例说明:

CREATE TABLE People (Id INTEGER PRIMARY KEY AUTOINCREMENT, Name TEXT NOT NULL);
INSERT INTO People (Name) VALUES ("Astrid");
SELECT changes();

这里 changes() 返回 1，正如预期的那样，因为我们只是 INSERT 编辑了 1 行.

Here changes() returns 1, as expected because we just INSERTed 1 row.

UPDATE People SET Name = "Emma" WHERE Id = 1;
SELECT changes();

这里 changes() 返回 1，正如预期的那样，因为 1 行是 UPDATEd(即实际上 changed: Person 和 Id = 1 的 Name 是 "Astrid" 但现在是 "Emma").

Here changes() returns 1, as expected because 1 row was UPDATEd (i.e. actually changed: the Name of the Person with Id = 1 was "Astrid" but is now "Emma").

UPDATE People SET Name = "John" WHERE Id = 200;
SELECT changes();

这里 changes() 返回 0，正如预期的那样，因为没有 Id = 200 的行.

Here changes() returns 0, as expected because there is no row with Id = 200.

到目前为止一切顺利.但是现在看看下面的 UPDATE 语句，它确实确实匹配了现有的行，但没有实际上改变em> 它根本没有(Name 仍然设置为 "Emma")...

So far so good. But now have a look at the following UPDATE statement, which does indeed match an existing row, but does not actually change it at all (Name remains set to "Emma")...

UPDATE People SET Name = "Emma" WHERE Id = 1;
SELECT changes();

这里 changes() 返回 1，而我当然希望 0 :-(.

Here changes() returns 1, while I was of course hoping for 0 :-(.

如果函数被称为matched_rows() 或affected_rows() 之类的东西，也许这会有意义.但是对于一个名为 changes() 的函数，并按原样记录在案，这种行为让我觉得不合逻辑，或者充其量是令人困惑的.

Perhaps this would have made sense if the function was called something like matched_rows() or affected_rows(). But for a function called changes(), and documented as it is, this behaviour strikes me as illogical, or confusing at best.

无论如何，有人可以解释为什么会发生这种情况，或者更好的是，提出一种替代策略来以可靠(且有效)的方式实现我的目标?

So anyway, can somebody explain why this happens, or, even better, suggest an alternative strategy to achieve my goal in a reliable (and efficient) way?

我能想到的就是实际执行类似 SELECT * FROM People WHERE Id = x 的操作，将所有返回的列值与我将在 UPDATE 中设置的值进行比较 语句，从而决定我是否需要执行 UPDATE.但这不会很有效率，对吧?当然，在这个玩具示例中，它可能无关紧要，但在我的实际应用程序中，我正在处理包含更多列的表，其中一些是(可能很大)BLOBs.

All I can think of is to actually do something like SELECT * FROM People WHERE Id = x, compare all returned column values with the values I'm about to set in the UPDATE statement and thereby decide whether I need to execute the UPDATE at all. But that can't be very efficient, right? Of course in this toy example it might not matter much, but in my actual application I'm dealing with tables with many more columns, some of which are (potentially big) BLOBs.

推荐答案

数据库不比较新旧值；即使值碰巧相同，任何 UPDATEd 行也始终计为已更改".文档 说

The database does not compare old and new values; any UPDATEd row always counts as "changed" even if the values happen to be the same. The documentation says that

UPDATE 会影响……将 WHERE 子句表达式作为布尔表达式求值为 true 的那些行.

the UPDATE affects … those rows for which the result of evaluating the WHERE clause expression as a boolean expression is true.

如果你想检查旧值，你必须明确地做:

If you want to check the old value, you have to do it explicitly:

UPDATE People SET Name = 'Emma' WHERE Id = 1 AND Name IS NOT 'Emma';

这篇关于SQLite changes() 计算不变的 UPDATEs的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！