Introduction

In MySQL, integer columns are signed by default. That means an INT stores both negative and positive values. UNSIGNED shifts the range upward so the same 4 bytes can store 0 through 4294967295 instead of -2147483648 through 2147483647.

The Real Question: Can the Value Ever Be Negative?

That is the main design question. If negative values are meaningful in the domain, use a signed type. If negative values should never exist and you want a larger positive range, UNSIGNED is a good fit.

Examples that usually belong in signed columns:

• temperature offsets
• account deltas that may be negative
• score adjustments
• relative counters that move above and below zero

Examples that often fit UNSIGNED:

• auto-increment identifiers
• inventory counts that cannot drop below zero
• page view counters
• age in days or seconds since an event

Basic Table Design Examples

1CREATE TABLE inventory_items (
2    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
3    sku VARCHAR(50) NOT NULL,
4    quantity INT UNSIGNED NOT NULL,
5    adjustment INT NOT NULL,
6    PRIMARY KEY (id)
7);

Here, id and quantity are naturally nonnegative. adjustment is signed because a stock change may be positive or negative.

Another example:

1CREATE TABLE ledger_entries (
2    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
3    amount_cents BIGINT NOT NULL,
4    balance_after BIGINT NOT NULL,
5    PRIMARY KEY (id)
6);

Financial deltas and balances often need signed values because refunds, chargebacks, or debt are real states. Do not force them into UNSIGNED just because you hope values stay positive.

What You Gain with UNSIGNED

For integer types, UNSIGNED does not change storage size. It changes the usable range.

For INT, the ranges are:

• signed: -2147483648 to 2147483647
• unsigned: 0 to 4294967295

So UNSIGNED is useful when the extra positive headroom matters and negative values are invalid.

This is why many schemas use INT UNSIGNED or BIGINT UNSIGNED for surrogate keys.

When Signed Is the Better Choice

Use signed columns when the sign itself carries meaning or when arithmetic should naturally produce negative results.

A common example is inventory movement versus inventory total:

• 'quantity_on_hand can be UNSIGNED'
• 'quantity_change should be signed'

That split keeps the stored state realistic while still allowing operations like -3 for a removal or +10 for a restock.

Watch Out for Arithmetic and Joins

Mixing signed and unsigned values can create subtle problems. In MySQL, arithmetic involving unsigned integers can yield surprising results, especially with subtraction rules and type promotion.

SELECT CAST(1 AS UNSIGNED) - 2;

That is the sort of expression where signedness can matter more than you expected.

Another practical issue is foreign-key compatibility and joins. If one table stores an id as INT UNSIGNED and another stores the related value as plain INT, you have introduced unnecessary friction. Keep related key columns consistent.

1CREATE TABLE users (
2    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
3    PRIMARY KEY (id)
4);
5
6CREATE TABLE orders (
7    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
8    user_id INT UNSIGNED NOT NULL,
9    PRIMARY KEY (id),
10    FOREIGN KEY (user_id) REFERENCES users(id)
11);

Matching type, width, and signedness across related columns prevents avoidable schema problems.

SIGNED Usually Adds Nothing Explicitly

Because integer types are signed by default in MySQL, writing SIGNED is often just documentation. It rarely changes behavior unless you are being explicit for clarity or for generated SQL.

In practice, the meaningful choice is usually between default signed behavior and UNSIGNED.

Common Pitfalls

The most common mistake is using UNSIGNED for any value that "should not go negative" without thinking about future requirements. Real systems change. Credits become debts, counters become deltas, and once-simple fields start representing more nuanced states.

Another mistake is mixing UNSIGNED and signed columns for the same conceptual id across tables. That can complicate joins, foreign keys, and application code.

A third issue is using UNSIGNED as a validation substitute. If the domain rule is more specific than "not negative," add a CHECK constraint or application validation instead of overloading numeric signedness with business meaning.

Summary

• Use signed integers when negative values are meaningful in the domain
• Use UNSIGNED when values must be nonnegative and the extra upper range is useful
• 'UNSIGNED changes range, not storage size, for integer columns'
• Keep related columns consistent in width and signedness, especially keys and foreign keys
• Do not use UNSIGNED as a shortcut for business validation when the rule is more specific than nonnegative