MySQL 变量从 SUM() 获取 NULL

Question

我有这个 MySQL 语句:

SELECT  @my_sum := SUM(delibera.particolari_prodotti),
@my_sum_copy := @my_sum + 1
FROM delibera

我不明白为什么@my_sum 有一个数值但@my_sum_copy 是NULL。

我也试过这个:

SELECT  @my_sum := SUM(delibera.particolari_prodotti),
@my_sum_copy := CONVERT(@my_sum, UNSIGNED) + 1
FROM delibera

...还有这个:

SELECT  @my_sum := CONVERT(SUM(delibera.particolari_prodotti), UNSIGNED),
@my_sum_copy := @my_sum + 1
FROM delibera

但它是一样的。

如何让@my_sum_copy 具有有效的数值？

非常感谢。

更新:附上代码

SELECT
    @`my_sum`,
    @`my_sum_copy` := @`my_sum` + 1
    FROM 
    (
        SELECT
            @`my_sum` := SUM(`particolari_prodotti`)
        FROM
        `delibera`,
            (SELECT
                @`my_sum` := NULL,
                @`my_sum_copy` := NULL
            ) `inner_der` 
        ) `outer_der`;

我终于有了@my_sum_copy 的值。

但是现在@my_sum 设置为 [BLOB - 8 B]。

Answer 1

来自文档:

9.4 User-Defined Variables

...

As a general rule, other than in SET statements, you should never assign a value to a user variable and read the value within the same statement.

...

For other statements, such as SELECT, you might get the results you expect, but this is not guaranteed.

...

..., the order of evaluation for expressions involving user variables is undefined.

...

一个有用的选项是使用子查询:

SELECT
  @`my_sum`,
  @`my_sum_copy` := @`my_sum` + 1
FROM 
  (
    SELECT
      @`my_sum` := SUM(`particolari_prodotti`)
    FROM
      `delibera`,
      (SELECT
         @`my_sum` := NULL,
         @`my_sum_copy` := NULL
      ) `inner_der` 
  ) `outer_der`;

SQL Fiddle demo