从版本5.0.3起将负数存储在mysql表的十进制字段中 [英] storing negative number in decimal field of mysql table as of version 5.0.3

问题描述

1. 我的表中的字段很少，其金额列的类型为十进制.
2. 此列的存款金额(正值)或提款金额(负值).
3. 我将正值存储为120，将负值存储为-50.
4. 我对该列求和并得到预期的结果.
5. MySQL版本为:5.1.33-community.

6. 当我查看有关十进制的mysql文档时，我对其描述感到困惑.

1. My table has few fields with an amount column of type decimal.
2. This column will have either a deposited amount (a positive value) or a withdraw amount (a negative value).
3. I store the positive value as just 120 and the negative value as -50.
4. I sum the column and got the result as expected.
5. Mysql version is: 5.1.33-community.

6. When i checked mysql documentation about decimal i confused with their description.

在MySQL 5.0.3之前，如果已插入 +0003.1到DECIMAL(5,1)列中，它存储为+0003.1.从MySQL开始 5.0.3 ，它存储为3.1.对于负数，文字- 字符不再存储. 依赖较旧版本的应用程序 行为必须修改为帐户 为了这个改变. http://dev.mysql.com/doc/refman/5.0/zh-CN/precision-math-decimal-changes.html

Before MySQL 5.0.3, if you inserted +0003.1 into a DECIMAL(5,1) column, it was stored as +0003.1. As of MySQL 5.0.3, it is stored as 3.1. For negative numbers, a literal - character is no longer stored. Applications that rely on the older behavior must be modified to account for this change. http://dev.mysql.com/doc/refman/5.0/en/precision-math-decimal-changes.html

当我在phpmyadmin中列出行时，我可以看到负号，并且当我计算结果符合预期时.但是文档说不会出现任何迹象.

When i listed the rows in phpmyadmin i could see the negative sign and when i calculated the result was as expected. but the documentation said no sign will appear.

以十进制存储负数是否很好(我是否在学校学习过?很困惑)?...还是我们必须使用float.

Is it good to store negative number in decimal(haven't i studied in school? confused)?... or do we have to use float.

我不知道float会使计算复杂化，因此在某些情况下建议使用十进制.

I learn't that float would complicate calculations and was advised to stick with decimal for certain conditions.

我想对此提出建议.

推荐答案

据我了解，文档说它不会存储文字-" 字符，这意味着它现在可能正在执行其他已签名的INTEGER字段所执行的操作，并且正在存储一个符号位来表示负数.

From what I understand, the documentation is saying it won't store a literal "-" character, which means it's probably now doing what the other signed INTEGER fields have always done and it's storing a sign bit to denote negative numbers instead.

在数字前您仍会看到一个负号，因为它是由MySQL通过该符号位生成的.

You're still seeing a minus sign preceding the number because it's being generated by MySQL as a result of that sign bit.

如果您不了解符号位，则可以考虑带符号的字节如何存储-128到127之间的数字，而无符号的字节如何存储0到255之间的数字.这是因为a中的8位之一带正负号的数字用于存储+/-(1为负，0为正)，而其余位提供的数字最大为2 ^ 7(-128或127).

If you don't understand the sign bit, you can consider how a signed byte can store numbers from -128 to 127, while an unsigned byte can store numbers from 0 to 255. That's because one of the 8 bits in a signed number is being used to store +/- (1 is negative, 0 is positive), while the remaining bits offer numbers up to 2^7 (-128 or 127).

因此，例如，如果位1111具有符号位，则它们将等于-7(负+ 4 + 2 + 1)，但是如果它们未签名，则它们将等于15(8 + 4 + 2 + 1) .存储的位数仍然相同.

So, for example, if the bits 1111 had a sign bit they would equal -7 (negative+4+2+1), but if they were unsigned they'd equal 15 (8+4+2+1). It's still the same amount of bits being stored.

您可能想知道为什么有符号数中的负数限制可以使用第8位，而正数限制为7位的总和(比第8位少1).这是因为10000000同时被视为负数和第8位，因为它的-0表示形式否则与00000000表示0冗余.负零和正零之间没有区别，因此负最高有效位始终是该值本身(但为负).

You may wonder why the negative bound in a signed number can use the 8th bit, while the positive bound is limited to the sum of the 7 bits (1 less than the 8th bit). This is because 10000000 is considered to be both negative and the 8th bit simultaneously, because its representation of -0 otherwise is redundant with 00000000 which represents 0. There's no distinction between negative and positive zero, so a negative most significant bit is always the value of that bit itself (but negative).

这篇关于从版本5.0.3起将负数存储在mysql表的十进制字段中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！