What is the difference between BIT and TINYINT in MySQL?

---

BIT can be used to store the value of 1 bit. It could be 0 or 1. We cannot store, for example 2 with data type BIT. If we try to insert 2 with BIT data type, MySQL raises an error.

TINYINT can be used to store value of 8 bits. The maximum value we can store is 127.We cannot store, for example 987 with 8 bit value. If we try to insert 987 with TINYINT data type, MySQL raises an error.

Let us work it through MySQL version 8.0.12.

To check the version installed on your system.

mysql> select version();

The following is the output.

+-----------+
| version() |
+-----------+
| 8.0.12    |
+-----------+
1 row in set (0.00 sec)

BIT Type

Creating a table with BIT type.

mysql> create table BitDemo
   -> (
   -> Id BIT
   -> );
Query OK, 0 rows affected (0.56 sec)

Inserting records into the table ‘BitDemo’.

mysql> insert into BitDemo values(1);
Query OK, 1 row affected (0.13 sec)

mysql>  insert into BitDemo values(0);
Query OK, 1 row affected (0.33 sec)

We get an error whenever we insert 2 into Bit.

mysql> insert into BitDemo values(2);
ERROR 1406 (22001): Data too long for column 'Id' at row 1

TINYINT

Creating a table with TINYINT data type.

mysql> create table TinyIntDemo
   -> (
   -> Id TINYINT
   -> );
Query OK, 0 rows affected (0.69 sec)

Inserting records.

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

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

mysql>  insert into TinyIntDemo values(127);
Query OK, 1 row affected (0.16 sec)

We get an error whenever we try to insert value exceeding 127.

mysql> insert into TinyIntDemo values(9876);
ERROR 1264 (22003): Out of range value for column 'Id' at row 1
mysql>  insert into TinyIntDemo values(987);
ERROR 1264 (22003): Out of range value for column 'Id' at row
mysql> insert into TinyIntDemo values(255);
ERROR 1264 (22003): Out of range value for column 'Id' at row 1
mysql> insert into TinyIntDemo values(254);
ERROR 1264 (22003): Out of range value for column 'Id' at row 1
mysql>  insert into TinyIntDemo values(200);
ERROR 1264 (22003): Out of range value for column 'Id' at row 1
mysql>  insert into TinyIntDemo values(199);
ERROR 1264 (22003): Out of range value for column 'Id' at row 1

The maximum value we can store is 127.

To display all valid inserted records, let us use the SELECT command.

mysql> select *from TinyIntDemo;

Here is the output.

+------+
| Id   |
+------+
|  123 |
|   97 |
|  127 |
+------+
3 rows in set (0.00 sec)

• Related Questions & Answers
• What is the difference between MySQL TINYINT(2) vs TINYINT(1)?
• What is the difference between TINYINT(1) and Boolean in MySQL?
• What is difference between Boolean and tinyint(1) in MySQL?
• What is the difference between SQL and MySQL?
• What is the difference between MySQL NOW() and SYSDATE()?
• What is the difference between int and integer in MySQL?
• What is the difference between CHAR and NCHAR in MySQL?
• What is the difference between CHAR and VARCHAR in MySQL?
• In MySQL, what is the difference between SERIAL and AUTO_INCREMENT?
• Difference between 32-bit and 64-bit operating systems
• In MySQL what is the difference between != NULL and IS NOT NULL?
• What is the difference between MySQL INSTR() and FIND_IN_SET() functions?
• What is the difference between MySQL stored procedure and function?
• What is the difference between MySQL NOW() and CURDATE() function?
• What is the difference between MySQL TRUNCATE and DELETE command?