Why data type selection matters
A MySQL column’s data type defines the values it can represent and affects storage, comparisons, sorting, validation, and index size. The best type is not simply the smallest one available: it must cover valid current and expected future values without converting data incorrectly. A well-chosen type makes the schema communicate intent and reduces application-side parsing and validation work. A poor choice can cause rounding errors, truncated values, surprising comparisons, or expensive changes when the data outgrows its range.
Integer and exact numeric types
Integer families such as TINYINT, SMALLINT, INT, and BIGINT provide different ranges. Use the smallest type that safely supports the domain and its growth, while keeping related foreign-key definitions compatible. The UNSIGNED attribute excludes negative values and increases the nonnegative range for integer types. It is not a substitute for defining the valid range clearly. MySQL’s integer display width, historically written as forms such as INT(11), does not change the stored range and is deprecated/ignored in modern MySQL versions; do not use it as a range declaration.
Use DECIMAL(precision, scale) for exact decimal values such as money, where rounding must be controlled. The precision is the total number of digits and the scale is the digits after the decimal point. Floating-point types such as FLOAT and DOUBLE represent approximate binary values; they are appropriate for scientific measurements where approximation is expected, but direct equality and exact currency calculations can be unsafe. Define a currency and rounding policy in addition to selecting a numeric type.
CREATE TABLE invoice_lines (
invoice_line_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
quantity INT UNSIGNED NOT NULL,
unit_price DECIMAL(12, 2) NOT NULL,
PRIMARY KEY (invoice_line_id)
);Text types, character sets, and collation
Use CHAR for fixed-width values when that fixed width is meaningful, such as a carefully validated three-character currency code. Use VARCHAR for variable-length text with a practical maximum, such as an email address or product name. Large text types are available for longer content, but they have different storage and indexing considerations. Do not select a type solely based on one current sample value; establish the product’s actual maximum and validation rules.
Character sets define the characters that can be stored, while collations define comparison and sorting rules. For Unicode content, utf8mb4 is the modern MySQL choice. Collations may be case-insensitive, accent-sensitive, or otherwise tailored, and this affects equality and unique indexes. If a username is intended to be case-sensitive, confirm the column’s collation matches that requirement. Do not assume that visually different strings compare differently, or that every Unicode normalization form will compare as product design expects.
Dates, times, and time-zone meaning
Choose DATE for a calendar date with no time, such as a birthday. DATETIME stores a date and time value without automatic time-zone conversion. TIMESTAMP values are converted between the session time zone and UTC for storage and retrieval, and the supported range differs from DATETIME. For an event that represents a global moment, many systems normalize input to UTC and configure sessions consistently. For a recurring local appointment, retaining the intended local time zone may require storing a zone identifier separately.
Avoid keeping dates as formatted strings: lexical ordering may not match chronology, validation is weaker, and date arithmetic becomes awkward. Use half-open ranges for time filters, for example values greater than or equal to a start time and less than the next boundary. This avoids assumptions about fractional seconds. Learn practical date expressions in MySQL date functions.
NULL, defaults, and JSON
Nullability is a data-model decision. NULL means a value is absent or unknown; it is not the same as zero, an empty string, or a default. Declare NOT NULL when a meaningful value is required, and choose defaults only when the default is correct rather than a way to hide missing input. Filters need IS NULL or IS NOT NULL; ordinary equality does not match NULL values. See NULL handling in MySQL.
The native JSON type validates JSON documents and supports JSON functions, but it does not automatically make every property an efficient search key. Stable, frequently filtered, joined, or constrained attributes are often better modeled as ordinary columns. If a JSON property becomes important to query performance, evaluate generated columns and indexing for the target MySQL version. More examples are in MySQL JSON data and functions.
A repeatable selection process
For each column, define the valid domain, minimum and maximum, whether missing values are valid, how values are compared, and which queries use the column. Then choose a type, add constraints where supported, and test boundaries including empty, maximum-length, negative, and fractional values. Confirm how the selected driver maps database values into the application language; large integer and decimal values may need deliberate handling to avoid losing precision during conversion.
The right data type is a contract between the schema and every application that writes or reads it. Make that contract precise, test its boundaries, and use migrations to change it safely when requirements evolve. Continue with table creation, constraints, and schema migrations.