Skip to content
Tutorials Center
My learning

Topics

Lesson 12 of 75 ยท Data Types

Choosing the Right Data Types

Pick MySQL column types with confidence: smallest sufficient integers, DECIMAL for money, BINARY(16) UUIDs, INET6_ATON for IPs, booleans and the effect on indexes.

Every column type is a small bet. A type that is too large wastes disk, memory and cache on every row and every index entry. A type that is too small fails the day your data grows past it. And some types, such as FLOAT for money, are simply wrong for the job. A table with a billion rows turns a wasteful choice of a few bytes into gigabytes.

This lesson collects the practical rules: pick the smallest type that safely fits, use exact types for exact values, store identifiers and addresses compactly, and remember that the primary key is copied into every secondary index. You have seen the individual types in String Types and Date and Time Types. Here we compare and decide.

Four rules of thumb

Smallest that fits Smaller rows mean more rows per page.
Exact for money DECIMAL or integer cents, never FLOAT.
Compact identifiers 16 bytes beat 36 characters.
Short primary keys Every index carries a copy of it.
1 / 2

Integers: the smallest sufficient type

MySQL has five integer sizes: TINYINT (1 byte), SMALLINT (2), MEDIUMINT (3), INT (4) and BIGINT (8). With UNSIGNED the range starts at 0 and the maximum doubles. The numeric types themselves are covered in Numeric Types. The choice rule is straightforward:

Data Reasonable type
Flags, small enumerations (0 to 255) TINYINT UNSIGNED
Years, port numbers, small counters SMALLINT UNSIGNED (up to 65,535)
Row ids for tables that stay below ~4 billion rows INT UNSIGNED (up to 4,294,967,295)
Row ids for event, log or analytics tables BIGINT UNSIGNED

A common mistake is BIGINT everywhere "to be safe". The cost is real: 4 extra bytes per row, and again in each index. The opposite mistake is a signed INT id on a busy table, which overflows at 2,147,483,647. When it does, inserts start failing with "Out of range" and the only fix is a slow ALTER TABLE. For a table that inserts 1,000 rows per second, a signed INT lasts about 25 days over two billion rows. Estimate growth and choose with margin.

Money: never FLOAT

Binary floating point cannot represent most decimal fractions exactly. FLOAT and DOUBLE are approximations, and tiny errors add up in sums and comparisons. Use an exact type for money:

  • DECIMAL(p, s) stores exact decimal digits. DECIMAL(10,2) holds up to 99,999,999.99. MySQL calculates with it exactly.
  • Integer minor units (cents stored in a BIGINT) are also exact and fast. They require your code to convert at the edges.
SQL
CREATE TABLE invoices (
  id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  total        DECIMAL(12,2) NOT NULL,            -- exact: 1234567890.12
  tax_rate     DECIMAL(5,4)  NOT NULL DEFAULT 0.0825,
  currency     CHAR(3)       NOT NULL             -- ISO 4217 code such as 'USD'
);

Store the currency alongside the amount, and pick a scale for the most precise currency you use. Some currencies have no decimals (JPY) and some have three (KWD), and crypto or fuel prices can need more. A cheap rule: DECIMAL(19,4) is common for ledgers that need room for rounding.

SQLite has no DECIMAL type, but you can see why the choice matters. This stand-in adds 0.1 ten times as a floating-point number and as integer tenths:

SQL Floating point drift versus exact integers (stand-in for FLOAT vs DECIMAL)

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

The first column shows a value just below one (0.99999999999999990, the closest 17-digit printout of the stored double), so the third column is 0. The integer version is exactly ten tenths, 1.0, and the last column is 1. A MySQL DECIMAL(3,1) column would behave like the integer version. That is why WHERE price = 0.3 can quietly fail on float columns and succeed on DECIMAL.

Strings: realistic limits

Use VARCHAR with a limit that matches real data: 190 characters for an email index in older utf8mb4 setups, 2 for a CHAR(2) country code, 255 for most names. Do not use TEXT for short values, since it blocks normal indexing and may live off-page. Remember the 768-character index limit explained in Character Sets and Collations.

UUIDs

A UUID is a 128-bit value, usually written as 36 characters like 6f1c2a4e-9b7d-4f3a-8c21-0a5d7e9b1c34. Storing it as CHAR(36) costs at least 36 bytes (the digits and hyphens are ASCII), and every index repeats it. The compact form is BINARY(16), which is 16 bytes.

MySQL 8 has two functions for the conversion:

SQL
CREATE TABLE sessions (
  id         BINARY(16) PRIMARY KEY,
  user_id    BIGINT UNSIGNED NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO sessions (id, user_id) VALUES (UUID_TO_BIN(UUID(), 1), 42);

SELECT BIN_TO_UUID(id, 1) AS id, user_id FROM sessions;

The second argument, the swap flag, matters for version 1 UUIDs. UUID() returns a version 1 value whose first part is the low bits of a timestamp, so consecutive values look random, and random primary keys scatter inserts across the clustered index and cause page splits. With swap_flag = 1, MySQL moves the time parts to the front, so values become roughly ordered. Always use the same flag when writing and reading.

UUIDv7 is the modern answer: its first 48 bits are a millisecond timestamp, so ids are naturally ordered, and you do not need to swap anything. MySQL has no built-in v7 generator, so generate it in the application and store it with UNHEX(REPLACE(uuid, '-', '')) or the driver's binary parameter. We cover id strategies in depth in AUTO_INCREMENT, UUIDs and ID Strategies.

The size gap is visible even in a stand-in. The example converts a text UUID into 16 raw bytes with unhex():

SQL UUID text versus binary size (stand-in for UUID_TO_BIN)

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

char36_bytes is 36 and binary16_bytes is 16: less than half the size in the table and in every index that includes it. The price is readability: you read binary ids with BIN_TO_UUID() or HEX().

IP addresses

An IPv4 address is 4 bytes and an IPv6 address 16, yet VARCHAR(45) is a common choice because 45 characters fit the longest IPv6 text form. For tables with millions of rows, store them binary:

  • INET_ATON('192.168.1.10') returns an unsigned integer (3,232,235,786) that fits INT UNSIGNED, and INET_NTOA reverses it. IPv4 only.
  • INET6_ATON(addr) returns a VARBINARY(16) for IPv4 and IPv6, with INET6_NTOA to reverse it.
SQL
CREATE TABLE logins (
  user_id BIGINT UNSIGNED NOT NULL,
  ip      VARBINARY(16) NOT NULL,   -- INET6_ATON(...)
  at      DATETIME NOT NULL
);

INSERT INTO logins VALUES (7, INET6_ATON('203.0.113.9'), NOW());
SELECT INET6_NTOA(ip) FROM logins;

A bonus of the integer form is range queries: a subnet is a pair of numbers, so BETWEEN can use an index. The live stand-in below turns the four parts into an integer with arithmetic, which is what INET_ATON does:

SQL An IPv4 address as an integer (stand-in for INET_ATON)

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

The result for 192.168.1.10 is 3232235786. This is the same number INET_ATON returns on a MySQL server.

Booleans

MySQL has no true boolean type. BOOLEAN and BOOL are aliases for TINYINT(1): 0 is false, any other value is true, and the literals TRUE and FALSE are 1 and 0. A TINYINT can hold 127 by accident, so add a CHECK (is_active IN (0, 1)) when it matters. Use NOT NULL DEFAULT 0 so three-valued logic does not enter your flags through NULL.

Dates, JSON and others

Use DATETIME or TIMESTAMP instead of strings for time, as the date lesson explains. Use the JSON type only for data that really has no fixed shape, because JSON values cannot be indexed directly without generated columns (Generated Columns). Store files in object storage and keep only the key, and use BIT or TINYINT bitmasks only when the saving is real.

How types affect indexes and memory

InnoDB keeps rows in a B-tree sorted by the primary key (the clustered index). Every secondary index entry contains the indexed columns plus a copy of the primary key. This has direct consequences:

  • A fat primary key, such as a CHAR(36) UUID, multiplies through every secondary index. A table with five indexes stores that key six times per row.
  • Indexes and rows are stored in 16 KB pages. Smaller values mean more entries per page, shallower trees and a higher share of the working set in the buffer pool, so more queries run from memory.
  • Random primary keys (v4 UUIDs) insert into the middle of the tree, splitting pages. Sequential keys (AUTO_INCREMENT, UUIDv7) append at the end.
  • A wide VARCHAR(255) costs no more space than the text it holds, but MySQL may reserve its maximum size for sorting and temporary tables. Over-long limits can therefore slow queries even though the data is short.

Side by side: the pairs people confuse

Several MySQL types look alike and are often mixed up. This section puts each pair next to its twin. The behavior shown was checked on a MySQL 8.4 server with the default utf8mb4_0900_ai_ci collation.

CHAR versus VARCHAR

CHAR(n) VARCHAR(n)
Length Fixed: always n characters stored Variable: only the text, plus 1 or 2 length bytes
Trailing spaces Removed when you read the value Kept exactly as stored
Best for Values that are always the same length (CHAR(2) country codes, CHAR(3) currencies) Everything else: names, emails, titles
Maximum 255 characters 65,535 bytes shared by the whole row

The trailing-space rule is the one that surprises people:

SQL
CREATE TABLE tc (c CHAR(5), v VARCHAR(5));
INSERT INTO tc VALUES ('ab ', 'ab ');
SELECT CONCAT('[', c, ']'), CONCAT('[', v, ']'), LENGTH(c), LENGTH(v) FROM tc;
-- [ab] | [ab ] | 2 | 3
SELECT v = 'ab', v = 'ab ' FROM tc;
-- 0 | 1       (the default collation does not ignore trailing spaces)

Use VARCHAR unless every value really has the same length. A CHAR(255) full of short words wastes space, and a VARCHAR for a two-letter code wastes a length byte per row.

FLOAT versus DOUBLE versus DECIMAL

FLOAT DOUBLE DECIMAL(p, s)
Storage 4 bytes 8 bytes Depends on digits (a few bytes)
Precision About 7 significant digits About 15 to 16 Exact, up to 65 digits
Kind Approximate (binary) Approximate (binary) Exact (decimal)
Use for Rough measurements, large arrays of sensor data Scientific values where tiny error is fine Money, quantities that must add up exactly
SQL
CREATE TABLE tn (f FLOAT, d DOUBLE, m DECIMAL(10,2));
INSERT INTO tn VALUES (0.1, 0.1, 0.1);
SELECT f * 3, d * 3, m * 3 FROM tn;
-- 0.30000000447035 | 0.3 | 0.30
SELECT f = 0.1, d = 0.1, m = 0.1 FROM tn;
-- 0 | 1 | 1

The FLOAT column stores the nearest 4-byte binary value to 0.1, which is not exactly 0.1, so multiplying it by 3 shows the error and the equality test fails. DOUBLE hides the same kind of error in digits that are not displayed. DECIMAL keeps exact decimal digits, which is why it is the type for money (Money: never FLOAT). Also note that FLOAT runs out of whole-number precision early: 16777217 stored in a FLOAT column is stored as 16777216 (the client may display it rounded as 16777200), because 24 bits of precision cannot represent it.

TEXT versus BLOB

TEXT family BLOB family
Holds Characters, with a character set and collation Raw bytes, with no character set
Comparison Uses the collation: case-insensitive by default Byte for byte: case-sensitive
Sizes TINYTEXT 255 bytes, TEXT 64 KB, MEDIUMTEXT 16 MB, LONGTEXT 4 GB TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB with the same limits
Use for Articles, comments, descriptions Small binary payloads: thumbnails, hashes, encrypted data

Both types need a prefix length to be indexed, cannot hold ordinary column defaults (expression defaults aside), and may be stored away from the row, so avoid them for short values. The comparison difference is easy to see:

SQL
CREATE TABLE tb (t TEXT, b BLOB);
INSERT INTO tb VALUES ('Hello', 'Hello');
SELECT t = 'hello', b = 'hello', b = 'Hello' FROM tb;
-- 1 | 0 | 1

For files, store them in object storage and keep a key in the table instead of a large BLOB.

NOW(), CURRENT_DATE and friends

These functions return the current moment in different shapes:

Function Returns Example
NOW() and CURRENT_TIMESTAMP (synonyms) Date and time, fixed at the start of the statement 2026-11-09 22:00:02
CURRENT_DATE or CURDATE() Date only 2026-11-09
CURRENT_TIME or CURTIME() Time only 22:00:02
UTC_TIMESTAMP() Date and time in UTC, whatever the session time zone 2026-11-09 18:00:02
SYSDATE() Date and time at the moment it executes changes within one statement

NOW() returns the same value everywhere in one statement, which is what you want for consistent timestamps. SYSDATE() does not. With a two-second SLEEP, this query printed NOW() twice with the same value and SYSDATE() two seconds later:

SQL
SELECT NOW() AS a, SLEEP(2), NOW() AS b, SYSDATE() AS c;
-- 22:04:59 | 0 | 22:04:59 | 22:05:01

The same idea separates the column types. DATE stores a day, TIME a time of day or duration, DATETIME both with no time zone, and TIMESTAMP both converted to and from UTC with the session time zone. The date lesson explains the choice.

SQLite has no CHAR, FLOAT or BLOB distinctions of this kind, so the live example below uses SQLite collations to mimic the behaviors above: NOCASE plays the role of a TEXT column's case-insensitive comparison, RTRIM plays the role of a PAD SPACE comparison, and integer cents stand in for DECIMAL.

SQL Comparison rules as collations, and exact money as integers (SQLite stand-in)

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

The first query prints 1 and 0. The second prints 1, 0 and 3. The third prints 2026-03-09, 14:05:00 and 2026-03-10 14:05:00. The last prints 0, 1 and 1: floating-point addition is not exactly 0.3, but rounding or counting in cents is.

A short checklist

  1. Can this integer ever be negative? If not, UNSIGNED.
  2. What is the largest value in the next five years? Pick the smallest type that covers it twice over.
  3. Is it money, a measurement that must be exact, or a count? Use DECIMAL or integers, not FLOAT.
  4. Is it an identifier or an address that is indexed? Prefer a binary form.
  5. Is the primary key short, stable and ordered?
  6. Does a CHECK or foreign key protect values the type alone cannot?

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Which type is the right choice for storing prices that must add up exactly?

    DECIMAL stores exact decimal digits. FLOAT and DOUBLE are binary approximations and accumulate rounding errors.
  2. 2. How many bytes does a UUID need in BINARY(16) compared with CHAR(36) in ASCII text?

    A UUID is 128 bits, which is 16 bytes in binary form. The hyphenated text form is 36 characters.
  3. 3. Why does a long primary key make every secondary index larger?

    Each secondary index entry stores the indexed columns plus the row's primary key, which it uses to find the full row in the clustered index.
  4. 4. What does BOOLEAN mean in MySQL?

    MySQL has no real boolean type. BOOL and BOOLEAN are aliases for TINYINT(1), where 0 is false and nonzero is true.
  5. 5. Which function converts an IPv6 address to a compact binary value?

    INET6_ATON returns a VARBINARY(16) for IPv4 and IPv6 addresses. INET_ATON handles only IPv4 and returns an integer.

Summary

  • Choose the smallest type that safely fits, use UNSIGNED where values cannot be negative, and leave room for growth.
  • Money belongs in DECIMAL or integer minor units, never FLOAT or DOUBLE.
  • Store UUIDs as BINARY(16) (with UUID_TO_BIN(..., 1) for v1) or use time-ordered UUIDv7, and IP addresses as INT UNSIGNED or VARBINARY(16).
  • BOOLEAN is TINYINT(1), so add NOT NULL and a CHECK where it matters.
  • Every secondary index copies the primary key, so a small, ordered key keeps all indexes small and the buffer pool effective.

Next: Generated Columns.