MySQL 对表达式中的 SET 用户变量抛出警告

Question

由于退出处理程序捕获警告并退出 block ，我有一个存储过程在 MySQL 8.0.17 中不再工作。我猜测错误是由于 @transql 变量而引发的，但我不能 100% 确定。这样做的主要问题是，由于退出，正在调用的存储过程永远不会到达它。

+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| @p1   | @p2                                                                                                                                                                                                                                                                                                                                                 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| HY000 | 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)'. |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

我检查了其他一些问题，但无法将它们转换为此查询。

BEGIN
 DECLARE EXIT HANDLER FOR SQLEXCEPTION, sqlwarning
    BEGIN
   GET DIAGNOSTICS CONDITION 1 @p1 = RETURNED_SQLSTATE, @p2 = MESSAGE_TEXT;
   ROLLBACK;
   END;

  START TRANSACTION;
  SET @key1 = key1;
    SET @transkey = transkey;
    SET @transql = CONCAT('SELECT @cckey:=cckey FROM `', key1,
    '_kr` AS t1 WHERE `transkey` = ?');
    PREPARE stmt FROM @transql;
    EXECUTE stmt
    USING @transkey;
    DEALLOCATE PREPARE stmt;

    CALL sp_procedure1(key1, @cckey, @transkey)

  COMMIT;
END

Answer 1

警告

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)'.

几乎是您问题的完美答案。但是为了帮助您......第一个建议不适用于准备好的语句，因此您需要使用第二个建议:

SET @transql = CONCAT('SELECT cckey INTO @cckey FROM `', key1,
    '_kr` AS t1 WHERE `transkey` = ?');

参见:SELECT ... INTO Syntax