How do I keep the variable value in a MySQL stored procedure from changing when records are updated?

Question

I must be overlooking something simple. I'm setting a variable from a query result in a MySQL stored procedure like this:

SELECT @myName := username FROM User WHERE ID=1;

So, @myName is storing the username of userid 1, which is 'Paul'. Great.

But later in the stored procedure I run an update on this record:

UPDATE User SET username = 'Fred' WHERE ID=1;

Now, for some reason, @myName = 'Fred' when it should still equal 'Paul', right? It appears MySQL is just creating a pointer to the record rather than storing a static value in the @myName variable.

So in short, I want to be able to store a value in a variable from a query result and assure the value of my variable doesn't change, even when the data in the table it was set from changes.

What am I missing? Thanks in advance!

Answer 1

That's very surprising, I agree. I'm not sure how to explain it, but for what it's worth, try this instead:

SELECT username INTO myName FROM User WHERE ID=1;

See http://dev.mysql.com/doc/refman/5.0/en/select-into-statement.html

update: I'm trying to reproduce this problem, but I can't. I'm using MySQL 5.0.51 on Mac OS X.

drop table if exists user;
create table user (
  id serial primary key,
  username varchar(10)
);
insert into user (username) values ('Paul');

drop procedure if exists doit;
delimiter !!
create procedure doit()
begin
  declare name varchar(10);
  select @name:=username from user where id=1;
  select @name; -- shows 'Paul' as expected
  update user set username = 'Fred' where id=1;
  select @name; -- still shows 'Paul'
end!!
delimiter ;

call doit();

Can you try the code above in your test database and let us know if it exhibits the problem you describe?

Answer 2

Variable @name still has the value Paul. If you want to update your variable you should assign the new value :

drop procedure if exists doit; delimiter !! create procedure doit() begin declare name varchar(10); select @name:=username from user where id=1; select @name; -- shows 'Paul' as expected update user set username = 'Fred' where id=1; select @name:=username from user where id=1; -- this will update the @name value select @name; -- shows 'Fred' end!! delimiter ;

call doit();