Tiny Integer in MySQL

This tutorial explores the concept of a tiny integer in the MySQL database.

Most businesses and organizations that use MySQL for data analysis or visualization need to sort or fetch different table values of their users based on the date of entry or expiry or something else altogether.

It is made possible with the different entries made into the table with the help of a data analyst. This analyst is in charge of assigning certain data types to each of the different fields associated with the table.

Some commonly used data types are integers, strings, floating points, date time, characters, varchar, blob and text.

Out of these, there is a particular sub-type of the integer data type. It is called the Tiny integer.

Tiny Integer or TINYINT Data Type in MySQL

Sometimes, certain fields don’t need higher values set for certain integer-based fields in businesses. This field can be assigned the tiny integer data type.

This tutorial will extend this knowledge by working with this tiny integer.

Let us understand this statement in greater depth.

Before we begin, we create a dummy dataset to work with. Here we create a table, student_details_table_tiny, along with a few rows.

-- create the table student_details_table_tiny
CREATE TABLE student_details_table_tiny(
  stu_id tinyint,
  stu_firstName varchar(255),
  stu_lastName varchar(255) DEFAULT NULL,
  primary key(stu_id, stu_firstName)
);
-- insert rows to the table student_details_table_tiny
INSERT INTO student_details_table_tiny(stu_id,stu_firstName,stu_lastName)
 VALUES(1,"Preet","Sanghavi"),
 (2,"Rich","John"),
 (3,"Veron","Brow"),
 (4,"Geo","Jos"),
 (5,"Hash","Shah"),
 (6,"Sachin","Parker"),
 (7,"David","Miller");

As we can see, we have set the stu_id and stu_firstName as the primary key for our table student_details_table_tiny. It would ensure that the values in this column cannot be duplicated or NULL.

It should also be noted that a primary key cannot possess Null values in its column. We will certainly get an error if we push or insert Null values in this column.

Sometimes, it becomes necessary for organizations to get rid of this key to insert multiple similar values or null values.

The above query creates a table with rows of the students’ first and last names. To view the entries in the data, we use the following code.

SELECT * FROM student_details_table_tiny as our_table;

The query would give the following output.

stu_id	stu_firstName	stu_lastName
1	      Preet	        Sanghavi
2	      Rich	        John
3	      Veron	        Brow
4	      Geo	        Jos
5	      Hash	        Shah
6	      Sachin	    Parker
7	      David	        Miller

Now let us use the DESCRIBE statement to understand our table in greater depth. This operation can be performed using the following query.

DESCRIBE student_details_table_tiny;

This statement would help us fetch the table’s intricate details like the data type associated with each column, different columns and their names, the keys associated with each column, and any extra information relevant to the table.

The query would give us the following output.

Field				Type			Null	Key		Default		Extra
stu_id				tinyint			NO		PRI
stu_firstName		varchar(255)	NO		PRI
stu_lastName		varchar(255)	YES

As we can see from the illustrated table, the tinyint data type is used for the stu_id column. However, it’s important to understand that this data type is only used when working with smaller numbers.

The tiny integer data type ranges from -128 to 127 for signed tiny integer and 0 to 255 for unsigned tiny integer.

We use the alias our_table with the AS keyword in MySQL to increase readability.

Therefore, using the SELECT clause and the DESCRIBE statement, we can efficiently understand the usage of the tiny integer in MySQL.

Some other related topics that can help learn the concept better are below.

• SELECT statement in MySQL.
• DESCRIBE statement in MySQL.