16

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)
user0000001
  • 2,092
  • 2
  • 20
  • 48

3 Answers3

50

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.

Community
  • 1
  • 1
Jason OOO
  • 3,567
  • 2
  • 25
  • 31
1

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.

David A. Gray
  • 1,039
  • 12
  • 19
  • 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](https://bugs.mysql.com/) if not already done by someone else) – Fr0zenFyr Jun 26 '19 at 10:31
  • 1
    BTW, as per [the docs](http://download.nust.na/pub6/mysql/doc/refman/5.1/en/numeric-types.html) **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. – Fr0zenFyr Jun 26 '19 at 10:39
0

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.
at54321
  • 8,726
  • 26
  • 46