What does “unsigned” in MySQL mean and when to use it?

The “unsigned” in MySQL is a data type. Whenever we write an unsigned to any column that means you cannot insert negative numbers. Suppose, for a very large number you can use unsigned type.

The maximum range with unsigned int is 4294967295.

Note: If you insert negative value you will get a MySQL error.

Here is the example demo of unsigned type. Let us first create a table with “unsigned” column. The following is the query to create a table −

mysql> create table UnsignedDemoWithPositiveValue
   -> (
   -> Distance int unsigned
   -> );
Query OK, 0 rows affected (0.86 sec)

If you will try to insert the upper value with unsigned 4294967295, then an error will generate since the value is out of range.

Inserting out of range value.

mysql> insert into UnsignedDemoWithPositiveValue values(4294967296);
ERROR 1264 (22003): Out of range value for column 'Distance' at row 1

In the above example, I have inserted 4294967296, which is out of range, therefore error generates.

Now I am inserting another value 4294967295 into the table.

mysql> insert into UnsignedDemoWithPositiveValue values(4294967295);
Query OK, 1 row affected (0.30 sec)

Above, you can see that the query executed successfully.

Now, let us see another example. If you insert negative records, then the following error can be seen −

mysql> insert into UnsignedDemoWithPositiveValue values(-124);
ERROR 1264 (22003): Out of range value for column 'Distance' at row 1

I will now insert only positive value with value 124. The query is as follows −

mysql> insert into UnsignedDemoWithPositiveValue values(124);
Query OK, 1 row affected (0.86 sec)

As you can see above, the query executed successfully.

Let us display the record with the help of select statement. The query is as follows −

mysql> select *from UnsignedDemoWithPositiveValue;

Here is the output −

+------------+
| Distance   |
+------------+
| 4294967295 |
| 124        |
+------------+
2 rows in set (0.00 sec)

Ankith Reddy

e

Updated on: 30-Jul-2019

11K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started