MySQL 8.0.28存储过程触发用户变量表达式设置弃用警告求解

在MySQL 8.0.28环境中创建了名为po_sp_update_category的存储过程，功能正常运行，但触发警告：

"Setting user variables within expressions is deprecated Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'."

我已经通过SELECT INTO为声明的本地变量赋值，但仍出现该警告，不清楚问题所在，存储过程代码如下：

DROP PROCEDURE IF EXISTS `po_sp_update_category`//
CREATE PROCEDURE `po_sp_update_category`(
  IN    p_ID            INT,
  IN    p_Category_Group_ID INT,
  IN    p_Name          VARCHAR(50),
  IN    p_Sort_Order        INT,
  IN    p_Description       TEXT,
  IN    p_Picture       INT
)
BEGIN

  DECLARE   v_RecordsUpdated    INT;
  DECLARE   v_ID            INT;
  DECLARE   v_DuplicateCategory INT;

  SELECT  COUNT(ID)
  INTO    v_DuplicateCategory
  FROM    po_tblcategory
  WHERE   Name          =   p_Name
  AND     ID            <>  p_ID
  ;

  IF v_DuplicateCategory > 0 THEN

    SELECT  2105        AS  DB_ERROR,
            p_ID        AS  DB_ID
    FROM    DUAL
    ;

  ELSE

    IF p_ID = -1 THEN
    
      INSERT INTO po_tblcategory
        (
    ID,
    Category_Group_ID,
    Name,
    Sort_Order,
    Description,
    Picture
    )
      VALUES
    (
    NULL,
    p_Name,
    p_Category_Group_ID,
    p_Sort_Order,
    p_Description,
    p_Picture
    )
    ;
        
    SELECT  ROW_COUNT()
    INTO    v_RecordsUpdated
    ;
        
    SELECT  717854
    INTO    v_ID
    ;
    
    IF v_RecordsUpdated > 0 THEN
                 
        SELECT  100     AS  DB_ERROR,
            v_ID        AS  DB_ID
        FROM    DUAL
        ;
                
    ELSE
                        
        SELECT  2107        AS  DB_ERROR,
            v_ID        AS  DB_ID
        FROM    DUAL
        ;
            
    END IF;
    ELSE
    
    UPDATE  po_tblcategory
    SET Name            =   p_Name,
        Category_Group_ID   =   p_Category_Group_ID,
        Sort_Order      =   p_Sort_Order,
        Description     =   p_Description,
        Picture         =   p_Picture
    WHERE   ID          =   p_ID
    ;
        
    SELECT  100         AS  DB_ERROR,
        p_ID            AS  DB_ID
    FROM    DUAL
    ;
        
    END IF;
        
    SET @ordering_inc = 10;
    SET @new_ordering = 0;

    UPDATE  po_tblcategory  
    SET     Sort_Order      =   (@new_ordering := @new_ordering + @ordering_inc)
    ORDER BY    Sort_Order      ASC,
        Name            ASC
    ;       


  END IF;
END

请问问题出在哪里？

警告的根源是你在UPDATE语句的表达式中直接赋值用户变量@new_ordering：

SET     Sort_Order      =   (@new_ordering := @new_ordering + @ordering_inc)

MySQL 8.0已经弃用了这种在表达式内设置用户变量的写法，即使你已经对本地变量使用了SELECT INTO，这段代码依然会触发警告。

替代方案：使用窗口函数重新排序 #

利用MySQL 8.0支持的ROW_NUMBER()窗口函数，替代用户变量的方式来重新生成Sort_Order值，完全避免弃用语法：

-- 替换原来的SET变量和UPDATE语句
UPDATE po_tblcategory t
JOIN (
    SELECT 
        ID,
        ROW_NUMBER() OVER(ORDER BY Sort_Order ASC, Name ASC) * 10 AS new_sort
    FROM po_tblcategory
) AS sorted ON t.ID = sorted.ID
SET t.Sort_Order = sorted.new_sort;

额外注意：INSERT语句的字段顺序错误 #

你代码中p_ID = -1分支的INSERT语句存在字段值顺序错误：原代码中Category_Group_ID对应的是p_Name，Name对应的是p_Category_Group_ID，这会导致数据插入错误，修正后的INSERT应该是：

INSERT INTO po_tblcategory
(
    ID,
    Category_Group_ID,
    Name,
    Sort_Order,
    Description,
    Picture
)
VALUES
(
    NULL,
    p_Category_Group_ID, -- 修正：对应Category_Group_ID字段
    p_Name, -- 修正：对应Name字段
    p_Sort_Order,
    p_Description,
    p_Picture
);

内容的提问来源于stack exchange，提问作者Kyle White