MySQL INT Data Type
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.
Introduction to MySQL INT
type
In MySQL, INT
stands for the integer that is a whole number. An integer can be written without a fractional component e.g., 1, 100, 4, -10, and it cannot be 1.2, 5/3, etc. An integer can be zero, positive, and negative.
MySQL supports all standard SQL integer types INTEGER
or INT
and SMALLINT
. In addition, MySQL provides TINYINT
MEDIUMINT
, and BIGINT
as extensions to the SQL standard.
MySQL INT
the 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 |
---|---|---|---|
(Bytes) | (Signed/Unsigned) | (Signed/Unsigned) | |
TINYINT | 1 | -128 | 127 |
0 | 255 | ||
SMALLINT | 2 | -32768 | 32767 |
0 | 65535 | ||
MEDIUMINT | 3 | -8388608 | 8388607 |
0 | 16777215 | ||
INT | 4 | -2147483648 | 2147483647 |
0 | 4294967295 | ||
BIGINT | 8 | -9223372036854775808 | 9223372036854775807 |
0 | 18446744073709551615 |
MySQL INT
data type examples
Let’s look at some examples of using integer data types.
A) Using MySQL INT
for a column example
Because integer type represents exact numbers, you usually use it as the primary key of a table. In addition, the INT
the column can have an AUTO_INCREMENT
attribute.
When you insert a NULL
value or 0 into the INT AUTO_INCREMENT
column, the value of the column is set to the next sequence value. Notice that the sequence value starts with 1.
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 the next value of the inserted value.
First, create a new table named items
with an integer column as the primary key:
CREATE TABLE items (
item_id INT AUTO_INCREMENT PRIMARY KEY,
item_text VARCHAR(255)
);
You can use either INT
or INTEGER
in the CREATE TABLE
the statement above because they are interchangeable. Whenever you insert a new row into the items
table, the value of the item_id
the column is increased by 1.
Next, the following INSERT
statement inserts three rows into the items
table.
INSERT INTO
items(item_text)
VALUES
('laptop'),
('mouse'),
('headphone');
Then, query data from the items
table using the following SELECT
statement:
SELECT * FROM items;
After that, insert a new row whose value of the item_id
the column is specified explicitly.
INSERT INTO items(item_id,item_text)
VALUES(10,'Server');
Since the current value of the item_id
the column is 10, the sequence is reset to 11. If you insert a new row, the AUTO_INCREMENT
the column will use 11 as the next value.
INSERT INTO items(item_text)
VALUES('Router');
Finally, query the data of the items
table again to see the result.
SELECT * FROM items;
Note that starting from MySQL 5.1, the AUTO_INCREMENT
column accepts only positive values and does not allow negative values.
B) Using MySQL INT UNSIGNED
example
First, create a table called classes
that has the column total_member
with the unsigned integer data type:
CREATE TABLE classes (
class_id INT AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
total_member INT UNSIGNED,
PRIMARY KEY (class_id)
);
Second, insert a new row into the classes
table:
INSERT INTO classes(name, total_member)
VALUES('Weekend',100);
It worked as expected.
Third, attempt to insert a negative value into the total_member
column:
INSERT INTO classes(name, total_member)
VALUES('Fly',-50);
MySQL issued the following error:
Error Code: 1264. Out of range value for column 'total_member' at row 1
MySQL INT
with the display width attribute
MySQL provides an extension that allows you to specify the display width along with the INT
datatype. 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 the ZEROFILL
attribute
In addition to the display width attribute, MySQL provides a non-standard ZEROFILL
attribute. In this case, MySQL replaces spaces with zero. Consider the following example.
First, create a table named zerofill_tests
:
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 zerofill_tests
table.
INSERT INTO zerofill_tests(v1,v2,v3)
VALUES(1,6,9);
Third, query data from the zerofill_tests
table.
SELECT
v1, v2, v3
FROM
zerofill_tests;
The v1
the column has a display width of 2 including ZEROFILL.
Its value is 1 therefore, you see 01
it 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
it as the leading zeros.
The v3 column has a display width of 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, you have learned how to use the MySQL INT
the data type for designing database tables.
0 Comments
CAN FEEDBACK
Emoji