MYSQL - 警告:#1287 在表达式中设置用户变量已被弃用，并将在未来版本中删除

2021-09-25 00:00:00 window-functions mysql row-number

警告:#1287 在表达式中设置用户变量已被弃用，并将在未来版本中删除.考虑替代方案:SET variable=expression, ..."或SELECT expression(s) INTO variables(s)".

Warning: #1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.

这是 MySQL 8.0.21 吐出来的.代码有效......但我也需要它在未来版本中工作......所以我的问题是这个警告的正确方法是什么?

This is what MySQL 8.0.21 spits out. The code works... But I need it to work in future versions too.... So my question is what is the correct approach to this warning?

我需要将一个变量设置为某个数字(我将通过一行的 ID 获得).而且我需要它在每个 SET 语句中加 1.

I need a variable to be set to some number (which I will get via ID of a row). And I need it to be incremented by 1 with every SET statement.

我应该如何更改此代码以使警告消失?

How should I change this code so the warning would go away?

SET @fromorder = (SELECT `order` FROM forms WHERE id=5);
/* SET @fromorder = 4;  - so this is the same in the example below */
UPDATE forms SET `order` = @fromorder := @fromorder + 1 WHERE `order` > -1 and `order` <= 4 ORDER BY `order` ASC;
SET @fromorder = null;

据我所知，问题在于:

`order` = @fromorder := @fromorder + 1

我猜这需要改变吗?

因此:

+-------+-----------+
|   id  |   order   |
+-------+-----------+
|   1   |   0       |
|   2   |   1       |
|   3   |   2       |
|   4   |   3       |
|   5   |   4       |
+-------+-----------+

我想通过这个查询得到这个(例如):

I want to get this (for example) with this query:

+-------+-----------+
|   id  |   order   |
+-------+-----------+
|   1   |   5       |
|   2   |   6       |
|   3   |   7       |
|   4   |   8       |
|   5   |   9       |
+-------+-----------+

这是一个简单的 for 循环，增量 += 1;

It is an easy for loop with incrementation += 1;

推荐答案

你的 MySql 版本支持窗口函数，通过使用 ROW_NUMBER() 窗口函数你可以避免变量:

Your version of MySql supports window functions and by using ROW_NUMBER() window function you can avoid variables:

UPDATE forms f
CROSS JOIN (SELECT `order` FROM forms WHERE id = 5) c
INNER JOIN (
  SELECT *, ROW_NUMBER() OVER (ORDER BY `order`) rn
  FROM forms
  WHERE `order` > -1 and `order` <= 4 
) t ON t.id = f.id
SET f.`order` = c.`order` + t.rn;

请参阅演示.

相关文章