mysql update语句根据用户变量是否存在而表现不同

I'm reseting a sort column that has duplicate or missing values like so:

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

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;

(Go through all the rows sorted by a number of key fields assigning progressively incremented values to sort; whenever any of those fields change, restart at 0.)

(遍历按许多键字段排序的所有行,分配逐步递增的值以进行排序;每当这些字段中的任何一个更改时,重新启动为0.)

It seems to work only if the @sort variable was created prior to doing the update (though it doesn't matter what it was set to). Without the 'set @sort', all the sort values are set to either 0 or NULL.

它似乎只有在执行更新之前创建了@sort变量时才起作用(尽管设置它并不重要)。如果没有'set @sort',所有排序值都将设置为0或NULL。

Any ideas why this would be so? MySQL version 5.0.51.

任何想法为什么会这样? MySQL版本5.0.51。

Update: To explain the logic more at length: on the first row, the @last=(@last:=...) will always be false, and thereafter it will be false when any of the key fields changes from the previous row. (N.B. none of the key fields being concat'd are ever NULL). When it's false, we start the sort counter over again at 0 (@sort:=0), otherwise, it is incremented (@sort:=@sort+1) and the new value is used.

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

In no case is @sort used before it is being set in the update statement, so whether or how it is set before the update statement should make no difference.

在任何情况下都不会在更新语句中设置@sort之前使用它,因此在更新语句之前设置它是否或如何设置应该没有区别。

1 个解决方案

#1

An unset user variable is treated as NULL if you reference it in an expression.

如果在表达式中引用未设置的用户变量,则将其视为NULL。

In SQL, NULL + 1 returns NULL. If you don't set @sort to a non-NULL value prior to this UPDATE, then it'll continue to be NULL no matter how many times you evaluate @sort:=@sort+1. Once you do @sort:=0, then it should increment normally.

在SQL中,NULL + 1返回NULL。如果在此UPDATE之前未将@sort设置为非NULL值,则无论您评估@sort:= @ sort + 1多少次,它都将继续为NULL。一旦你执行了@sort:= 0,那么它应该正常递增。

Try this without doing it in an 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

I would guess it's only a coincidence that you have no duplicates after the first time you set @sort:=0.

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

edit: The above is true, but as you point out, it doesn't explain the behavior you're seeing, since logically @sort should be guaranteed set to 0 during evaluation of the first row.

编辑:以上情况属实,但正如您所指出的那样,它并不能解释您所看到的行为,因为在评估第一行时,应保证@sort的逻辑设置为0。

However, I notice if I change the order of the terms in the IF() expression, it all works, even if @sort is unset as we begin:

但是,我注意到如果我改变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;

I'm not sure if I understand it well enough to explain exactly why this works, but there's some wacky stuff regarding when user variables are evaluated. See this blog for lots of examples and gory details: "Advanced MySQL user variable techniques".

我不确定我是否理解得足以解释为什么这样可行,但是在评估用户变量时会有一些古怪的东西。请参阅此博客,了解大量示例和血腥细节:“高级MySQL用户变量技术”。

#1