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.

mysql> CREATE TABLE Ages(Id SMALLINT, Age TINYINT) ENGINE=Memory;

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.

mysql> SHOW WARNINGS; +---------+------+----------------------------------------------+ | 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.

mysql> ALTER TABLE Ages MODIFY Age TINYINT UNSIGNED;

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 numbers represent real numbers in computing. Real numbers measure continuous quantities. Like weight, height or speed. MySQL has `FLOAT`

, `DOUBLE`

and `DECIMAL`

floating point values.

A `FLOAT`

is a single precision floating point number. MySQL uses four bytes to store a `FLOAT`

value. A`DOUBLE`

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.

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.