MySQL interview question · Most asked
How should you store money in MySQL?
Summary
Use DECIMAL(p, s) (exact decimal arithmetic) or an integer number of minor units (cents). Never use FLOAT or DOUBLE, which are binary approximations: 0.1 + 0.2 is not exactly 0.3.
Longer Answer
DECIMAL(19, 4) is a common choice for amounts that need sub-cent precision (FX rates, unit prices); integers in cents are simple and fast. Store the currency next to the amount, and do arithmetic with decimal types in the application too (PHP bcmath or a money library, Python Decimal, Java BigDecimal).
Key Points
- DECIMAL or integer minor units.
- Never FLOAT/DOUBLE.
- Store the currency code.
- Decimal arithmetic in code too.
Deep dive: Payments & Money APIs
More most-asked MySQL questions
- What is a clustered index in InnoDB, and why does primary key choice matter so much?
- Why is OFFSET pagination slow on large tables, and what is keyset pagination?
- How do you read EXPLAIN output in MySQL?
- Why might MySQL not use an index you created?
- What are the transaction isolation levels, and which does InnoDB use by default?
- How does a composite index work, and what is the leftmost-prefix rule?
- What causes deadlocks in MySQL, and how do you handle them?
- What are gap locks and next-key locks, and why do they surprise developers?