SQL UPDATE求值顺序

本文介绍了SQL UPDATE求值顺序的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

以下查询中的评估顺序是什么:

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.

Migs