What is the difference between int and integer in MySQL?

MySQLMySQLi Database

The int is the synonym of integer in MySQL 5.0. Here is the demo display both int and integer internally represents int(11).

Creating a table with int datatype

mysql> create table IntDemo
   -> (
   -> Id int
   -> );
Query OK, 0 rows affected (1.04 sec)

Here is description of the table. The query is as follows

mysql> desc IntDemo;

The following is the output

+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| Id    | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.06 sec)

Look at the column type, which is int(11). Now it stores the same range as defined for integer. The query to insert record is as follows

mysql> insert into IntDemo values(2147483647);
Query OK, 1 row affected (0.20 sec)

mysql> insert into IntDemo values(-2147483648);
Query OK, 1 row affected (0.42 sec)

Display all records from the table using select statement. The query is as follows

mysql> select *from IntDemo;

The following is the output

+-------------+
| Id          |
+-------------+
| 2147483647  |
| -2147483648 |
+-------------+
2 rows in set (0.00 sec)

Creating a table with data type integer.

The query to create a table is as follows

mysql> create table IntegerDemo
   -> (
   -> Id integer
   -> );
Query OK, 0 rows affected (0.93 sec)

Check the description of the table using desc command.

mysql> desc IntegerDemo;

The following is the output

+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| Id    | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

Insert record in the table using insert command. The integer takes the same range as int. The query is as follows

mysql> insert into IntegerDemo values(2147483647);
Query OK, 1 row affected (0.11 sec)

mysql> insert into IntegerDemo values(-2147483648);
Query OK, 1 row affected (0.27 sec)

Display all records from the table using select statement. The query is as follows

mysql> select *from IntegerDemo;

The following is the output

+-------------+
| Id          |
+-------------+
| 2147483647  |
| -2147483648 |
+-------------+
2 rows in set (0.00 sec)
raja
Published on 16-Jan-2019 12:06:50
Advertisements