{"id":4010,"date":"2014-11-01T18:38:50","date_gmt":"2014-11-02T02:38:50","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=4010"},"modified":"2024-01-29T20:14:21","modified_gmt":"2024-01-30T03:14:21","slug":"mysql-int","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-int\/","title":{"rendered":"MySQL INT Data Type"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn about MySQL INT<\/code> data type, and how to use it to store whole numbers in the databases.<\/p>\n\n\n\n

Introduction to MySQL INT data type<\/h2>\n\n\n\n

In MySQL, INT<\/code> stands for the integer that represents the whole numbers. An integer can be written without a fractional component such as 1, 100, 4, -10, and it cannot be 1.2, 5\/3, etc. An integer can be zero, positive, and negative.<\/p>\n\n\n\n

MySQL supports all standard SQL integer types INTEGER<\/code> or INT<\/code> and SMALLINT<\/code>. Additionally, MySQL provides\u00a0TINYINT<\/code> MEDIUMINT<\/code>, and BIGINT<\/code> as extensions to the SQL standard.<\/p>\n\n\n\n

MySQL INT<\/code> data type<\/a> can be signed and unsigned. The following table illustrates the characteristics of each integer type including storage in bytes, minimum value, and maximum value.<\/p>\n\n\n\n

Type<\/th>Storage<\/th>Minimum Value<\/th>Maximum Value<\/th><\/tr>
<\/th>(Bytes)<\/th>(Signed\/Unsigned)<\/th>(Signed\/Unsigned)<\/th><\/tr><\/thead>
TINYINT<\/td>1<\/td>-128<\/td>127<\/td><\/tr>
<\/td><\/td>0<\/td>255<\/td><\/tr>
SMALLINT<\/td>2<\/td>-32768<\/td>32767<\/td><\/tr>
<\/td><\/td>0<\/td>65535<\/td><\/tr>
MEDIUMINT<\/td>3<\/td>-8388608<\/td>8388607<\/td><\/tr>
<\/td><\/td>0<\/td>16777215<\/td><\/tr>
INT<\/td>4<\/td>-2147483648<\/td>2147483647<\/td><\/tr>
<\/td><\/td>0<\/td>4294967295<\/td><\/tr>
BIGINT<\/td>8<\/td>-9223372036854775808<\/td>9223372036854775807<\/td><\/tr>
<\/td><\/td>0<\/td>18446744073709551615<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

MySQL INT data type examples<\/h2>\n\n\n\n

Let’s look at some examples of using the integer data type.<\/p>\n\n\n\n

1) Using INT for a column example<\/h3>\n\n\n\n

Because integer type represents exact numbers, you usually use it as the primary key <\/a>of a table. In addition, the INT<\/code> column can have an\u00a0AUTO_INCREMENT<\/a><\/code> attribute.<\/p>\n\n\n\n

When you insert<\/a> a NULL<\/code> value or 0 into the INT AUTO_INCREMENT<\/code> column, the value of the column is set to the next sequence<\/a> value. Notice that the sequence value starts with 1.<\/p>\n\n\n\n

When you insert a value, which is not NULL<\/code> or zero, into the AUTO_INCREMENT<\/code> column, the column accepts the value. In addition, the sequence is reset to the next value of the inserted value.<\/p>\n\n\n\n

First, create a new table<\/a> named items<\/code> with an integer column as the primary key<\/a>:<\/p>\n\n\n

CREATE<\/span> TABLE<\/span> items (\n    item_id INT<\/span> AUTO_INCREMENT PRIMARY KEY<\/span>,\n    item_text VARCHAR<\/span>(255<\/span>)\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Next, the following INSERT<\/a><\/code> statement inserts three rows<\/a> into the items<\/code> table.<\/p>\n\n\n
INSERT<\/span> INTO<\/span> \n    items(item_text)\nVALUES<\/span>\n    ('laptop'<\/span>), \n    ('mouse'<\/span>),\n    ('headphone'<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
SELECT<\/span> * FROM<\/span> items;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
+---------+-----------+\r\n| item_id | item_text |\r\n+---------+-----------+\r\n|       1<\/span> | laptop    |\r\n|       2<\/span> | mouse     |\r\n|       3<\/span> | headphone |\r\n+---------+-----------+\r\n3<\/span> rows in<\/span> set<\/span> (0.00 sec)<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
After that, insert a new row whose value of the item_id<\/code> column is specified explicitly.<\/p>\n\n\n
INSERT<\/span> INTO<\/span> items(item_id,item_text)\nVALUES<\/span>(10<\/span>,'Server'<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Since the current value of the item_id<\/code> column is 10, the sequence is reset to 11. If you insert a new row, the AUTO_INCREMENT<\/code> column will use 11 as the next value.<\/p>\n\n\n
INSERT<\/span> INTO<\/span> items(item_text)\nVALUES<\/span>('Router'<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Finally, query the data of the items<\/code> table again to see the result.<\/p>\n\n\n
SELECT<\/span> * FROM<\/span> items;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
+---------+-----------+\r\n| item_id | item_text |\r\n+---------+-----------+\r\n|       1<\/span> | laptop    |\r\n|       2<\/span> | mouse     |\r\n|       3<\/span> | headphone |\r\n|      10<\/span> | Server    |\r\n|      11<\/span> | Router    |\r\n+---------+-----------+\r\n5<\/span> rows in<\/span> set<\/span> (0.00 sec)<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
2) Using INT UNSIGNED example<\/h3>\n\n\n\n
First, create a table<\/a> called classes<\/code> that has the column total_member<\/code> with the unsigned integer data type:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> classes (\n    class_id INT<\/span> AUTO_INCREMENT,\n    name<\/span> VARCHAR<\/span>(255<\/span>) NOT<\/span> NULL<\/span>,\n    total_member INT<\/span> UNSIGNED<\/span>,\n    PRIMARY KEY<\/span> (class_id)\n);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
INSERT<\/span> INTO<\/span> classes(name<\/span>, total_member)\nVALUES<\/span>('Weekend'<\/span>,100<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
It worked as expected.<\/p>\n\n\n\n
Third, attempt to insert a negative value into the total_member<\/code> column:<\/p>\n\n\n
INSERT<\/span> INTO<\/span> classes(name<\/span>, total_member)\nVALUES<\/span>('Fly'<\/span>,-50<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
MySQL issued the following error:<\/p>\n\n\n
Error Code: 1264. Out of range value for column 'total_member' at row 1<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Summary<\/h2>\n\n\n\n
    \n
  • INT <\/code>represents the integer type.<\/li>\n\n\n\n
  • MySQL offers various variants of the INT<\/code> type including TINYINT<\/code>, SMALLINT<\/code>, MEDIUMINT<\/code>, and BIGINT<\/code>.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful? <\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t
Note that the display width has been deprecated. Additionally, the ZEROFILL attribute has also been deprecated, and the suggested alternatives are to use LPAD<\/a> for zero-padding numbers or to store the formatted numbers in a CHAR<\/a> column.<\/p>\n\n\n\n
Second, insert a new row<\/a> into the classes<\/code> table:<\/p>\n\n\n
Then, query data from the items<\/code> table using the following SELECT<\/a><\/code> statement:<\/p>\n\n\n
You can use either INT<\/code> or INTEGER<\/code> in the CREATE TABLE<\/code><\/a> statement above because they are interchangeable. Whenever you insert a new row into the items<\/code> table, the value of the item_id<\/code> column is increased by 1.<\/p>\n\n\n\n