SQL UPDATE求值顺序 [英] SQL UPDATE order of evaluation
问题描述
以下查询中的评估顺序是什么:
What is the order of evaluation in the following query:
UPDATE tbl SET q = q + 1, p = q;
也就是说，将"tbl"."p"设置为q还是q + 1?此处的评估顺序受SQL标准约束吗?
That is, will "tbl"."p" be set to q or q + 1? Is order of evaluation here governed by SQL standard?
谢谢.
考虑 Migs的答案后，我对我能找到的所有数据库.虽然我不知道标准怎么说，但实现方式却有所不同.
After considering Migs' answer, I ran some tests on all DBs I could find. While I don't know what the standard says, implementations vary.
给予
CREATE TABLE tbl (p INT NOT NULL, q INT NOT NULL);
INSERT INTO tbl VALUES (1, 5); -- p := 1, q := 5
UPDATE tbl SET q = q + 1, p = q;
我发现"p"和"q"的值是:
database p q
-----------------+---+---
Firebird 2.1.3 | 6 | 6 -- But see "Update 2" below
InterBase 2009 | 5 | 6
MySQL 5.0.77 | 6 | 6 -- See "Update 3" below
Oracle XE (10g) | 5 | 6
PostgreSQL 8.4.2 | 5 | 6
SQLite 3.3.6 | 5 | 6
SQL Server 2016 | 5 | 6
更新2
Firebird 2.5更改了其行为以匹配我测试的大多数其他SQL引擎，而没有使用MySQL.相关的发行说明条目"SET子句中的逻辑更改" ，强烈建议 大多数行为符合SQL规范 .
UPDATE 2
Firebird 2.5 changes its behavior to match the majority of other SQL engines I tested, leaving MySQL alone. The relevant Release Notes entry, "Logic Change in SET Clause", strongly suggests that the majority behavior is correct per SQL specifications.
我对MySQL进行了错误处理，以对此行为发表评论(错误编号 52861 )，因为它们似乎是异常值.
I've bugged MySQL to comment on this behavior (bug no. 52861), as they seem to be the outlier.
上述错误今天(2010-05-19)已关闭，并且文档将更新，以使此行为在 UPDATE 说明和 Standard的区别中都明确SQL 部分.
The aforementioned bug is today (2010-05-19) closed, and the documentation set to be updated to make this behavior explicit both in the UPDATE description and in the Differences from Standard SQL section.
MySQL，Bravo.
Bravo, MySQL.
推荐答案
MySQL进行从左至右"评估，并查看"新值. (已在5.0.45-community-nt-log MySQL社区版上进行测试)
MySQL does "left to right" evaluation and does "see" the new values. (Tested on 5.0.45-community-nt-log MySQL Community Edition)
此外，来自MySQL手册:单表UPDATE分配通常是从左到右评估的.对于多表更新，不能保证以任何特定顺序执行分配."
Furthermore, from the MySQL manual: "Single-table UPDATE assignments are generally evaluated from left to right. For multiple-table updates, there is no guarantee that assignments are carried out in any particular order."
现在，考虑到评估的顺序很重要，一般"是很模糊的，无法保证"是非常糟糕的.
Now, "generally" is quite vague and "no guarantee" is very bad given that the order of evaluation is important.
那么，为了回答这个问题:行为是由"SQL标准"指定的还是仅仅是一个约定?
So, in order to answer the question: IS the behaviour specified by "the SQL standard" or is it just a convention?
更新:掌握了SQL92规范，该规范在"13.10更新语句:已搜索"项"6"中声明.在更新T的任何行之前，有效评估了T的每一行(值表达式)."
UPDATE: Got hold of the SQL92 specs which state at "13.10 update statement: searched" item "6) The (value expression)s are effectively evaluated for each row of T before updating any row of T."
恕我直言，这并不是绝对明确的，但足以考虑到标准不是要看到"您自己的更新结果. 考虑您的示例，Oracle，PostgreSQL和Interbase的执行方式.
IMHO not absolutely unambiguous, but enough to consider that the STANDARD is NOT to "see" the results of your own update. Considering your example, the way Oracle, PostgreSQL and Interbase do it.
这篇关于SQL UPDATE求值顺序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
