Data types, tables and constraints

Pick numeric, string and temporal types that behave correctly under sorting and summing, and let keys, defaults and constraints enforce rules you would otherwise enforce in application code.

Types that behave the way you expect

A column type decides what may be stored, how much space it takes and which comparisons are legal. Getting it wrong is paid for later, when a migration has to rewrite a large table while the application is running.

Family	Common choices	Watch out for
Integer	`TINYINT`, `INT`, `BIGINT`	Use `BIGINT UNSIGNED` for ids you cannot prove will stay small
Exact decimal	`DECIMAL(p,s)`	Use for money; the value is stored exactly
Floating point	`FLOAT`, `DOUBLE`	Measurements, not currency: rounding drifts in sums
Text	`VARCHAR(n)`, `TEXT`, `CHAR(n)`	`VARCHAR(n)` counts characters; index limits count bytes
Binary	`BINARY`, `VARBINARY`, `BLOB`	Stored as bytes, compared without a collation
Temporal	`DATE`, `DATETIME`, `TIMESTAMP`	`TIMESTAMP` converts by session time zone, `DATETIME` does not
Boolean	`BOOLEAN`	A synonym for `TINYINT(1)`; the stored values are 0 and 1
Document	`JSON`	Validated, but not a substitute for typed columns you filter on

CREATE TABLE orders (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  customer_id BIGINT UNSIGNED NOT NULL,
  status      ENUM('new','paid','shipped','cancelled') NOT NULL DEFAULT 'new',
  total       DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  currency    CHAR(3) NOT NULL DEFAULT 'USD',
  note        VARCHAR(255) NULL,
  placed_at   DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updated_at  DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3)
              ON UPDATE CURRENT_TIMESTAMP(3),
  body        JSON NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

VARCHAR(n) is a character count, but index limits are measured in bytes. Under utf8mb4 a character may take four bytes, so a VARCHAR(255) column can need up to 1020 bytes of key space, and older configurations cap a single key part at 767 bytes.

Keys, constraints and defaults

ALTER TABLE orders
  ADD CONSTRAINT fk_orders_customer
      FOREIGN KEY (customer_id) REFERENCES customers (id)
      ON DELETE RESTRICT ON UPDATE CASCADE,
  ADD CONSTRAINT chk_total_positive CHECK (total >= 0);

CREATE TABLE users (
  id    BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  email VARCHAR(320) NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_users_email (email)
);

CREATE INDEX idx_orders_customer ON orders (customer_id);

In InnoDB the primary key is the clustered index: rows are stored in its order. Keep it narrow and monotonic, because every secondary index stores a copy of it.
AUTO_INCREMENT leaves gaps after a rolled-back insert or a deleted row. A gap is not corrupt data.
A foreign key needs an index on the referencing column. MySQL creates one if you do not, and its column order may not match the join you actually run.
ON DELETE RESTRICT refuses to delete a parent that still has children; CASCADE deletes them silently. Choose deliberately, and know which one you chose.
CHECK is enforced from 8.0.16 onwards. Earlier versions parsed and ignored it, which matters when you restore an old schema onto a new server.

💡

Declare every column NOT NULL unless absence is genuinely meaningful. A nullable column makes every arithmetic and comparison expression three-valued, and the NULL handling then has to be repeated in every query that touches it.

Traps that cost a migration

Trap	What happens	Do instead
Dates as `VARCHAR`	Sorting is lexicographic and ranges are unreliable	Use `DATE` or `DATETIME`
`TEXT` for everything	No usable index without a prefix length; rows grow	Declare a realistic `VARCHAR(n)`
`utf8` instead of `utf8mb4`	Some characters are rejected outright	Use `utf8mb4` everywhere
`ENUM` for a growing list	Adding a value rebuilds the table	Use a lookup table with a foreign key
`INT` for a growing id	Inserts fail at the type maximum	Use `BIGINT UNSIGNED`
`FLOAT` for money	Sums drift by fractions of a cent	Use `DECIMAL`

Changing a type is not free. ALTER TABLE ... MODIFY may copy and rebuild the entire table, holding locks while it runs. Ask for the cheapest algorithm explicitly with ALGORITHM=INPLACE, LOCK=NONE, and treat a refusal as information rather than an obstacle to work around.

FAQ

VARCHAR(255) or VARCHAR(1000)?

Size for the real maximum, not for a round number. A shorter declared length keeps the row smaller, fits more rows per page and lets the whole column be indexed without a prefix length. Unused capacity is still capacity the optimiser has to reason about.

Does the primary key type affect performance?

Yes, particularly in InnoDB. The primary key is the clustered index, so every secondary index stores it as the row pointer. A narrow, ever-increasing integer keeps the table and its indexes compact and makes inserts sequential.

Installation, databases, users and engines Indexes and reading EXPLAIN

Last refreshed 2026-09-18.

Data types, tables and constraints

Types that behave the way you expect

Keys, constraints and defaults

Traps that cost a migration

FAQ

Related