MySQL: Size of decimal data type

MySQL: Size of decimal data type

  1. Blog
  2. 15 November 2026
  3. 3 min read

Stefan Bogdanescu

Founder & Senior Architect

MySQL: Size of decimal data type

Determining the correct dimensions for DECIMAL columns in MySQL often feels like guessing until you hit an overflow error or waste significant storage space. When dealing with financial calculations, precision is non-negotiable, but declaring a column as DECIMAL(10, 2) isn't inherently "correct." The right choice depends entirely on the maximum value your data will ever hold and how many decimal places are required for accuracy.

The core constraint you must respect is that MySQL treats the first argument of DECIMAL(M, D)—the total number of digits—as a hard limit on the magnitude of the entire number. This includes everything to the left of the decimal point (the integer part) and everything to the right (the fractional part). If your data requires more than $M$ digits in total, MySQL will truncate or round the value, potentially corrupting financial records.

Consider a tax rate column where the input is 0.014840000000. While this specific value fits easily into DECIMAL(5, 6), that setting implies a maximum range of 99.99999. If your business logic ever needs to store a rate like 123.456789 (perhaps for a bulk discount scenario), DECIMAL(5, 6) would fail. The total digit count $M$ dictates the ceiling of the value, not just the current input.

For monetary amounts like units, initial_charge, or base_price, standard practice usually reserves two decimal places for currency. However, looking at your base_price example of 41.8500, you are currently storing three zeros after the decimal point. Unless your application logic explicitly requires that trailing zero precision for display formatting within the database, declaring it as DECIMAL(6, 2) (max value ~9999.99) is often more efficient than DECIMAL(8, 4). The extra storage isn't just disk space; it can impact query performance on large tables due to wider row sizes and increased memory footprint during joins.

When calculating derived fields like tax or link_tax, the fractional precision becomes critical. If you need six decimal places of accuracy (as seen in your tax value 0.042986), your total digit count $M$ must be large enough to accommodate that fraction plus a reasonable integer buffer. For instance, if your tax amount could theoretically reach $1000$, a DECIMAL(7, 3) would only allow you to store up to 999.999. To safely store 999.999999, you need at least DECIMAL(12, 6).

Your edit suggests specific configurations that balance these constraints well:

-- tax_rate: High fractional precision needed for small values
tax_rate DECIMAL(15, 12); 

-- units: Standard currency format
units DECIMAL(6, 2); 

-- initial_charge & charge: Moderate magnitude with 4 decimals
initial_charge DECIMAL(9, 4); 
charge DECIMAL(9, 4); 

-- link_tax: Needs higher total digits for 6 decimal places
link_tax DECIMAL(9, 6); 

-- exempt: Standard currency format
exempt DECIMAL(9, 4); 

-- tax: High magnitude requirement for calculated totals
tax DECIMAL(10, 6); 

-- base_price: High magnitude with standard 4 decimals
base_price DECIMAL(10, 4); 

Notice how link_tax uses DECIMAL(9, 6). The total digits are 9, meaning the maximum value is roughly 999.999999. If your tax calculation logic ever sums multiple fees and exceeds $999$, this column will overflow. Always calculate your worst-case scenario: sum all possible inputs to ensure the resulting precision fits within the available integer digits ($M - D$).

If you are building a Laravel application, remember that the Eloquent model casts often handle these conversions automatically when saving, but the underlying schema definition must still adhere to these physical constraints. You can inspect your current table structure using:

mysql> DESCRIBE your_table_name;

Reviewing the Decimals column output here helps you audit whether your chosen sizes match your actual data ranges. While floating-point types like FLOAT or DOUBLE are faster for non-monetary data, they introduce rounding errors that make them unsuitable for financial storage. Stick to DECIMAL when cents and fractions matter, but choose $M$ and $D$ based on a calculated maximum, not just the current sample data.

Our network