問題描述
警告:#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;
請參閱演示.
這篇關于MYSQL - 警告:#1287 在表達式中設置用戶變量已被棄用，并將在未來版本中刪除的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！