Skip to content
1.1
Integers
1.3

title: 'Announcing the Larabelles Laracon US conference giveaway, sponsored by PlanetScale' heroImage: '/assets/blog/content/larabelles-laracon-us-2023/larabelles-laracon-planetscale_blog.png' socialImage: '/assets/blog/content/larabelles-laracon-us-2023/larabelles-laracon-planetscale_social.png' authors: [taylor] readDuration: 3 publishDate: '2023-05-25T09:00:00.000Z' snippet: 'PlanetScale is sponsoring Larabelles, which will give three folks who have been historically under-represented or marginalised due to their gender a trip to Laracon US in July.'


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:

  • TINYINT
  • SMALLINT
  • MEDIUMNINT
  • INT
  • BIGINT

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.

About this lesson

Here we cover all the different ways to store integer data: TINYINT, SMALLINT, MEDIUMINT, INT, and BIGINT. We'll cover storage requirements and when to consider each type.

06:36
Closed captioned

Meet your instructor, Aaron Francis

Developer Education

I’m a dad to one-year-old twins and a software developer with a keen interest in processes and automation.

Feedback or questions? Reach out to our team at education@planetscale.com.

By submitting your email, you agree to the processing of your personal information by PlanetScale as described in the Privacy Policy.