Understanding integers: types and ranges
When it comes to databases, one of the most important aspects is understanding the different data types that can be used to store information. In this video, we'll focus specifically on integers, the different types of columns that can hold integer values, and the ranges for each.
Getting started
To demonstrate the different types of columns that can hold integer values, we'll be using a simple example: a books table. The table has an ID and a title, and we'll be defining the number of pages column. Before we can do that, however, we need to figure out the data type and any other attributes that are required.
Types of columns that hold integer values
There are five data types that can be used to store integers:
TINYINTSMALLINTMEDIUMNINTINTBIGINT
It's worth noting that any other types of integers that you come across are likely aliases, meaning that they're just different words that refer to the same data type. For example, "integer" is simply another word for "int".
Ranges for each data type
Each data type for integers has different storage requirements, which means that they're able to store different amounts of data. Here are the ranges for each data type:
TINYINT: Occupies one byte and can store values from 0 to 255 (or -128 to 127, if negative numbers are supported).SMALLINT: Occupies two bytes and can store values from 0 to 65,535 (if negative numbers aren't supported).MEDIUMNINT: Occupies three bytes and can store values from 0 to 16,777,215 (if negative numbers aren't supported).INT: Occupies four bytes and can store values from 0 to 4,294,967,295 (if negative numbers aren't supported).BIGINT: Occupies eight bytes and can store values from 0 to 18,446,744,073,709,551,615 (if negative numbers aren't supported).
Understanding negative numbers
In databases, negative numbers are supported by dedicating one of the bits to the sign (either positive or negative), with the remaining bits reserved for the value. For example, a TINYINT with negative numbers enabled has one bit dedicated to the sign and seven bits for the value, which means it can store values from -128 to 127.
Defining the number of pages column
For our books table, we'll be defining the number of pages column, which we expect will never exceed 65,000 pages. Given this, we don't need to use the INT data type, which can hold up to 4.2 billion values. Instead, we can use the SMALLINT data type, which occupies two bytes and can store values from 0 to 65,535.
However, we also need to specify that negative numbers aren't supported for this column. We can do this by adding the unsigned keyword, which tells MySQL that we're not interested in negative numbers.
Common misconception: INT(11)
It's common to see something like INT(11) and assume that the 11 controls the range of data that can be stored. However, the number in parentheses has no effect on the underlying storage or the range of the column. This notation was only ever intended to define a display width. It is deprecated and will be removed soon.
Conclusion
Understanding the different types of columns that can hold integer values and their respective ranges is crucial to designing a database schema that's both efficient and scalable. By selecting the appropriate data type for each column, we can avoid unnecessary storage requirements and ensure efficient queries.