unsigned is both non-negative, this type can increase the data length!
For example if tinyint max is 127, that tinyint unsigned max can go to 127 * 2
The unsigned property is only for integral types, whereas the binary property is used only for char and varchar.
Type |
Description |
tinyint |
A very small integer |
smallint |
Smaller integer |
Mediumint |
Medium size integer |
Int |
Standard integer |
bigint |
Larger integer |
Float |
Single-precision floating-point number |
Double |
Double-precision floating-point number |
Decimal |
A string of floating-point numbers |
The name and range of values for each numeric type are shown in table 2.
Type description |
Range of values |
Tinyint[(m)] |
Signed values: 128 to 127 (-27 to 27-1) Unsigned values: 0 to 255 (0 to 28-1) 1 bytes |
Smallint[(m)] |
Signed values: 32768 to 32767 (-215 to 215-1) Unsigned values: 0 to 65535 (0 to 21 6-1) 2 bytes |
Mediumint[(m)] |
Signed values: 8388608 to 8388607 (-22 3 to 22 3-1) Unsigned values: 0 to 16777215 (0 to 22 4-1) 3 bytes |
Int[(m)] |
Signed values: 2147683648 to 2147683647 (-231 to 231-1) Unsigned values: 0 to 4294967295 (0 to 232-1) 4 bytes |
Bigint[(m)] |
Signed values: 9223372036854775808 to 9223373036854775807 (-263 to 263-1) Unsigned values: 0 to 18446744073709551615 (0 to 264–1) 8 bytes |
Float[(M, D)] |
Minimum non 0 value: ±1.175494351e-38 |
double[(M,D)] |
Minimum non 0 value: ±2.2250738585072014e-308 |
Decimal (M, D) |
The range of values depends on M and D |
Table 2: Value ranges for numeric column types
The amount of storage required for the various types of values is shown in table 3.
Type description |
Storage requirements |
Tinyint[(m)] |
1 bytes |
Smallint[(m)] |
2 bytes |
Mediumint[(m)] |
3 bytes |
Int[(m)] |
4 bytes |
Bigint[(m)] |
8 bytes |
Float[(M, D)] |
4 bytes |
Double[(M, D)] |
8 bytes |
Decimal (M, D) |
M bytes (MySQL < 3.23), m+2 bytes (mysql > 3.23) |
Table 3: Storage requirements for numeric column types
MySQL offers five integral types: tinyint, smallint, mediumint, int, and bigint. int is an abbreviation of integer. These types are different in the range of values that can be represented. Integer columns can be defined as unsigned thereby disabling negative values, which makes the column range above 0. Various types of storage requirements are also different. A type with a large range of values requires a large amount of storage.
MySQL offers three floating-point types: float, double, and decimal. Unlike integers, floating-point types cannot be unsigned, and their range of values differs from integer types, not only because they have the largest value, but also have a minimum value of 0. The minimum value provides a measure of the corresponding type precision, which is important for recording scientific data (and, of course, negative maximum and minimum values).
unsigned in MySQL