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

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

The maximum range of unsigned int is 4294967295.

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

Here is an example demonstration of unsigned types. Let's first create a table with an "unsigned" column. Following is the query that creates the table -

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

If you try to insert an unsigned upper bound value of 4294967295, an error will be generated as the value is out of range.

Insert out-of-range values.

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

In the above example, I inserted 4294967296, which is out of range, so an error is generated.

Now I insert 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 was executed successfully.

Now, let’s look at another example. If I insert negative records, I get the following error -

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 values ​​with value 124. The query is as follows -

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

As shown above, the query is executed successfully.

Let us use the select statement to display records. The query is as follows -

mysql> select *from UnsignedDemoWithPositiveValue;

This is the output -

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

The above is the detailed content of What does 'unsigned' mean in MySQL and when to use it?. For more information, please follow other related articles on the PHP Chinese website!