Unsigned in MySQL

This tutorial aims at understanding unsigned in MySQL.

Using the unsigned Keyword in MySQL

Businesses and organizations must assign certain columns with the data type as Unsigned. However, it’s important to understand when and where to use this data type before using it.

While we understand that int can be used to provide values anywhere in the range from -2147483648 to 2147483647, the unsigned data type works a bit differently.

It cannot support negative values. Moreover, its range increases from 0 to 4294967295.

Thus, it’s evident that there are two particular instances during which one can choose to work with the unsigned data type.

1. When the business does not require negative values for a particular column, we can opt for the unsigned data type under such a situation. For example, consider a product-based company.

   If the company were to assign a data type to the sales column for their table, they could assign an unsigned data type to the sales column. It is because the values in the sales column can never be negative.

2. Secondly, if an organization requires very large positive integer values to be used for a column, they might assign that column with the unsigned data type. For example, if a product-based organization were to understand the total income of its sales, it might have to work with very large numbers.

   These numbers can be such that the integer range or limit might overflow. The organization can assign that column with the unsigned data type to eliminate this situation.

Now we know when to use this data type. Let us understand how this data type works and visualize it in a table.

However, we create three dummy datasets to work with before we begin. Here we create a table, student_details_dummy, along with a few rows.

-- create the table student_details
CREATE TABLE student_details_dummy(
  stu_id int unsigned,
  stu_firstName varchar(255) DEFAULT NULL,
  stu_lastName varchar(255) DEFAULT NULL,
  primary key(stu_id)
);
-- insert rows to the table student_details_dummy
INSERT INTO student_details_dummy(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");

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

Moreover, we have set the data type as unsigned for the stu_id column. 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_dummy;

The above code 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 try to visualize the details of this table using the DESCRIBE TABLE statement in MySQL.

The DESCRIBE statement helps us visualize the table such that we can understand each field of the table in-depth.

It also shows us any key assigned to any column, the data type assigned to each column, and any other extra information relevant to the table. This statement can be illustrated with the help of the following query.

DESCRIBE name_of_the_table;

In our case, we need to write the following query to check the data type associated with each column in our table.

DESCRIBE student_details_dummy;

The query would fetch us the following output.

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

Therefore, with the help of the unsigned keyword and the DESCRIBE statement, we can efficiently assign and visualize the unsigned data type from a table in MySQL.