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)
Published on 19-Mar-2019 12:24:21
