Everything You Need to Know About MySQL SMALLINT

In this tutorial, we will learn everything about the SMALLINT datatype in MySQL. It is similar to the integer datatype but has a smaller range. So, let’s get started!

Also read: MySQL INT Datatype

Brushing Up the Basics

MySQL provides us with multiple versions of integer datatype such as TINYINT, SMALLINT, MEDIUMINT, INT, and BIGINT. This helps the developers to save excess space in the storage device and faster processing of the data.

The question that might be raised here is why we need this many types of Integer datatype, why can’t we just use the integer datatype in the application?

Let’s take an example.

Suppose you want to store the roles in the table. There can be a maximum of 5 roles. In this case, it’s best to use the TINYINT than the INT because our values will not go out of range of the TINYINT.

To store the value 1, if we use the INT rather than TINYINT, it will use still use 4 bytes instead of 1 byte. That’s a waste of storage space, right?

Similarly, we can use SMALLINT, MEDIUMINT, INT, and BIGINT according to our application needs.

Now let’s talk about the SMALLINT data type.

Introduction to SMALLINT

The SMALLINT data type has a range larger than the TINYINT but smaller than MEDIUMINT. It can store the signed values from -32768 to 32767. Whereas, the maximum unsigned value that can be stored in the SMALLINT type is 65535.

Note that, if you define a column type as an unsigned SMALLINT, it will not allow you to store values less than 0. The smallest value that it can store is zero. Whereas, signed SMALLINT can store negative values up to -32768.

MySQL SMALLINT Usecases

As said earlier, the INT type is used to store the numbers. However, it is not a good solution to use the INT type to store the numerical values.

For example, suppose you want to store phone numbers. In this case, BIGINT can store the numbers. However, it is not the right way to store them. Because you can’t store the phone numbers starting with the digit 0, you can’t use the plus (+) sign or hyphen (-) as well.

Now, you can use the SMALLINT datatype to store the dates and times.

Moreover, suppose you want to store the product categories in the table. In that case, the number of categories might exceed the count of one thousand but obviously will be less than ten thousand. In this case, we can use SMALLINT to store the ids of the product categories.

MySQL SMALLINT Examples

The syntax of specifying the SMALLINT type is exactly the same as the INT. Just replace the INT with the SMALLINT. That’s it.

Let’s take some examples.

Here, we will create two columns in the table. One will have the SIGNED SMALLINT and another will have the UNSIGNED SMALLINT.

CREATE TABLE smallIntDemo( 
usi SMALLINT UNSIGNED, 
si SMALLINT );Code language: SQL (Structured Query Language) (sql)

Here, we have created the first column as an unsigned SMALLINT and the second column as a signed SMALLINT. Note that, if you don’t mention the signedness of the column explicitly, MySQL will consider it as a signed type.

Now let’s insert some values into it.

INSERT INTO smallIntDemo VALUES
(20,-100),(4300,-4000);Code language: SQL (Structured Query Language) (sql)

Inserting Out of Range Values in SMALLINT

Now let’s try inserting values out of the range of the SMALLINT. It should give us the “value out of range” error.

Note that, the first column is of unsigned type. Therefore, it will not accept values less than 0 and greater than 65535. Whereas, the second column is of signed type and will accept the values from -32768 to 32767.

INSERT INTO smallIntDemo VALUES (-100,200);
INSERT INTO smallIntDemo VALUES (100, 35000);Code language: SQL (Structured Query Language) (sql)

Here, the first statement will try to insert a negative value in the unsigned SMALLINT column. On the other hand, the second statement will try to insert a value greater than 32767. We should get an error both times.

As you can see, both statement returns an error as “out of range value”.

Wrapping Up

In this tutorial, we have learned about the SMALLINT data type. We have seen some information about the INT data type and different types of the INT type. We have also seen why we need the different varieties of the INT datatype in the application. I hope you have understood everything we tried to explain above.