問題描述

警告:#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模板網！