The Right MySQL Data Type for Money, and the FLOAT Trap

MySQL has no MONEY type. Store currency in a DECIMAL column, never in FLOAT or DOUBLE, and size it as DECIMAL(19,4) unless you have a specific reason to do otherwise.

That is the answer, and it is worth understanding rather than copying, because the reason FLOAT is wrong is also the reason a surprising number of production systems are quietly off by a few pennies. There is also a long-running argument about storing cents as integers instead, which is not wrong so much as frequently misapplied.

Why FLOAT and DOUBLE are the wrong answer
DECIMAL is exact, and that is the whole point
Choosing the precision and scale
The integer-cents argument, settled
Currency belongs in its own column
A schema worth copying
How this fits the rest of the stack
FAQ

Why FLOAT and DOUBLE are the wrong answer

FLOAT and DOUBLE are binary floating point. They store numbers as a sign, an exponent and a fraction in base two, and a great many ordinary decimal values have no exact representation in base two. One tenth is one of them.

You can watch it happen:

CREATE TABLE t (amount DOUBLE);
INSERT INTO t VALUES (0.1), (0.2);
SELECT SUM(amount) FROM t;
-- 0.30000000000000004

Four hundredths of a trillionth of a penny is not, in itself, a problem. The problem is what happens next. That value gets compared against 0.3 and the equality quietly fails. It gets rounded for display so nobody notices, then summed across a million rows until the total drifts. It gets used in a WHERE clause on a reconciliation query that returns nothing, and somebody spends a day looking for a bug in the application code.

Accounting systems do not tolerate approximately. If the balance column cannot represent the number on the invoice exactly, the difference will eventually surface in front of someone who cares about it a great deal.

DECIMAL is exact, and that is the whole point

DECIMAL stores numbers in a packed decimal format. The value 0.1 is stored as its digits, not as the nearest binary approximation, so it comes back out as 0.1. Arithmetic on DECIMAL columns in MySQL is exact arithmetic.

CREATE TABLE t2 (amount DECIMAL(19,4));
INSERT INTO t2 VALUES (0.1), (0.2);
SELECT SUM(amount) FROM t2;
-- 0.3000

The declaration is DECIMAL(precision, scale). Precision is the total number of significant digits; scale is how many of those sit to the right of the decimal point. DECIMAL(19,4) means 19 digits in total with 4 after the point, so the largest value is 999,999,999,999,999.9999.

NUMERIC is an alias for DECIMAL in MySQL. They are the same type with two names. Use whichever reads better to you, and use it consistently.

Choosing the precision and scale

The scale question is the one worth thinking about, and the answer is usually more than two.

DECIMAL(19,4) is the sensible default. Four decimal places handle tax calculations, per-unit pricing, currency conversion and interest without rounding mid-calculation, and 19 total digits is more headroom than any real ledger needs.
DECIMAL(10,2) is fine for a simple invoice total that is never divided or converted. Two decimal places force every intermediate result to round, which is correct for a final amount and wrong for a calculation.
DECIMAL(19,6) or wider is worth it if you handle currencies with unusual minor units, or per-unit rates that are genuinely fractional.
Anything with a scale of zero is not a money column, it is a count.

The trap in DECIMAL(10,2) is not the size, it is the rounding. Price a line item at 19.99, apply a discount and a tax rate across twelve items, and every intermediate step rounds to the nearest cent. Do that a few thousand times and the total drifts away from what the same calculation produces at four decimal places. It is a small error, and it is exactly the kind of small error that makes two systems disagree about a number that should be identical.

Storage cost is not a reason to choose the smaller one. DECIMAL(19,4) takes nine bytes per row. You will not notice it, and you will notice a reconciliation failure.

The integer-cents argument, settled

A well-known school of thought says to skip DECIMAL entirely and store money as an integer number of the smallest unit: 1999 for 19.99, in a BIGINT column. The reasoning is that integer arithmetic is exact everywhere, in every language, with no decimal library required, and that it removes any chance of a floating point value sneaking in through the application layer.

That reasoning is sound, and the approach genuinely works. It is not, however, free.

Every read and write has to scale by 100, and every place that forgets is a bug that is wrong by two orders of magnitude.
Ad-hoc SQL becomes harder to read. A support query returning 1999 when someone asked for a price is a small tax paid forever.
Not every currency has 100 minor units. Japanese yen has one, Kuwaiti dinar has a thousand. A hardcoded 100 is a bug waiting for an international customer.
Sub-cent precision, which you need for rates and conversions anyway, has to be handled with a different scaling factor on top.

The honest rule: if you work in a language where decimal handling is genuinely awkward and your team is disciplined about a single conversion boundary, integer cents is a reasonable choice. Otherwise DECIMAL gives you exactness at the database level without asking every developer to remember a scaling factor, and MySQL does the arithmetic correctly on its own.

What is not defensible is picking integer cents because you heard DECIMAL is slow. On any workload that is not a synthetic benchmark, the difference is invisible next to the cost of the query that fetched the row in the first place.

Currency belongs in its own column

A money amount without a currency is not a money amount. If the application will ever handle more than one, store the code alongside the value:

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

CHAR(3) for the ISO 4217 code. It is fixed width, it is always three characters, and VARCHAR here buys you nothing but a length byte.

Two further rules that save arguments later. Never sum across currencies without converting, and enforce that in the query rather than trusting every caller to remember. And if you do convert, store the rate and the timestamp you used, because a converted figure without its rate cannot be audited or reproduced six months later.

Also resist the urge to add a formatted display column. Formatting is a presentation concern, it is locale dependent, and a currency string in the database is a value you cannot do arithmetic on.

A schema worth copying

Putting it together, for a typical order line table:

CREATE TABLE order_items (
  id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_id   BIGINT UNSIGNED NOT NULL,
  quantity   INT UNSIGNED    NOT NULL,
  unit_price DECIMAL(19,4)   NOT NULL,
  tax_rate   DECIMAL(9,6)    NOT NULL DEFAULT 0,
  currency   CHAR(3)         NOT NULL,
  INDEX (order_id)
) ENGINE=InnoDB;

Note the tax rate at six decimal places. A rate is not money and does not want a money scale; 0.082500 is a perfectly ordinary tax rate, and rounding it to two places would be actively wrong.

Migrating an existing FLOAT column is a straightforward ALTER, but do it carefully:

ALTER TABLE payments
  MODIFY COLUMN amount DECIMAL(19,4) NOT NULL;

Take a backup first and check the totals before and after on a restored copy. Values that were already approximate convert to the nearest representable decimal, which means the migration is the moment any accumulated drift becomes permanent. Much better to find that on a copy than in production. On a large table the ALTER also rewrites the whole table, so plan for the lock or reach for an online schema change tool.

How this fits the rest of the stack

Schema decisions like this one are cheap on day one and expensive to revisit once there are millions of rows and a reporting pipeline reading them, which is a reasonable argument for getting the database onto something managed early rather than late. Managed MySQL and Postgres on RunxBuild come with backups, connection limits and private networking, on the same plan ladder as the services that talk to them, and the RunxBuild hosting calculator shows the database line next to the application line so the full number is visible before you commit to it.

Useful related references:

FAQ

Does MySQL have a MONEY data type?

No. Some other database engines ship a fixed four decimal place money type, but MySQL does not. The equivalent is DECIMAL with a scale you choose, which is more flexible and behaves the same way for exact arithmetic.

Should I use DECIMAL(10,2) or DECIMAL(19,4) for money?

DECIMAL(19,4) unless you are certain the value is only ever a final total that is never divided, converted or taxed. The extra two decimal places stop intermediate calculations rounding, which is where small discrepancies between systems come from. The storage difference is a few bytes per row.

Is it better to store money as an integer number of cents?

It is a valid approach and it is exact, but it costs readability in ad-hoc SQL, adds a scaling boundary every developer has to remember, and breaks on currencies that do not have 100 minor units. DECIMAL gets you exactness without those costs, so prefer it unless your language makes decimal handling genuinely painful.

What happens if I use FLOAT for currency?

Values are stored as the nearest binary approximation, so exact comparisons fail, sums drift across many rows, and reconciliation queries return results that do not match the application. It usually stays hidden because display rounding covers it, then surfaces during an audit or a total that will not balance.

Can I change a FLOAT column to DECIMAL on a live table?

Yes, with ALTER TABLE MODIFY COLUMN, but back up and test on a restored copy first. Existing approximate values convert to the nearest decimal, so any drift already present becomes permanent at that moment. On a large table the ALTER rewrites the table, so plan for the lock or use an online schema change tool.