Mysql update statement behaves differently depending on whether a user variable exists
I am rewriting a sort column that has duplicate or missing values:
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;
(Loop through all rows sorted by multiple key fields sequentially assigning increments to sort values; whenever any of these fields change, reboot from 0.)
This seems to work only if the @sort variable was created before the update was performed (although it doesn't matter what it was set to). Without "set @sort", all sort values are set to 0 or NULL.
Any ideas why this is so? MySQL version 5.0.51.
Update. To explain the logic in more detail: in the first line, @last = (@last: = ...) will always be false, and after that it will be false when any of the key fields from the previous line are changed. (NB none of the key fields concat'd ever be null). When false, we start the sort count again at 0 (@sort: = 0), otherwise it is incremented (@sort: = @sort + 1) and the new value is used.
In no case is @sort used before it is set in an update statement, so how it is set before the update statement has no meaning.
source to share
A disabled user variable is treated as NULL
if you reference it in an expression.
In SQL, NULL
+ 1 returns NULL
. If you haven't set a @sort
value NULL
before UPDATE
, it will stay NULL
no matter how many times you evaluate @sort:=@sort+1
. When you do @sort:=0
, then it should increase normally.
Try this without doing it in 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 is a coincidence where you have no duplicates after you first install @sort:=0
.
edit: The above is correct, but as you point out, it does not explain the behavior you are seeing, as it @sort
should logically be guaranteed to be set to 0 at the time of evaluating the first line.
However, I notice that if you change the order of the members in the expression IF()
, it all works, even if @sort
not started:
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 this well enough to explain exactly why this works, but there is some nonsense around evaluating custom variables. See this blog for lots of examples and details: " MySQL Custom Variable Advanced Methods ".
source to share