Introduction

Use DECIMAL(19, 4) for storing money in MySQL. It gives you exact fixed-point arithmetic with no rounding surprises, supports values up to trillions, and handles four-decimal currencies like stock prices and crypto amounts without modification. The alternative is storing values as integers in the smallest currency unit (cents, pips), which trades readability for raw speed. Never use FLOAT or DOUBLE for money.

Why DECIMAL Is the Standard Choice

DECIMAL (also called NUMERIC in SQL) stores numbers as exact fixed-point values. Unlike floating-point types, it does not approximate. When you store 19.99, MySQL stores exactly 19.9900, not 19.9900000000000002 or some other binary approximation.

1CREATE TABLE orders (
2    order_id    BIGINT AUTO_INCREMENT PRIMARY KEY,
3    subtotal    DECIMAL(19, 4) NOT NULL DEFAULT 0,
4    tax         DECIMAL(19, 4) NOT NULL DEFAULT 0,
5    total       DECIMAL(19, 4) NOT NULL DEFAULT 0,
6    currency    CHAR(3)        NOT NULL DEFAULT 'USD',
7    created_at  TIMESTAMP      NOT NULL DEFAULT CURRENT_TIMESTAMP
8);

Choosing precision and scale

The syntax is DECIMAL(M, D) where M is the total number of digits and D is the number of digits after the decimal point.

A common mistake is using DECIMAL(10, 2). That works until your application needs to handle aggregate totals, currency conversions with intermediate precision, or markets that use more than two decimal places. Starting with DECIMAL(19, 4) avoids schema migrations later.

Why FLOAT and DOUBLE Fail for Money

FLOAT and DOUBLE use IEEE 754 binary floating-point representation. They cannot represent most decimal fractions exactly. This is not a MySQL bug. It is how binary floating-point works in every language and database.

1-- Demonstration of floating-point precision loss
2CREATE TABLE float_test (amount FLOAT);
3INSERT INTO float_test VALUES (0.1 + 0.2);
4SELECT amount, amount = 0.3 AS is_equal FROM float_test;
5-- Result: amount = 0.30000001192092896, is_equal = 0

Compare with DECIMAL:

1CREATE TABLE decimal_test (amount DECIMAL(10, 2));
2INSERT INTO decimal_test VALUES (0.1 + 0.2);
3SELECT amount, amount = 0.3 AS is_equal FROM decimal_test;
4-- Result: amount = 0.30, is_equal = 1

In financial applications, these small errors compound. A billing system that processes millions of transactions with floating-point arithmetic will produce totals that do not balance. Auditors and regulators will not accept "floating-point rounding" as an explanation.

The Integer Approach (Storing Cents)

An alternative to DECIMAL is storing money as integers in the smallest currency unit. For USD, that means cents. For JPY, which has no fractional unit, you store the yen amount directly.

1CREATE TABLE payments (
2    payment_id   BIGINT AUTO_INCREMENT PRIMARY KEY,
3    amount_cents BIGINT NOT NULL,
4    currency     CHAR(3) NOT NULL DEFAULT 'USD'
5);
6
7-- Store $49.99 as 4999
8INSERT INTO payments (amount_cents, currency) VALUES (4999, 'USD');
9
10-- Display as dollars in a query
11SELECT
12    payment_id,
13    amount_cents / 100.0 AS amount_dollars,
14    currency
15FROM payments;

Application-layer conversion

The conversion between cents and dollars happens in application code:

1# Python example
2price_cents = 4999
3price_dollars = price_cents / 100  # 49.99
4
5# When receiving user input
6user_input = 49.99
7stored_value = round(user_input * 100)  # 4999

1// Java example
2long priceCents = 4999L;
3BigDecimal priceDollars = BigDecimal.valueOf(priceCents, 2); // 49.99
4
5// When receiving user input
6BigDecimal userInput = new BigDecimal("49.99");
7long storedValue = userInput.movePointRight(2).longValueExact(); // 4999

When integers make sense

Stripe, Square, and many payment processors use integer cents in their APIs. If your application integrates heavily with these services, storing cents natively avoids constant conversion at the API boundary. Integer arithmetic is also marginally faster than DECIMAL arithmetic, though the difference is negligible for most workloads.

Comparison Table

Handling Multiple Currencies

Real applications often handle more than one currency. Currencies differ in their number of fractional digits: USD has 2, JPY has 0, BHD (Bahraini Dinar) has 3, and ETH has 18.

1CREATE TABLE transactions (
2    transaction_id BIGINT AUTO_INCREMENT PRIMARY KEY,
3    amount         DECIMAL(19, 4) NOT NULL,
4    currency       CHAR(3)        NOT NULL,
5    exchange_rate  DECIMAL(12, 6),
6    amount_usd     DECIMAL(19, 4) GENERATED ALWAYS AS (amount * exchange_rate) STORED,
7    created_at     TIMESTAMP      NOT NULL DEFAULT CURRENT_TIMESTAMP,
8    INDEX idx_currency (currency),
9    INDEX idx_created (created_at)
10);

If you use the integer approach, store the exponent alongside the currency so your application knows how to convert:

1CREATE TABLE currency_config (
2    currency_code  CHAR(3) PRIMARY KEY,
3    decimal_places TINYINT NOT NULL,
4    name           VARCHAR(50) NOT NULL
5);
6
7INSERT INTO currency_config VALUES
8    ('USD', 2, 'US Dollar'),
9    ('JPY', 0, 'Japanese Yen'),
10    ('BHD', 3, 'Bahraini Dinar');

Indexing and Query Performance

Money columns frequently appear in WHERE, ORDER BY, and aggregate queries. Index them accordingly:

1-- Index for range queries on amount
2ALTER TABLE orders ADD INDEX idx_total (total);
3
4-- Composite index for filtered aggregation
5ALTER TABLE orders ADD INDEX idx_currency_created (currency, created_at);
6
7-- Example aggregate query
8SELECT
9    currency,
10    SUM(total)   AS revenue,
11    COUNT(*)     AS order_count,
12    AVG(total)   AS avg_order_value
13FROM orders
14WHERE created_at >= '2025-01-01'
15GROUP BY currency;

DECIMAL columns are slightly larger than integers, but the difference in index size is minimal for tables under hundreds of millions of rows.

Common Pitfalls

Using FLOAT or DOUBLE for money. The precision loss is small per operation, but it accumulates across millions of transactions and makes reconciliation impossible.

Using DECIMAL(10, 2) without thinking about growth. This caps your maximum at $99,999,999.99. If your system ever handles aggregate reports, currency conversions with intermediate values, or high-value transactions, you will hit the ceiling.

Mixing integer and decimal storage without documentation. If some tables store cents and others store dollars, developers will inevitably write a query that joins them without converting, producing numbers that are off by a factor of 100.

Ignoring currency entirely. Storing a bare number without a currency code makes it impossible to correctly handle multi-currency scenarios later. Always store the currency alongside the amount.

Performing division in SQL without controlling precision. SQL integer division truncates. If you divide an integer cents column by 100 to get dollars, use amount_cents / 100.0 (note the decimal literal) to force decimal division.

Summary

• Use DECIMAL(19, 4) as the default for money columns in MySQL. It provides exact arithmetic and enough range for virtually any financial application.
• Never use FLOAT or DOUBLE for monetary values. Binary floating-point cannot represent most decimal fractions exactly.
• Storing money as BIGINT cents is a valid alternative, especially when integrating with payment APIs that use integer amounts. The tradeoff is additional conversion logic in your application.
• Always store a currency code alongside every monetary value.
• Index money columns that appear in WHERE, ORDER BY, or aggregate queries.
• Start with more precision than you think you need. Expanding DECIMAL scale later requires an ALTER TABLE that rewrites the entire table.