Syntax error What is the smallest datatype for one bit in MySQL?

What is the smallest datatype for one bit in MySQL?

The smallest datatype for one bit can be bit(1). The syntax is as follows −

yourColumnName bit(1)

To understand the above syntax, let us create a table. The query to create a table is as follows −

mysql> create table bitDemo
   -> (
   -> isValid bit(1)
   -> );
Query OK, 0 rows affected (0.49 sec)

Now you can check all the details of table with the help of SHOW CREATE command. The query is as follows −

mysql> show create table bitDemo;

Here is the output −

+---------+-----------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                |
+---------+-----------------------------------------------------------------------------------------------------------------------------+
| bitDemo | CREATE TABLE `bitdemo` (`isValid` bit(1) DEFAULT NULL) ENGINE =InnoDB DEFAULT CHARSET =utf8mb4 COLLATE =utf8mb4_0900_ai_ci  |
+---------+-----------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Insert some records in the table using insert command. The query is as follows −

mysql> insert into bitDemo values(0);
Query OK, 1 row affected (0.13 sec)
mysql> insert into bitDemo values(1);
Query OK, 1 row affected (0.10 sec)
mysql> insert into bitDemo values(1);
Query OK, 1 row affected (0.07 sec)
mysql> insert into bitDemo values(0);
Query OK, 1 row affected (0.14 sec)

Display all records from the table using select statement. The query is as follows −

mysql> select *from bitDemo;

Here is the output −

+---------+
| isValid |
+---------+
|         |
|         |
|         |
|         |
+---------+
4 rows in set (0.00 sec)

To show the bit value, use the following query −

mysql> select isValid+0 from bitDemo;

The following is the output −

+-----------+
| isValid+0 |
+-----------+
| 0         |
| 1         |
| 1         |
| 0         |
+-----------+
4 rows in set (0.00 sec)
Updated on: 2019-07-30T22:30:25+05:30

191 Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements