How do signed and unsigned integer types affect query results, index usage, and storage size? Can you demonstrate an example where overflow behavior differs?

Question

Accepted Answer

MySQL integer types (TINYINT, SMALLINT, INT, BIGINT) can be defined as SIGNED or UNSIGNED. This affects the allowed range of values, how MySQL evaluates comparisons, how indexes are used, and what happens during overflow. SIGNED integers allow negative values, while UNSIGNED integers only store 0 and positive values. UNSIGNED effectively doubles the maximum positive range (e.g., INT SIGNED max = 2,147,483,647 vs INT UNSIGNED max = 4,294,967,295). Indexing works the same structurally, but the value distribution affects query selectivity and performance. Comparisons behave differently when negative values are involved (SIGNED) vs always non-negative (UNSIGNED). Storage size is identical between SIGNED and UNSIGNED versions of the same type. Only the allowed numeric range differs; bytes used are the same. Example: INT (SIGNED or UNSIGNED) always uses 4 bytes. UNSIGNED indexes may provide better selectivity when the dataset contains large positive-only values. Comparisons between SIGNED and UNSIGNED columns may cause implicit type conversion, reducing index usage. Sorting differs because SIGNED sorts negatives first, while UNSIGNED always starts from 0. Example: A SIGNED column can store −100 to 100, but UNSIGNED cannot store negative numbers; attempts will cause errors or wrap-around. SIGNED TINYINT range: −128 to 127 UNSIGNED TINYINT range: 0 to 255 Inserting 130 into SIGNED TINYINT overflows and wraps to −126 Inserting −1 into UNSIGNED TINYINT becomes 255 (wrap-around) CREATE TABLE test_overflow (
  a TINYINT SIGNED,
  b TINYINT UNSIGNED
);

INSERT INTO test_overflow VALUES (130, -1);
SELECT a, b FROM test_overflow; Result: `a = -126` and `b = 255` due to wrapping behavior.

How do signed and unsigned integer types affect query results, index usage, and storage size? Can you demonstrate an example where overflow behavior differs?

Signed vs Unsigned Integer Types in MySQL

How do signed and unsigned integer types affect query results, index usage, and storage size? Can you demonstrate an example where overflow behavior differs?

Signed vs Unsigned Integer Types in MySQL

Scenario Questions

Follow-up Questions