Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Is there a difference in using INT(1) vs TINYINT(1) in MySQL?
The number 1 used in parenthesis is only for width display. The INT(1) and TINYINT(1) does not influence the storage.
The TINYINT takes 1 byte that means it has range -128 to +127 while int takes 4 bytes; it has range -2147483648 to +2147483647
To understand the width display, let us create a table −
mysql> create table intAndTinyint −> ( −> FirstNumber int(1) zerofill, −> SecondNumber tinyint(1) zerofill −> ); Query OK, 0 rows affected (0.52 sec)
Now you can insert records in the table. The query is as follows −
mysql> insert into intAndTinyint values(1,1); Query OK, 1 row affected (0.32 sec) mysql> insert into intAndTinyint values(12,12); Query OK, 1 row affected (0.26 sec) mysql> insert into intAndTinyint values(123,123); Query OK, 1 row affected (0.14 sec)
Display all records from the table with select statement. The query is as follows −
mysql> select *from intAndTinyint;
The following is the output −
+-------------+--------------+ | FirstNumber | SecondNumber | +-------------+--------------+ | 1 | 1 | | 12 | 12 | | 123 | 123 | +-------------+--------------+ 3 rows in set (0.00 sec)
You will understand this when number 1 of parenthesis will be increased to more than 1 with zerofill. Let us see an example only for INT to understand the concept of zerofill for width.
Create a table. The following is the query to create a table −
mysql> create table intVsIntAnyThingDemo −> ( −> Number1 int(11) unsigned zerofill, −> Number int(13) unsigned zerofill −> ); Query OK, 0 rows affected (1.17 sec)
Now you can insert record in the table with the help of insert command. Here, we have set different width for INT. The query is as follows −
mysql> insert into intVsIntAnyThingDemo values(12345,6789); Query OK, 1 row affected (0.44 sec) mysql> insert into intVsIntAnyThingDemo values(3,2); Query OK, 1 row affected (0.20 sec) mysql> insert into intVsIntAnyThingDemo values(12,89); Query OK, 1 row affected (0.15 sec) mysql> insert into intVsIntAnyThingDemo values(123,6789); Query OK, 1 row affected (0.17 sec) mysql> insert into intVsIntAnyThingDemo values(1234,6789); Query OK, 1 row affected (0.14 sec)
Display all records with the help of select statement. The query is as follows −
mysql> select *from intVsIntAnyThingDemo;
The following is the output displaying different widths and zerofill
+-------------+---------------+ | Number1 | Number | +-------------+---------------+ | 00000012345 | 0000000006789 | | 00000000003 | 0000000000002 | | 00000000012 | 0000000000089 | | 00000000123 | 0000000006789 | | 00000001234 | 0000000006789 | +-------------+---------------+ 5 rows in set (0.00 sec)
1K+ Views
