What is the Maximum Value of smallint(6) unsigned in MySQL?

MySQLMySQLi Database

The maximum value of SMALLINT(6) unsigned in MySQL is 65535. The number 6 does not affect the actual range. It can only be used to display width on the command line.

The Minimum Value signed is

-32768

The Maximum Value unsigned is

65535

The Maximum value signed is

32767

Let us understand this with zerofill and create a table using the following query.

mysql> create table smallIntDemo
-> (
-> FirstNumber smallint(6) zerofill
-> );
Query OK, 0 rows affected (1.95 sec)

Now you can insert records in the table using insert command. Whenever you insert beyond the range 65535, it will not insert in the table, since this is the maximum value. The query is as follows inserting the values less than the maximum range.

mysql> insert into smallIntDemo values(2);
Query OK, 1 row affected (0.21 sec)

mysql> insert into smallIntDemo values(23);
Query OK, 1 row affected (0.21 sec)

mysql> insert into smallIntDemo values(234);
Query OK, 1 row affected (0.17 sec)

mysql> insert into smallIntDemo values(2345);
Query OK, 1 row affected (0.15 sec)

mysql> insert into smallIntDemo values(23456);
Query OK, 1 row affected (0.48 sec)

Now, let us see some records that will not insert in the table since it extends the maximum value.

mysql> insert into smallIntDemo values(234567);
ERROR 1264 (22003): Out of range value for column 'FirstNumber' at row 1

mysql> insert into smallIntDemo values(111111);
ERROR 1264 (22003): Out of range value for column 'FirstNumber' at row 1

Now you can display all records from the table using select statement. The query is as follows -

mysql> select *from smallIntDemo;

The following is the output displaying the usage of width i.e. number in SMALLINT(6). The width is 6.

+-------------+
| FirstNumber |
+-------------+
| 000002 |
| 000023 |
| 000234 |
| 002345 |
| 023456 |
+-------------+
5 rows in set (0.00 sec)
raja
Updated on 30-Jul-2019 22:30:24

Advertisements