What is the difference between DATETIME and TIMESTAMP?

DATETIME stores the literal value you give it, independent of time zone, with a wide range up to year 9999. TIMESTAMP is converted to UTC on write and back to the session time zone on read, but only spans 1970 to 2038.

Should I use VARCHAR or TEXT?

Use VARCHAR for short, bounded strings that you may want to index fully; it is stored inline in the row. Use TEXT for long content — it can be stored off-page and has length limits up to 4 GB for LONGTEXT, but indexes need a prefix length.

Why use DECIMAL for money instead of FLOAT?

FLOAT and DOUBLE are binary floating point and cannot represent many decimal fractions exactly, causing rounding errors. DECIMAL(M,D) stores the exact value, so it is the correct choice for currency and other exact figures.

What does UNSIGNED do?

UNSIGNED removes the negative half of an integer type's range and doubles the positive maximum. For example INT UNSIGNED goes from 0 to about 4.29 billion instead of roughly negative to positive 2.1 billion.

What happens to out-of-range values?

In the default STRICT SQL mode, inserting an out-of-range or wrongly typed value raises an error and the statement fails. Without strict mode, MySQL would silently clamp or truncate the value and emit a warning.

What is the MySQL Data Types Reference?

Searchable MySQL and MariaDB data type reference covering integer, fixed and floating point, string, binary, date/time and JSON types — with storage size, value range and STRICT-mode notes. It runs free in your browser on Gera Tools, with nothing uploaded.

MySQL Data Types Reference

Name: MySQL Data Types Reference
Creator: Gera Tools
License: https://creativecommons.org/licenses/by/4.0/

Choose the right MySQL column type

MySQL and MariaDB provide integer, fixed and floating-point, string, binary, date/time and JSON types, each with its own storage cost and range. This reference lets you search those types by name or range and filter by category, so you can size each column correctly. It runs entirely in your browser.

Integer types: size and range at a glance

Type	Storage	Signed range	Unsigned range
TINYINT	1 byte	-128 to 127	0 to 255
SMALLINT	2 bytes	-32,768 to 32,767	0 to 65,535
MEDIUMINT	3 bytes	-8,388,608 to 8,388,607	0 to 16,777,215
INT	4 bytes	~-2.1B to ~2.1B	0 to ~4.3B
BIGINT	8 bytes	~-9.2 quintillion	0 to ~18.4 quintillion

Choose the smallest type that comfortably fits your expected values. A TINYINT column holding a status flag (0-4) saves 3 bytes per row compared to INT, which multiplies across millions of rows and every index entry.

String types: inline vs off-page

Type	Max length	Storage location	Full-index support
CHAR(n)	255 chars	Inline in row	Yes
VARCHAR(n)	65,535 bytes	Inline in row	Yes (within prefix limit)
TINYTEXT	255 bytes	Off-page	Prefix only
TEXT	65,535 bytes	Off-page	Prefix only
MEDIUMTEXT	~16MB	Off-page	Prefix only
LONGTEXT	~4GB	Off-page	Prefix only

VARCHAR stores only the characters used plus a 1 or 2 byte length prefix, while CHAR(n) always occupies exactly n characters. Use VARCHAR for variable-length strings you may index fully; use TEXT family for long content you do not need to index beyond a prefix.

Date and time types: key differences

DATETIME stores an absolute value with no time zone conversion, from 1000-01-01 to 9999-12-31. It is the right choice when the stored value is a wall-clock time that should not change across time zone settings.

TIMESTAMP converts to UTC on write and back to the session time zone on read. Range is only 1970-01-01 to 2038-01-19 (the 32-bit Unix timestamp limit). It is useful for “record when this row was last modified” columns but problematic for historical or future dates outside its range.

DATE stores a date without a time component (3 bytes). TIME stores a duration or time-of-day without a date (3-4 bytes depending on precision).

A typical well-typed table

CREATE TABLE orders (
  id        BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id   INT UNSIGNED NOT NULL,
  total     DECIMAL(12,2) NOT NULL,
  status    ENUM('new','paid','shipped','refunded') NOT NULL,
  created   DATETIME NOT NULL,
  updated   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  notes     TEXT,
  payload   JSON
);

Common mistakes

Using FLOAT or DOUBLE for currency. Binary floating point cannot represent most decimal fractions exactly, causing rounding errors in sums. Always use DECIMAL(M,D) for money.
Using DATETIME when you need time zone awareness. If users are in multiple time zones and you need to store a UTC timestamp, TIMESTAMP auto-converts. If you need a wider date range with time zone handling, store DATETIME with a separate timezone column.
Forgetting UNSIGNED on auto-increment primary keys. A plain INT runs out at ~2.1 billion rows; INT UNSIGNED doubles that to ~4.3 billion; BIGINT UNSIGNED is effectively unlimited for practical purposes.