MySQL BOOLEAN and TINYINT(1)

In MySQL, BOOL and BOOLEAN are synonyms for TINYINT(1). FALSE is an alias for 0 and TRUE is an alias for 1; in Boolean expressions, any nonzero non-NULL numeric value evaluates as true.

SELECT 1 IS TRUE, 0 IS FALSE, NULL IS UNKNOWN;

Output:

For strict two-state application flags, storing only 0 and 1 keeps the meaning clear.

Compare stored values

SELECT * FROM plus2_boolean WHERE feb = TRUE;

SELECT * FROM plus2_boolean WHERE feb = 1;

Output:

SELECT * FROM plus2_boolean WHERE feb = FALSE;

Output:

Full sample table:

Use IS with Boolean expressions

SELECT * FROM plus2_boolean WHERE mar IS TRUE;

Output:

SELECT * FROM plus2_boolean WHERE jan IS NOT TRUE;

Output:

SELECT * FROM plus2_boolean WHERE jan IS UNKNOWN;

Output:

IS TRUE / IS FALSE / IS UNKNOWN is useful when NULL needs to be distinguished from false.

NULL-safe equality

SELECT * FROM plus2_boolean WHERE jan = feb;

Output:

With ordinary =, comparisons involving NULL evaluate to NULL.

SELECT * FROM plus2_boolean WHERE jan <=> feb;

Output:

The NULL-safe equality operator <=> returns 1 when both operands are NULL and 0 or 1 for other comparisons.

Display Boolean values with IF()

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;

Output:

Returning checkbox markup from SQL

SELECT name,
  IF(jan, '<input type=checkbox checked>', '<input type=checkbox>') AS jan
FROM plus2_boolean;

Rendered checkbox example:

Updating Boolean-style values

UPDATE plus2_boolean SET mar = TRUE WHERE name = 'Ronn';

UPDATE plus2_boolean SET mar = 0 WHERE name = 'Ronn';

UPDATE plus2_boolean SET mar = NOT mar WHERE name = 'Ronn';

Insert Boolean-style values

INSERT INTO plus2_boolean (name, jan, feb, mar) VALUES
('Alex', 1, 0, 1), ('Ronn', 0, 1, 0);

INSERT INTO plus2_boolean (name, jan, feb, mar) VALUES
('Alex2', TRUE, FALSE, TRUE), ('Ronn2', FALSE, TRUE, FALSE);

See the related checkbox data-matrix tutorial

Download the sample data

Related operations: comparison operators, UPDATE, and INSERT.

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author   Watch on YouTube

Connect: