To store Boolean data, MySQL uses Tinyint(1) field type. We can store, update or delete Boolean data by using Tinyint(1) field type.
Boolean data can take values TRUE or FALSE or UNKNOWN.
SELECT 1 IS TRUE, 0 IS FALSE , NULL IS UNKNOWN
Output
1 IS TRUE | 0 IS FALSE | NULL is UNKNOWN |
1 | 1 | 1 |
IS Operator for checking Boolean values→
Reading data from tinyint field
SELECT * FROM `plus2_boolean` WHERE feb = True
We will get a return where feb column is equal to 1. This query will also return the same result.
SELECT * FROM `plus2_boolean` WHERE feb = 1
name | jan | feb | mar |
Ronn | 0 | 1 | 0 |
Lone | 1 | 1 | 4 |
Raju | NULL | 1 | 0 |
Using False
SELECT * FROM `plus2_boolean` WHERE feb = False
Output is here
name | jan | feb | mar |
Alex | 1 | 0 | 1 |
John | 1 | 0 | 2 |
The full table is here, download the SQL dump at the end of this page to create your sample table.
name | jan | feb | mar |
Alex | 1 | 0 | 1 |
Ronn | 0 | 1 | 0 |
John | 1 | 0 | 2 |
Lone | 1 | 1 | 4 |
Raju | NULL | 1 | 0 |
King | NULL | NULL | 1 |
Using IS boolean operator
SELECT * FROM `plus2_boolean` WHERE mar IS TRUE
name | jan | feb | mar |
Alex | 1 | 0 | 1 |
John | 1 | 0 | 2 |
Lone | 1 | 1 | 4 |
King | NULL | NULL | 1 |
SELECT * FROM `plus2_boolean` WHERE jan IS NOT TRUE
This includes NULL data also.
name | jan | feb | mar |
Ronn | 0 | 1 | 0 |
Raju | NULL | 1 | 0 |
King | NULL | NULL | 1 |
Boolean operator IS can be used to check UNKNOWN values. This will return True for Null values.
SELECT * FROM `plus2_boolean` WHERE jan IS UNKNOWN
name | jan | feb | mar |
Raju | NULL | 1 | 0 |
King | NULL | NULL | 1 |
Watch the difference between IS NOT TRUE and IS UNKNOWN, both are not same.
Using NULL safe operator <=>
Null safe operator will return null values also as part of the comparison. Watch the difference in these two queries (outputs)
SELECT * FROM `plus2_boolean` WHERE jan = feb
SELECT * FROM `plus2_boolean` WHERE jan <=> feb
name | jan | feb | mar |
Lone | 1 | 1 | 4 |
King | NULL | NULL | 1 |
←More on NULL Value & Null safe operator
Using IF Condition
We can get different output strings by using SQL IF conditions.
SELECT name, if(jan,'OK','NOT OK') as jan,
if(feb,'OK','NOT OK') as feb,
if(mar,'OK','NOT OK') as mar
FROM `plus2_boolean`
name | jan | feb | mar |
Alex | OK | NOT OK | OK |
Ronn | NOT OK | OK | NOT OK |
John | OK | NOT OK | OK |
Lone | OK | OK | OK |
Raju | NOT OK | OK | NOT OK |
King | NOT OK | NOT OK | OK |
Showing checkbox data
Using the above concepts we can show checked checkbox as selected for True or only checkbox for False data.
Tutorial on how checkbox is used to update Boolean data in MySQL table.
SELECT name,
if(jan,'<input type=checkbox name=c1 value=yes checked>',
'<input type=checkbox name=c1 value=yes>') as jan,
if(feb,'<input type=checkbox name=c1 value=yes checked>',
'<input type=checkbox name=c1 value=yes>') as feb,
if(mar,'<input type=checkbox name=c1 value=yes checked>',
'<input type=checkbox name=c1 value=yes>') as mar
FROM `plus2_boolean`
Output is here
Updating data
UPDATE `plus2_boolean` set mar=True WHERE name='Ronn'
This will update mar field to 1 for record with name='Ronn'
We can use 0 or 1 also.
UPDATE `plus2_boolean` set mar=0 WHERE name='Ronn'
Toggling data ( updating )
UPDATE `plus2_boolean` set mar = !mar WHERE name='Ronn'
Above query will change the data of mar column from True to False or from False to True
Storing data in tinyint field
We can use 0 or 1 to insert data.
INSERT INTO `plus2_boolean` (name ,jan ,feb ,mar)
VALUES ('Alex', 1, 0, 1), ('Ronn', 0, 1, 0)
We can use True or False to add data
INSERT INTO `plus2_boolean` (name ,jan ,feb ,mar)
VALUES ('Alex2', True, False, 1), ('Ronn2', 0, True, 0)
SQL dump of plus2_boolean table
← Data Types
← SQL CASE
SQL sum in multiple columns→
← Subscribe to our YouTube Channel here