
- Learn MySQL
- MySQL - Home
- MySQL - Introduction
- MySQL - Installation
- MySQL - Administration
- MySQL - PHP Syntax
- MySQL - Connection
- MySQL - Create Database
- MySQL - Drop Database
- MySQL - Select Database
- MySQL - Data Types
- MySQL - Create Tables
- MySQL - Drop Tables
- MySQL - Insert Query
- MySQL - Select Query
- MySQL - Where Clause
- MySQL - Update Query
- MySQL - Delete Query
- MySQL - Like Clause
- MySQL - Sorting Results
- MySQL - Using Join
- MySQL - NULL Values
- MySQL - Regexps
- MySQL - Transactions
- MySQL - Alter Command
- MySQL - Indexes
- MySQL - Temporary Tables
- MySQL - Clone Tables
- MySQL - Database Info
- MySQL - Using Sequences
- MySQL - Handling Duplicates
- MySQL - SQL Injection
- MySQL - Database Export
- MySQL - Database Import
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 Articles
- 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?
- Difference between 32-bit and 64-bit Operating Systems
- In MySQL, what is the difference between SERIAL and AUTO_INCREMENT?
- What is the difference between CHAR and NCHAR in MySQL?
- What is the difference between CHAR and VARCHAR in MySQL?
- What is the difference between int and integer in MySQL?
- Is there a difference in using INT(1) vs TINYINT(1) in MySQL?
- What is the difference between MySQL NOW() and SYSDATE()?
- In MySQL what is the difference between != NULL and IS NOT NULL?
- Difference between Byte stuffing and Bit stuffing
- Difference between Bit Rate and Baud Rate
- What is the difference between MySQL NOW() and CURDATE() function?
