getButterfly Logo getButterfly code wrangling since 2005


Integers are a subset of the real numbers. They are written without a fraction or a decimal component. Integers fall within a set Z = {…, -2, -1, 0, 1, 2, …} Integers are infinite. Computers can practically work only with a subset of integer values, because computers have finite capacity. Integers are used to count discrete entities. We can have 3, 4, 6 cars, but we cannot have 3.33 cars. We can have 3.33 kilograms.

The following is a table of integer types in MySQL. TINYINT, MEDIUMINT and BIGINT are MySQL extensions to the SQL standard.

Data type Bytes Minimum value Maximum value
TINYINT 1 -128 127
SMALLINT 2 -32768 32767
MEDIUMINT 3 -8388608 8388607
INTEGER 4 -2147483648 2147483647
BIGINT 8 -9223372036854775808 9223372036854775807

The integer types differ in their storage. We can choose values that fit our requirements.


We have created a temporary Ages table. This will be only a temporary testing table, so there will be only a few rows. SMALLINT will certainly suffice. We don’t know anyone older than 120 years, so TINYINT will be OK for the Age column.

mysql> INSERT INTO Ages VALUES(1, 43);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO Ages VALUES(2, 128);
Query OK, 1 row affected, 1 warning (0.00 sec)

We insert two rows into the table. There is a warning for the second SQL statement.

| Level   | Code | Message                                      |
| Warning | 1264 | Out of range value for column 'Age' at row 1 |

We use the SHOW WARNINGS SQL statement to show the last warning message. We have tried to insert a value which is larger than the column data type allows. There is no integer overflow, as we know from the C language. In such a case, the largest allowable integer is written and a warning is issued.

When we are dealing with ages, we do not need negative integer values. MySQL supports unsigned integers. This way we can further optimize our table definitions.


We use the SQL statement to change the Age column to have a TINYINT UNSIGNED data type. Now we can insert values from 0 to 255.

mysql> INSERT INTO Ages VALUES(3, 240);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM Ages;
| Id   | Age  |
|    1 |   43 |
|    2 |  127 |
|    3 |  240 |

We have inserted a hypothetical 240. Now the column accepts it.

Floating point values

Floating point numbers represent real numbers in computing. Real numbers measure continuous quantities. Like weight, height or speed. MySQL has FLOATDOUBLE and DECIMAL floating point values.

FLOAT is a single precision floating point number. MySQL uses four bytes to store a FLOAT value. ADOUBLE is a double precision floating point number. MySQL uses eight bytes to store a DOUBLE value.DECIMAL data type is best used for financial calculations.

Floats, doubles and decimals may have specified their precision and scale. In DECIMAL[M, D] the M is the maximum number of digits, the precision. The D is the number of digits to the right of the decimal point. It is the scale. If you have a column with DECIMAL(3, 1), you can insert numbers with maximum of three digits. Two before and one after the decimal point.

mysql> SELECT 1/3;
| 1/3    |
| 0.3333 |
1 row in set (0.02 sec)

mysql> SELECT 0.3333 = 1/3;
| 0.3333 = 1/3 |
|            0 |
1 row in set (0.00 sec)

You might expect that the comparison in the second SQL statement returns true, but it does not. The reason is the way, how floating point values are stored.

Caution must be exercised when working with floating point values. Floats and doubles are faster to deal with, but they are not accurate to the last digit. There is a small rounding error, which is OK in many cases. In many real word situations, we just need to have an approximate value. For example, you have a shop in which you have 7.5321 kg of apples, 4.372 kg of oranges. It is perfectly valid to store these two values as 7.5 kg and 4.4 kg. No big deal. On the other hand, when we do exact mathematical calculations; let’s say we add some financial data or any scientific calculations, we need more precision. For such cases, we use the DECIMAL data type.

mysql> CREATE TABLE Numbers (Id TINYINT, Floats FLOAT, Decimals DECIMAL(3, 2));

We create a table, in which we are going to store a few floats and decimals.

mysql> INSERT INTO Numbers VALUES (1, 1.1, 1.1), (2, 1.1, 1.1), (3, 1.1, 1.1);

We insert three rows into the newly created table.

mysql> SELECT * FROM Numbers;
| Id   | Floats | Decimals |
|    1 |    1.1 |     1.10 |
|    2 |    1.1 |     1.10 |
|    3 |    1.1 |     1.10 |

This is how the table looks.

mysql> SELECT SUM(Floats), SUM(Decimals) FROM Numbers;
| SUM(Floats)      | SUM(Decimals) |
| 3.30000007152557 |          3.30 |

The two results differ. The decimal calculation is more precise. Due to some internal rounding, the sum of floats is not accurate.

Subscribe to getButterfly Blog

Once a week or so we send an email with our best content. We never bug you, we just send you our latest piece of content.

If you found any value in this post, agree, disagree, or have anything to add - please do. I use comments as my #1 signal for what to write about. Read our comment policy before commenting! Comments such as "Thank you!", "Awesome!", "You're the man!" are either marked as spam or stripped from URL.

Leave a reply