Skip to content
Tutorials Center
My learning

Topics

Lesson 7 of 75 ยท Data Types

Numeric Types

Pick the right MySQL numeric type: TINYINT to BIGINT, UNSIGNED, DECIMAL vs FLOAT and DOUBLE, BIT and BOOLEAN, plus how overflow behaves in strict mode.

Choosing a numeric type looks trivial until a counter overflows at 2.1 billion rows or an invoice total is off by a cent. MySQL has a family of integer types, two kinds of fractional types, and a few special cases such as BIT and BOOLEAN. Knowing the ranges and the exact-versus-approximate split lets you pick the smallest type that is safe, which also keeps tables and indexes small.

The type definitions are read-only because only a MySQL server enforces them. The live examples below use SQLite to demonstrate the underlying ideas: exact money arithmetic with integers, the rounding trap of binary floating point, and range checks written by hand.

Integer types

MySQL has five integer types. Each uses a fixed number of bytes, and each has a signed and an unsigned range:

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 -8,388,608 to 8,388,607 0 to 16,777,215
INT (INTEGER) 4 about -2.1 billion to 2.1 billion 0 to about 4.29 billion
BIGINT 8 about -9.2 quintillion to 9.2 quintillion 0 to about 18.4 quintillion

UNSIGNED removes the negative half and doubles the positive range. Use it for values that cannot be negative, such as ids, counts and quantities:

SQL
CREATE TABLE page_views (
  id      BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  page_id INT UNSIGNED NOT NULL,
  hits    SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (id)
);

Two cautions about UNSIGNED. First, subtraction involving unsigned values is also unsigned, so SELECT CAST(3 AS UNSIGNED) - 5 raises "BIGINT UNSIGNED value is out of range" instead of returning -2. Second, the UNSIGNED attribute on FLOAT, DOUBLE and DECIMAL is deprecated and should not be used. Use a CHECK (price >= 0) constraint instead.

The number in INT(11) means nothing

You will see INT(11) in old schemas and tutorials. The number was a display width, only used by the client for zero-padding with ZEROFILL. It never affected storage or range, and MySQL 8.0.17 deprecated it. Write plain INT. Display width on TINYINT(1) is the one survivor, because tools use it as a hint for booleans.

Which size to choose

Pick the smallest type that will hold the largest value the column can ever have, with some headroom:

  • A status code with ten values: TINYINT UNSIGNED.
  • A year or a small counter: SMALLINT UNSIGNED.
  • Row ids for most tables: INT UNSIGNED (4.29 billion rows).
  • Ids that may exceed that, such as events, logs or clicks: BIGINT UNSIGNED.

Size matters more than it seems. Every secondary index stores a copy of the primary key, so a BIGINT key makes every index on that table 4 bytes wider per row. Choosing the Right Data Types returns to this.

Pick the smallest safe integer

TINYINT 1 byte, up to 255 unsigned.
SMALLINT 2 bytes, up to 65,535.
INT 4 bytes, about 4.29 billion.
BIGINT 8 bytes, for huge counters.

Smaller columns make every index smaller too.

1 / 2

Overflow: what happens when a value does not fit

In strict mode (the default, see SQL Modes), inserting a value outside the range fails:

SQL
CREATE TABLE levels (stars TINYINT UNSIGNED NOT NULL);
INSERT INTO levels VALUES (200);   -- OK
INSERT INTO levels VALUES (300);
-- ERROR 1264 (22003): Out of range value for column 'stars' at row 1

Without strict mode, the value is clamped to the nearest limit (255) and you get only a warning. That is silent data loss, which is the reason to keep strict mode on.

Arithmetic overflow is also an error: SELECT 9223372036854775807 + 1 fails with "BIGINT value is out of range". A different surprise is AUTO_INCREMENT overflow. When a counter reaches its type's maximum, the next insert fails with a duplicate key error on the maximum value, so plan for growth rather than waiting for the outage.

SQLite does not enforce integer sizes, so a column declared TINYINT accepts 300. You can express the same rule with CHECK, and this works in both engines:

SQL Enforcing a TINYINT UNSIGNED range by hand

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

The first three rows go in. The fourth fails with CHECK constraint failed, the same outcome as strict MySQL's out-of-range error. The point to take away is that the range is part of the type in MySQL, while here you had to add it yourself.

DECIMAL: exact numbers for money

DECIMAL(M, D) stores an exact number with M digits in total, D of which come after the decimal point. DECIMAL(8,2) holds values from -999,999.99 to 999,999.99. NUMERIC is a synonym. MySQL stores decimals as strings of digits internally, so arithmetic on them gives exact results, including rounding rules you can predict.

SQL
CREATE TABLE invoice_lines (
  id         INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  unit_price DECIMAL(10,2) NOT NULL,
  quantity   SMALLINT UNSIGNED NOT NULL
);
INSERT INTO invoice_lines (unit_price, quantity) VALUES (19.99, 3);
SELECT unit_price * quantity AS total FROM invoice_lines;   -- 59.97, exactly

If you insert a value with more decimals than the column allows, such as 19.999 into DECIMAL(10,2), MySQL rounds it (to 20.00) and issues a note, rather than failing. Insert too many integer digits and strict mode raises an out-of-range error.

SQLite has no exact decimal type, but you can get the same exactness by storing integer cents, a trick many billing systems use even on MySQL:

SQL Exact money arithmetic with integer cents

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

The total is 10,022 cents, shown as 100.22. Integer arithmetic never drifts, no matter how many lines you add. In MySQL DECIMAL gives you the same safety with a friendlier type. (The printf formatting is SQLite's. MySQL would use FORMAT() or CONCAT.)

FLOAT and DOUBLE: fast but approximate

FLOAT is a 4-byte single-precision number and DOUBLE an 8-byte double-precision one. They store values in binary, and many decimal fractions, including 0.1, have no exact binary form. The tiny errors are invisible in a single value and surface when you compare or add:

SQL
SELECT 0.1 + 0.2 = 0.3;        -- literals are DECIMAL: returns 1
SELECT 0.1e0 + 0.2e0 = 0.3e0;  -- DOUBLE literals: returns 0

The same trap exists in any language, and you can see it live:

SQL Binary floating point surprises

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

equals_point_three is 0 (false), because the sum is really 0.30000000000000004. The tolerance comparison gives 1. Use DOUBLE for scientific measurements, coordinates, sensor readings and statistics, where small relative error is acceptable and speed or range matters. Never use it for money. FLOAT(p) takes a precision hint, but the forms FLOAT(M,D) and DOUBLE(M,D) are deprecated.

BIT and BOOLEAN

MySQL has no true boolean type. BOOLEAN and BOOL are aliases for TINYINT(1): FALSE is 0, TRUE is 1, and any other number is stored as it is.

SQL
CREATE TABLE flags (id INT PRIMARY KEY, active BOOLEAN NOT NULL DEFAULT TRUE);
INSERT INTO flags VALUES (1, TRUE), (2, FALSE), (3, 5);
SELECT id, active FROM flags WHERE active;   -- rows 1 and 3

Because 5 is accepted and counts as true in a WHERE, add CHECK (active IN (0, 1)) if you want a real two-valued column. BIT(M) stores up to 64 bits, written as b'101', and is displayed as raw bytes in many clients, which makes it awkward. For flags, TINYINT(1) or BOOLEAN is easier to work with, and for several flags in one column, SET or a few boolean columns are usually clearer.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What is the maximum value of an UNSIGNED TINYINT column?

    One byte holds 256 values. Unsigned they range from 0 to 255, and signed from -128 to 127.
  2. 2. What does the 11 in INT(11) mean in MySQL 8?

    INT is always 4 bytes with the same range. The display width only mattered for ZEROFILL formatting and is deprecated.
  3. 3. Which type should store prices and account balances?

    DECIMAL is exact. FLOAT and DOUBLE are binary approximations and accumulate tiny errors.
  4. 4. In strict mode, what happens when you insert 300 into a TINYINT UNSIGNED column?

    Strict mode raises error 1264 and stores nothing. Without strict mode the value would be clamped to 255 with a warning.
  5. 5. What is BOOLEAN in MySQL?

    TRUE and FALSE are the integers 1 and 0, and other numbers are accepted, so add a CHECK constraint if only 0 and 1 are allowed.

Summary

  • Integers come in five sizes, TINYINT (1 byte) to BIGINT (8 bytes); add UNSIGNED for non-negative values and choose the smallest safe size.
  • INT(11) display widths are meaningless and deprecated. Overflow is an error in strict mode and silent clamping otherwise.
  • DECIMAL(M,D) is exact and the right choice for money; integer cents are an equally safe alternative.
  • FLOAT and DOUBLE are approximate, so never compare them with = and never use them for currency.
  • BOOLEAN is just TINYINT(1); add a CHECK to keep it to 0 and 1.

Next: String Types.