如何在 SQL Server 中存储十进制值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/813287/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to store decimal values in SQL Server?
提问by Alex
I'm trying to figure out decimal data type of a column in the SQL Server. I need to be able to store values like 15.5, 26.9, 24.7, 9.8, etc
我试图找出 SQL Server 中列的十进制数据类型。我需要能够存储 15.5、26.9、24.7、9.8 等值
I assigned decimal(18, 0)to the column data type but this not allowing me to store these values.
我分配decimal(18, 0)给列数据类型，但这不允许我存储这些值。
What is the right way to do this?
这样做的正确方法是什么？
回答by marc_s
DECIMAL(18,0)will allow 0 digits after the decimal point.
DECIMAL(18,0)将允许小数点后为 0 位。
Use something like DECIMAL(18,4)instead that should do just fine!
使用类似的东西DECIMAL(18,4)来代替应该没问题！
That gives you a total of 18 digits, 4 of which after the decimal point (and 14 before the decimal point).
这给你总共 18 位数字，其中 4位在小数点后（小数点前有 14 位）。
回答by DForck42
You should use is as follows:
你应该使用如下：
DECIMAL(m,a)
mis the number of total digits your decimal can have.
m是您的小数可以拥有的总位数。
ais the max number of digits you can have after the decimal point.
a是小数点后的最大位数。
http://www.tsqltutorials.com/datatypes.phphas descriptions for all the datatypes.
http://www.tsqltutorials.com/datatypes.php有所有数据类型的描述。
回答by DForck42
The settings for Decimalare its precision and scale or in normal language, how many digits can a number have and how many digits do you want to have to the right of the decimal point.
的设置Decimal是它的精度和小数位数或在正常语言中，一个数字可以有多少位数字以及您希望小数点右侧有多少位数字。
So if you put PIinto a Decimal(18,0)it will be recorded as 3?
所以如果你放入PIaDecimal(18,0)它会被记录为3?
If you put PIinto a Decimal(18,2)it will be recorded as 3.14?
如果你放入PIaDecimal(18,2)它会被记录为3.14?
If you put PIinto Decimal(18,10)be recorded as 3.1415926535.
如果你把PI到Decimal(18,10)被记录为3.1415926535。
回答by Hnin Htet Htet Aung
For most of the time, I use decimal(9,2) which takes the least storage (5 bytes) in sql decimal type.
在大多数情况下，我使用 decimal(9,2) ，它在 sql 十进制类型中占用最少的存储空间（5 个字节）。
Precision => Storage bytes
精度 => 存储字节
- 1 - 9 => 5
- 10-19 => 9
- 20-28 => 13
- 29-38 => 17
- 1 - 9 => 5
- 10-19 => 9
- 20-28 => 13
- 29-38 => 17
It can store from 0 up to 9 999 999.99 (7 digit infront + 2 digit behind decimal point = total 9 digit), which is big enough for most of the values.
它可以存储从 0 到 9 999 999.99（前 7 位+小数点后 2 位=总共 9 位），对于大多数值来说足够大了。
回答by Biddut
You can try this
你可以试试这个
decimal(18,1)
The length of numbers should be totally 18. The length of numbers after the decimal point should be 1 only and not more than that.
数字的总长度应为18。小数点后的数字长度应为1，不得超过1。
回答by Raja Done
In MySQL DB decimal(4,2)allows entering only a total of 4 digits. As you see in decimal(4,2), it means you can enter a total of 4 digits out of which two digits are meant for keeping after the decimal point.
在 MySQL DB 中decimal(4,2)，总共只允许输入 4 位数字。正如您在 中看到的decimal(4,2)，这意味着您总共可以输入 4 位数字，其中两位数字用于保留小数点后。
So, if you enter 100.0 in MySQL database, it will show an error like "Out of Range Value for column".
因此，如果您在 MySQL 数据库中输入 100.0，则会显示“列值超出范围”之类的错误。
So, you can enter in this range only: from 00.00 to 99.99.
因此，您只能在此范围内输入：从 00.00 到 99.99。
回答by Matthew Flaschen
The other answers are right. Assuming your examples reflect the full range of possibilities what you want is DECIMAL(3, 1). Or, DECIMAL(14, 1)will allow a total of 14 digits. It's your job to think about what's enough.
其他答案都对。假设您的示例反映了您想要的所有可能性DECIMAL(3, 1)。或者，DECIMAL(14, 1)将允许总共 14 位数字。你的工作是思考什么是足够的。
回答by freya
request.input("name", sql.Decimal, 155.33) // decimal(18, 0)
request.input("name", sql.Decimal(10), 155.33) // decimal(10, 0)
request.input("name", sql.Decimal(10, 2), 155.33) // decimal(10, 2)
