sql - mysql 更新语句根据用户变量是否存在而表现不同

Question

我正在重置具有重复值或缺失值的排序列，如下所示:

set @last='';
set @sort=NULL;
update conf_profile set sort=
    if(
        @last=(@last:=concat(org_id,',',profile_type_id,',',page,',',col)),
        (@sort:=@sort+1),
        (@sort:=0)
    )
order by org_id,profile_type_id,page,col,sort,id;

(遍历按多个关键字段排序的所有行，逐步分配递增的值进行排序；每当这些字段中的任何一个发生变化时，从 0 重新开始。)

它似乎只有在执行更新之前创建了@sort 变量时才有效(尽管它的设置无关紧要)。没有'set @sort'，所有排序值设置为 0 或 NULL。

知道为什么会这样吗？ MySQL 版本 5.0.51。

更新:更详细地解释逻辑:在第一行，@last=(@last:=...)将始终为 false，此后当任何关键字段更改时它将为 false从前一行。 (注意，被连接的关键字段都不是 NULL)。当它为 false 时，我们从 0 (@sort:=0) 重新开始排序计数器，否则，它是递增 (@sort:=@sort+1) 并使用新值。

在任何情况下都不会在更新语句中设置之前使用@sort，所以无论是在更新语句之前如何设置应该没有区别。

Answer 1

如果您在表达式中引用未设置的用户变量，则该变量将被视为 NULL。

在 SQL 中，NULL + 1 返回 NULL。如果您在此 UPDATE 之前未将 @sort 设置为非 NULL 值，则它将继续为 NULL 无论您对 @sort:=@sort+1 求值多少次。一旦你执行了 @sort:=0，它就会正常递增。

不在 UPDATE 中尝试这个:

mysql> set @sort := NULL;
mysql> SELECT @sort; -- returns NULL
mysql> set @sort := @sort + 1;
mysql> SELECT @sort; -- returns NULL again
mysql> set @sort := 0;
mysql> set @sort := @sort + 1;
mysql> SELECT @sort; -- returns 1

我猜你第一次设置 @sort:=0 后没有重复项只是巧合。

编辑:以上是正确的，但正如您所指出的，它并不能解释您所看到的行为，因为逻辑上 @sort 应该被保证设置在评估第一行期间为 0。

但是，我注意到如果我更改 IF() 表达式中的项的顺序，一切都会起作用，即使在我们开始时未设置 @sort 也是如此:

set @last='';
-- set @sort=NULL;
update conf_profile set sort=
    if(
        @last!=(@last:=concat(org_id,',',profile_type_id,',',page,',',col)),
        (@sort:=0),
        (@sort:=@sort+1)
    )
order by org_id,profile_type_id,page,col,sort,id;

我不确定我是否理解得足够清楚，无法准确解释为什么会这样，但是关于何时评估用户变量，有一些古怪的东西。有关大量示例和详细信息，请参阅此博客:“Advanced MySQL user variable techniques ”。