Syntax error MySQL always returning the bit values as blank? How to get the original values?

MySQL always returning the bit values as blank? How to get the original values?

To get the original value, use the following syntax −

Syntax

select yourBitColumnName+0 from yourTableName;

The above syntax cast the bit column to an integer. To understand the above concept, let us create a table and check how the returning value is blank. We will also see how to get the original value.

The query to create a table.

mysql> create table BitDemo
-> (
-> hasValidId bit not null
-> );
Query OK, 0 rows affected (1.21 sec)

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

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

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

Displaying value using a select statement. This returns a blank value −

mysql> select *from BitDemo;

The following is the output displaying blank value −

+------------+
| hasValidId |
+------------+
|            |
|            |
+------------+
2 rows in set (0.00 sec)

To get the original value for a bit column, use the following query −

mysql> select hasValidId+0 from BitDemo;

Output

+--------------+
| hasValidId+0 |
+--------------+
| 1            |
| 0            |
+--------------+
2 rows in set (0.00 sec)
Updated on: 2020-06-25T11:17:50+05:30

361 Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements