Tinyint(1) field type for boolean data in MySQL table

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 TRUE0 IS FALSENULL is UNKNOWN
111
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
namejanfebmar
Ronn010
Lone114
RajuNULL10
Using False
SELECT * FROM  `plus2_boolean` WHERE feb = False
Output is here
namejanfebmar
Alex101
John102
The full table is here, download the SQL dump at the end of this page to create your sample table.
namejanfebmar
Alex101
Ronn010
John102
Lone114
RajuNULL10
KingNULLNULL1

Using IS boolean operator

SELECT * FROM `plus2_boolean` WHERE mar IS TRUE
namejanfebmar
Alex101
John102
Lone114
KingNULLNULL1
SELECT * FROM `plus2_boolean` WHERE jan  IS NOT TRUE
This includes NULL data also.
namejanfebmar
Ronn010
RajuNULL10
KingNULLNULL1
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
namejanfebmar
RajuNULL10
KingNULLNULL1
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
namejanfebmar
Lone114
SELECT * FROM `plus2_boolean` WHERE jan  <=> feb
namejanfebmar
Lone114
KingNULLNULL1
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`
namejanfebmar
AlexOKNOT OKOK
RonnNOT OKOKNOT OK
JohnOKNOT OKOK
LoneOKOKOK
RajuNOT OKOKNOT OK
KingNOT OKNOT OKOK

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
namejanfebmar
Alex
Ronn
John
Lone
Raju
King

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
SQL CASE SQL sum in multiple columns


plus2net.com






Post your comments , suggestion , error , requirements etc here





SQL Video Tutorials










We use cookies to improve your browsing experience. . Learn more
HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
©2000-2020 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer