set autocommit=false; set @roomid:=-1; select min(roomid) into @roomid from room_info where roomid > ( select floor(max(roomid) * rand() + 1) from room_info ) and state = 1;
update room_info set state = 2 where roomid =@roomid and state = 1;
大招版本: set autocommit=false; set @roomid:=-1; select max(roomid) into @roomid from room_info; set @roomid:=floor(rand()*@roomid+1); update room_info set state = 2 where roomid = coalesce ( (select roomid from (select min(roomid) roomid from room_info where state=1 and roomid > @roomid) a), (select roomid from (select max(roomid) roomid from room_info where state=1 and roomid < @roomid) b) ) and state = 1 and @roomid:=roomid;
