MYSQL SQL SERVER

[Solved-9 Answers] Which MySQL Datatype should be used for storing boolean values?

MYSQL Articles, Errors and Fixes
MYSQL Articles, Errors and Fixes

Since MySQL doesn’t seem to have any ‘boolean’ datatype, which datatype do we ‘abuse’ for storing true/false information in MySQL? Especially in the context of writing and reading from/to a PHP-Script.

The several approaches are :

  • tinyint, varchar fields containing the values 0/1,
  • varchar fields containing the strings ‘0’/’1′ or ‘true’/’false’
  • And finally enum Fields containing the two options ‘true’/’false’.

Other Vendor Type MySQL Type
BOOL TINYINT
BOOLEAN TINYINT
  • For MySQL 5.0.3 and higher, we can use the BIT:
  • As of MySQL 5.0.3, the BIT data type is used to store bit-field values. A type of BIT(M) enables storage of M-bit values. M can range from 1 to 64.
  • Otherwise, according to the MySQL, we can use bool and Boolean which are at the moment aliases of tinyint(1):
  • Bool, Boolean: These types are synonyms for TINYINT(1). A value of zero is considered false. Non-zero values are considered true.

  • This is an elegant solution that it uses zero data bytes:
mysql database code
some_flag CHAR(0) DEFAULT NULL

To set it to true, set some_flag = ” and to set it to false, set some_flag = NULL.

  • using a CHAR(0), where ” == true and NULL == false.

  • using TINYINT(1) in order to store boolean values in Mysql.
  • mysql can store boolean (BOOL) and it store it as a tinyint(1)

  • Bit is only advantageous over the various byte options (tinyint, enum, char(1)) if we have a lot of boolean fields.
  • One bit field still takes up a full byte. Two bit fields fit into that same byte. Three, four,five, six, seven, eight.
  • After which they start filling up the next byte.
  • Ultimately the savings are so small, there are thousands of other optimizations we should focus on.
  • Unless we’re dealing with an enormous amount of data, those few bytes aren’t going to add up to much.
  • If we’re using bit with PHP we need to typecast the values going in and out.
READ  C Programming - Union and Intersection of two Linked Lists

Until MySQL implements a bit datatype, if our processing is truly pressed for space and/or time, such as with high volume transactions, create a TINYINT field called bit_flags, for all our boolean variables, and mask and shift the boolean bit we decide in our SQL query.

According to the application usage, if one wants only 0 or 1 to be stored, bit(1) is the better choice.
Use TINYINT unsigned NOT NULL DEFAULT 0 (or 1).

  1. The most common is:
    • TINYINT unsigned NOT NULL
  2. The trivial Boolean or Bool column type which is just a synonyms for TINYINT(1):
    • BOOLEAN NOT NULL DEFAULT 0
  3. Declaring an enumeration with “true” and “false”:
    • ENUM(‘false’, ‘true’) NOT NULL DEFAULT ‘false’
  4. The newest concept is:
    • CHAR(0) NULL DEFAULT NULL

All of them are quite straightforward except the last one which might need some explanation. Char(0) means a zero length string where null value or lack of the value means false and empty string (”) means true.

We can enter a true or false because they’re synonyms for a 1 or 0 respectively. TINYINT is the supported data type, and BIT, BOOL, and BOOLEAN are synonyms for the base data type.

mysql database code
mysql> CREATE TABLE data_type (TYPE bool);
 mysql> DESCRIBE data_type;

+——-+————+——+—–+———+——-+
| FIELD | TYPE | NULL | KEY | DEFAULT | Extra |
+——-+————+——+—–+———+——-+
| TYPE | tinyint(1) | YES | | NULL | |
+——-+————+——+—–+———+——-+

mysql database code
mysql> INSERT INTO data_type VALUES (TRUE),(FALSE);
 mysql> SELECT * FROM data_type;

+——+
| TYPE |
+——+
| 1 |
| 0 |
+——+
The results of the test demonstrate that only a 0 or 1 value validates against the false or true constants, as shown:
+——-+———-+——-+———+
| Value | Unsigned | Value | Signed |
+——-+———-+——-+———+
| 0 | False | 0 | False |
| 1 | True | 1 | True |
| 1 | True | -1 | Invalid |
| 2 | Invalid | 2 | Invalid |
+——-+———-+——-+———+
Based on the comment, the 256 value range of TINYINT is found here.

  • This article provides some of the basic informations on mysqldump database , mysql database , mysql php , create database mysql , mysql server , sql server data types , nodejs mysql , mysql select database , php mysql connect , php mysql query , mysql create database , mysql optimize , mysql license , mysql injection , mysql query , mysql download , download mysql , mysql dba , mysql tutorial pdf , mysql 5 , mysql syntax , mysql comment , mysql basics , mysql commands , mysql command line , what is mysql , list of data types , mysql setup , mysql operators , mysql interview questions , replication in mysql.
READ  Remove a MySQL User on Linux via Command Line

About the author

Venkatesan Prabu

Venkatesan Prabu

Wikitechy Founder, Author, International Speaker, and Job Consultant. My role as the CEO of Wikitechy, I help businesses build their next generation digital platforms and help with their product innovation and growth strategy. I'm a frequent speaker at tech conferences and events.

Add Comment

Click here to post a comment