Summary: in this tutorial, you will learn about MySQL INT or integer data type and how to use it in your database table design. In addition, we will show you how to use the display width and ZEROFILL attributes of an integer column in a table.
Introduction to MySQL INT type
INT stands for the integer that is a whole number. An integer can be written without a fractional component e.g., it is 1, 100, 4, -10, etc., and it cannot be 1.2, 5/3, etc. An integer can be zero, positive, and negative.
MySQL supports all standard SQL integer types
SMALLINT. In addition, MySQL provides
BIGINT as extensions to the standard SQL.
MySQL INT data type can be signed and unsigned. The following table illustrates the characteristics of each integer type including storage in bytes, minimum value, and maximum value.
|Type||Storage||Minimum Value||Maximum Value|
Using INT in a column
Because integer type represents exact numbers, you usually use it as the primary key of a table. In addition, the
INT column can have an
When you insert a value, which is not
NULL or zero, into the
AUTO_INCREMENT column, the column accepts the value. In addition, the sequence is reset to next value of the inserted value.
Let’s take a look at an example of a table that uses an integer column with the
First, create a new table named
items with an integer column as the primary key using the following statement:
CREATE TABLE items (
item_id INT AUTO_INCREMENT PRIMARY KEY,
you can use either
INTEGER in the CREATE TABLE statement above because they are interchangeable. Whenever you insert a new row into the
items table, the value of the
item_id column is increased by 1.
Next, the following INSERT statement inserts three rows into the
INSERT INTO items(item_text)
Then, query data from the
items table using the following SELECT statement:
After that, insert a new row whose value of the
item_id column is specified explicitly.
INSERT INTO items(item_id,item_text)
Because the current value of the
item_id column is 10, the sequence is reset to 11. If you insert a new row, the
AUTO_INCREMENT column will use 11 as the next value.
INSERT INTO items(item_text)
Finally, query the data of the
items table again to see the result.
Note that since MySQL 5.1, the
AUTO_INCREMENT column only accepts positive values. Negative values are not supported for the
MySQL INT and display width attribute
MySQL provides an extension that allows you to specify the display width along with the
INT data type. The display width is wrapped inside parentheses following the
INT keyword e.g.,
INT(5) specifies an
INT with the display width of five digits.
It is important to note that the display width attribute does not control the value ranges that the column can store. The display width attribute is typically used by the applications to format the integer values. MySQL includes the display width attribute as the metadata of the returned result set.
MySQL INT with ZEROFILL attribute
In addition to the display width, MySQL provides a non-standard
ZEROFILL attribute. In this case, MySQL replaces the spaces with zero. Consider the following example.
First, create a table named
zerofill_tests using the following statement:
CREATE TABLE zerofill_tests(
id INT AUTO_INCREMENT PRIMARY KEY,
v1 INT(2) ZEROFILL,
v2 INT(3) ZEROFILL,
v3 INT(5) ZEROFILL
Second, insert a new row into the
INSERT into zerofill_tests(v1,v2,v3)
Third, query data from the
v1, v2, v3
The v1 column has a display width 2 including
ZEROFILL.Its value is 1 therefore, you see
01 in the output. MySQL replaces the first space by 0.
The v2 column has a display with 3 including
ZEROFILL. Its value is 6 therefore, you see
00 as the leading zeros.
The v3 column has the display width 5 with
ZEROFILL, while its value is 9, therefore MySQL pads
0000 at the beginning of the number in the output.
Note that if you use
ZEROFILL attribute for an integer column, MySQL will automatically add an
UNSIGNED attribute to the column.
In this tutorial, we have shown you how to use MySQL INT data type in the table and also introduced you to the display width and
ZEROFILL attributes of an integer column.