mysql 脚本变量和 max 函数 [英] mysql script variables and max function

问题描述

这个问题很简单.我只是找不到解决方案，所以我问，这是*.sql文件的内容:

The question is fairly simple. I just couldn't locate the solution so I'm asking, Here is the contents of a *.sql file:

select @max_command_idx=max(command_idx)+1  from command;
insert into command values(3,0,@max_command_idx+1 ,"sub","ctrlr",1,0,"plan",0);
insert into command values(4,1,@max_command_idx+1 ,"sub","ctrlr",1,0,"pla1n",0);

当我执行脚本时，我希望@max_command_idx 是一个固定值.这意味着它应该在随后的两个插入语句中替换相同"的值.但它没有这样做.它所做的是为每个插入语句提供 max_command_idx 的新值.这是为什么?我该如何解决?谢谢

when i executed the script, i expected @max_command_idx to be a fixed value.which means it should substitute "same" value in both of the subsequent insert statements. But it didn't do that. what it did was to give me new value of max_command_idx for each insert statement. why is that? and how may i solve it? Thank you

推荐答案

第一行会将@max_command_idx 设置为空，因为您实际上是在选择一个布尔条件.所以在运行时:

The first row will set @max_command_idx to be null since you are actually selecting a boolean condition. So when running this:

select @max_command_idx=max(command_idx)+1  from command;

您实际上是在说如果@max_command_idx 等于当前最大command_idx + 1，则选择我1，如果不是，则选择我0，如果@max_command_idx 为空，则选择我".

You are actually saying "select me 1 if @max_command_idx is equal to the current maximum command_idx + 1, select me 0 if it is not and select me null if @max_command_idx is null".

在您的情况下，@max_command_idx 为空，因此布尔结果为空，因此看起来好像@max_command_idx 在每个后续插入中重新初始化.

In your case @max_command_idx is null so the boolean result is null hence it appears as if @max_command_idx is re-initialised in each of the subsequent inserts.

试试这个:

select max(command_idx)+1 into @max_command_idx  from command;
insert into command values(3,0,@max_command_idx+1 ,"sub","ctrlr",1,0,"plan",0);
insert into command values(4,1,@max_command_idx+1 ,"sub","ctrlr",1,0,"pla1n",0);

应该做你想做的...

这篇关于mysql 脚本变量和 max 函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！