Skip to content
Tutorials Center
My learning

Topics

Lesson 12 of 40 ยท Data Types and Storage Engines

MariaDB Data Types

Choose the right MariaDB data type: integer sizes and UNSIGNED, DECIMAL versus FLOAT, CHAR and VARCHAR, TEXT and BLOB, DATE, DATETIME, TIMESTAMP, ENUM, SET, BOOLEAN, JSON, UUID and INET6.

Every column has a data type. The type decides which values are allowed, how much space they take, how they sort and how fast queries run. A good choice prevents bad data and saves storage; a careless one (such as storing money in FLOAT) causes bugs that surface years later. This lesson tours the types MariaDB offers and tells you when to use each.

Integers

Type Bytes Signed range UNSIGNED range
TINYINT 1 -128 to 127 0 to 255
SMALLINT 2 -32,768 to 32,767 0 to 65,535
MEDIUMINT 3 about ยฑ8.4 million 0 to 16.7 million
INT 4 about ยฑ2.1 billion 0 to 4.29 billion
BIGINT 8 about ยฑ9.2 quintillion 0 to 1.8e19

Add UNSIGNED when a value can never be negative (ids, counts), which doubles the positive range. The number in INT(11) is only a display hint (used with ZEROFILL) and does not limit the range, so you can ignore it. BOOLEAN and BOOL are aliases for TINYINT(1): 0 is false and anything else is true, with TRUE and FALSE as the constants 1 and 0.

A typical primary key is INT UNSIGNED AUTO_INCREMENT (4 billion rows) or BIGINT UNSIGNED for huge tables.

Exact and approximate numbers

  • DECIMAL(p, s) (also NUMERIC) stores exact decimal numbers: p total digits, s after the point. DECIMAL(10,2) holds up to 99,999,999.99. Use it for money.
  • FLOAT (4 bytes) and DOUBLE (8 bytes) are approximate binary floating point. They are fast and fine for measurements, but they cannot represent 0.1 exactly.
SQL Why money must not use floating point

Press Run (or Ctrl + Enter) to see the result here.

Store money either as DECIMAL(12,2) or as an integer number of cents. Never compare floats with =; compare within a tolerance.

Strings

Type Notes
CHAR(n) Fixed length, padded with spaces, up to 255. Good for codes of constant length (CHAR(2) country code).
VARCHAR(n) Variable length up to 65,535 bytes per row. Default choice for names, emails.
TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT 255 B, 64 KB, 16 MB, 4 GB. For long content.
BINARY, VARBINARY Like CHAR/VARCHAR but bytes, not characters.
TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB Binary data; usually better stored as files with a path in the database.

With utf8mb4 each character may take up to 4 bytes, so a VARCHAR(255) can need 1,020 bytes. This matters for index key length limits (3,072 bytes for InnoDB). TEXT columns cannot have a default value in older versions and are stored partly off-row, so avoid them for short strings.

Dates and times

Type Format Range Notes
DATE YYYY-MM-DD 1000-01-01 to 9999-12-31 Date only
TIME HH:MM:SS -838:59:59 to 838:59:59 Also durations
DATETIME YYYY-MM-DD HH:MM:SS 1000 to 9999 No time zone conversion
TIMESTAMP same 1970 to 2038 (2106 on newer versions) Stored as UTC, shown in session time zone
YEAR YYYY 1901 to 2155

Add fractional seconds with a precision: DATETIME(3) stores milliseconds. TIMESTAMP is handy for created_at and updated_at because it can fill itself:

SQL
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

Use DATETIME for calendar values that must not shift with the server time zone (appointments), and TIMESTAMP or UTC DATETIME for event times. Always write dates in ISO format ('2026-03-14'). SQLite stores dates as text, so date comparisons still work in the editor:

SQL Dates are ISO strings that sort correctly ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

ENUM and SET

ENUM('small','medium','large') stores one value from a fixed list in 1 or 2 bytes and sorts by the list position. SET('a','b','c') stores any combination. They are compact, but changing the list means an ALTER TABLE, and ENUM values hide a lookup table. Often a small reference table with a foreign key (or a CHECK constraint) is more flexible:

SQL
status ENUM('pending','shipped','delivered','cancelled') NOT NULL DEFAULT 'pending'
-- portable alternative
status VARCHAR(20) NOT NULL CHECK (status IN ('pending','shipped','delivered','cancelled'))
SQL A CHECK constraint as an ENUM stand-in

Press Run (or Ctrl + Enter) to see the result here.

(Enable the commented statement to see the constraint refuse a bad value.)

JSON, UUID, INET and friends

  • JSON in MariaDB is an alias for LONGTEXT with a validity check. Use functions such as JSON_VALUE(col, '$.name'), JSON_EXTRACT, JSON_SET. You can index a JSON value through a generated column.
  • UUID (10.7+) stores a UUID in 16 bytes. Random UUID primary keys fragment the index, so prefer UUID_v7-style ordered values or an integer key plus a UUID column.
  • INET4 and INET6 (10.10+) store IP addresses compactly.
  • GEOMETRY, POINT and friends hold spatial data.
  • BIT(n) stores bit fields.

Choosing types: a checklist

  1. Smallest type that safely fits. TINYINT UNSIGNED for an age or a rating, not INT.
  2. Exact for money: DECIMAL.
  3. Variable text: VARCHAR; fixed codes: CHAR.
  4. Dates: DATE, DATETIME or TIMESTAMP, never strings that you format by hand.
  5. NOT NULL by default.
  6. Same type on both sides of a join or foreign key, including UNSIGNED, so indexes can be used.
flowchart TD
  Q["What do you store?"] --> N{"Number?"}
  N -->|"whole"| I["TINYINT / INT / BIGINT"]
  N -->|"money"| D["DECIMAL(p,s)"]
  N -->|"measurement"| F["DOUBLE"]
  Q --> T{"Text?"}
  T -->|"short"| V["VARCHAR(n)"]
  T -->|"long"| X["TEXT"]
  Q --> W{"Date or time?"}
  W --> DT["DATE / DATETIME / TIMESTAMP"]

A simple decision path.

How types behave in SQLite (the runner)

The in-browser engine uses type affinity and is lenient: any column can hold any value. MariaDB is strict. Insert 'abc' into an INT column and MariaDB (in strict mode) refuses. When you test a lesson on the runner, remember that real MariaDB enforces the declared type.

SQL typeof shows what SQLite really stored

Press Run (or Ctrl + Enter) to see the result here.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Which type should you use for prices in a shop?

    DECIMAL stores exact decimal values, so sums and comparisons are correct. FLOAT and DOUBLE are approximate.
  2. 2. What does the 11 in INT(11) mean?

    INT is always 4 bytes. The number only affects ZEROFILL display and is deprecated.
  3. 3. Which is the best choice for a two-letter country code such as 'DE'?

    Fixed-length codes fit CHAR well and are compact.
  4. 4. What is special about TIMESTAMP compared with DATETIME?

    TIMESTAMP values are converted to and from the session time zone; DATETIME is stored as written.
  5. 5. Why can a VARCHAR(255) column need more than 255 bytes?

    Length counts characters, and a character may need up to four bytes, which also matters for index limits.

Summary

  • Pick the smallest integer that fits; UNSIGNED doubles the positive range; INT(11) is only a display hint.
  • Use DECIMAL for money and DOUBLE for measurements; never compare floats with =.
  • VARCHAR for variable text, CHAR for fixed codes, TEXT/BLOB for long content.
  • DATE, DATETIME and TIMESTAMP handle time; JSON, UUID and INET6 cover special cases.

Next: Keys and constraints