如何解释数据库中数字的精度和小数位数?
我在数据库中指定了以下列:decimal(5,2)
如何解释这一点?
根据 SQL Server Management studio 中查看的列属性,我可以看到它的含义:小数(数字精度,数字小数位数)。
精度和规模的实际含义是什么?
很容易将其解释为具有 5 位数字和两位小数的小数......即 12345.12
P.S.我已经能够从一位同事那里确定正确的答案,但在网上找到答案却遇到了很大的困难。因此,我希望将问题和答案记录在 stackoverflow 上以供将来参考。
I have the following column specified in a database: decimal(5,2)
How does one interpret this?
According to the properties on the column as viewed in SQL Server Management studio I can see that it means: decimal(Numeric precision, Numeric scale).
What do precision and scale mean in real terms?
It would be easy to interpret this as a decimal with 5 digits and two decimals places...ie 12345.12
P.S. I've been able to determine the correct answer from a colleague but had great difficulty finding an answer online. As such, I'd like to have the question and answer documented here on stackoverflow for future reference.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(4)
数字精度是指数字中存在的最大位数。
即 1234567.89 的精度为 9
数字标度是指小数位数的最大位数
即 123456.789 的标度为 3
因此,decimal(5,2) 的最大允许值为 999.99
Numeric precision refers to the maximum number of digits that are present in the number.
ie 1234567.89 has a precision of 9
Numeric scale refers to the maximum number of decimal places
ie 123456.789 has a scale of 3
Thus the maximum allowed value for decimal(5,2) is 999.99
数字的精度是指数字的位数。
数字的小数位数是小数点后的位数。
在字段定义上设置精度和小数位数时通常意味着它们代表最大值。
例如,使用
cision=5
和scale=2
定义的小数字段将允许使用以下值:123.45
(p=5,s=2 )12.34
(p=4,s=2)12345
(p=5,s=0)123.4
(p=4,s= 1)0
(p=0,s=0)不允许使用以下值,否则会导致数据丢失:
12.345
(p=5,s=3) = >可以截断为12.35
(p=4,s=2)1234.56
(p=6,s=2) =>可以截断为1234.6
(p=5,s=1)123.456
(p=6,s=3) =>可以截断为123.46
(p=5,s=2)123450
(p=6,s=0) =>超出范围请注意,范围通常由精度定义:
|value| < 10^p
...Precision of a number is the number of digits.
Scale of a number is the number of digits after the decimal point.
What is generally implied when setting precision and scale on field definition is that they represent maximum values.
Example, a decimal field defined with
precision=5
andscale=2
would allow the following values:123.45
(p=5,s=2)12.34
(p=4,s=2)12345
(p=5,s=0)123.4
(p=4,s=1)0
(p=0,s=0)The following values are not allowed or would cause a data loss:
12.345
(p=5,s=3) => could be truncated into12.35
(p=4,s=2)1234.56
(p=6,s=2) => could be truncated into1234.6
(p=5,s=1)123.456
(p=6,s=3) => could be truncated into123.46
(p=5,s=2)123450
(p=6,s=0) => out of rangeNote that the range is generally defined by the precision:
|value| < 10^p
...SQL Server 2000 文档中的精度、小数位数和长度内容如下:
Precision, Scale, and Length in the SQL Server 2000 documentation reads:
精度是指总位数,而小数位数是指小数点后允许的位数。
引用的示例的精度为 7,小数位数为 2。
此外,DECIMAL( precision,scale) 是一种精确值数据类型,与存储近似数值数据的 FLOAT( precision,scale) 等类型不同。
例如,定义为 FLOAT(7,4) 的列显示为 -999.9999。 MySQL 在存储值时执行舍入,因此如果将 999.00009 插入 FLOAT(7,4) 列,则近似结果为 999.0001。
让我知道这是否有帮助!
Precision refers to the total number of digits while scale refers to the digits allowed after the decimal.
The example quoted by would have a precision of 7 and a scale of 2.
Moreover, DECIMAL(precision, scale) is an exact value data type unlike something like a FLOAT(precision, scale) which stores approximate numeric data.
For example, a column defined as FLOAT(7,4) is displayed as -999.9999. MySQL performs rounding when storing values, so if you insert 999.00009 into a FLOAT(7,4) column, the approximate result is 999.0001.
Let me know if this helps!