MENU


Data Types

A column's type decides which values it accepts, what each costs on disk and in every index, and how values compare and sort. MySQL is full of surprises here, rounding or converting some bad values with a mere note, so every result below comes from MySQL 9.7.2 in its default strict sql_mode (Section 3.2.8), run against the shop database and a few test tables.

3.3.1 Choosing the Narrowest Type That Works

Pick the smallest type that holds every legitimate value, with room to grow. Narrow rows fit more per 16 KB InnoDB page and in memory, and the primary key matters most because every secondary index copies it (Section 3.11.2). Here 200,000 events go into habitual types and into narrow ones:

CREATE TABLE events_wide (id BIGINT AUTO_INCREMENT PRIMARY KEY, uuid CHAR(36) NOT NULL,

status VARCHAR(20) NOT NULL, at DATETIME(6) NOT NULL, KEY (uuid));

CREATE TABLE events_narrow (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

uuid BINARY(16) NOT NULL, status ENUM('pending','paid','shipped','cancelled') NOT NULL,

at DATETIME NOT NULL, KEY (uuid));

SET SESSION cte_max_recursion_depth = 300000;

INSERT INTO events_wide (uuid, status, at)

WITH RECURSIVE n (i) AS (SELECT 1 UNION ALL SELECT i + 1 FROM n WHERE i < 200000)

SELECT UUID(), ELT(1 + i % 4, 'pending', 'paid', 'shipped', 'cancelled'),

'2026-01-01' + INTERVAL i MINUTE FROM n;

INSERT INTO events_narrow (uuid, status, at)

SELECT UUID_TO_BIN(uuid), status, at FROM events_wide;

ANALYZE TABLE events_wide, events_narrow;

SELECT TABLE_NAME, TABLE_ROWS, ROUND(DATA_LENGTH / 1048576, 1) AS data_mb,

ROUND(INDEX_LENGTH / 1048576, 1) AS index_mb FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME LIKE 'events%';

Listing 3.22: The same data in habitual types and in narrow types

...

+---------------+------------+---------+----------+

| TABLE_NAME | TABLE_ROWS | data_mb | index_mb |

+---------------+------------+---------+----------+

| events_narrow | 199826 | 9.5 | 5.5 |

| events_wide | 199213 | 16.5 | 10.6 |

+---------------+------------+---------+----------+

Output of Listing 3.22 The same information takes 42% less data and 48% less index: the key shrank from 8 bytes to 4, the UUID from 36 to 16, the status to 1, and the time lost 3 bytes of microseconds. Leave room to grow, though: retyping a key later rewrites every referencing table.

3.3.2 Integer Types, UNSIGNED, and AUTO_INCREMENT Ranges

The integer types differ only in size and range, and UNSIGNED trades the negatives for double the maximum: Type Bytes Signed range UNSIGNED maximum TINYINT 1 -128 to 127 255 SMALLINT 2 -32,768 to 32,767 65,535 MEDIUMINT 3 ±8.4 million 16,777,215 INT 4 ±2.15 billion 4,294,967,295 BIGINT 8 ±9.22 × 10^18 1.84 × 10^19 Table 3.4: Integer types and their ranges The 11 in INT(11) was only a display width for ZEROFILL padding, and both are deprecated:

CREATE TABLE int_demo (tiny TINYINT, utiny TINYINT UNSIGNED, w INT(11));

INSERT INTO int_demo (tiny, utiny) VALUES (128, -1);

SELECT CAST(1 AS UNSIGNED) - 2;

CREATE TABLE seq (id TINYINT UNSIGNED AUTO_INCREMENT PRIMARY KEY);

INSERT INTO seq VALUES (254), (NULL);

INSERT INTO seq VALUES (NULL);

Listing 3.23: Deprecated display width, overflow, unsigned arithmetic and an exhausted counter

Warning (Code 1681): Integer display width is deprecated and will be removed in a future

release.

ERROR 1264 (22003): Out of range value for column 'tiny' at row 1

ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(cast(1 as unsigned) - 2)'

ERROR 1062 (23000): Duplicate entry '255' for key 'seq.PRIMARY'

Output of Listing 3.23 Strict mode rejects the overflow. With sql_mode = '', inserting (300, -5) succeeds with two warnings and silently stores 127 and 0, so never run production without strict mode. Arithmetic with an UNSIGNED operand is unsigned, so 1 - 2 fails. An exhausted AUTO_INCREMENT stays at the maximum and the next insert collides with it; the view sys.schema_auto_increment_columns now reports an auto_increment_ratio of 1.0000 for seq.

3.3.3 DECIMAL Versus FLOAT and DOUBLE

DECIMAL(M,D) stores an exact number of up to 65 digits, D of them after the point: the bookshop's DECIMAL(8,2) prices reach 999,999.99 in 4 bytes. FLOAT (4 bytes) and DOUBLE (8 bytes) store approximate binary fractions, and most decimals, 0.1 among them, have no exact binary form.

CREATE TABLE money (d DECIMAL(8,2), f FLOAT);

INSERT INTO money VALUES (0.1, 0.1), (0.2, 0.2), (39.90, 39.90);

SELECT SUM(d), SUM(f), SUM(f = 39.9) AS f_hits, 0.1 + 0.2 = 0.3 AS exact,

0.1e0 + 0.2e0 = 0.3e0 AS approx, ROUND(2.5) AS r_dec, ROUND(2.5e0) AS r_dbl FROM money;

INSERT INTO money (d) VALUES (12.345), (1234567.8);

INSERT INTO money (d) VALUES (12.345);

SELECT d FROM money WHERE f IS NULL;

Listing 3.24: Exact and approximate numbers side by side

+--------+--------------------+--------+-------+--------+-------+-------+

| SUM(d) | SUM(f) | f_hits | exact | approx | r_dec | r_dbl |

+--------+--------------------+--------+-------+--------+-------+-------+

| 40.20 | 40.200001530349255 | 0 | 1 | 0 | 3 | 2 |

+--------+--------------------+--------+-------+--------+-------+-------+

ERROR 1264 (22003): Out of range value for column 'd' at row 2

Note (Code 1265): Data truncated for column 'd' at row 1

+-------+

| d |

+-------+

| 12.35 |

+-------+

Output of Listing 3.24 The FLOAT sum drifts and the stored 39.90 no longer equals 39.9. ROUND() rounds exact values half away from zero but approximate ones to even. An extra DECIMAL decimal place is rounded with only a note, even in strict mode, while an extra integer digit fails the whole statement. Use DECIMAL for money, DOUBLE for measurements. FLOAT(7,4) digit counts and UNSIGNED on these types are deprecated; use CHECK (price >= 0) (Section 3.4.3).

3.3.4 CHAR, VARCHAR, TEXT, and BLOB

CHAR(M) (up to 255 characters) is space-padded on storage and stripped on retrieval. VARCHAR(M) stores exactly what you give it plus a 1- or 2-byte length, and M counts characters, so a utf8mb4 VARCHAR(100) may need 400 bytes. BINARY and VARBINARY hold bytes.

CREATE TABLE pad (c CHAR(5), v VARCHAR(5));

INSERT INTO pad VALUES ('ab ', 'ab ');

SELECT CONCAT('[', c, ']') AS c, CONCAT('[', v, ']') AS v, c = 'ab' AS c_eq, v = 'ab' AS v_eq,

CAST(v AS BINARY) = 'AB ' AS bytes_eq FROM pad;

INSERT INTO pad (v) VALUES ('abcdef');

Listing 3.25: Padding, trailing spaces and the length limit

+------+--------+------+------+----------+

| c | v | c_eq | v_eq | bytes_eq |

+------+--------+------+------+----------+

| [ab] | [ab ] | 1 | 0 | 0 |

+------+--------+------+------+----------+

ERROR 1406 (22001): Data too long for column 'v' at row 1

Output of Listing 3.25 v_eq is 0 because the default collation, utf8mb4_0900_ai_ci, is NO PAD: trailing spaces count (older PAD SPACE collations ignore them), so trim input in PHP. As bytes, ab and AB differ. A row's columns share 65,535 bytes, so two utf8mb4 VARCHAR(10000) columns fail with error 1118, "Row size too large". TEXT and BLOB live off the row, counting 9 to 12 bytes, and hold up to 255 bytes (TINY), 64 KB, 16 MB (MEDIUM) or 4 GB (LONG). Prefer VARCHAR for values you filter or sort, since TEXT can only be prefix-indexed (Section 3.11.7).

3.3.5 ENUM, SET, and BIT

An ENUM such as orders.status stores one value from a fixed list as its position, from 1, in one byte. A SET stores any combination of up to 64 members as a bitmask. BIT(M) stores M bits, and BOOL is merely TINYINT(1):

SELECT DISTINCT status, status + 0 AS idx, status > 'paid' AS text_cmp, status > 2 AS idx_cmp

FROM orders ORDER BY status;

CREATE TABLE flags (perms SET('read','write','delete'), bits BIT(4), active BOOL);

INSERT INTO flags VALUES ('write,read', b'1010', TRUE), ('delete,read,read', 5, 7);

SELECT perms, perms + 0 AS mask, FIND_IN_SET('write', perms) AS has_write,

BIN(bits) AS bin, bits + 0 AS num, active FROM flags;

Listing 3.26: How ENUM, SET, BIT and BOOL store and compare values

+-----------+-----+----------+---------+

| status | idx | text_cmp | idx_cmp |

+-----------+-----+----------+---------+

| pending | 1 | 1 | 0 |

| paid | 2 | 0 | 0 |

| shipped | 3 | 1 | 1 |

| cancelled | 4 | 0 | 1 |

+-----------+-----+----------+---------+

+-------------+------+-----------+------+------+--------+

| perms | mask | has_write | bin | num | active |

+-------------+------+-----------+------+------+--------+

| read,write | 3 | 2 | 1010 | 10 | 1 |

| read,delete | 5 | 0 | 101 | 5 | 7 |

+-------------+------+-----------+------+------+--------+

Output of Listing 3.26 ORDER BY status sorts by list position. Against a string MySQL compares the text, against a number the index, and inserting the unknown status 'refunded' fails with ERROR 1265, a misleading "Data truncated for column 'status'". The SET reordered its members and dropped the duplicate, and BOOL stored 7, which fails WHERE active = TRUE because TRUE is 1. Read BIT with BIN() or + 0. Appending an ENUM member is instant, but inserting one mid-list rebuilds the table, so a growing list belongs in a lookup table (Section 3.10.3).

3.3.6 DATE, TIME, DATETIME, and TIMESTAMP

DATE (3 bytes) and DATETIME (5 bytes) span the years 1000 to 9999, and TIME (3 bytes) holds a time of day or a duration up to ±838:59:59. TIMESTAMP (4 bytes) stores a moment in UTC, converting through the session time_zone, and ends at 2038-01-19 03:14:07 UTC. Six fractional digits, as in DATETIME(6), cost up to 3 more bytes.Figure 3.2: TIMESTAMP converts through UTC; DATETIME stores the literal wall-clock value

CREATE TABLE tz (dt DATETIME, ts TIMESTAMP);

SET time_zone = '+08:00';

INSERT INTO tz VALUES ('2026-09-23 09:00:00', '2026-09-23 09:00:00'),

('2026-09-23 09:00:00-04:00', '2026-09-23 09:00:00-04:00');

SET time_zone = '+00:00';

SELECT dt, ts, UNIX_TIMESTAMP(ts) AS epoch FROM tz;

INSERT INTO tz (ts) VALUES ('2038-01-19 03:14:08');

CREATE TABLE frac (d0 DATETIME, d3 DATETIME(3), t TIME);

INSERT INTO frac VALUES ('2026-12-31 23:59:59.5', '2026-12-31 23:59:59.12345', '1130');

SELECT * FROM frac;

Listing 3.27: Time zones, the 2038 limit and fractional seconds

+---------------------+---------------------+------------+

| dt | ts | epoch |

+---------------------+---------------------+------------+

| 2026-09-23 09:00:00 | 2026-09-23 01:00:00 | 1790125200 |

| 2026-09-23 21:00:00 | 2026-09-23 13:00:00 | 1790168400 |

+---------------------+---------------------+------------+

ERROR 1292 (22007): Incorrect datetime value: '2038-01-19 03:14:08' for column 'ts' at row 1

+---------------------+-------------------------+----------+

| d0 | d3 | t |

+---------------------+-------------------------+----------+

| 2027-01-01 00:00:00 | 2026-12-31 23:59:59.123 | 00:11:30 |

+---------------------+-------------------------+----------+

Output of Listing 3.27 In UTC the TIMESTAMP shows different clock times; the DATETIME does not. The -04:00 offset was converted to the session zone and discarded. Strict mode rejects the second after 2038, as it does February 30. Extra fractional digits are rounded, so half a second pushed d0 into 2027, and '1130' means 00:11:30. Run connections in UTC, convert in PHP (Section 4.7.11), and prefer DATETIME beyond 2038.

3.3.7 The JSON, Spatial, and VECTOR Types

A JSON column such as products.attributes rejects invalid documents on write and stores valid ones in a binary format for fast access by path. Spatial types (POINT, LINESTRING, POLYGON, GEOMETRY and collections) store coordinates, ideally with an SRID: loc POINT NOT NULL SRID 4326. Section 3.9 covers both in depth. VECTOR(N), added in MySQL 9.0.0 (July 2024), holds up to N 4-byte floats for machine-learning embeddings; N defaults to 2048 and may reach 16383.

CREATE TABLE emb (product_id INT UNSIGNED PRIMARY KEY, v VECTOR(4));

INSERT INTO emb VALUES (1, STRING_TO_VECTOR('[0.12, 0.98, -0.34, 0.05]')),

(2, STRING_TO_VECTOR('[1, 2, 3]'));

SELECT product_id, VECTOR_TO_STRING(v) AS v, VECTOR_DIM(v) AS dims, LENGTH(v) AS bytes

FROM emb;

SELECT DISTANCE(v, STRING_TO_VECTOR('[0.1, 0.9, -0.3, 0.0]'), 'COSINE') FROM emb;

Listing 3.28: Storing and reading embeddings in a VECTOR column

+------------+----------------------------------------------------+------+-------+

| product_id | v | dims | bytes |

+------------+----------------------------------------------------+------+-------+

| 1 | [1.20000e-01,9.80000e-01,-3.40000e-01,5.00000e-02] | 4 | 16 |

| 2 | [1.00000e+00,2.00000e+00,3.00000e+00] | 3 | 12 |

+------------+----------------------------------------------------+------+-------+

ERROR 1305 (42000): FUNCTION shop.DISTANCE does not exist

Output of Listing 3.28 VECTOR(4) is a maximum, so three elements fit; VECTOR(16384) fails with error 6137. DISTANCE() exists only in MySQL HeatWave on OCI and MySQL AI, so a LAMP stack ranks embeddings in PHP. A VECTOR cannot be a key and compares only for equality.

3.3.8 Character Sets and Collations

A character set maps characters to bytes; a collation compares and sorts them. The default is utf8mb4 (full UTF-8, 1 to 4 bytes per character) with utf8mb4_0900_ai_ci: Unicode 9.0 rules, accent- and case-insensitive. The deprecated utf8mb3, which the bare name utf8 still means, stops at 3 bytes and cannot store emoji.

SET @s = CONCAT('Café ', _utf8mb4 0xF09F9098); -- 0xF09F9098 is U+1F418, an emoji

CREATE TABLE cs (old VARCHAR(20) CHARACTER SET utf8, new VARCHAR(20));

INSERT INTO cs (old) VALUES (@s);

SELECT CHAR_LENGTH(@s) AS chars, LENGTH(@s) AS bytes, 'resume' = 'Résumé' AS ai_ci,

'resume' = 'Résumé' COLLATE utf8mb4_0900_as_ci AS as_ci,

'Resume' = 'resume' COLLATE utf8mb4_0900_as_cs AS as_cs,

'ß' = 'ss' AS ss_0900, 'ß' = 'ss' COLLATE utf8mb4_general_ci AS ss_general;

Listing 3.29: Bytes versus characters, and what each collation treats as equal

Warning (Code 3719): 'utf8' is currently an alias for the character set UTF8MB3, but will be an

alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be

unambiguous.

ERROR 1366 (HY000): Incorrect string value: '\xF0\x9F\x90\x98' for column 'old' at row 1

+-------+-------+-------+-------+-------+---------+------------+

| chars | bytes | ai_ci | as_ci | as_cs | ss_0900 | ss_general |

+-------+-------+-------+-------+-------+---------+------------+

| 6 | 10 | 1 | 0 | 0 | 1 | 0 |

+-------+-------+-------+-------+-------+---------+------------+

Output of Listing 3.29 Six characters take ten bytes (é two, the emoji four). The default collation finds "resume" equal to "Résumé", right for search but wrong for a case-sensitive coupon code, which needs utf8mb4_0900_as_cs or utf8mb4_bin; the legacy utf8mb4_general_ci even disagrees on ß. Use one collation throughout, with charset=utf8mb4 in the PHP DSN (Section 4.16.13): joining a utf8mb4_0900_ai_ci column to a utf8mb4_general_ci one fails with ERROR 1267, "Illegal mix of collations".

3.3.9 NULL Handling, Defaults, and Type Conversion

NULL means "unknown", so almost any expression with a NULL is NULL, even NULL = NULL: test with IS NULL or <=>. Defaults may be expressions in parentheses.

SELECT NULL = NULL AS eq, NULL <=> NULL AS null_safe, CONCAT('a', NULL) AS cat,

COUNT(body) AS bodies, COUNT(*) AS all_rows, '5 apples' + 1 AS a, '1e3' + 0 AS b,

'abc' = 0 AS c, '10' > '9' AS d, 10 > '9' AS e FROM reviews;

INSERT INTO customers (email, country) VALUES ('ivy@example.com', 'US');

CREATE TABLE defs (id BINARY(16) DEFAULT (UUID_TO_BIN(UUID())), qty INT NOT NULL DEFAULT 1,

due DATE DEFAULT (CURRENT_DATE + INTERVAL 14 DAY));

INSERT INTO defs () VALUES ();

SELECT LENGTH(id) AS id_bytes, qty, due > CURRENT_DATE AS future FROM defs;

Listing 3.30: NULL logic, implicit conversion and expression defaults

+------+-----------+------+--------+----------+---+------+---+---+---+

| eq | null_safe | cat | bodies | all_rows | a | b | c | d | e |

+------+-----------+------+--------+----------+---+------+---+---+---+

| NULL | 1 | NULL | 6 | 7 | 6 | 1000 | 1 | 0 | 1 |

+------+-----------+------+--------+----------+---+------+---+---+---+

Warning (Code 1292): Truncated incorrect DOUBLE value: '5 apples'

Warning (Code 1292): Truncated incorrect DOUBLE value: 'abc'

ERROR 1364 (HY000): Field 'name' doesn't have a default value

+----------+-----+--------+

| id_bytes | qty | future |

+----------+-----+--------+

| 16 | 1 | 1 |

+----------+-----+--------+

Output of Listing 3.30 A string meeting a number becomes a DOUBLE read from its leading digits, so 'abc' = 0 is true; two strings compare as text, so '10' > '9' is false. Only column writes are strict: setting stock = '12 units' fails with ERROR 1265. For WHERE sku = 123, EXPLAIN warns "Cannot use ref access on index 'sku' due to type or collation conversion". Pass values in the column's type (Section 4.16.5) and CAST() deliberately.