SQLlite3 Update statment没有效果 [英] SQLlite3 Update statment has no effect

问题描述

我不是SQL专家，但在iPhone应用程序中有一个错误，并且UPDATE语句对数据库没有影响。
我一直使用SQLlite manger插件FireFox通过重复修改和运行db上的UPDATE来尝试dbug。我还运行语句彻底和SQL验证器，它说它符合核心SQL标准。

I'm no SQL expert, but have a bug in an iPhone app where and UPDATE statement has no effect on the db. I have been using the SQLlite manger plugin for FireFox to try dbug by repeatedly amending and running the UPDATE on the db. I also ran the statement thorough and SQL Validator which said it complied to the core SQL standard.

你能发现下面的语句错误吗？

Can you spot anything wrong with the statement given below?

UPDATE sections
SET
    title = 'What is acne ? ABC',
    text = 'Pus on your face',
    created = '2010-03-10 18:46:55',
    modified = '2011-07-04 17:38:44',
    position = 1,
    condition_id = 4
WHERE id = 10;

推荐答案

SQLite和SQLite的方式有些混乱和不一致Mozilla，Google，Adobe和其他人的各种实现处理数据库中的数字主键，这些数据库的表是在这些实现之外创建的，并且主键定义为整数类型，而不是INTEGER[逐字] ，它们被定义为INT或INT16或INT32等。

There is some confusion and inconsistency in the way SQLite and the various implementations by Mozilla, Google, Adobe, and others handle numeric primary keys in databases whose tables were created outside of these implementations and where the primary keys were defined as an integer type but not as "INTEGER" [verbatim] -- that is, they were defined as INT or INT16 or INT32 etc.

        INTEGER PRIMARY KEY in mothership SQLite is an alias for the rowid.
        INT PRIMARY KEY in mothership SQLite is not an alias for the rowid.

联盟成员（或任何实施者）可能遵守或不遵守此规则。 （SQLite当然是在公共领域。）

A consortium member (or any implementor) may or may not follow this rule. (SQLite is in the public domain, of course.)

请参阅第2.0节： http://www.sqlite.org/datatypes.html

，请参阅RowId和主键部分： a href =http://www.sqlite.org/lang_createtable.html#rowid =nofollow> http://www.sqlite.org/lang_createtable.html#rowid

and see section on RowId and Primary Key here: http://www.sqlite.org/lang_createtable.html#rowid

如果
声明的类型名称正好是INTEGER，PRIMARY KEY列将仅成为一个整数主键。诸如
INT或BIGINT或SHORT INTEGER或UNSIGNED INTEGER的其他整数类型名称导致
主键列表现为具有整数
亲和性的普通表列，唯一索引不是rowid的别名。 [emphasis
added]

A PRIMARY KEY column only becomes an integer primary key if the declared type name is exactly "INTEGER". Other integer type names like "INT" or "BIGINT" or "SHORT INTEGER" or "UNSIGNED INTEGER" causes the primary key column to behave as an ordinary table column with integer affinity and a unique index, not as an alias for the rowid. [emphasis added]

不遵守规则的实施者可能甚至不知道他们打破了规则在第一位，因为它是一个骗子奥秘类的规则。无论如何，这意味着实际上是一个实现可能将提供的值作为rowid的别名，而另一个实现可能不会。如果给定值10，则可以检索其rowid = 10的元组，并且可以检索指定列的值为10的元组。这当然会导致查询中的假结果 - 并且它们可能看起来完全好，

An implementor who does not follow the rule might not have even been aware that they were breaking the rule in the first place, since it is a "gotcha" arcane sort of rule. Anyway, what this means practically is that one implementation may treat a supplied value as an alias for the rowid and another implementation might not. If given the value 10, one might retrieve the tuple whose rowid = 10 and one might retrieve the tuple where the specified column's value = 10. This, of course, leads to spurious results in queries -- and they might look like perfectly good and plausible results but they are dead wrong.

考虑下面的简单测试：使用旗舰SQLite的实用程序，而不是其中一个实现器提供的实用程序，执行以下DDL和DML语句;然后在您的实现中，打开数据库并再次执行DML语句以比较DML结果：

Consider the following simple test: using flagship SQLite's utilities, not those provided by one of the implementors, execute the following DDL and DML statements; then, in your implementation, open the database and execute the DML statements again to compare the DML results:

        CREATE TABLE TEST
        ("id" INT PRIMARY KEY, "name" text)  -- ** NOTE "INT" not "INTEGER"

        INSERT INTO TEST
        (id, name)
        VALUES
        (7,'seven')


         ** *** N.B. THE ROWID OF THE ROW INSERTED ABOVE =  1  *** **


        select rowid, id, name from test

       result:  1 | 7 | seven


        select * from TEST 

        result:  7 | seven


        select * from TEST where id = 7

        result:  ?????  [ymmv]



        select * from TEST where id = 1


        result: ?????  [ymmv]

根据具体实现如何处理INT主键上面的第三个select语句* from TEST where id = 7）可能会返回一行或它可能不返回任何东西！

Depending on how the specific implementation treats an INT primary key the third select statement above (select * from TEST where id = 7) may return one row or it may return nothing!

如果实现将INT PK视为别名对于行id，好，没有rowid = 7的行，因此它将不返回任何内容。如果实现将INT PK视为正常值，它将找到该行。

If the implementation treats the INT PK as an alias for the row id, well, there is no row whose rowid = 7, and so it will return nothing. If the implementation treats the INT PK as a normal value, it will find the row.

现在，如果要在表TEST中插入更多行，在其中一个方式实现中，当您使用此where子句 - where id = 7 时，您可能会认为您正在寻址id = 7的元组，但你实际上是寻址的rowid = 7的元组。你会得到错误的元组，你可能不会意识到。考虑将子表连接到父表时的可能性：子表包含外键值7.内连接从父表返回什么元组？这取决于实现是否尊重INT和INTEGER主键之间的区别。

Now, if you were to insert more rows into table TEST, you would eventually create a row whose rowid = 7. In one of these wayward implementations, when you use this where-clause -- where id = 7 --- you might think you were addressing the tuple whose id=7, but you'd actually be addressing the tuple whose rowid=7. You would get the wrong tuple and you might not realize it. Consider the possibilities when joining a child table to a parent table: the child table contains foreign key value of 7. What tuple does an inner join return from the parent table? It depends on whether the implementation honors the distinction between INT and INTEGER primary keys.

去年，我为Adobe AIR，BTW彻底记录了这一点，并在SQLite新闻组。

Last year, I documented this thoroughly for Adobe AIR, BTW, and also reported it on the SQLite news group. It is possible that some implementations have changed the behavior in the interim.

在创建SQLite表时，最好使用INTEGER [verbatim]作为主键，不是任何其他已识别的int类型。

这篇关于SQLlite3 Update statment没有效果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！