How to Store Money in MySQL: DECIMAL vs Integer Cents

Store money in MySQL as an exact type, never a floating-point one. The general default is DECIMAL(19,4), an exact fixed-point column that holds amounts down to a hundredth of a cent without rounding drift. The high-volume alternative is to store the integer count of minor units (cents) in a BIGINT and divide by 100 for display. Either way you also store the currency code separately in a CHAR(3) column, because an amount with no currency attached is a bug waiting to surface. What you must not do is reach for FLOAT or DOUBLE. They are binary floating point and cannot represent most decimal fractions exactly, so your totals slowly drift away from the truth.

Short answer: amount DECIMAL(19,4) NOT NULL plus currency CHAR(3) NOT NULL for almost every application. Switch the amount to BIGINT integer cents only when you are running a high-throughput ledger and want integer arithmetic end to end. Do the money math in the database's exact types or in integer minor units, and round only at well-defined points with ROUND(). Never let a native float touch a monetary value at any layer.

Why you must never use FLOAT or DOUBLE for money

FLOAT and DOUBLE are IEEE 754 binary floating-point types. They store numbers as a sign, a binary mantissa, and a binary exponent, which is great for measurements where a tiny relative error is fine, and wrong for money where every cent is supposed to be exact.

The problem is that most decimal fractions have no exact binary representation. 0.1 in binary is a repeating fraction, the same way 1/3 is 0.333... in decimal. So the database stores the nearest representable value, which is almost 0.1 but not quite, and adding enough of those almost-values together compounds the error. The canonical demonstration is that 0.1 + 0.2 does not equal 0.3 in floating point, it lands a hair above. MySQL's own manual has a "Problems with Floating-Point Values" page for exactly this reason.

In a money column that surfaces as a 19.99 stored as 19.989999999999998, a SUM() over ten thousand invoices that is off by a few cents, and a WHERE price = 19.99 that matches nothing because the stored value was never exactly 19.99. None of it is a MySQL bug. It is what binary floating point does, and the fix is to not use it for money.

Option A: DECIMAL(19,4), the recommended default

DECIMAL(M,D) (the SQL standard also calls it NUMERIC, and MySQL treats the two as identical) is an exact fixed-point type. You declare M total significant digits and D digits after the decimal point, and MySQL stores the value exactly, no binary rounding. M can go up to 65 digits and D up to 30, which is far more headroom than money needs.

DECIMAL(19,4) is the workhorse currency declaration: 4 digits after the decimal point and 15 digits in front of it (19 minus 4). The four decimal places give you sub-cent precision, which matters for fuel pricing, per-unit rates, interest accrual, and foreign-exchange amounts that quote to four or more places. Four places is also the conventional GAAP-style accounting scale, so a column that holds intermediate financial figures without truncating to the cent is one less thing to revisit during an audit. The 15 integer digits hold any realistic total. Storage is compact: MySQL packs each group of 9 decimal digits into 4 bytes, with leftover digits taking a fraction of a byte. DECIMAL(19,4) works out to 9 bytes per value: the 15 integer digits take 7 bytes (4 for the first 9, 3 for the next 6) and the 4 fractional digits take 2 bytes.

If you only ever deal with whole-cent amounts on smaller values, DECIMAL(10,2) is the lighter choice: two decimal places, up to 8 integer digits (just under 100 million), and 5 bytes per row. It is perfectly correct for a typical store. I still default to DECIMAL(19,4) on anything that might touch FX, sub-cent rates, or large aggregate totals, because widening later is an ALTER TABLE you would rather skip.

The reason DECIMAL is safe where FLOAT is not: it is decimal fixed point. 0.1 is stored as the digits 1 at scale 1, exactly, so 0.1 + 0.2 is exactly 0.3 and a hundred thousand of them sum to exactly the right total.

Option B: BIGINT integer cents, for high-volume ledgers

The other exact approach skips decimals entirely and stores the integer count of minor units. A US dollar's minor unit is the cent, so $19.99 is stored as the integer 1999 in a BIGINT, and you divide by 100 when you display it. This is exact for the same reason DECIMAL is exact (integers have no fractional rounding at all) and it is fast, because the database and the application both do plain integer arithmetic with no decimal machinery in the path.

The trade is that you carry two facts in your application rather than the column: the scale (how many minor units per major unit, 2 for dollars and euros, 0 for yen) and the currency itself. You divide by 100 for display, multiply by 100 on input, and you have to keep that consistent everywhere. BIGINT holds up to about 9.2 quintillion, so even at cents you have room for trillions of dollars.

I reach for integer cents when the workload is a high-throughput accounting or payments ledger where every microsecond of arithmetic and every byte of row width counts, and where the team is disciplined about the scale convention. For a normal application, DECIMAL(19,4) is less error-prone because the scale lives in the schema, not in everyone's head.

Comparison: FLOAT/DOUBLE vs DECIMAL(19,4) vs BIGINT cents

Both exact options are correct. The choice between them is ergonomics versus throughput, not accuracy. The float row is in the table only so it is unambiguous: it is never the answer for a monetary value.

Always store the currency code separately

An amount is meaningless without a currency. 1000 could be a thousand dollars or a thousand yen, and those differ by two orders of magnitude. Store the currency alongside the amount in its own CHAR(3) column holding the ISO 4217 three-letter code: USD, EUR, GBP, JPY. CHAR(3) is exactly the right width (the codes are always three uppercase letters) and fixed-length, so there is no length-prefix overhead.

Keeping currency in its own column also stops a subtle bug: if you ever SUM() a mixed-currency table without grouping by currency, you add dollars to euros and get a nonsense number. With a currency column you GROUP BY currency, and the schema makes the multi-currency reality visible instead of hiding it.

A worked schema

Here is a minimal payments table using the recommended default. The amount is exact DECIMAL(19,4), the currency is a required CHAR(3), both NOT NULL.

CREATE TABLE payments (
  id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  order_id   BIGINT UNSIGNED NOT NULL,
  amount     DECIMAL(19,4) NOT NULL,
  currency   CHAR(3) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_order (order_id)
) ENGINE=InnoDB;

INSERT INTO payments (order_id, amount, currency) VALUES
  (1, 19.99, 'USD'),
  (1,  5.00, 'USD'),
  (1,  0.10, 'USD'),
  (1,  0.20, 'USD');

-- exact: this returns 25.2900, not 25.28999...
SELECT currency, SUM(amount) AS total
FROM payments
WHERE order_id = 1
GROUP BY currency;

That SUM() returns exactly 25.2900. The two rows of 0.10 and 0.20 add to exactly 0.30, because DECIMAL is decimal fixed-point and never went near a binary mantissa. Run the same data through a DOUBLE column and the total can land a fraction off, which is the whole reason the column type matters.

When you genuinely need to drop precision (charging a customer can only happen to the cent, not the hundredth of a cent), round at a well-defined point with ROUND():

SELECT ROUND(SUM(amount), 2) AS charge_amount
FROM payments
WHERE order_id = 1;

The rule is to keep full precision through the calculation and round once, deliberately, at the moment a real-world value is produced (the line you charge, the figure you print on the invoice). Rounding early and repeatedly accumulates its own error. And do the arithmetic itself in the database's exact types or in integer minor units, never by pulling amounts into application floats and adding them there, which reintroduces exactly the drift you used DECIMAL to avoid. For the exact byte sizes of DECIMAL, BIGINT, and every other column type, see MySQL data types and sizes.

MariaDB behaves the same

Everything here applies unchanged to MariaDB. DECIMAL/NUMERIC are exact fixed-point with the same 9-digits-per-4-bytes packing, FLOAT and DOUBLE carry the same binary drift, BIGINT and CHAR(3) are identical, and ROUND() and SUM() behave the same way. The schema above runs without modification on either engine, so the way you store money in MariaDB is the way you store it in MySQL.

Keep floats out of the application layer too

Picking the right column type only gets you halfway. If your application reads a DECIMAL out of MySQL and parses it into a native float or double, you have thrown away the exactness at the boundary. Carry money through your code in an exact type:

• PHP: use the BCMath extension (bcadd, bcsub, bcmul, bcdiv) for arbitrary-precision decimal arithmetic on the string form, never (float) on the amount.
• Python: use decimal.Decimal, constructed from the string, not from a float literal. Most MySQL drivers already hand you Decimal objects for DECIMAL columns, which is what you want.
• Node.js / JavaScript: JavaScript has only IEEE 754 numbers, so either store and compute in integer cents (plain integer math, safe up to Number.MAX_SAFE_INTEGER) or use a decimal library like decimal.js or big.js. Never add two money amounts as plain JS numbers.

The principle is one sentence: money is exact from the column, through the query, into the application type, and back, with floats nowhere on the path.

FAQ

See also

• Storing money in PostgreSQL covers the numeric equivalent and the same exact-type rule on Postgres.
• Money in MongoDB handles the document-database case, where the float trap is even easier to fall into.
• The MySQL data type sizes reference gives the exact byte cost of DECIMAL, BIGINT, and CHAR.
• For DECIMAL, SUM, and ROUND syntax on one page, reach for the MySQL cheat sheet.
• Need a throwaway server to try the schema? See running MySQL in Docker.
• When an ENUM would otherwise hold a currency code, weigh it against an ENUM versus a lookup table in MySQL.