MySQL: Size of decimal data type

Question

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.

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.

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:

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)

Answer 1

In MySQL DECIMAL(M,D) /or the equivalent NUMERIC(M,D)/:

• M is the total number of digits,
• D is the number of digits in the fractional part.

Here is an example of a number that would fit in (at least) DECIMAL(8,3):

Integer  Fractional
   part  part (D)
   ┌─┴─┐ ┌┴┐
   58282.349
   └───┬───┘
       M

Before explaining the details, here is a teaser-answer for the above example:

3 bytes   2 bytes
   ┌─┴─┐ ┌┴┐
   58282.349

As per the docs, the number of bytes used internally for the integer and fractional parts of a number is determined separately. But the method is the same: each multiple of 9 digits requires 4 bytes, and any remaining leftover digits require some fraction of 4 bytes, as per the following table:

Leftover Digits	Number of Bytes
0	0
1-2	1
3-4	2
5-6	3
7-9	4

Here are a few examples:

DECIMAL(M,D)	Example	Bytes INTEGER	Bytes FRACTIONAL	Bytes TOTAL
DECIMAL(4,0)	1822	2	-	2
DECIMAL(9,0)	764891821	4	-	4
DECIMAL(10,0)	2520521346	5	-	5
DECIMAL(19,0)	4638624678248624756	9	-	9
DECIMAL(5,2)	452.76	2	1	3
DECIMAL(9,2)	7567634.55	4	1	5
DECIMAL(11,2)	146249930.28	4	1	5
DECIMAL(18,2)	5852340342645782.52	8	1	9
DECIMAL(20,2)	414576457834678934.38	8	1	9
DECIMAL(6,5)	8.34801	1	3	4
DECIMAL(5,5)	.73678	-	3	3

Negative numbers fall into the same ranges. For example:

• DECIMAL(3,0) can store numbers between -999 and 999.
• DECIMAL(4,3) can store numbers between -9.999 and 9.999.

Answer 2

Since the accepted answer left me wanting, I ran SHOW CREATE TABLE on a table that contains several columns defined as DECIMAL DEFAULT NULL (note the absence of values for M and 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.

Thou shalt always specify thine M and D values.

Answer 3

From 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.

Consider this number: 123456789.12345 here M is 14 and D is 5 then based on this principle you can set DECIMALS(M,D) for each column based on Their expected maximum values.