php MySQL：十进制数据类型的大小
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19201266/
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
MySQL: Size of decimal data type
提问by user0000001
I have a few values that come in from a server that need to be stored in my database. I'm not a MySQL expert, but I understand it well enough for basic input/output. Right now I am trying to determine the length I should be using when storing the following decimals.
我有一些来自服务器的值需要存储在我的数据库中。我不是 MySQL 专家，但我对基本输入/输出的理解已经足够了。现在我正在尝试确定在存储以下小数时应该使用的长度。
tax_rate [DECIMAL ?,?]: value(0.014840000000)
units [DECIMAL ?,?]: value(1.00)
initial_charge [DECIMAL ?,?]: value(2.5110)
charge [DECIMAL ?,?]: value(2.8967)
link_tax [DECIMAL ?,?]: value(0.385652)
exempt [DECIMAL ?,?]: value(0.0000)
tax [DECIMAL ?,?]: value(0.042986)
base_price [DECIMAL ?,?]: value(41.8500)
I'm hoping someone could suggest the correct size I need to use for these values AS WELL explain why they chose the values. Or maybe link to an article that explains MySQL decimals in depth.
我希望有人可以建议我需要为这些值使用的正确大小，以及解释他们为什么选择这些值。或者可能链接到深入解释 MySQL 小数的文章。
Any help would be appreciated.
任何帮助，将不胜感激。
Thank you!
谢谢！
-------Edit--------
-------编辑--------
After reading the MySQL docs, there is a very good explanation to determining the size of the decimal type. These are the sizes I have set for my use case:
阅读 MySQL 文档后，有一个很好的解释来确定小数类型的大小。这些是我为我的用例设置的大小：
tax_rate [DECIMAL 15,12]: value(0.014840000000) ? max(999.999999999999)
units [DECIMAL 6,2]: value(1.00) ? max(9999.99)
initial_charge [DECIMAL 9,4]: value(2.5110) ? max(99999.9999)
charge [DECIMAL 9,4]: value(2.8967) ? max(99999.9999)
link_tax [DECIMAL 9,6]: value(0.385652) ? max(999.999999)
exempt [DECIMAL 9,4]: value(0.0000) ? max(9999.9999)
tax [DECIMAL 10,6]: value(0.042986) ? max(999999.999999)
base_price [DECIMAL 10,4]: value(41.8500) ? max(999999.9999)
回答by Jason OOO
From MySQL:
从MySQL：
The declaration syntax for a DECIMAL column is DECIMAL(M,D). The ranges of values for the arguments in MySQL 5.1 are as follows:
M is the maximum number of digits (the precision). It has a range of 1 to 65. (Older versions of MySQL permitted a range of 1 to 254.)
D is the number of digits to the right of the decimal point (the scale). It has a range of 0 to 30 and must be no larger than M.
DECIMAL 列的声明语法是 DECIMAL(M,D)。MySQL 5.1 中参数的取值范围如下：
M 是最大位数（精度）。它的范围是 1 到 65。（旧版本的 MySQL 允许范围是 1 到 254。）
D 是小数点右边的位数（刻度）。它的范围是 0 到 30，并且不能大于 M。
Consider this number: 123456789.12345 here Mis 14and Dis 5then based on this principle you can set DECIMALS(M,D) for each column based on Their expected maximum values.
想想这个数字：123456789.12345这里M是14和D被5然后根据这个原理，你可以设置基于他们预期的最大值每列小数（男，d）。
回答by David A. Gray
Since the accepted answer left me wanting, I ran SHOW CREATE TABLEon a table that contains several columns defined as DECIMAL DEFAULT NULL(note the absence of values for Mand D.
由于接受的答案让我想要，我运行SHOW CREATE TABLE了一个包含多个定义为的列的表DECIMAL DEFAULT NULL（注意M和D的值的缺失。
The result follows.
结果如下。
data_warehouse as davidg Wed Dec 05 12:10:36 2018 >SHOW CREATE TABLE erth_calendarmonths_historic_usage_preload;
+--------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| erth_calendarmonths_historic_usage_preload | CREATE TABLE `erth_calendarmonths_historic_usage_preload` (
`market` varchar(100) NOT NULL,
`commodity` varchar(100) NOT NULL,
`account_number` varchar(100) NOT NULL,
`meter_number` varchar(100) NOT NULL,
`period_year_month` int(11) NOT NULL,
`estimated_usage_amount` decimal(18,7) DEFAULT NULL,
`unit` varchar(100) DEFAULT NULL,
`meter_read_start_date_part_1` datetime DEFAULT NULL,
`meter_read_end_date_part_1` datetime DEFAULT NULL,
`gross_nonadjusted_usage_amount_part_1` decimal(10,0) DEFAULT NULL,
`applied_nonadjusted_usage_amount_part_1` decimal(10,0) DEFAULT NULL,
`meter_read_start_date_part_2` datetime DEFAULT NULL,
`meter_read_end_date_part_2` datetime DEFAULT NULL,
`gross_nonadjusted_usage_amount_part_2` decimal(10,0) DEFAULT NULL,
`applied_nonadjusted_usage_amount_part_2` decimal(10,0) DEFAULT NULL,
`utility_rate_class` varchar(100) DEFAULT NULL,
`utility_rate_subclass` varchar(100) DEFAULT NULL,
`load_profile` varchar(100) DEFAULT NULL,
`hu_type` varchar(100) DEFAULT NULL,
`type` varchar(100) DEFAULT NULL,
`utility_duns` varchar(100) DEFAULT NULL,
`create_date` datetime DEFAULT NULL,
`update_date` datetime DEFAULT NULL,
`UsedBuckets` tinyint(4) NOT NULL DEFAULT '0',
PRIMARY KEY (`market`,`commodity`,`account_number`,`meter_number`,`period_year_month`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
The foregoing makes the default values of M and D self-evident; M = 10, while D = 0. Needless to say, this is almost certainly not the desired outcome.
以上使得M和D的默认值不言而喻；M = 10，而D = 0。不用说，这几乎肯定不是想要的结果。
Thou shalt always specify thine M and D values.
你应该总是指定你的 M 和 D 值。
