在一个查询中执行 UPDATE 并设置用户变量

发布于 2024-07-14 07:06:28 字数 477 浏览 9 评论 0原文

每个熟悉 php 的 mysql_query 命令的人都知道，它不允许我们在一次调用中执行多个查询（由 ; 分隔符分隔）...

我的问题是我无法定义用户变量在我的 UPDATE 查询中，以便我为每一行增加它（有点像自动增量）。它应该看起来像这样：

SET @t1=0;
UPDATE `mytable` SET `order` = (@t1:=(@t1+1)) ORDER BY `order` ASC;

我的问题是，由于我无法定义变量然后进行更新，所以我找不到在查询中设置变量的方法。我尝试定义它是否为 NULL：

... `order` = (IFNULL( @t1 := ( @t1 + 1 ) , @t1 := 0 )) ...

但它不起作用，因为变量在它工作的每一行都会重置。

任何熟悉 mysql 的人都看到了解决方案吗？提前致谢。

原文

Everyone familiar with php's mysql_query command, knows that it doesn't allow us to perform more than one query (separated by the ; delimiter) in one call...

My problem is that I wan't to be able to define a user variable in my UPDATE query so that I increment it for each row (kinda like auto increment).
It should look like something as this:

SET @t1=0;
UPDATE `mytable` SET `order` = (@t1:=(@t1+1)) ORDER BY `order` ASC;

My problem is that, since I can't define my variable and then make the update, I can't find a way to set the variable inside the query.
I've tried to define it if it was NULL:

... `order` = (IFNULL( @t1 := ( @t1 + 1 ) , @t1 := 0 )) ...

but it didn't worked since the variable resets at each row it works on.

Anyone familiar with mysql that see's a solution?
Thanks in advance.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

天赋异禀 2024-07-21 07:06:28

老问题，但无论如何，这里有一个答案：

UPDATE `mytable` SET `order` = (@t1 := IFNULL(@t1, 0) + 1) ORDER BY `order` ASC;

如果 @t1 没有值或返回 @t1 的值，IFNULL(@t1, 0) 返回 0 如果它有值。

因此，第一行 @t1 未设置，它更新为 order = (@t1 := 0 + 1) ，在接下来的行 @t1< /code> 已经有一个值并且每行加 +1。

Old question but here's an answer anyway:

UPDATE `mytable` SET `order` = (@t1 := IFNULL(@t1, 0) + 1) ORDER BY `order` ASC;

IFNULL(@t1, 0) returns 0 if @t1 doesn't have a value or returns the value of @t1 if it has a value.

So on the first row @t1 is not set and it updates as order = (@t1 := 0 + 1) and on the following rows @t1 already has a value and adds +1 each row.

回复收藏 0 原文

难忘№最初的完美 2024-07-21 07:06:28

您可以使用 mysqli 库，它允许使用

mysqli->multiple_query( string $querys);

http://us.php.net 在一个查询中进行多个查询/mysqli_multi_query

You could use the mysqli library, it allows for multiple querys in one query using the

mysqli->multiple_query( string $querys);

http://us.php.net/mysqli_multi_query

回复收藏 0 原文

爱的十字路口 2024-07-21 07:06:28

冒险，怎么样...

... `order` = (SELECT `order`+1 FROM `mytable` ORDER BY `order` DESC LIMIT 1)

或者类似的东西作为子查询？...我不确定子查询是否在每次更新后重新运行，但如果是的话，它应该选择以前最高的 order 值并增加它？

Going out on a limb, how about...

... `order` = (SELECT `order`+1 FROM `mytable` ORDER BY `order` DESC LIMIT 1)

or something like that as a subquery?... I'm not sure whether the subquery re-runs after each update, but if it does, it should select the previously highest order value and increment it?

回复收藏 0 原文

~没有更多了~