MySQL INT Datatype

In this tutorial, we will learn about the INT or integer datatype, its different types, and how to use it in real-time applications while creating a database table. Also, we will see some useful functions to play with the integer datatype.

Introduction to MySQL INT

INT stands for integer type of data. It indicates the value must the whole number and it should be written without any fractional component. For example- 1,10,-40,210 are the integer values. Whereas, 1.2, and 4/5 are non-integer values.

The value of an integer can be zero, positive or negative too.

MySQL provides an array of different integer value data types such as INTEGER or INT, SMALLINT, BIGINT, MEDIUMINT, and TINYINT.

All the data types above mentioned can hold positive as well as negative values. The positive values are called unsigned values because any numeric value is by default positive. However, negative values are called signed values as they have negative signs.

Storage Size and Range of Integer Datatype

The following table shows the storage capacity of each standard of integer datatype in bytes and the range of values they can hold, i.e., minimum value and maximum value.

Note that, the minimum value that can an integer datatype hold for an unsigned type is always a zero.

MySQL INT Examples

Let’s now take a look at some examples of the INT datatype for a column.

Using INT datatype as a column ID

The first and widely used application of the integer data type is the auto_incremented value or a primary key.

Without any trouble, you can create auto_incremented values as well as a primary key using int datatype. As you set it as a primary key, it won’t accept a NULL value either. So, it becomes only a single line code to create an auto_increment value, primary key as well as a non-null attribute using an integer data type.

Now let’s see how you can achieve this.

CREATE TABLE intDemo(
id INT AUTO_INCREMENT PRIMARY KEY,
item_name VARCHAR(100)
);Code language: SQL (Structured Query Language) (sql)

Here as you can see, it took us only a single line to create a primary key, auto-incrementing value as well as a non-null value.

Note that, you must not insert value manually into the int column of the recently created table. Because it automatically starts its count from 1 and increments by 1.

But, if you try to insert some value manually using the insert statement, the auto_increment sequence will start from the last value you inserted. So, if you insert any value by mistake, it will continue to increment a new value from the last value you have inserted.

Let’s see it by performing the operations.

INSERT INTO intDemo(item_name)VALUES
("laptop"),("bottle");

SELECT * FROM intDemo;Code language: SQL (Structured Query Language) (sql)

INSERT INTO intDemo(id, item_name)VALUES(101,"bag");
INSERT INTO intDemo(item_name)VALUES("notebook");

SELECT * FROM intDemo;Code language: SQL (Structured Query Language) (sql)

As you can see, the count resets and starts from the last value you inserted.

MySQL UNSIGNED INT

MySQL UNSIGNED INT values are always positive and you must specify them while creating a table column.

Let’s see how you can specify an attribute to hold an unsigned integer value.

CREATE TABLE intDemo(
id INT UNSIGNED ,  
name VARCHAR(100) );

INSERT INTO intDemo(id,name)VALUES
(-100,"Bob");Code language: SQL (Structured Query Language) (sql)

MySQL INT With the ZEROFILL Attribute

If you try to insert an integer value something like 001 in the MySQL table, it will automatically trim the left zero and insert only the final value.

However, sometimes you don’t want the value to be trimmed and want to save it as it is at the time of insertion.

You can achieve this using a ZEROFILL attribute. But, the ZEROFILL attribute will fill all the left spaces by zero and you can not control how many zeros.

However, you can specify the length of an integer column while creating a table so that you will get a fixed length of value as a final result after filling zeros in the left.

Let’s take an example to understand it better.

CREATE TABLE intDemo(
num1 INT(2) ZEROFILL,
num2 INT(3) ZEROFILL,
num3 INT(4) ZEROFILL
);

INSERT INTO intDemo VALUES(1,2,3);Code language: SQL (Structured Query Language) (sql)

Here, the length of an integer value will remain fixed that you have specified. The attribute will only fill the remaining spaces by zero and insert it into the table.

As you can see, we have received the expected output.

Conclusion

In this tutorial, we have learned about MySQL integer datatype. We have learned there are multiple standards of the integer datatype and you can use different integer types based on your applications. However, the INT type is always an ideal choice for everyone but not necessary should be yours.