Database

Choose MySQL Data Types for Correctness and Capacity

Select numeric, temporal, text, and identifier types based on domain limits and comparison semantics.

Overview

Data types are part of the integrity model. Choosing a type that is too narrow can cause overflow; choosing one that is too broad wastes storage and can make indexes less efficient. Implicit conversions can also prevent expected index use.

Start with the workload and the invariant

For identifiers, decide whether values are numeric, externally assigned, or opaque strings. Use matching signedness and width across foreign-key relationships. Avoid storing numeric identifiers as strings unless formatting or external semantics require it. If leading zeros are meaningful, they are not numeric quantities.

Practical example

Use DECIMAL for exact monetary values and other fixed-precision quantities. Floating-point types represent approximate values and can produce rounding surprises in accounting logic. Define currency separately and avoid assuming every currency has two decimal places.

CREATE TABLE invoices (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    amount DECIMAL(13,2) NOT NULL,
    currency CHAR(3) NOT NULL,
    issued_at DATETIME NOT NULL
);

Trade-offs and failure modes

Choose DATE, DATETIME, and TIMESTAMP based on meaning and timezone policy. Store a consistent instant for events and define whether the application converts to UTC. A local appointment time may need a timezone identifier as well as a wall-clock value. Daylight-saving transitions can make naive local timestamps ambiguous.

Verification and rollout

Bound VARCHAR lengths to domain rules, but remember the maximum row size and character encoding. TEXT types are useful for longer content, but they have different indexing and retrieval characteristics. Avoid using one generic text column to store unrelated structured fields.

Engineering checklist

Test boundary values, nullability, uniqueness, sorting, and round-trip serialization through PHP. Review strict SQL mode and ensure application validation agrees with database constraints. Correct types reduce bugs before application code has a chance to encounter them.

Before shipping, review the database version, storage engine, SQL mode, character set, collation, and deployment process used by the target environment. Features and DDL behavior can differ between MySQL and MariaDB releases. Validate the exact statements on a representative staging database, capture a baseline, and keep a tested recovery path for changes that affect production data.

Share this page Help other developers discover this resource.