如何在mysql中存储一个十进制的calcaulation结果，并将其返回到内存中 [英] How to store a decimal calcaulation result in mysql and retrive it back as they were in memory

问题描述

MySQL文档说： / p>

DECIMAL和NUMERIC类型存储精确的数字数据值。使用这些类型时，重要的是要保持精确的精度，例如货币数据。

我在该列上做了这个测试 decimal_column DECIMAL（31,30） 。

$ p $ insert into tests（decimal_column）values（1/3）;


然后检查已经存储的内容给出这个

  select * from tests; 
>结果是：0.333333333000000000000000000000 
  

然后用这个查询反转数学运算给这个

 从test中选择decimal_column * 3; 
>结果：0.999999999000000000000000000000 
  

当我们在我们的计算器上进行操作时，我期待得到整数1一个excel表单！像这样

  #calculator或excel表格
>输入：1/3 
>结果： 0.3333333333333333333333333333333333 
>输入：* 3 
>结果：1 
  

<为什么MySQL没有存储（1/3）的确切的二进制表示，所以我可以在计算中再次使用这个结果，就像计算器或Excel表一样在内存中。

2 - 如何在mysql中存储（1/3）在计算时间的内存中，所以我可以检索确切的值，并执行像 3 * $ storedValue 类似的结果，使得 1 为整数我们在一个计算器或Excel表格中。

在你的例子中，这个值在被存储到表中之前已经被破坏了。

不幸的是，如果你写1/3，那么将会使用默认值代表：

 选择1/3 
 
 0.333333333 
  
 
 
 
你可以看到，精确度不够。
 
 
另外一个问题是，当你发送一个常数（1或3）到服务器，你可以使用一个库或连接器，这可以自由调整值。例如，它可能认为1和3是整数，它们的结果被视为一个整数。所以你得到1/3 = 0，但1./3 = 0.333333，因为在1点。使连接器意识到它需要使用其默认浮点。而且只有6个3，因为连接器的默认浮点有6位数。然后将其存储到数据库中，但已经太迟了。你正在高精度地存储一个已被截断为低精度的值。
 
 
你可以尝试从头开始转换常量。代替1，你使用1作为足够大的小数。我在这里使用你的31,30，但检查你不需要存储更大的数字。可能31,20会更好。 
 
 
  mysql> SELECT 1/3 UNION SELECT CAST（1 AS DECIMAL（31,30））/ CAST（3 AS DECIMAL（31,30））; 
 + ---------------------------------- + 
 | 1/3 | 
 + ---------------------------------- + 
 | 0.333333333000000000000000000000 | 
 | 0.333333333333333333333333333333 | 
 + ---------------------------------- + 
 2行（0.00秒）
  
非常尴尬，但结果应该会更好。另外，我认为只需要在表达式中输入一个值; MySQL会根据需要提升所有涉及的数量。因此，将CAST（0 AS DECIMAL（x，y））加到sum和CAST（1 AS DECIMAL（x，y））上可能就足够了。 
 
 
 的MySQL> SELECT 3 * CAST（1 AS DECIMAL（31,30））/ CAST（3 AS DECIMAL（31,30））; 
 + --------------------------------------------- ---------- + 
 | 3 * CAST（1 AS DECIMAL（31,30））/ CAST（3 AS DECIMAL（31,30））| 
 + --------------------------------------------- ---------- + 
 | 1.000000000000000000000000000000 | 
 + --------------------------------------------- ---------- + 
 1行设置（0.00秒）
 
 mysql> SELECT CAST（1 AS DECIMAL（31,30））* 1/3; 
 + ---------------------------------- + 
 | CAST（1 AS DECIMAL（31,30））* 1/3 | 
 + ---------------------------------- + 
 | 0.333333333333333333333333333333 | 
 + ---------------------------------- + 
 1行（0.00秒）
  
请注意 不起作用，因为乘法具有更高的优先级： 
 
 
  mysql> SELECT CAST（0 AS DECIMAL（31,30））+ 1/3; 
 + ---------------------------------- + 
 | CAST（0 AS DECIMAL（31,30））+ 1/3 | 
 + ---------------------------------- + 
 | 0.333333333000000000000000000000 | 
 + ---------------------------------- + 
 1行（0.00秒）
  
 
MySQL documentation says :

  
The DECIMAL and NUMERIC types store exact numeric data values. These types are used when it is important to preserve exact precision, for example with monetary data. 
I did this test on that column decimal_column DECIMAL(31,30).
insert into tests (decimal_column) values(1/3);
then inspecting what has been stored gives this
select * from tests ;
> result : 0.333333333000000000000000000000
then reversing the math operation with this query gives this
select decimal_column*3 from test;
> result: 0.999999999000000000000000000000
I was expecting to get integer "1" as we do it on our calculators and in an excel sheet ! like this
 #calculator or excel sheet
 >input: 1 / 3
 >result: 0.3333333333333333333333333333333333
 >input: * 3
 >result: 1
1- why MySQL didn't store the exact binary representation of (1 / 3) so I can use that result again in my calculations as they are in memory like a calculator or an excel sheet.

2- How to store in mysql the result of (1/3) as they are in the memory during calculation time, So I can retrieve the exact value back and do something like 3 * $storedValue to result in 1 as integer as we do in a calculator or excel sheet.
解决方案
The problem is not in storage. In your example, the value was broken before storing it in the table.

Unfortunately, if you write 1/3, that will be calculated (and inserted) using the default representation:
SELECT 1/3

0.333333333
which, as you can see, has insufficient precision.

An additional problem is that when you send a constant (1, or 3) to the server, yo do so using a library or a connector, which can take liberties with the value. For example, it might believe that "1" and "3" are integers and their result is to be treated as an integer. So you get "1/3 = 0", but "1./3 = 0.333333", because the dot in "1." makes the connector realize that it needs to use its default floating point. And you get only six 3's because the "default floating point" of the connector has 6 digits. Then you store it into the database, but it is too late. You're storing with high precision a value that has been truncated to low precision.

You can try casting constants from the beginning. Instead of "1", you use the casting of 1 as a sufficiently large decimal. I'm using your 31,30 here, but check that you don't need to store larger numbers. Possibly, "31,20" would be better.
mysql> SELECT 1/3 UNION SELECT CAST(1 AS DECIMAL(31,30))/CAST(3 AS DECIMAL(31,30));
+----------------------------------+
| 1/3                              |
+----------------------------------+
| 0.333333333000000000000000000000 |
| 0.333333333333333333333333333333 |
+----------------------------------+
2 rows in set (0.00 sec)
It is very awkward, but results should be better. Also, I think that it's only necessary to cast one value in an expression; MySQL will then promote all involved quantities as necessary. So, adding CAST(0 AS DECIMAL(x,y)) to sums and CAST(1 AS DECIMAL(x,y)) to multiplications might be enough.
mysql> SELECT 3*CAST(1 AS DECIMAL(31,30))/CAST(3 AS DECIMAL(31,30));
+-------------------------------------------------------+
| 3*CAST(1 AS DECIMAL(31,30))/CAST(3 AS DECIMAL(31,30)) |
+-------------------------------------------------------+
|                      1.000000000000000000000000000000 |
+-------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT CAST(1 AS DECIMAL(31,30))*1/3;
+----------------------------------+
| CAST(1 AS DECIMAL(31,30))*1/3    |
+----------------------------------+
| 0.333333333333333333333333333333 |
+----------------------------------+
1 row in set (0.00 sec)
Note that this doesn't work because multiplication has higher precedence:
mysql> SELECT CAST(0 AS DECIMAL(31,30))+1/3;
+----------------------------------+
| CAST(0 AS DECIMAL(31,30))+1/3    |
+----------------------------------+
| 0.333333333000000000000000000000 |
+----------------------------------+
1 row in set (0.00 sec)


                        
这篇关于如何在mysql中存储一个十进制的calcaulation结果，并将其返回到内存中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！