What is Unsigned in MySQL: Explanation and Usage

In MySQL, UNSIGNED is an attribute for numeric columns that allows only non-negative values by removing the ability to store negative numbers. This effectively doubles the positive range of values the column can hold compared to signed numbers.

How It Works

Think of numbers in a MySQL column like a measuring cup. Normally, the cup can hold both positive and negative amounts (signed numbers). When you mark a column as UNSIGNED, it's like saying the cup can only hold zero or positive amounts—no negatives allowed.

This means the space that would have been used to store negative numbers is now used to store larger positive numbers. For example, a regular 4-byte integer can store values from -2,147,483,648 to 2,147,483,647. But if you make it UNSIGNED, it stores values from 0 to 4,294,967,295.

This is useful when you know your data will never be negative, like counts or IDs, so you get a bigger positive range without using more storage.

Example

This example shows how to create a table with a signed and an unsigned integer column, then insert values to see the difference.

sql

CREATE TABLE example_unsigned (
  id INT SIGNED,
  count INT UNSIGNED
);

INSERT INTO example_unsigned (id, count) VALUES (-10, 10);
INSERT INTO example_unsigned (id, count) VALUES (20, 4294967295);

SELECT * FROM example_unsigned;

Output

id | count
---|------------
-10 | 10
20  | 4294967295

When to Use

Use UNSIGNED when you know a numeric column will never have negative values. Common cases include:

• IDs or keys that only increase
• Counts of items, like stock or visits
• Monetary amounts that cannot be negative

This helps prevent errors from negative inputs and allows storing larger positive numbers without extra storage.

Key Points

• UNSIGNED removes negative values from numeric columns.
• It doubles the positive range of values for that column.
• Useful for IDs, counts, and other always-positive data.
• Helps prevent accidental negative data entry.

Key Takeaways

UNSIGNED in MySQL means the column stores only zero or positive numbers.

It doubles the maximum positive value the column can hold compared to signed numbers.

Use UNSIGNED for data that should never be negative, like IDs or counts.

It helps avoid errors from negative values and optimizes storage range.