MySQL Numeric Types
debt(d5/e5/b3/t7)
Closest to 'specialist tool catches it' (d5). The detection_hints list semgrep as the tool, with an automated pattern matching FLOAT or DOUBLE columns used for price/amount/total/cost. This is not a default linter check but a specialist SAST pattern — exactly d5.
Closest to 'touches multiple files / significant refactor in one component' (e5). The quick_fix is conceptually simple (swap FLOAT for DECIMAL(10,2), swap INT for BIGINT UNSIGNED), but the common_mistakes note that 'the ALTER TABLE is expensive on large tables' — meaning the actual remediation on a production table with millions of rows involves schema migration planning, potential downtime or online DDL, and application-layer changes to match. This goes beyond a one-line patch into multi-step work.
Closest to 'localised tax' (b3). The choice of numeric column type is scoped to individual table column definitions. It applies to web and cli contexts but doesn't impose a cross-cutting architectural burden — it's felt most in the schema and the code that reads/writes those specific columns, not across the entire codebase.
Closest to 'serious trap' (t7). The misconception field is explicit: 'FLOAT is fine for prices since the errors are tiny.' This contradicts how floating-point works in financial contexts and is a well-known gotcha that competent developers from non-financial backgrounds routinely get wrong. The INT overflow silent wrapping is an additional serious trap. Combined, these are contradictions of reasonable developer intuition, scoring t7.
Also Known As
TL;DR
Explanation
Integer types: TINYINT (1B), SMALLINT (2B), MEDIUMINT (3B), INT (4B, ~2.1B signed), BIGINT (8B). UNSIGNED doubles the positive range. DECIMAL(p,s) stores exact decimal values using string-based arithmetic — use for money and prices. FLOAT (4B) and DOUBLE (8B) are IEEE 754 approximate values — never use for currency as 0.1 + 0.2 ≠ 0.3. Use DECIMAL(10,2) for prices, BIGINT for large counters, INT UNSIGNED for most IDs.
Common Misconception
Why It Matters
Common Mistakes
- Using FLOAT or DOUBLE for prices, totals, or tax calculations.
- Using signed INT when UNSIGNED would double the available range for no cost.
- Not switching to BIGINT before an INT column approaches its limit — the ALTER TABLE is expensive on large tables.
Avoid When
- Never use FLOAT or DOUBLE for financial values — use DECIMAL.
- Avoid signed integer types for IDs — UNSIGNED doubles the range at zero cost.
When To Use
- Use DECIMAL for any monetary or precise decimal value.
- Use BIGINT UNSIGNED for primary keys on tables expected to exceed hundreds of millions of rows.
Code Examples
-- FLOAT for money: rounding errors accumulate
price FLOAT NOT NULL,
-- INT for high-volume event log: overflows at ~2.1B rows
id INT AUTO_INCREMENT PRIMARY KEYCREATE TABLE products (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, -- up to 4.2B
price DECIMAL(10,2) NOT NULL, -- exact: 99999999.99
quantity MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, -- up to 16.7M
weight_kg DECIMAL(8,3) -- exact decimal
);
CREATE TABLE events (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY -- up to 18.4 quintillion
);