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)

Arjun Thakur

Published on 23-Nov-2018 14:59:37