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)

In depth: official docs at dev.mysql.com/doc/refman/5.0/en/fixed-point-types.html — Amadan
– Amadan, Commented Oct 5, 2013 at 18:36

Community · Accepted Answer · 2020-06-20 09:12:55Z

51

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.

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Oct 5, 2013 at 18:39

Jason OOO

3,5622 gold badges28 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user0000001 Over a year ago

Awesome. That basically sums it up... I will edit my post to reflect the correct values.

machineaddict Over a year ago

@zonabi D is always included in M

at54321 · Accepted Answer · 2023-04-21 20:26:35Z

8

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.

edited Apr 21, 2023 at 20:26

answered Apr 20, 2023 at 22:07

at54321

12.3k5 gold badges51 silver badges75 bronze badges

2 Comments

Guss Feb 25 at 11:21

If I understand the linked docs correctly, each part (integer or fraction) requires at least 4 bytes - to store the first 9 digits - then up to 4 additional bytes to store up to 9 additional digits (and so forth). The examples do not have DECIMALs with less than 9 digits overall, but to my understanding the size is never less than 4, so any DECIMAL with a fraction part take at least 8 bytes, or 4 bytes if there's no fractional part - DECIMAL(5,2) costs 8 bytes, not 3.

at54321 Mar 1 at 15:48

@Guss I don't interpret the docs that way. They say "Each multiple of nine digits requires 4 bytes". But zero is also a multiple. They provide an example of DECIMAL(20,6) and explain "The six fractional digits require 3 bytes". I'd assume the same would apply regardless of the size of the integer part. And also that the exact same rule would apply to the integer part itself.

David A. Gray · Accepted Answer · 2018-12-05 19:03:13Z

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.

Hmmm. I don't think so... I don't know why nobody commented on this yet! I'm somehow very confident that this won't be the case if you create your table with something like DECIMAL(10,3) DEFAULT NULL. You never told MySQL how many decimals to store, it can't guess any better than "0 decimals", you know! If this still doesn't work as expected, you have definitely come across a bug in your version of MySQL (go ahead and report it if not already done by someone else)
BTW, as per the docs In standard SQL, the syntax DECIMAL(M) is equivalent to DECIMAL(M,0). Similarly, the syntax DECIMAL is equivalent to DECIMAL(M,0), where the implementation is allowed to decide the value of M. MySQL supports both of these variant forms of the DECIMAL and NUMERIC syntax. The default value of M is 10. Since you never defined M (precision) and D (scale), they became 10 and 0 respectively.

Collectives™ on Stack Overflow

MySQL: Size of decimal data type

3 Answers 3

2 Comments

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related