JIYIK CN >

This tutorial aims to understand unsigned in MySQL.

unsignedUsing keywords in MySQL

Businesses and organizations have to assign data type as unsigned for certain columns. However, it is important to understand when and where to use this data type before using it.

While we know intthat can be used to provide any value in the range from -2147483648 to 2147483647, the unsigned data type works slightly differently.

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

Therefore, it is clear that there are two specific instances where you may choose to use unsigned data types.

Now that we know when to use this data type, let’s understand how this data type works and visualize it in a table.

However, before we get started, we create three dummy datasets to work with. Here, we create a table student_details_dummyand 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");

As we can see, we stu_idset as student_detailsthe primary key of the table . It will ensure that the values ​​in this column cannot be duplicated or NULL.

Furthermore, we stu_idset the data type of the column to unsigned. The above query creates a table containing the first and last names of the students.

To see the entries in the data, we use the following code.

SELECT * FROM student_details_dummy;

The above code will 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

DESCRIBE TABLENow let us try to visualize the details of this table using statement in MySQL .

DESCRIBEThe statement helps us visualize the table so that we can drill down into each field of the table.

It also shows us any keys assigned to any columns, the data type assigned to each column, and any other extra information related 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 the table.

DESCRIBE student_details_dummy;

This query will 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

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