MySQL – Data Types

Every column has a declared type. The type decides what can be stored, how much space it takes, how it sorts and compares, and — for two of them — whether your arithmetic is correct. This lesson covers the types you will actually use and the handful of choices that cost real money to get wrong.

Integers

SELECT ~0 >> 33 AS int_max_signed, ~0 >> 32 AS int_max_unsigned, ~0 >> 1 AS bigint_max_signed;

+----------------+------------------+---------------------+
| int_max_signed | int_max_unsigned | bigint_max_signed   |
+----------------+------------------+---------------------+
|     2147483647 |       4294967295 | 9223372036854775807 |
+----------------+------------------+---------------------+

UNSIGNED forbids negatives and doubles the positive range. It is right for a quantity that genuinely cannot be negative, and it has a sharp edge: subtracting past zero does not give you a negative number, it errors or wraps depending on sql_mode.

Every primary key in the pizza schema is BIGINT. Four extra bytes per row is nothing; an INT primary key that reaches 2.1 billion is an outage, because AUTO_INCREMENT stops and every insert fails. It happens to real systems, usually to the append-only table nobody thought would grow — and note the counter does not reuse gaps, so deleted rows do not buy you room. Use BIGINT for keys.

One thing that is not a size: INT(11). The number is a display-width hint that almost nothing honours, and it is deprecated in MySQL 8. INT(1) and INT(11) both store the same four bytes.

Money: DECIMAL, never FLOAT

FLOAT and DOUBLE are binary floating point. They cannot represent 0.1 exactly, any more than decimal can write ⅓ exactly, so the errors accumulate:

SELECT CAST(0.1 AS FLOAT) + CAST(0.2 AS FLOAT) AS float_sum,
       CAST(0.1 AS DECIMAL(10,2)) + CAST(0.2 AS DECIMAL(10,2)) AS decimal_sum;

+---------------------+-------------+
| float_sum           | decimal_sum |
+---------------------+-------------+
| 0.30000000447034836 |        0.30 |
+---------------------+-------------+

That is a cent appearing from nowhere, and on a total of a million orders it is a reconciliation meeting. Every price column in the pizza schema is DECIMAL(10,2): exact base-10, ten significant digits, two after the point.

Read DECIMAL(p, s) as precision (total digits) and scale (digits after the point), so DECIMAL(10,2) holds up to 99,999,999.99. Pick the scale from the currency — two for dollars, zero for yen, and four if you are storing unit prices that get multiplied before rounding.

FLOAT and DOUBLE are the right choice for measurements, where the input was approximate anyway: sensor readings, coordinates, percentages. Never for money, and never for anything you will compare with =.

Text: VARCHAR, CHAR and TEXT

• VARCHAR(n) — variable length, up to n characters. The default for almost all text.
• CHAR(n) — fixed length, padded with spaces, and the padding is stripped on read. Only worth it when values are genuinely all one length: a country code, or the CHAR(36) UUID column the pizza schema uses.
• TEXT — up to 64 KB, with MEDIUMTEXT and LONGTEXT above it. Stored away from the row, so SELECT * on a table with a TEXT column costs more than you think. It also cannot have a default, and can only be indexed by prefix.

VARCHAR(255) is a habit rather than a decision — it comes from an old limit that no longer applies. The length is a constraint, so choose it to mean something: the pizza schema uses VARCHAR(120) for a product name and VARCHAR(180) for an email because those are the lengths that are actually reasonable. There is no storage saving in a larger limit, but there is no protection in it either.

utf8mb4, and why plain utf8 is a trap

MySQL's utf8 is a 3-byte encoding that predates the rest of Unicode. It cannot store anything outside the Basic Multilingual Plane — which means emoji, and a menu is exactly where someone will paste one. utf8mb4 is real, 4-byte UTF-8.

SELECT CHAR_LENGTH('piZZa🍕') AS characters, LENGTH('piZZa🍕') AS bytes;

+------------+-------+
| characters | bytes |
+------------+-------+
|          6 |     9 |
+------------+-------+

Six characters, nine bytes — the emoji is four of them on its own. VARCHAR(120) means 120 characters, not bytes, so a name column does not shrink when someone writes in Japanese.

The collation decides sorting and comparison. utf8mb4_0900_ai_ci — the MySQL 8 default — is accent-insensitive and case-insensitive, which is why WHERE name = 'pepsi' matches Pepsi. Use utf8mb4_0900_as_cs when you need case to matter, and set it on the column rather than wrapping queries in UPPER(), which costs you the index. See LIKE.

BOOLEAN is TINYINT(1)

SELECT TRUE = 1 AS true_is_one, CAST(TRUE AS UNSIGNED) AS true_value;

+-------------+------------+
| true_is_one | true_value |
+-------------+------------+
|           1 |          1 |
+-------------+------------+

MySQL has no real boolean type. BOOLEAN and BOOL are aliases for TINYINT(1), and TRUE/FALSE are aliases for 1 and 0 — which is why DESCRIBE product reports tinyint(1) for active and deleted. Write BOOLEAN anyway; it documents intent. Just be aware the column will accept 2.

ENUM, and why the pizza schema does not use it

ENUM('PIZZA','DRINK') stores a small integer and constrains the values, which sounds ideal. The pizza schema uses VARCHAR(20) with a comment instead, and that is a deliberate trade:

• Adding a value is an ALTER TABLE — a schema migration to add a menu category.
• The order of the values is their sort order, so inserting one in the middle reorders existing queries' results.
• Application enums and database enums drift, and the database wins at the worst moment.

Use ENUM for a genuinely closed set that will not change — a weekday. For anything a product manager might extend, use VARCHAR with a CHECK constraint, or a lookup table.

Choosing, in one paragraph

Keys BIGINT. Money DECIMAL. Text VARCHAR with a length that means something, on utf8mb4. Times DATETIME(6) — see date and time types. Flags BOOLEAN. Add NOT NULL unless you can say what NULL means for that column, and reach for FLOAT only when the number was an approximation to begin with.