INT in MySQL is an integer data type, which is a whole number. It allows numbers to be written without fractional part (no decimal component). For example, 1, 5, -10 is an integer type, while 1.5, 5/7 cannot be an integer. It is to note that an integer value can be positive, negative, or zero. We can use this data type in various arithmetic operations such as addition (+), subtraction (-), division (/), multiplication (*) and modulo (%).
Generally, MySQL supports all SQL standard integer types named INTEGER or INT and SMALLINT. In extension to the standard integer types, it also provides TINYINT MEDIUMINT and BIGINT.
INT data type in MySQL can be both signed and unsigned. We can understand it with the following table that illustrates each integer type's characteristics, including required storage and range (minimum value, and maximum value) for each integer type.
The choice of data type we are going to use depends on the type of data we are going to store. For example, the TINYINT data type is sufficient for the number of students in a class with a maximum of 35 people. However, if we need to store everyone's contact number in a country, we must choose INT or BIGINT data type. Whenever possible, it is ideal to choose the smallest data type because it minimizes the database's size.
MySQL INT Data Type Examples
Let us understand how to work with integer data types using various examples in MySQL.
1. MySQL INT for a Column
We usually use it as the primary key of a table because the integer data type represents exact numbers. Also, we can specify an AUTO_INCREMENT attribute with this column.
If we insert a NULL or 0 into the INT AUTO_INCREMENT column, the column's value is set to the next sequence value that starts with 1. And, if we insert a non-null value into the INT AUTO_INCREMENT column, the column accepts the value and its sequence is reset to the next value of the inserted value.
Let's see with a practical example. First, create a new table named student with an integer column as the primary key and auto-increment attribute:
We can use either INT or INTEGER data type while creating a table with a CREATE TABLE statement because they are interchangeable. Since we have used the AUTO_INCREMENT attribute in the above query, whenever we insert a new row into the student table, the stud_roll_num column's value is increased by 1.
Next, execute the below INSERT statement that will inserts three rows into the student table:
Now, we need to execute the SELECT statement to query data from the student table:
mysql> SELECT * FROM student;
Again, insert a new row where we will specify the value of the stud_roll_num column explicitly:
Since the current value of the stud_roll_num column is 10, the next sequence is reset to 11. Execute the below statement where we will not specify the stud_roll_num:
Finally, query the student table's data again by using the SELECT statement to see the result.
2. MySQL INT UNSIGNED Example
An unsigned integer in MySQL is stored only positive numbers. If we try to store a negative number in an unsigned integer, it gives an error. We can understand it by creating a new table named "branches" with the column total_students, which stores unsigned integer data type.
Next, we need to insert data into this table using the below statement:
The above statement works as expected because we have not specified any negative numbers in the total_students column. See the below output:
Again, we will try to store a negative number in the total_students column:
In this case, MySQL produces the following error:
MySQL INT with the Display Width Attribute
MySQL supports an extension that optionally allows us to specify the display width along with the integer data types. The display width is wrapped inside parentheses following the base keyword for the type. For example, INT (4) specifies an integer with the display width of four digits.
It is important to note that this attribute does not control the range of value that can be stored in the column. We mainly used this attribute in the application to format the integer values. MySQL includes it as the metadata of the returned result set.
For example, if we specified the column with the SMALLINT (3), it has the value range of -32768 to 32767. And values outside the range are displayed in full by using more than three digits.
MySQL INT with the ZEROFILL Attribute
MySQL provides a non-standard ZEROFILL attribute that can be used in conjunction with the display width attribute. In this case, MySQL replaces the padding of spaces with zeros. Let us understand with the help of an example:
First, we will create a new table named numeric_tests using the below statement:
Next, we will insert the data into a table with the below statement:
Now, we will query data of the table using the SELECT statement:
We will get the result as below screenshot:
In the above output, we can see that column val1 has a display width of 2, including ZEROFILL. Here, we have inserted its value 1; therefore, we will see 01 in the output, and MySQL replaces the first space by one zero (0).
The column val2 has a display with 3, including ZEROFILL. Here, we have inserted its value 5; therefore, we will see 005 in the column, and MySQL replaces the leading spaces with two zeros.
The column val3 has the display width 5, including ZEROFILL, while we have inserted its value as 8; therefore, we will see 00008 in the column, and MySQL replaces the leading spaces with four zeros (0000) at the beginning of the number in the output.
Note: If we use the ZEROFILL attribute with the integer column, MySQL will automatically add an UNSIGNED attribute with this column.