Data Types
What the domain tests
The Data Types domain checks whether you can choose a column type whose range, precision, storage, and comparison behavior match the application. Questions often hinge on exact versus approximate numerics, temporal range and time-zone handling, and string types under MySQL 8.0's utf8mb4 default.
Core rules
- TINYINT, SMALLINT, MEDIUMINT, INT, and BIGINT differ in range. UNSIGNED shifts the range upward and makes subtraction underflow an error in strict mode.
- DECIMAL(p,s) is exact. FLOAT and DOUBLE are approximate binary floating-point types. Use DECIMAL for money.
- CHAR pads on storage and strips trailing spaces on retrieval. VARCHAR stores variable-length values. TEXT and BLOB need a prefix length for an ordinary index, and default values require expression defaults in MySQL 8.0.13 and later.
- DATETIME has no time-zone conversion. TIMESTAMP converts to and from UTC and has a narrower range. Both support fractional seconds.
- ENUM stores one value from a list. SET stores a bitmap of list values.
- JSON validates documents and stores them in a binary format.
Worked example
DECIMAL(7,2) allows seven total digits, two after the decimal, so the maximum is 99999.99. Inserting 1234.567 rounds to 1234.57, with a warning depending on SQL mode. If the same value is stored in FLOAT, an equality test such as WHERE amount = 1234.57 may fail because the stored binary value is not exact. The reasoning is precision: DECIMAL is fixed-point, while FLOAT is approximate.
Traps
- Assuming utf8 means full Unicode. In MySQL, utf8 is utf8mb3 and cannot store four-byte characters such as emoji. Use utf8mb4.
- Forgetting that TIMESTAMP values are converted using the session time zone on insert and select.
- Using implicit string-to-number conversion in WHERE clauses, which prevents index use and can produce surprising matches.
- Inserting out-of-range values with strict SQL mode disabled, where MySQL clips or converts instead of raising an error.