如何更新记录更新时，如何保持MySQL存储过程中的变量值不变？

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

我必须忽略一些简单的事情。我正在MySQL存储过程中的查询结果中设置一个变量,如下所示:

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

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

因此,@ myName存储用户标识1的用户名,即“保罗”。大。

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.

现在,出于某种原因,@ myName ='Fred',它应该仍然等于'Paul',对吗?似乎MySQL只是创建一个指向记录的指针,而不是在@myName变量中存储静态值。

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!

我错过了什么?提前致谢!

3 个解决方案

#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.

更新:我正在尝试重现这个问题,但我不能。我在Mac OS X上使用MySQL 5.0.51。

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?

您可以在测试数据库中尝试上面的代码,并告诉我们它是否表现出您描述的问题?

#2

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

变量@name仍然具有值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:=username from user where id=1; -- this will update the @name value
  select @name; -- shows 'Fred'
end!!
delimiter ;

call doit();

#3

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

set  @name = '' /*Initialize @name with '' and then get the result as you expected*/

update user set username = 'Fred' where id=1;
select @name; -- now you will get exepected results
end!!
delimiter ;

call doit();

Regards, Jitendra Pasi.(Ugam)

此致,Jitendra Pasi。(Ugam)

#1