Course
INT
MySQL Tutorial
This SQL tutorial is structured for beginners to guide them from the foundational concepts to advanced data manipulation and querying techniques in SQL. By the end of this tutorial, you will have developed a robust understanding of SQL, equipping you with the knowledge to efficiently manage and analyze data across various database systems. This guide sets the stage for your growth into a skilled data professional, ready to tackle complex data challenges and contribute to the field of data analysis and database management.
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
PHPNodeJSJavaPython
To create a column of INT datatype through a PHP program, we need to execute the "CREATE TABLE" statement using the mysqli function query() as follows −
$sql = 'CREATE TABLE students (Id INT AUTO_INCREMENT primary key not null, reg_No INT )';$mysqli->query($sql);
Example
Following are the programs −
PHPNodeJSJavaPython
$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();
Output
The output obtained is as follows −
Table Records:ID: 1, Reg: 101ID: 2, Reg: 102