Introduction to MySQL SMALLINT Data Type

SMALLINT is a data type in MySQL used for storing integer data that ranges from -32768 to 32767.

Syntax

When creating a table, you can use the following syntax to define a column with SMALLINT data type:

column_name SMALLINT [UNSIGNED] [ZEROFILL]

where column_name is the name of the column, and UNSIGNED and ZEROFILL are optional. If UNSIGNED is specified, it means that the column only stores positive numbers. If ZEROFILL is specified, it means that leading digits will be filled with zeros when displaying the value of this column.

Use Cases

Due to the ability to store small integer values, SMALLINT data type is commonly used in the following scenarios:

• Storing enumeration type data
• Storing status indicators such as enabled/disabled, true/false, etc.
• Storing small counters or metrics

Examples

Example 1

Suppose we need to store some user data, including user ID, username, and user age. User age is a small integer, and we can use SMALLINT data type to store it. Here is an example of creating a user table:

CREATE TABLE users (
  id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  age SMALLINT UNSIGNED NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Below is an example of inserting some data into the users table:

INSERT INTO users (name, age) VALUES ('Alice', 23);
INSERT INTO users (name, age) VALUES ('Bob', 27);
INSERT INTO users (name, age) VALUES ('Charlie', 19);

Example 2

Another example is storing a table that requires small integer values. Suppose we need to store an employee table that includes employee ID and years of service. We can use SMALLINT data type to store the years of service. The table creation statement would be as follows:

CREATE TABLE employees (
    id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    age SMALLINT UNSIGNED,
    PRIMARY KEY (id)
);

Below is an example of inserting some data into the employees table:

INSERT INTO employees (name, age) VALUES ('Alice', 2);
INSERT INTO employees (name, age) VALUES ('Bob', 5);
INSERT INTO employees (name, age) VALUES ('Charlie', 1);

Conclusion

SMALLINT data type is used for storing small integer values, and it is commonly used for storing enumeration type data, status indicators, and small counters or metrics. If you need to store larger integer values, consider using other integer types such as INT or BIGINT. When using SMALLINT data type, it is important to choose the appropriate signed or unsigned type based on the data range and size to fully utilize storage space.