Boolean Datatype in MySQL

Photo by Kevin Ku on Unsplash

Table Of Contents

• Introduction
• Trying It Out
  - TINYINT
  - VARCHAR
• Summary

Introduction

This article is meant to highlight ways of accommodating boolean values in MySQL. Boolean datatypes aren’t supported and can prove to be a headache should the need arise.

Ignorant me believed MySQL accommodated boolean datatypes. I have never had to use boolean and would still be holding onto this wrong notion if I hadn’t tried loading a CSV file into a MySQL database some weeks ago.

I needed to accommodate a column with “True” or “False” values while loading the file into a specific table, and soon realized I needed to think intuitively to execute my CSV loader successfully.

Trying It Out

To test the theory of MYSQL not supporting boolean datatypes, try creating a table with a boolean field. This would result in a MySQL error.

CREATE TABLE todo(
 id smallint(5) NOT NULL AUTO_INCREMENT,
 name VARCHAR(50) NOT NULL,
 body TEXT NOT NULL,
 completed BOOLEAN(50) NOT NULL,
 PRIMARY KEY(id)
);

Simply put, SQL doesn’t support boolean datatypes.

I find googling about my bugs boring and decided to create my table manually, and turned on XAMPP so as to see the available datatypes for creating a boolean column.

Boolean filed in XAMPP environment

XAMPP has “Boolean” as an available datatype option but hovering over the text displays a note that booleans are synonymous with “TINYINT”. Ignoring the statement I saved the table and went back to double-check my table structure. I realized in no time that the boolean was only a placeholder for the TINYINT datatype.

BOOLEAN field converted to TINYINT in XAMPP

Thus, boolean fields aren’t supported and will always be converted to TINYINT in XAMPP.

This proved to be a problem that I intuitively found two ways of solving using:

• TINYINT
• VARCHAR

TINYINT

TINYINT is an SQL datatype that holds integer values of 0 to 25 in length.

I ended up using TINYINT for saving boolean fields as 0s and 1s. This required me to convert boolean values in code, from:

• False to 0
• True to 1

CREATE TABLE todo(
 id smallint(5) NOT NULL AUTO_INCREMENT,
 name VARCHAR(50) NOT NULL,
 body TEXT NOT NULL,
 completed TINYINT(1) NOT NULL,
 PRIMARY KEY(id)
);

VARCHAR

VARCHAR is an SQL datatype that stores the varying length of character strings up to 8,000 ASCII characters.

Not everyone would want to have their boolean values as binary. VARCHAR can be used to hold boolean values of True or False as strings.

CREATE TABLE todo(
 id smallint(5) NOT NULL AUTO_INCREMENT,
 name VARCHAR(50) NOT NULL,
 body TEXT NOT NULL,
 completed VARCHAR(5) NOT NULL,
 PRIMARY KEY(id)
);

Summary

MySQL does not support boolean values but that shouldn’t discourage you from creating a persistent database schema.

You can intuitively achieve this using VARCHAR or TINYINT types.

If this article was informative enough, please do leave a comment or constructive criticism, as it would enable me to become better at voicing my experience.