MySQL - Simultaneous浮动增量和5.6之前选择中断的赋值

Question

为什么像SELECT (@sum:=(@var:=@sum)+some_table.val)...这样的查询会在MySQL≤5.5中为DECIMAL-type some_table.val自动将(@var:=@sum)转换为整数，而将其转换为DOUBLE/FLOAT呢？对5.6中的预期行为进行了哪些修改？

背景和阐述：

考虑下表：

CREATE TABLE t ( 
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, 
    sum_component FLOAT 
);
INSERT INTO t (sum_component) VALUES (0.5), (0.6), (0.4), (0.5);

我正在设计一个累积和查询，它获取累积和大于某个值的条目的id。通常情况下，这个查询符合以下要求：

SELECT t.id, 
    @cumulative_sum
    FROM t 
    CROSS JOIN (SELECT @cumulative_sum:=0) a 
    WHERE (@cumulative_sum:=@cumulative_sum+t.sum_component) > 1.3 
    ORDER BY id ASC LIMIT 1;

...but I碰巧还需要在所选条目之前存储累积和，以便以后进行计算，而来自此查询的cumulative_sum不返回预期的结果，重复计算最后一个条目。在本例中，我希望这个查询能够设置一个变量，该变量可以存储值1.1 (0.5 + 0.6)，而不必进行额外的计算。

如果我在增量步骤中将@cumulative_sum的旧值分配给@another_variable，我应该能够精确地做到这一点。

SELECT t.id, 
    @cumulative_sum
    FROM t 
    CROSS JOIN (SELECT @cumulative_sum:=0) a 
    WHERE (@cumulative_sum:=(@another_variable:=@cumulative_sum)+t.sum_component) > 1.3
    ORDER BY id ASC LIMIT 1;

在我的两台机器上--一台运行MySQL 5.6，另一台运行MariaDB 10.0.7 --上面的查询按预期执行：

MariaDB [a51]>     SELECT  t.id, @cumulative_sum
    FROM  t
    CROSS JOIN  
      ( SELECT  @cumulative_sum:=0) a
    WHERE  (@cumulative_sum:=(@another_variable:=@cumulative_sum)
                             +t.sum_component) > 1.3
    ORDER BY  id ASC
    LIMIT  1;

+----+--------------------+
| id | @cumulative_sum    |
+----+--------------------+
|  3 | 1.5000000298023224 |
+----+--------------------+
1 row in set (0.00 sec)

MariaDB [a51]> SELECT @another_variable;
+-------------------+
| @another_variable |
+-------------------+
| 1.100000023841858 |
+-------------------+
1 row in set (0.01 sec)

但在MySQL 5.5上，它没有：

mysql>     SELECT  t.id, @cumulative_sum
    FROM  t
    CROSS JOIN  
      ( SELECT  @cumulative_sum:=0) a
    WHERE  (@cumulative_sum:=(@another_variable:=@cumulative_sum)+t.sum_component) > 1.3
    ORDER BY  id ASC
    LIMIT  1;

Empty set (0.18 sec)

mysql> SELECT @another_variable;
+-------------------+
| @another_variable |
+-------------------+
|                 0 |
+-------------------+
1 row in set (0.01 sec)

观察查询是如何增加的，我们可以看到潜在的问题。下面是一个FLOAT sum_component的结果

mysql>     SELECT  t.id, (@cumulative_sum := (@another_variable:=@cumulative_sum)
                  + t.sum_component) AS cumulative_sum,
        sum_component
    FROM  t
    CROSS JOIN  
      ( SELECT  @cumulative_sum:=0) a
    ORDER BY  id ASC;

+----+----------------+---------------+
| id | cumulative_sum | sum_component |
+----+----------------+---------------+
|  1 |            0.5 |           0.5 |
|  2 |            0.6 |           0.6 |
|  3 |            0.4 |           0.4 |
|  4 |            0.5 |           0.5 |
+---+----------------+----------------+
4 rows in set (0.04 sec)

下面是拥有一个DECIMAL sum_component的样子：

mysql> ALTER TABLE t MODIFY sum_component DECIMAL(4,2);
Query OK, 4 rows affected, 2 warnings (0.16 sec)
Records: 4  Duplicates: 0  Warnings: 2

mysql>     SELECT  t.id, (@cumulative_sum := (@another_variable:=@cumulative_sum) 
                           + t.sum_component) AS cumulative_sum,
        sum_component
    FROM  t
    CROSS JOIN  
      ( SELECT  @cumulative_sum:=0) a
    ORDER BY  id ASC;

+----+----------------+---------------+
| id | cumulative_sum | sum_component |
+----+----------------+---------------+
|  1 |           0.50 |          0.50 |
|  2 |           1.60 |          0.60 |
|  3 |           2.40 |          0.40 |
|  4 |           2.50 |          0.50 |
+----+----------------+---------------+
4 rows in set (0.18 sec)

SQL Fiddle

Answer 1

这是因为在旧版本中，MySQL将0视为整数；您的初始赋值@cumulative_sum:=0将变量设置为整数。将赋值更改为@cumulative_sum:=0.0会在5.5上产生所需的行为：

SELECT t.id,
    @cumulative_sum
    FROM t
    CROSS JOIN (SELECT @cumulative_sum:=0.0) a
    WHERE (@cumulative_sum:=(@another_variable:=@cumulative_sum)+t.sum_component) > 1.3
    ORDER BY id ASC LIMIT 1;

手册解决了这个问题，但我没有发现有人提到在5.5到5.6之间行为的变化：

将值赋值给变量并在相同的非SET语句中读取值的另一个问题是，变量的默认结果类型基于语句开头的类型。以下示例说明了这一点： mysql> SET @a='test'; mysql> SELECT @a,(@a:=20) FROM tbl_name; 对于此SELECT语句，MySQL向客户端报告第一列是字符串，并将@a的所有访问转换为字符串，即使@a设置为第二行的数字。在SELECT语句执行后，@a被视为下一个语句的数字。 若要避免此行为出现问题，请不要在单个语句中为同一变量赋值并读取其值，或者将变量设置为0、0.0或“”，以便在使用该变量之前定义其类型。

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.5.44-log Source distribution

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test;
Database changed
mysql> CREATE TABLE t ( 
    ->     id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, 
    ->     sum_component FLOAT 
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO t (sum_component) VALUES (0.5), (0.6), (0.4), (0.5);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT t.id,
    ->     @cumulative_sum
    ->     FROM t
    ->     CROSS JOIN (SELECT @cumulative_sum:=0.0) a
    ->     WHERE (@cumulative_sum:=(@another_variable:=@cumulative_sum)+t.sum_component) > 1.3
    ->     ORDER BY id ASC LIMIT 1;
+----+----------------------------------+
| id | @cumulative_sum                  |
+----+----------------------------------+
|  3 | 1.500000029802322400000000000000 |
+----+----------------------------------+
1 row in set (0.00 sec)

mysql> SELECT @another_variable;
+-------------------+
| @another_variable |
+-------------------+
| 1.100000023841858 |
+-------------------+
1 row in set (0.00 sec)