MySQL – BOOLEAN


MySQL – Boolean Datatype



”;


A Boolean data type is used to represent truth values of logic and Boolean algebra. It has two possible values: either true or false.

For example, if a customer wants to see all the bikes that are black in colour, we can filter them using BOOLEAN operator, as given in the following table −


Boolean

Here, ”IS_BLACK” is the BOOLEAN column that returns either true or false values based on the colours of the bikes.

Boolean in MySQL

In MySQL, there is no built-in Boolean or Bool data type. Instead MySQL provides us with the TINYINT datatype to store the Boolean values.

MySQL considers the value 0 as FALSE and 1 as TRUE. We can also store NULL values using the TINYINT datatype.

The Boolean values (such as TRUE and FALSE) are not case-sensitive.

Syntax

Following is the syntax of the BOOLEAN operator in MySQL −


CREATE TABLE table_name (
   Column_name BOOLEAN
);

Example

In MySQL, 0 is defined as FALSE and any non-zero values are defined as TRUE −


SELECT TRUE, FALSE;

Output

As we can see in the output below, TRUE and FALSE are represented as 1 and 0 −




TRUE FALSE
1 0

Example

In MySQL, the Boolean values (TRUE and FALSE) are case-insensitive −


SELECT true, false, TRUE, FALSE, True, False;

Output

The output produced is as given below −




true false TRUE FALSE True False
1 0 1 0 1 0

Example

Now, let”s create a table with the name CUSTOMERS using the following query. Here, the AVAILABILITY column specifies whether the customer is available or not. If the bit value is 0 (FALSE), the customer is not available. If it is 1(TRUE), the customer is available −


CREATE TABLE CUSTOMERS (
   ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
   NAME VARCHAR(40),
   AVAILABILITY BOOLEAN
);

Following is the output produced −


Query OK, 0 rows affected (0.02 sec)

To get the information about the CUSTOMERS table, use the following query −


DESCRIBE CUSTOMERS;

If we look at the AVAILABILITY column, which has been set to BOOLEAN while creating the table, it now shows type of TINYINT −




Field Type Null Key Default Extra
ID int NO PRI NULL auto_increment
NAME varchar(40) YES

NULL
AVAILABILITY tinyint(1) YES

NULL

Now, let us insert some records into the CUSTOMERS table using the following INSERT query −


INSERT INTO CUSTOMERS VALUES 
(1, ''Ramesh'', FALSE),
(2, ''Khilan'', TRUE),
(4, ''Kaushik'', NULL);

Using the below query, we can display all the values in the table −


SELECT * FROM CUSTOMERS;

We can see the values in the AVAILABILITY column are set to 0 and 1 respectively. −






ID NAME AVAILABILITY
1 Ramesh 0
2 Khilan 1
4 Kaushik NULL

Replacing BOOLEAN 0,1 with TRUE and FALSE

As we can see in the above CUSTOMERS table, the BOOLEAN data type shows 0 and 1 values instead of TRUE and FALSE. In MySQL, we can convert BOOLEAN data type to TRUE and FALSE values using the CASE statement.

The MySQL CASE statement is a conditional statement that goes through conditions and return a values when the first condition is met. Therefore, once a condition is true, it will stop reading the next piece of code and return the result.

If no conditions are true, it will return the value in the ELSE clause. If no ELSE clause is present and no conditions are true, it returns NULL.

Syntax

Following is the syntax of CASE statement in MySQL −


CASE
   WHEN condition1 THEN result1
   WHEN condition2 THEN result2
   ...
   WHEN conditionN THEN resultN
   ELSE result
END;

Example

To get a better understanding, let us consider the BIKES table created using the query below −


CREATE TABLE BIKES (
   S_NO INT AUTO_INCREMENT PRIMARY KEY,
   COMPANY VARCHAR(40) NOT NULL,
   PRICE INT NOT NULL,
   COLOUR VARCHAR(40) NOT NULL,
   IS_BLACK BOOLEAN
);

Example

Output of the above code is as follows −


Query OK, 0 rows affected (0.03 sec)

Now, let us insert values into the BIKES table using the INSERT statement as shown below −


INSERT INTO BIKES (COMPANY, PRICE, COLOUR, IS_BLACK) 
VALUES (''Royal Enfield'', 300000, ''Black'', 1);
INSERT INTO BIKES (COMPANY, PRICE, COLOUR, IS_BLACK) 
VALUES (''BMW'', 900000, ''Blue'', 0);
INSERT INTO BIKES (COMPANY, PRICE, COLOUR, IS_BLACK) 
VALUES (''Jawa'', 150000, ''Black'', 1);
INSERT INTO BIKES (COMPANY, PRICE, COLOUR, IS_BLACK) 
VALUES (''Triumph'', 1200000, ''Red'', 0);

The BIKES table obtained is as follows −







S_NO COMPANY PRICE COLOUR IS_BLACK
1 Royal Enfield 300000 Black 1
2 BMW 900000 Blue 0
3 Jawa 150000 Black 1
4 Triumph 1200000 Red 0

Now, let us display all the records from the BIKES table, where the colour BLACK is represented by either TRUE or FALSE −


SELECT *,
CASE IS_BLACK
WHEN 1 THEN ''TRUE''
WHEN 0 THEN ''FALSE''
END AS IS_BOOLEAN
FROM BIKES;

Output

The output is displayed as follows −







S_NO COMPANY PRICE COLOUR IS_BLACK IS_BOOLEAN
1 Royal Enfield 300000 Black 1 TRUE
2 BMW 900000 Blue 0 FALSE
3 Jawa 150000 Black 1 TRUE
4 Triumph 1200000 Red 0 FALSE

Example

In the following query, we are filtering the records from the BIKES table where colour black is TRUE −


SELECT * FROM BIKES WHERE IS_BLACK IS TRUE;

Output

As we can see the output below, Royal Enfield and Jawa are black in color (true) −





S_NO COMPANY PRICE COLOUR IS_BLACK
1 Royal Enfield 300000 Black 1
3 Jawa 150000 Black 1

Example

In here, we are filtering the records from the BIKES table where color black is FALSE −


SELECT * FROM BIKES WHERE IS_BLACK IS FALSE;

Output

The output says that BMW and Triumph are not black in colour(false) −





S_NO COMPANY PRICE COLOUR IS_BLACK
2 BMW 900000 Blue 0
4 Triumph 1200000 Red 0


Boolean Operator Using a Client Program

In addition to perform the Boolean Operator in MySQL table using MySQL query, we can also perform the another operation on a table using a client program.

MySQL provides various Connectors and APIs using which you can write programs (in the respective programming languages) to communicate with the MySQL database. The connectors provided are in programming languages such as, Java, PHP, Python, JavaScript, C++ etc.
This section provides programs to execute Boolean Operator in MySQL Table.

Syntax

Following are the syntaxes of the MySQL Boolean Operator in various programming languages −

The MySQL PHP connector mysqli provides a function named query() to execute an SQL query in the MySQL database. Depending on the type of query, it retrieves data or performs modifications within the database.

This function accepts two parameters namely −

  • $sql: This is a string value representing the query.
  • $resultmode: This is an optional parameter which is used to specify the desired format of the result. Which can be MYSQLI_STORE_RESULT (buffered result set object) or, MYSQLI_USE_RESULT (unbuffered result set object) or, MYSQLI_ASYNC.

To perform the Boolean operation in MySQL table, we need to execute the CREATE TABLE statement using this function as −


$sql = "CREATE TABLE table_name ( Column_name BOOLEAN )";
$mysqli->query($sql);

The MySQL NodeJS connector mysql2 provides a function named query() to execute an SQL query in the MySQL database. This function accepts a string value as a parameter representing the query to be executed.

To perform the Boolean operation in MySQL table, we need to execute the CREATE TABLE statement using this function as −


sql= "CREATE TABLE table_name ( Column_name BOOLEAN )";  
con.query(sql);

We can use the JDBC type 4 driver to communicate to MySQL using Java. It provides a function named execute() to execute an SQL query in the MySQL database. This function accepts a String value as a parameter representing the query to be executed.

To perform the Boolean operation in MySQL table, we need to execute the CREATE TABLE statement using this function as −


String sql = "CREATE TABLE table_name ( Column_name BOOLEAN )";
statement.execute(sql);

The MySQL Connector/Python provides a function named execute() to execute an SQL query in the MySQL database. This function accepts a string value as a parameter representing the query to be executed.

To perform the Boolean operation in MySQL table, we need to execute the CREATE TABLE statement using this function as −


query = "CREATE TABLE table_name (Column_name BOOLEAN)"
cursorObj.execute(query);

Example

Following are the implementations of this operation in various programming languages −


$dbhost = ''localhost'';
$dbuser = ''root'';
$dbpass = ''password'';
$dbname = ''TUTORIALS'';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if($mysqli->connect_errno ) {
   printf("Connect failed: %s
", $mysqli->connect_error); exit(); } //printf(''Connected successfully.
''); //create table with boolean column $sql = ''CREATE TABLE CUSTOMERS ( ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY, NAME VARCHAR(40), AVAILABILITY BOOLEAN);''; $result = $mysqli->query($sql); if($result){ printf("Table created successfully...!n"); } //insert data into created table $q = "INSERT INTO CUSTOMERS(ID, NAME, AVAILABILITY) VALUES (1, ''Ramesh'', TRUE)"; If($res = $mysqli->query($q)){ printf("Data inserted successfully...!n"); } //now display the table records $s = "SELECT * FROM CUSTOMERS"; If($r = $mysqli->query($s)){ printf("Select query executed successfully...!n"); printf("Table records: n"); while($row = $r->fetch_assoc()) { printf("Id %d, Name: %s, AVAILABILITY: %s", $row["ID"], $row["NAME"], $row["AVAILABILITY"]); printf("n"); } } else { printf(''Failed''); } $mysqli->close();

Output

The output obtained is as follows −


Table created successfully...!
Data inserted successfully...!
Select query executed successfully...!
Table records:
Id 1, Name: Ramesh, AVAILABILITY: 1       


var mysql = require("mysql2");
var con = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "Nr5a0204@123",
});

//Connecting to MySQL
con.connect(function (err) {
  if (err) throw err;
  console.log("Connected!");
  console.log("--------------------------");

  //Select database
  sql = "CREATE DATABASE TUTORIALS";
  con.query(sql);

  //Select database
  sql = "USE TUTORIALS";
  con.query(sql);

  //Creating CUSTOMERS table
  sql = "CREATE TABLE CUSTOMERS (ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY, NAME VARCHAR(40), AVAILABILITY BOOLEAN);"
  con.query(sql , function(err){
    if (err) throw err;
    console.log("Table created Successfully...");
  });

  //Inserting Records
  sql = "INSERT INTO CUSTOMERS(ID, NAME, AVAILABILITY) VALUES (1, ''Ramesh'', 0), (2, ''Khilan'', 1), (4, ''Kaushik'', NULL);"
  con.query(sql);

  //Displaying all the records of the CUSTOMERS table
  sql = "SELECT * FROM CUSTOMERS;"
  con.query(sql, function (err, result) {
    if (err) throw err;
    console.log(result);
  });
});    

Output

The output produced is as follows −


Connected!
--------------------------
Table created Successfully...
[
  { ID: 1, NAME: ''Ramesh'', AVAILABILITY: 0 },
  { ID: 2, NAME: ''Khilan'', AVAILABILITY: 1 },
  { ID: 4, NAME: ''Kaushik'', AVAILABILITY: null }
]      


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class BooleanOperator {
  public static void main(String[] args) {
    String url = "jdbc:mysql://localhost:3306/TUTORIALS";
    String user = "root";
    String password = "password";
    ResultSet rs;
    try {
      Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection(url, user, password);
            Statement st = con.createStatement();
            //System.out.println("Database connected successfully...!");
            //create a table(having boolean field/column)
            String sql = "CREATE TABLE CUSTOMERS (ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,NAME VARCHAR(40),AVAILABILITY BOOLEAN)";
            st.execute(sql);
            System.out.println("Table created successfully...!");
            //now lets insert some records
            String sql1 = "INSERT INTO CUSTOMERS(ID, NAME, AVAILABILITY) VALUES (1, ''Ramesh'', 0), (2, ''Khilan'', 1), (4, ''Kaushik'', NULL)";
            st.executeUpdate(sql1);
            System.out.println("Records inserted successfully...!");
            //lets display the records....
            String sql2 = "SELECT * FROM CUSTOMERS";
            rs = st.executeQuery(sql2);
            System.out.println("Table records are: ");
            while(rs.next()) {
              String id = rs.getString("Id");
              String name = rs.getString("Name");
              String is_available = rs.getString("AVAILABILITY");
              System.out.println("Id: " + id + ", Name: " + name + ", Is_available: " + is_available);
            }
    }catch(Exception e) {
      e.printStackTrace();
    }
  }
}                             

Output

The output obtained is as shown below −


Table created successfully...!
Records inserted successfully...!
Table records are: 
Id: 1, Name: Ramesh, Is_available: 0
Id: 2, Name: Khilan, Is_available: 1
Id: 4, Name: Kaushik, Is_available: null        


import mysql.connector
# Establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
table_name = ''customer''
# Creating a cursor object 
cursorObj = connection.cursor()
# Create table with boolean column
sql = '''''' CREATE TABLE CUSTOMER(
    ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    NAME VARCHAR(40),
    AVAILABILITY BOOLEAN)''''''
cursorObj.execute(sql)
print("The table is created successfully!")
# Insert data into the created table
insert_query = ''INSERT INTO CUSTOMER(ID, NAME, AVAILABILITY) VALUES (1, "Ramesh", TRUE);''
cursorObj.execute(insert_query)
print("Row inserted successfully.")
# Now display the table records
select_query = "SELECT * FROM CUSTOMER"
cursorObj.execute(select_query)
result = cursorObj.fetchall()
print("Tutorial Table Data:")
for row in result:
    print(row)
cursorObj.close()
connection.close()                                

Output

Following is the output of the above code −


The table is created successfully!
Row inserted successfully.
Tutorial Table Data:
(1, ''Ramesh'', 1) 


Advertisements

”;

Leave a Reply

Your email address will not be published. Required fields are marked *