MySQL - INT

The MySQL INT Data Type

The MySQL INT data type is used to store whole numbers without the decimal places (integers).

However, MySQL provides various integer data types, such as TINYINT, SMALLINT, MEDIUMINT, and BIGINT to cater to different ranges of whole numbers.

The following table illustrates the characteristics of different integer types in MySQL, including storage in bytes, minimum value, and maximum value for both signed and unsigned options −

We have to choose the data types based on the kind (type) of data being stored. If possible, we need to use smaller data types to minimize the database size. TINYINT can be used for small numbers, while INT or BIGINT is used for large numbers like phone numbers in a country.

Auto Increment with MySQL INT

In MySQL, you can use the AUTO_INCREMENT attribute with an INT column to automatically generate unique values for that column. Here's how it works −

• Initial Value − When you create a table with an AUTO_INCREMENT INT column, the sequence starts with 1.

• Inserting NULL or 0 − When you insert a record with a NULL or 0 value for the AUTO_INCREMENT column, MySQL sets the value to the next sequence value. This means it assigns the next available integer starting from 1.

• Inserting Non-NULL Values − If you insert a non-NULL value into the AUTO_INCREMENT column, MySQL accepts that value and continues the sequence based on the new value inserted.

Example

First of all, we are creating a table named STUDENTS with an AUTO_INCREMENT INT column named "ID" −

CREATE TABLE STUDENTS (
   ID int auto_increment,
   NAME varchar(20),
   primary key (ID)
);

When we insert records into this table without specifying values for the "ID" column, MySQL automatically generates unique values for "ID" starting from 1.

Here, we are inserting three rows into the STUDENTS table using the below INSERT query −

INSERT INTO STUDENTS (NAME) VALUES 
('Tilak'), ('Akash'), ('Surya'), ('Deepak');

The STUDENTS table created is as follows −

Now, let us insert a row where we provide an explicit value for the "ID" column −

INSERT INTO STUDENTS (ID, NAME) VALUES (15, 'Arjun');

Following is the output obtained −

Query OK, 1 row affected (0.01 sec)

Since we specified the "ID" as 15, MySQL resets the sequence to 16. If we insert a new row without specifying the "ID," MySQL will use 16 as the next AUTO_INCREMENT value −

INSERT INTO STUDENTS (NAME) VALUES ('Dev');

The output obtained is as follows −

Query OK, 1 row affected (0.01 sec)

Now, let us retrieve the records from the "STUDENTS" table −

SELECT * FROM STUDENTS;

The table produced is −

From MySQL 5.1 version and onwards, the AUTO_INCREMENT column accepts only positive values and does not allow negative values.

MySQL INT UNSIGNED

In MySQL, when you define an UNSIGNED INT on a column, that column is restricted to storing only non-negative values (i.e., positive values). Negative values are not allowed in such columns.

Example

Let us create a table with the name EMPLOYEES using the following query −

CREATE TABLE EMPLOYEES (
   ID int auto_increment,
   NAME varchar(30) not null,
   AGE int UNSIGNED,
   Primary key(ID)
);

Following is the output obtained −

Query OK, 0 rows affected (0.04 sec)

Now, let us insert a row with a non-negative value into the "AGE" column −

INSERT INTO EMPLOYEES (NAME, AGE) VALUES ('Varun', 32);

The above query will execute successfully since the value provided for the "AGE" column is non-negative.

Query OK, 1 row affected (0.01 sec)

However, if we attempt to insert a negative value into the "AGE" column, MySQL will generate an error −

INSERT INTO EMPLOYEES (NAME, AGE) VALUES ('Sunil', -10);

MySQL will issue an error as shown below −

ERROR 1264 (22003): Out of range value for column 'AGE' at row 1

MySQL INT with Display Width Attribute

In MySQL, you can specify a display width for the INT data type by using parentheses after the INT keyword. For instance, using INT(5) sets the display width to five digits.

It's important to note that the display width attribute for INT in MySQL doesn't affect the range of values that can be stored in the column. It formats integer values in applications, and is included as metadata in the result set.

For example, if you insert the value 12345 into the id column of the EMPLOYEES table, it will be stored as is. When you retrieve it, some applications may choose to pad it with leading zeros to ensure it is displayed as five digits (e.g., 012345).

MySQL INT with ZEROFILL Attribute

In MySQL, the ZEROFILL attribute is a non-standard attribute that can be applied to numeric data types. It adds leading zeros to the displayed values, making sure the number is displayed with a fixed width, especially useful for numerical codes.

Example

Let us create a table with the name ZEROFILL_TABLE with ZEROFILL applied to INT columns using the query below −

CREATE TABLE ZEROFILL_TABLE (
   col1 int(4) ZEROFILL,
   col2 int(6) ZEROFILL,
   col3 int(8) ZEROFILL
);

The output obtained is as follows −

Query OK, 0 rows affected, 6 warnings (0.02 sec)

Now, we are inserting a new row into the above-created table −

INSERT INTO ZEROFILL_TABLE (col1, col2, col3) 
VALUES (1, 7, 3);

Following is the output of the above code −

Query OK, 1 row affected (0.00 sec)

Now, let us display the records from the ZEROFILL_TABLE table −

SELECT * FROM ZEROFILL_TABLE;

We can see in the output below, the values are displayed with the specified width, and leading zeros are added to maintain that width, as determined by the ZEROFILL attribute −

INT Datatype Using a Client Program

We can also create column of the INT datatype using the client program.

Syntax

$sql = 'CREATE TABLE students (Id INT AUTO_INCREMENT primary key not null, reg_No INT )';
$mysqli->query($sql);

sql = "CREATE TABLE students (Id INT AUTO_INCREMENT primary key not null, reg_No INT )";
con.query(sql);

String sql = "CREATE TABLE students(Id INT AUTO_INCREMENT primary key not null, reg_No INT)";
statement.execute(sql);

sql = 'CREATE TABLE students (ID int auto_increment, NAME varchar(20), primary key (ID) )'    
cursorObj.execute(sql)

Example

Following are the programs −

$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$dbname = 'TUTORIALS';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if ($mysqli->connect_errno) {
   printf("Connect failed: %s
", $mysqli->connect_error);
   exit();
}
//printf('Connected successfully.
');    
//create table with boolean column    
$sql = 'CREATE TABLE students (Id INT AUTO_INCREMENT primary key not null, reg_No INT )';
$result = $mysqli->query($sql);
if ($result) {
   printf("Table created successfully...!\n");
}
//insert data into created table 
$q = "INSERT INTO students (reg_No) VALUES (101), (102)";
if ($res = $mysqli->query($q)) {
   printf("Data inserted successfully...!\n");
}
//now display the table records    
$s = "SELECT Id, reg_No FROM students";
if ($r = $mysqli->query($s)) {
   printf("Table Records: \n");
   while ($row = $r->fetch_assoc()) {
      printf(" ID: %d, Reg: %s", $row["Id"], $row["reg_No"]);
      printf("\n");
   }
} else {
   printf('Failed');
}
$mysqli->close();

Table Records:
ID: 1, Reg: 101
ID: 2, Reg: 102         

var mysql = require("mysql2");
var con = mysql.createConnection({
   host: "localhost",
   user: "root",
   password: "password",
}); //Connecting to MySQL

con.connect(function (err) {
   if (err) throw err;
   //console.log("Connected successfully...!");
   //console.log("--------------------------");
   sql = "USE TUTORIALS";
   con.query(sql);

   //create a students table, that accepts one column of int type.
   sql = "CREATE TABLE students (Id INT AUTO_INCREMENT primary key not null, reg_No INT )";
   con.query(sql);

   //insert data into created table
   sql = "INSERT INTO students (reg_No) VALUES (101), (102)";
   con.query(sql);

   //select datatypes of salary
   sql = `SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'students' AND COLUMN_NAME = 'reg_No'`;
   con.query(sql, function (err, result) {
      if (err) throw err;
      console.log(result);
   });
});  

[ { DATA_TYPE: 'int' } ]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class Int {
   public static void main(String[] args) {
      String url = "jdbc:mysql://localhost:3306/TUTORIALS";
      String username = "root";
      String password = "password";
      try {
         Class.forName("com.mysql.cj.jdbc.Driver");
         Connection connection = DriverManager.getConnection(url, username, password);
         Statement statement = connection.createStatement();
         System.out.println("Connected successfully...!");

         //Int data types...!;
         String sql = "CREATE TABLE students(Id INT AUTO_INCREMENT primary key not null, reg_No INT)";
         statement.execute(sql);
         System.out.println("column of a INT type created successfully...!");
         ResultSet resultSet = statement.executeQuery("DESCRIBE students");
         while (resultSet.next()){
            System.out.println(resultSet.getString(1)+" "+resultSet.getString(2));
         }
         connection.close();
      } catch (Exception e) {
         System.out.println(e);
      }
   }
}  

Connected successfully...!
column of a INT type created successfully...!
Id int
reg_No int  

import mysql.connector
# Establishing the connection
connection = mysql.connector.connect(
   host='localhost',
   user='root',
   password='password',
   database='tut'
)
# Creating a cursor object
cursorObj = connection.cursor()
# Create table with int column
sql = '''CREATE TABLE students (
ID int auto_increment,
NAME varchar(20),
primary key (ID)
)'''
cursorObj.execute(sql)
print("The table is created successfully!")
# Data to be inserted
data_to_insert = [
    (1, 'Tilak'),
    (2, 'Akash'),
    (3, 'Surya'),
    (4, 'Deepak'),
    (15, 'Arjun'),
    (16, 'Dev')
]
# Insert data into the created table
insert_query = "INSERT INTO STUDENTS (ID, NAME) VALUES (%s, %s)"
cursorObj.executemany(insert_query, data_to_insert)
# Commit the changes after the insert operation
connection.commit()
print("Rows inserted successfully.")
# Now display the table records
select_query = "SELECT * FROM Students"
cursorObj.execute(select_query)
result = cursorObj.fetchall()
print("Table Data:")
for row in result:
    print(row)
cursorObj.close()
connection.close()  

The table is created successfully!
Rows inserted successfully.
Table Data:
(1, 'Tilak')
(2, 'Akash')
(3, 'Surya')
(4, 'Deepak')
(15, 'Arjun')
(16, 'Dev')