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" 的值是:
I found the values of "p" and "q" were:
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 描述和 与标准的差异中明确说明此行为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.
推荐答案
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屋！
