Numeric Data type in MySQL
Choosing the right data type can create significant improvement in application performance.
Many programmers/developers who opt to choose MySQL as the backend for their operations often fail miserably in choosing the most efficient data types.
MySQL Numeric Data type:
| Type | Bytes | Minimum | Maximum |
| (Signed/Unsigned) | Signed/Unsigned) | ||
TINYINT |
1 | -128 |
127 |
0 |
255 |
||
SMALLINT |
2 | -32768 |
32767 |
0 |
65535 |
||
MEDIUMINT |
3 | -8388608 |
8388607 |
0 |
16777215 |
||
INT |
4 | -2147483648 |
2147483647 |
0 |
4294967295 |
||
BIGINT |
8 | -9223372036854775808 |
9223372036854775807 |
0 |
18446744073709551615 |
To explain the above table a bit more, let us take the first row where it gives the values for “Tinyint” data type. It states “Bytes” = 1 which means TINYINT data type has 1 Byte which is equal to 8 bits (n = 8). Maximum signed value for TINYINT is given to be 127 and the calculation for it is [2^(n-1) – 1] = [2^(8-1) – 1]
To test the maximum value for TINYINT data type in MySQL try creating a table with a column whose data type is TINYINT. Then try inserting into TINYINT field the following values 126, 127 and 128 to know the difference.
On inserting the value 128 into TINYINT field you will get the following warning message and the value resets to 127
Warning: #1264 Out of range value adjusted for column ‘mytinyintcolumn’ at row 1
When declaring the table, we assign the value TINYINT(2). Some may not understand what that “2” is for. This is the value for padding to the left during display. Set the attribute to ZEROFILL and then try testing with different combinatios for “value” field and you will be able to notice the way the display is affected with different left-padding settings.
