Introduction to MySQL TINYINT Data Type

MySQL TINYINT is a data type used for storing small integers. The TINYINT type can store integers in the range of -128 to 127, or unsigned integers in the range of 0 to 255. It is commonly used for storing boolean values (0 or 1) or other small integer values.

Syntax

The syntax for MySQL TINYINT data type is as follows:

TINYINT[(M)] [UNSIGNED] [ZEROFILL]

Where M represents the field width, UNSIGNED indicates unsigned value, and ZEROFILL indicates padding the value with 0s to reach the field width.

Use Cases

TINYINT data type is commonly used for storing boolean values (0 or 1) or other small integer values, such as storing the status of whether a user is subscribed to an email newsletter, or recording the status of a project. Since it only occupies 1 byte of storage space, it can save storage space in large databases.

Examples

Here are two examples that demonstrate how to use TINYINT data type in MySQL.

Example 1

Create a table named “users” with a TINYINT field named “is_subscribed” to store the status of whether a user is subscribed to an email newsletter.

CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  is_subscribed TINYINT(1)
);

INSERT INTO users (id, name, is_subscribed) VALUES
  (1, 'Alice', 1),
  (2, 'Bob', 0),
  (3, 'Charlie', 1),
  (4, 'Dave', 0);

The above SQL statements will create a table named “users” and add four users to the table. The “is_subscribed” column will store values of TINYINT data type with values of 0 or 1.

Example 2

Create a table named “tasks” with a TINYINT field named “status” to store the status of tasks.

CREATE TABLE tasks (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  status TINYINT(1) UNSIGNED
);

INSERT INTO tasks (id, name, status) VALUES
  (1, 'Task 1', 0),
  (2, 'Task 2', 1),
  (3, 'Task 3', 2),
  (4, 'Task 4', 1);

The above SQL statements will create a table named “tasks” and add four tasks to the table. The “status” column will store unsigned integer values of 0, 1, or 2 using TINYINT data type.

Conclusion

TINYINT data type is a data type used for storing small integer values, and it is commonly used for storing boolean values or other small integer values. Due to its small storage size of 1 byte, it can save storage space in large databases. If you need to store larger integers, consider using other integer types such as SMALLINT or INT. However, if your data requires a small integer range, TINYINT may be your best choice.

In conclusion, TINYINT data type has wide applications in MySQL, as it can efficiently store small integer values and save storage space. Whether it is for storing boolean values, status indicators, or other small integer values, TINYINT is a reliable data type choice.