Signed vs Unsigned
Learn via video course
FreeOverview
Our number system ranges from minus infinity to plus infinity, with zero in the middle. However, programming has two types of numbers: signed and unsigned. The potential to employ negative numbers is the primary difference between a signed and an unsigned number. Unsigned numbers can only have values that are equal to or greater than zero. On the other hand, signed numbers have a more natural range that covers negative and positive numbers.
Scope
From this article, we will know about:
- Difference between signed vs. unsigned numerals. Change in range of values by fixing only 1 bit for the sign in various storage units, like in 4 bit, 8 bit, 32 bit.
- Implementation of signed vs. unsigned numerals in MySQL syntax with a few examples.
- Main points that should be kept in mind while using signed vs. unsigned variables in MySQL.
Introduction
The requirement to express numeric values with limited resources drove the creation of signed vs. unsigned numbers. When utilizing 8 bits, there are only 256 possible combinations. Any value between 0 and 255 is considered an unsigned number. On the other hand, having a signed number means you've already lost a bit for representing the sign. With 7 bits, you can only have 128 permutations. Hence an 8-bit signed number's range is -128 to 127. Using unsigned numbers was the way to go if you had limited resources, which was the case in the early days of computers.
Unsigned integers range from 0 to 4,294,967,295 or around 4 billion in 32-bit integers. The signed version ranges from –2,147,483,648 to 2,147,483,647, or around -2 billion to +2 billion. The range is the same, but the values are shifted on the number line.
Difference between Signed and Unsigned in MySQL
Signed value - Variables with signed numerals can store 0, positive, and negative numbers.
Unsigned value - Variables with unsigned numerals can store only 0 and positive numbers.
Table of the Range of Values Each Integer Type Can Store Refer
| Type | Storage (Bytes) | Min signed value | Max signed value | Min unsigned value | Max unsigned value |
|---|---|---|---|---|---|
| TINYINT | 1 | -128 | 127 | 0 | 255 |
| SMALLINT | 2 | -32768 | 32767 | 0 | 65535 |
| MEDIUMINT | 3 | -8388608 | 8388607 | 0 | 16777215 |
| INT | 4 | -2147483648 | 2147483647 | 0 | 4924967295 |
| BIGINT | 8 | -9,223,372,036,854,775,808 | 9,223,372,036,854,775,807 | 0 | 18,446,744,073,709,551,615 |
Example
- To specify unsigned id-column
Here, the variable roll_no is of unsigned numeric type. Only 0 and other positive numbers are allowed. It is not capable of storing negative values.
- To specify signed column representing temperature column
Here, the temperature variable is of a signed numeric type. All negative and positive integers and zero can be stored in the temperature variable.
Conclusion
- An unsigned number contains just zero or positive values, whereas a signed number has both positive and negative numbers along with the value zero.
- The maximum value of signed numbers is half that of unsigned numbers.
- Combining signed and unsigned numbers might cause complications.
- Whether a number is signed or unsigned has no bearing on modern applications.