MySQL - Boolean Datatype

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);

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)