MySQL Tutorial/Data Types/SMALLINT

Insert value to SMALLINT column

mysql>
mysql> CREATE TABLE myTable(
    ->    ID SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->    Name VARCHAR(50) NOT NULL
    -> );
Query OK, 0 rows affected (0.06 sec)
mysql>
mysql>
mysql> INSERT INTO myTable (ID, Name)VALUES (1, "Ain\"t ");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> select * from myTable;
+----+--------+
| ID | Name   |
+----+--------+
|  1 | Ain"t  |
+----+--------+
1 row in set (0.01 sec)
mysql>
mysql> drop table myTable;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql>
mysql>

Set up default value for SMALLINT

mysql>
mysql>
mysql> CREATE TABLE Publishers
    -> (
    ->    PubID SMALLINT NOT NULL DEFAULT 1
    -> )
    -> ENGINE=INNODB;
Query OK, 0 rows affected (0.08 sec)
mysql>
mysql>
mysql> CREATE TABLE Books
    -> (
    ->    BookID SMALLINT NOT NULL,
    ->    BookName VARCHAR(40) NOT NULL,
    ->    PubID SMALLINT NOT NULL DEFAULT 1
    -> )
    -> ENGINE=INNODB;
Query OK, 0 rows affected (0.06 sec)
mysql>
mysql>
mysql> desc Books;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| BookID   | smallint(6) | NO   |     |         |       |
| BookName | varchar(40) | NO   |     |         |       |
| PubID    | smallint(6) | NO   |     | 1       |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql>
mysql> desc Publishers;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| PubID | smallint(6) | NO   |     | 1       |       |
+-------+-------------+------+-----+---------+-------+
1 row in set (0.02 sec)
mysql>
mysql> drop table Books;
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> drop table Publishers;
Query OK, 0 rows affected (0.06 sec)
mysql>

SMALLINT[(M)] [UNSIGNED] [ZEROFILL]

A small integer.

The signed range is -32768 to 32767.

The unsigned range is 0 to 65535.

mysql>
mysql> CREATE TABLE myTable
    -> (
    ->    ID SMALLINT(4) UNSIGNED ZEROFILL,
    ->    Quantity INT UNSIGNED
    -> );
Query OK, 0 rows affected (0.05 sec)
mysql>
mysql> desc myTable;
+----------+-------------------------------+------+-----+---------+-------+
| Field    | Type                          | Null | Key | Default | Extra |
+----------+-------------------------------+------+-----+---------+-------+
| ID       | smallint(4) unsigned zerofill | YES  |     | NULL    |       |
| Quantity | int(10) unsigned              | YES  |     | NULL    |       |
+----------+-------------------------------+------+-----+---------+-------+
2 rows in set (0.02 sec)
mysql>
mysql> drop table myTable;
Query OK, 0 rows affected (0.00 sec)

Use SMALLINT column as the primary key column

mysql>
mysql> CREATE TABLE myTable
    -> (
    ->    ID SMALLINT UNSIGNED NOT NULL PRIMARY KEY,
    ->    Description VARCHAR(40)
    -> );
Query OK, 0 rows affected (0.03 sec)
mysql>
mysql> desc myTable;
+-------------+----------------------+------+-----+---------+-------+
| Field       | Type                 | Null | Key | Default | Extra |
+-------------+----------------------+------+-----+---------+-------+
| ID          | smallint(5) unsigned | NO   | PRI |         |       |
| Description | varchar(40)          | YES  |     | NULL    |       |
+-------------+----------------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
mysql>
mysql> drop table myTable;
Query OK, 0 rows affected (0.00 sec)