What happens when a negative value is inserted to UNSIGNED column in MySQL?

Error occurs when you set a negative value to UNSIGNED column in MySQL. For example, let us first create a table with an UNSIGNED field −

mysql> create table UnsignedDemo
   -> (
   -> Id int UNSIGNED
   -> );
Query OK, 0 rows affected (0.79 sec)

The error is as follows whenever you insert negative value to column Id which is declared as UNSIGNED −

mysql> INSERT INTO UnsignedDemo VALUES(-100);
ERROR 1264 (22003): Out of range value for column 'Id' at row 1

Example

However, positive values work well for UNSIGNED. The same is shown in the example below. Insert some records in the above table using insert command. The query is as follows −

mysql> INSERT INTO UnsignedDemo VALUES(100);
Query OK, 1 row affected (0.15 sec)
mysql> INSERT INTO UnsignedDemo VALUES(1000);
Query OK, 1 row affected (0.15 sec)
mysql> INSERT INTO UnsignedDemo VALUES(0);
Query OK, 1 row affected (0.11 sec)
mysql> INSERT INTO UnsignedDemo VALUES(100000000);
Query OK, 1 row affected (0.27 sec)

Display all records from the table using a select statement. The query is as follows −

mysql> SELECT *FROM UnsignedDemo;

Output

+-----------+
| Id        |
+-----------+
|       100 |
|      1000 |
|         0 |
| 100000000 |
+-----------+
4 rows in set (0.00 sec)

Samual Sam

Published on 19-Mar-2019 07:54:21

• Related Questions & Answers
• What happens when I insert the value ‘NULL’ in an AUTO_INCREMENT MySQL column?
• What is unsigned in MySQL?
• Display the warning message when a FLOAT value is inserted into DECIMAL in MySQL?
• What happens when buffer is set to a value "none" in JSP?
• What happens when we try to add a number to undefined value?
• What happens when you add a double value to a String in java?
• What is the Maximum Value of smallint(6) unsigned in MySQL?
• What happens when we type a URL?
• Exclude rows based on column value when another duplicate column value is found in MySQL?
• What happens when a String is accepted or rejected by NPDA?
• What does “unsigned” in MySQL mean and when to use it?
• What happens when a subclass object is assigned to a superclass object in Java?
• What Happens When You Quit Smoking?
• What happens when a function is called before its declaration in C?
• What happens when MySQL encounters an out-of-range date?