LEARN. PRACTICE. GROW.Concept and mechanism
A schema defines more than storage space. DECIMAL represents exact values within declared precision and scale; DOUBLE uses binary approximation. For amounts or rates, also decide where to round and how to handle out-of-range values. Changing display alone does not correct an earlier calculation. TIMESTAMP converts between UTC and session time zone; DATETIME does not perform that automatic conversion. Neither type inherently preserves the original time-zone identifier. Collation defines comparison rules: a _ci collation ignores case differences, which may conflict with the identity of a business code.
Guided application
In a fictional fee batch, use cases involving fractions, boundaries, missing values, and different zones. NULL represents absence of a value rather than zero or an empty string. COUNT(*) counts rows, while COUNT(column) ignores NULL in that expression. Also check session sql_mode: strict mode, IGNORE, and types can change whether invalid input produces an error or warning. Review driver, calculation, and storage behavior together. Before migrating data, define reconciliation and acceptance criteria with the functional owner. Converting to DECIMAL does not reconstruct information already lost through approximate calculation.
References X, NULL, and Y: COUNT(*) returns 3; COUNT(reference) returns 2.
Common pitfalls
Formatting as precision; NULL as empty; collation as display-only; implicit pool time_zone.
Related topics: Deterministic queries and plans · InnoDB transactions and error paths · Safe integration and effective identity
The type must preserve the functional contract throughout the flow.
Reference: Exact decimal data types · MySQL 9.7 LTS with InnoDB reference semantics