mysql - Rmysql查询没有返回正确的值？

Question

我在 r 中有一个查询，使用 rmysql 包:

sql.query = paste0("SELECT * FROM performance_db.pcTab WHERE 
                       fk_ID_calc = (SELECT ID_calc FROM performance_db.calculationTab WHERE
                       turbine_model = '",input$t1,"' AND 
                       op_mode = '",input$t2,"' AND 
                       date_entered ='",input$t3,"')
                       AND sensor = 'P_el'"
  )
    spc = fetch(dbSendQuery(conn, sql.query), n = -1)

这返回一个表:

head(spc)
  ID_pc voltage_side sensor Air_density v2.5 v3  v4  v5  v6   v7   v8   v9  v10  v11  v12  v13  v14  v15  v16  v17  v18  v19
1     1            0   P_el       1.225    0 44 184 404 721 1165 1686 2107 2272 2300 2300 2300 2300 2300 2300 2300 2300 2300
2     2            0   P_el       1.000   NA 33 155 346 622 1010 1486 1967 2224 2286 2294 2300 2300 2300 2300 2300 2300 2300
3     3            0   P_el       1.030   NA 35 161 358 642 1041 1529 1999 2235 2291 2297 2300 2300 2300 2300 2300 2300 2300
4     4            0   P_el       1.060   NA 37 167 369 661 1072 1571 2036 2249 2295 2300 2300 2300 2300 2300 2300 2300 2300
5     5            0   P_el       1.090   NA 39 173 381 681 1103 1612 2060 2256 2300 2300 2300 2300 2300 2300 2300 2300 2300
6     6            0   P_el       1.120   NA 42 178 392 701 1134 1649 2080 2263 2300 2300 2300 2300 2300 2300 2300 2300 2300

我想在 Air_密度上添加额外的过滤器，例如:

sql.query = paste0("SELECT * FROM performance_db.pcTab WHERE 
                       fk_ID_calc = (SELECT ID_calc FROM performance_db.calculationTab WHERE
                       turbine_model = '",input$t1,"' AND 
                       op_mode = '",input$t2,"' AND 
                       date_entered ='",input$t3,"')
                       AND sensor = 'P_el' AND Air_density = 1"
  )

这仅返回一行包含 Air_密度 = 1 的信息:

ID_pc voltage_side sensor Air_density v2.5 v3  v4  v5  v6   v7   v8   v9  v10  v11  v12  v13  v14  v15  v16  v17  v18  v19
1     2            0   P_el           1   NA 33 155 346 622 1010 1486 1967 2224 2286 2294 2300 2300 2300 2300 2300 2300 2300

但是当我尝试根据其他空气密度过滤时，它不会返回任何内容。就像在上面提到的查询中我输入 AND Air_密度 = 1.225 :

head(spc)
 [1] ID_pc        voltage_side sensor       Air_density  v2.5         v3           v4           v5           v6          
[10] v7           v8           v9           v10          v11          v12          v13          v14          v15         
[19] v16          v17          v18          v19

我不知道为什么会这样!!!

Answer 1

FLOAT 和 DOUBLE 数据类型不精确。

如果保存值1.225，则可以将其另存为1.22500001。当您尝试查询值为 1.225 的所有数据时，它不会找到它，因为它被保存为 1.22500001。

您应该更好地使用数据类型DECIMAL或NUMERIC或者尝试将 FLOAT 值CAST 转换为 DECIMAL，如下所示:

CAST(Air_density as DECIMAL(10,3))

这是来自 MySQL 的文档:

11.2.3 Floating-Point Types (Approximate Value) - FLOAT, DOUBLE

The FLOAT and DOUBLE types represent approximate numeric data values. MySQL uses four bytes for single-precision values and eight bytes for double-precision values.

For FLOAT, the SQL standard permits an optional specification of the precision (but not the range of the exponent) in bits following the keyword FLOAT in parentheses. MySQL also supports this optional precision specification, but the precision value is used only to determine storage size. A precision from 0 to 23 results in a 4-byte single-precision FLOAT column. A precision from 24 to 53 results in an 8-byte double-precision DOUBLE column.

MySQL permits a nonstandard syntax: FLOAT(M,D) or REAL(M,D) or DOUBLE PRECISION(M,D). Here, (M,D) means than values can be stored with up to M digits in total, of which D digits may be after the decimal point. For example, a column defined as FLOAT(7,4) will look like -999.9999 when displayed. MySQL performs rounding when storing values, so if you insert 999.00009 into a FLOAT(7,4) column, the approximate result is 999.0001.

Because floating-point values are approximate and not stored as exact values, attempts to treat them as exact in comparisons may lead to problems. They are also subject to platform or implementation dependencies. For more information, see Section B.5.4.8, “Problems with Floating-Point Values”

For maximum portability, code requiring storage of approximate numeric data values should use FLOAT or DOUBLE PRECISION with no specification of precision or number of digits.

https://dev.mysql.com/doc/refman/5.7/en/floating-point-types.html

此链接也应该对您有帮助:

https://dev.mysql.com/doc/refman/5.7/en/problems-with-float.html