MySQL RLIKE Operator

The RLIKE operator in MySQL is used to search data in a database using patterns (or regular expressions), also known as pattern matching. In other words, the RLIKE operator is used to determine whether a given regular expression matches a record in a table or not. It returns 1 if the record is matched and 0, otherwise.

A regular expression is defined as a sequence of characters that represent a pattern in an input text. It is used to locate or replace text strings using some patterns; this pattern can either be a single/multiple characters or words, etc.

The functionally of this operator is equivalent to the MySQL REGEXP operator and is commonly used to search for specific patterns that meets certain criteria.

Syntax

Following is the basic syntax of the RLIKE operator in MySQL −

expression RLIKE pattern

Patterns used with RLIKE

RLIKE operator is used with several patterns or regular expressions. Following is the table of patterns that can be used along with the this operator.

Example

The following example uses the RLIKE operator to retrieve records with the help of regular expressions. To do so, we are first creating a table named CUSTOMERS using the following query −

CREATE TABLE CUSTOMERS (
   ID INT AUTO_INCREMENT,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

Now, insert some values into the above created table using the INSERT statements given below −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES
(1, ''Ramesh'', 32, ''Ahmedabad'', 2000.00 ),
(2, ''Khilan'', 25, ''Delhi'', 1500.00 ),
(3, ''Kaushik'', 23, ''Kota'', 2000.00 ),
(4, ''Chaitali'', 25, ''Mumbai'', 6500.00 ),
(5, ''Hardik'', 27, ''Bhopal'', 8500.00 ),
(6, ''Komal'', 22, ''Hyderabad'', 4500.00 ),
(7, ''Muffy'', 24, ''Indore'', 10000.00 );

Execute the following query to display all the records present in the CUSTOMERS table −

SELECT * FROM CUSTOMERS;

Following are the records present in CUSTOMERS table −

RLIKE with Patterns −

In the following query, we are finding all the records from CUSTOMERS table whose name starts with ”ch” −

SELECT * FROM CUSTOMERS WHERE NAME RLIKE ''^ch

Executing the query above will produce the following output −

The following query displays all the records whose names ends with ”sh” −

SELECT NAME FROM CUSTOMERS WHERE NAME RLIKE ''sh$

Following are records whose name ends with ”sh” −

Here, we are retrieving the records that have names containing ”an” −

SELECT NAME FROM CUSTOMERS WHERE NAME RLIKE ''an

Following are the records −

This following query retrieves all the records whose names are ending with an vowel −

SELECT NAME FROM CUSTOMERS WHERE NAME RLIKE ''[aeiou]$

Following are the records −

The below query finds all the names starting with a consonant and ending with ”ya” −

SELECT NAME FROM CUSTOMERS WHERE NAME RLIKE ''^[^aeiou].*ya$

As we observe the output, there are no records that starts with consonant and ends with ”ya”.

Empty set (0.00 sec)

RLIKE On Strings

The RLIKE operator can perform pattern matching not only on database tables but also on individual strings. Here, the result will obtain as 1 if the pattern exists in the given string, or 0 if it doesn”t. The result is retrieved as a result-set using the SQL SELECT statement.

Syntax

Following is the basic syntax of the RLIKE operator in MySQL −

SELECT expression RLIKE pattern;

Example

In the following example, we are using the RLIKE query to check if a pattern exists in an individual string or not −

SELECT ''Welcome To Tutorialspoint!'' RLIKE ''To

The result-set will contain 1 because the pattern ”TO” exists in the specifed string.

Here, the pattern ”Hello” does not exist in the specifed string, thus it returns 0 as output.

SELECT ''Welcome To Tutorialspoint!'' RLIKE ''Hello

Executing the query above will produce the following output −

Example

REGEXP is alternative syntax to the RLIKE in MySQL. Both the operators have same result.

In the below query, if the given pattern is not found in the specifed string, this operator returns 0 −

SELECT ''Welcome to Tutorialspoint'' REGEXP ''unknown

Following is the output −

Here, the pattern ”is” does not exist in the specifed string, thus it returns 1 as output.

SELECT ''This is a sample string'' REGEXP ''is

Executing the query above will produce the following output −

Example

If either of the first two operands is NULL, the RLIKE operator returns NULL.

SELECT NULL RLIKE ''value

Following is the output −

Here, the pattern we are searching is NULL, thus the output will also be NULL.

SELECT ''Tutorialspoint'' RLIKE NULL;

Executing the query above will produce the following output −

Example

If you use the NOT clause before RLIKE operator, it returns 0 in case of a match else returns 1 (reverse of the original return values).

SELECT NOT ''This is a sample string'' RLIKE ''is

Following is the output −

Here, the pattern ”unknown” is not present in the specifed string, thus the following query returns 1 as output.

SELECT NOT ''Welcome to Tutorialspoint'' REGEXP ''unknown

Executing the query above will produce the following output −

RLIKE Operator Using a Client Program

We can also perform the MySQL RLike operator using the client programs to search data in a database using patterns (or regular expressions).

Syntax

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

$sql = "SELECT * FROM person_tbl WHERE NAME RLIKE ''sh$''";
$mysqli->query($sql);

sql = "SELECT * FROM person_tbl WHERE NAME RLIKE ''sh$''";
con.query(sql);

String sql = "SELECT NAME FROM person_tbl WHERE NAME RLIKE ''^sa''";
statement.executeQuery(sql);

sql = ''SELECT NAME FROM person_tbl WHERE NAME RLIKE ''^sa''''
cursorObj.execute(sql)

Example

Following are the programs −

$dbhost = ''localhost
$dbuser = ''root
$dbpass = ''password
$db = ''TUTORIALS
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $db);
if ($mysqli->connect_errno) {
    printf("Connect failed: %s
", $mysqli->connect_error);
    exit();
}
//printf(''Connected successfully.
'');
$sql = "SELECT * FROM person_tbl WHERE NAME RLIKE ''sh$''";
if($result = $mysqli->query($sql)){
    printf("Table records: n");
    while($row = mysqli_fetch_array($result)){
        printf("Id %d, Name %s, Age %d, Address %s",
        $row[''ID''],
        $row[''NAME''],
        $row[''AGE''],
        $row[''ADDRESS'']);
        printf("n");
    }
}
if($mysqli->error){
    printf("Error message: ", $mysqli->error);
}
$mysqli->close();

Table records:
Id 3, Name Santosh, Age 34, Address Hyderabad
Id 6, Name Ramesh, Age 40, Address Mumbai

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

 //Connecting to MySQL
 con.connect(function(err) {
 if (err) throw err;
  //console.log("Connected successfully...!");
  //console.log("--------------------------");
 sql = "USE TUTORIALS";
 con.query(sql);
 sql = "SELECT * FROM person_tbl WHERE NAME RLIKE ''sh$''";
 console.log("Select query executed successfully..!");
 console.log("Table records: ");
 con.query(sql);
 con.query(sql, function(err, result){
 if (err) throw err;
 console.log(result);
 });
});

Select query executed successfully..!
Table records:
[
  { ID: 3, NAME: ''Santosh'', AGE: 34, ADDRESS: ''Hyderabad'' },
  { ID: 6, NAME: ''Ramesh'', AGE: 40, ADDRESS: ''Mumbai'' }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class RlikeOperator {
    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...!");
            String sql = "SELECT NAME FROM person_tbl WHERE NAME RLIKE ''^sa''";
            rs = st.executeQuery(sql);
            System.out.println("Table records: ");
            while(rs.next()) {
                String name = rs.getString("Name");
                System.out.println("Name: " + name);
            }
        }catch(Exception e) {
            e.printStackTrace();
        }
    }
}

Table records:
Name: Santosh

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
#Creating a cursor object
cursorObj = connection.cursor()
rlike_operator_query = f"SELECT NAME FROM person_tbl WHERE NAME RLIKE ''^sa''"
cursorObj.execute(rlike_operator_query)
result = cursorObj.fetchall()
print("Names that start with ''sa'':")
for row in result:
    print(row[0])
cursorObj.close()
connection.close()

Names that start with ''sa'':
Santosh

MySQL NOT LIKE Operator

We have previously learned that the LIKE Operator in MySQL database is a logical operator used to perform pattern matching operation on a database table. And NOT LIKE Operator is defined as opposite of this LIKE operator.

Both LIKE and NOT LIKE operators perform pattern matching in a database table. Thus, they both need wildcards and patterns to function. However, if the LIKE operator is used to find the similar patterns mentioned using the wildcards, NOT LIKE operator is used to find all the records that do not contain the specified pattern.

• The NOT LIKE operator is nothing but the amalgamation of two SQL operators, NOT and LIKE operators. Thus, having the combination of their functionalities.

• It is used to match a particular pattern in the given string and returns 0 in case of a match and returns 1 otherwise. If either of the two operands of this function is NULL, it returns NULL as result.

• This operator is useful for finding strings that do not match a specific pattern or do not have certain characteristics.

Syntax

Following is the basic syntax of MySQL NOT LIKE operator with a SELECT statement −

SELECT column_name(s) FROM table_name
WHERE column_name NOT LIKE [condition];

Using NOT LIKE Operator with Wildcards

Wildcards are special characters used in SQL queries to match patterns in the data. Following wildcards can be used in conjunction with the NOT LIKE operator −

Note: In the NOT LIKE operator, the above wildcard characters can be used individually as well as in combinations with each other. The two mainly used wildcard characters are ”%” and ”_”.

Example

Let us begin by creating a table named CUSTOMERS using the following query −

CREATE TABLE CUSTOMERS (
   ID INT AUTO_INCREMENT,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

Using the below INSERT statements, we are inserting records into the above created table −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES
(1, ''Ramesh'', 32, ''Ahmedabad'', 2000.00 ),
(2, ''Khilan'', 25, ''Delhi'', 1500.00 ),
(3, ''Kaushik'', 23, ''Kota'', 2000.00 ),
(4, ''Chaitali'', 25, ''Mumbai'', 6500.00 ),
(5, ''Hardik'', 27, ''Bhopal'', 8500.00 ),
(6, ''Komal'', 22, ''Hyderabad'', 4500.00 ),
(7, ''Muffy'', 24, ''Indore'', 10000.00 );

Execute the below query to display all the records present in the CUSTOMERS table −

Select * from CUSTOMERS;

Following is the CUSTOMERS table −

Now, let us use the MySQL NOTLIKE operator to displays the all the records in CUSTOMERS table whose name doesn”t starts with ”k”.

SELECT * FROM CUSTOMERS where NAME NOT LIKE ''k%

Following are the records whose name doesn”t starts with ”k” −

The following query displays the records of customers whose NAME doesn”t end with ”ik”.

SELECT * FROM CUSTOMERS where NAME NOT LIKE ''%ik

Following are the records whose name doesn”t ends with ”ik” −

Here, we are displaying all the records whose name does not contains the substring ”al”.

SELECT * FROM CUSTOMERS where NAME NOT LIKE ''%al%

Following are the records whose name doesn”t contains the substring ”al” −

The following query displays all the records whose name does not starts with ”m” and ends with ”y”.

SELECT * FROM CUSTOMERS WHERE NAME NOT LIKE ''m___y

As we can see in the output table, the seventh record is eliminated because the name starts with ”m” and ends with ”y”.

The below query displays all customer names that does not start with ”k” and have exactly 6 characters.

SELECT * FROM CUSTOMERS WHERE name NOT LIKE ''k_____

Following is the output −

Here, we are displaying the records of CUSTOMERS table, where the second character of the records in ADDRESS column is not “h”.

SELECT * FROM CUSTOMERS where ADDRESS NOT LIKE ''_h%

Following is the output −

Using NOT LIKE Operator with AND/OR Operators

We can use the MySQL NOT LIKE operator with different string patterns to choose rows, combining them with the AND or OR operators.

Syntax

Following is the syntax of using NOT LIKE operator with AND/OR operator −

SELECT column_name(s)
FROM table_name
WHERE column1 NOT LIKE pattern1 [AND|OR] column2 NOT LIKE pattern2 [AND|OR] ...;

Example

In the following example, we are displaying all records from the CUSTOMERS table where name does not start with ”k” and the address should not start with ”m” using AND operator −

SELECT * FROM CUSTOMERS
WHERE name NOT LIKE ''k%'' AND address NOT LIKE ''m%

Output

Executing the query above will produce the following output −

NOT LIKE Operator on Strings

The MySQL NOT LIKE operator can perform pattern matching not only on database tables but also on individual strings. Here, the result will obtain as 0 if the pattern exists in the given string, or 1 if it doesn”t. The result is retrieved as a result-set using the SQL SELECT statement.

Syntax

Following is the syntax of NOT LIKE operator in MySQL −

SELECT expression NOT LIKE pattern;

Example

In the following query, the pattern ”Tutorix” is not present in the specified string. So, this operator will return 1.

SELECT ''Tutorialspoint'' NOT LIKE ''Tutorix

Executing the query above will produce the following output −

Here, the pattern ”Tutorialspoint” is present in the specified string. Thus, it returns 0 as output.

SELECT ''Tutorialspoint'' NOT LIKE ''Tutorialspoint

Following is the output −

Example

If either (string or pattern operands) is NULL, this operator returns NULL. In the following query, the string is NULL, so that the output will be returned as NULL.

SELECT NULL NOT LIKE ''value

Executing the query above will produce the following output −

Here, the search pattern is NULL. So, the output will be returned as NULL.

SELECT ''Tutorialspoint'' NOT LIKE NULL;

Following is the output −

NOT LIKE Operator Using a Client Program

Besides using MySQL queries to perform the Not Like operator, we can also use client programs like Node.js, PHP, Java, and Python to achieve the same result.

Syntax

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

$sql = "SELECT * FROM EMP where Name NOT LIKE ''Su%''";
$mysqli->query($sql);

sql = "SELECT * FROM tutorials_tbl where tutorial_author Not like ''Jo%''";
con.query(sql);

String sql = "SELECT * FROM EMP where Name NOT LIKE ''Su%''";
statement.executeQuery(sql);

sql = "SELECT * FROM EMP where Name NOT LIKE ''Su%''"
cursorObj.execute(sql)

Example

Following are the programs −

$dbhost = ''localhost
$dbuser = ''root
$dbpass = ''password
$db = ''TUTORIALS
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $db);
if ($mysqli->connect_errno) {
    printf("Connect failed: %s
", $mysqli->connect_error);
    exit();
}
//printf(''Connected successfully.
'');
$sql = "SELECT * FROM EMP where Name NOT LIKE ''Su%''";
if($result = $mysqli->query($sql)){
    printf("Table records: n");
    while($row = mysqli_fetch_array($result)){
        printf("ID %d, Name %s, DOB %s, Location %s",
        $row[''ID''],
        $row[''Name''],
        $row[''DOB''],
        $row[''Location''],);
        printf("n");
    }
}
if($mysqli->error){
    printf("Error message: ", $mysqli->error);
}
$mysqli->close();

Table records:
ID 101, Name Amit, DOB 1970-01-08, Location Hyderabad
ID 0, Name Raja, DOB 1980-11-06, Location Goa
ID 109, Name Javed, DOB 1980-11-06, Location pune
ID 120, Name Vani, DOB 1980-11-06, Location Delhi
ID 0, Name Devi, DOB 1980-11-06, Location Goa

var mysql = require(''mysql2'');
var con = mysql.createConnection({
host:"localhost",
user:"root",
password:"password"
});
 //Connecting to MySQL
 con.connect(function(err) {
 if (err) throw err;
  //console.log("Connected successfully...!");
  //console.log("--------------------------");
 sql = "USE TUTORIALS";
 con.query(sql);
 sql = "SELECT * FROM tutorials_tbl where tutorial_author Not like ''Jo%''";
 console.log("Select query executed successfully..!");
 console.log("Table records: ");
 con.query(sql);
 con.query(sql, function(err, result){
 if (err) throw err;
 console.log(result);
 });
});

Select query executed successfully..!
Table records:
[
  {
    tutorial_id: 2,
    tutorial_title: ''Angular Java'',
    tutorial_author: ''Abdul S'',
    submission_date: 2023-08-07T18:30:00.000Z
  },
  {
    tutorial_id: 3,
    tutorial_title: ''Learning Java'',
    tutorial_author: ''Sanjay'',
    submission_date: 2007-05-05T18:30:00.000Z
  },
  {
    tutorial_id: 4,
    tutorial_title: ''Python Tutorial'',
    tutorial_author: ''Sasha Lee'',
    submission_date: 2016-09-03T18:30:00.000Z
  },
  {
    tutorial_id: 5,
    tutorial_title: ''Hadoop Tutorial'',
    tutorial_author: ''Chris Welsh'',
    submission_date: 2023-08-07T18:30:00.000Z
  }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class NotLikeOperator {
   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...!");
            String sql = "SELECT * FROM EMP where Name NOT LIKE ''Su%''";
            rs = st.executeQuery(sql);
            System.out.println("Table records: ");
            while(rs.next()) {
               String id = rs.getString("id");
               String name = rs.getString("Name");
               String dob = rs.getString("dob");
               String location = rs.getString("location");
               System.out.println("Id: " + id + ", Name: " + name + ", Dob: " + dob + ", Location: " + location);
            }
      }catch(Exception e) {
         e.printStackTrace();
      }
   }
}

Table records:
Id: 101, Name: Amit, Dob: 1970-01-08, Location: Hyderabad
Id: MyID2, Name: Raja, Dob: 1980-11-06, Location: Goa
Id: MyID2, Name: Raja, Dob: 1980-11-06, Location: Goa
Id: 109, Name: Javed, Dob: 1980-11-06, Location: pune
Id: 120, Name: Vani, Dob: 1980-11-06, Location: Delhi
Id: oo1, Name: Devi, Dob: 1980-11-06, Location: Goa

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
#Creating a cursor object
cursorObj = connection.cursor()
notlike_operator_query = f"SELECT * FROM EMP where Name NOT LIKE ''Su%''"
cursorObj.execute(notlike_operator_query)
# Fetching all the results
results = cursorObj.fetchall()
# display the results
print("Employees whose name does not start with ''Su'':")
for row in results:
    print(row)
cursorObj.close()
connection.close()

Employees whose name does not start with ''Su'':
(''101'', ''Amit'', datetime.date(1970, 1, 8), ''Hyderabad'')
(''MyID2'', ''Raja'', datetime.date(1980, 11, 6), ''Goa'')
(''109'', ''Javed'', datetime.date(1980, 11, 6), ''pune'')
(''120'', ''Vani'', datetime.date(1980, 11, 6), ''Delhi'')
(''oo1'', ''Devi'', datetime.date(1980, 11, 6), ''Goa'')

MySQL NOT REGEXP Operator

Technically, a regular expression is defined as a sequence of characters that represent a pattern in an input text. It is used to locate or replace text strings using some patterns; this pattern can either be a single character, multiple characters or words, etc.

In MySQL, the REGEXP operator is a powerful tool to perform complex search operations in a database to retrieve required data using regular expressions. And unlike the LIKE operator, the REGEXP operator is not restricted on search patterns (like % and _), as it uses several other meta characters to expand the flexibility and control during pattern matching.

NOT REGEXP Operator is a negation to this REGEXP operator. It is used to retrieve all the records present in a database that do not satisfy the specified pattern. Let us learn about this operator in detail further in this chapter.

Syntax

Following is the basic syntax of the MySQL NOT REGEXP operator −

expression NOT REGEXP pattern

Note − Since this is just a negation, the functionality of a NOT REGEXP operator will be the same as a REGEXP operator.

Examples

Let us start by creating a table named CUSTOMERS using the following query −

CREATE TABLE CUSTOMERS (
   ID INT AUTO_INCREMENT,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

The below INSERT statement inserts 7 records into the above created table −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES
(1, ''Ramesh'', 32, ''Ahmedabad'', 2000.00 ),
(2, ''Khilan'', 25, ''Delhi'', 1500.00 ),
(3, ''Kaushik'', 23, ''Kota'', 2000.00 ),
(4, ''Chaitali'', 25, ''Mumbai'', 6500.00 ),
(5, ''Hardik'', 27, ''Bhopal'', 8500.00 ),
(6, ''Komal'', 22, ''Hyderabad'', 4500.00 ),
(7, ''Muffy'', 24, ''Indore'', 10000.00 );

Execute the following query to display all the records present in CUSTOMERS table −

Select * from CUSTOMERS;

Following is the CUSTOMERS table −

Now, let us show the usage of NOT REGEXPM operator using several queries on this table.

Query to find all the names not starting with ”ra” −

SELECT * FROM CUSTOMERS WHERE NAME NOT REGEXP ''^ra

Following is the output −

Query to retrieve all the records whose names not ending with ”ik” −

SELECT * FROM CUSTOMERS WHERE NAME NOT REGEXP ''ik$

Following is the output −

Following is the query to find all the names that do not contain ”an” −

SELECT * FROM CUSTOMERS WHERE NAME NOT REGEXP ''an

Following is the output −

Example

The NOT RLIKE is alternative syntax to the NOT REGEXP in MySQL. Both the operators have same result. If either of the first two operands is NULL, this operator returns NULL.

In the below query, the string value is NULL. Thus it gives output as NULL.

SELECT NULL NOT RLIKE ''value

Following is the output −

Here, the specified pattern is NULL. So, the output will be retrieved as NULL.

SELECT ''Tutorialspoint'' NOT REGEXP NULL;

Following is the output −

NOT REGEXP Operator Using a Client Program

Besides using MySQL queries to perform the NOT REGEXP operator, we can also use client programs such as PHP, Node.js, Java, and Python to achieve the same result.

Syntax

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

$sql = "SELECT * FROM person_tbl WHERE NAME NOT REGEXP ''^sa''";
$mysqli->query($sql);

sql = "SELECT * FROM person_tbl WHERE NAME NOT REGEXP ''^sa''";
con.query(sql);

String sql = "SELECT * FROM person_tbl WHERE NAME NOT REGEXP ''^sa''";
statement.executeQuery(sql);

sql = "SELECT * FROM person_tbl WHERE NAME NOT REGEXP ''^sa''"
cursorObj.execute(sql)

Example

Following are the programs −

$dbhost = ''localhost
$dbuser = ''root
$dbpass = ''password
$db = ''TUTORIALS
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $db);
if ($mysqli->connect_errno) {
    printf("Connect failed: %s
", $mysqli->connect_error);
    exit();
}
//printf(''Connected successfully.
'');
$sql = "SELECT * FROM person_tbl WHERE NAME NOT REGEXP ''^sa''";
if($result = $mysqli->query($sql)){
    printf("Table records(where the name does not start with ''sa''): n");
    while($row = mysqli_fetch_array($result)){
        printf("ID %d, Name %s, Age %d, Address %s",
        $row[''ID''],
        $row[''NAME''],
        $row[''AGE''],
        $row[''ADDRESS''],);
        printf("n");
    }
}
if($mysqli->error){
    printf("Error message: ", $mysqli->error);
}
$mysqli->close();

Table records(where the name does not start with ''sa''):
ID 1, Name John, Age 33, Address New York
ID 2, Name Ram, Age 29, Address Pune
ID 4, Name Tanya, Age 26, Address Paris
ID 5, Name Anmol, Age 28, Address Surat
ID 6, Name Ramesh, Age 40, Address Mumbai

var mysql = require(''mysql2'');
var con = mysql.createConnection({
host:"localhost",
user:"root",
password:"password"
});
 //Connecting to MySQL
 con.connect(function(err) {
 if (err) throw err;
  //console.log("Connected successfully...!");
  //console.log("--------------------------");
 sql = "USE TUTORIALS";
 con.query(sql);
 sql = "SELECT * FROM person_tbl WHERE NAME NOT REGEXP ''^sa''";
 console.log("Select query executed successfully..!");
 console.log("Table records: ");
 con.query(sql);
 con.query(sql, function(err, result){
 if (err) throw err;
 console.log(result);
 });
});

Select query executed successfully..!
Table records:
[
  { ID: 1, NAME: ''John'', AGE: 33, ADDRESS: ''New York'' },
  { ID: 2, NAME: ''Ram'', AGE: 29, ADDRESS: ''Pune'' },
  { ID: 4, NAME: ''Tanya'', AGE: 26, ADDRESS: ''Paris'' },
  { ID: 5, NAME: ''Anmol'', AGE: 28, ADDRESS: ''Surat'' },
  { ID: 6, NAME: ''Ramesh'', AGE: 40, ADDRESS: ''Mumbai'' }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class regexp_not {
    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...!");
            String sql = "SELECT * FROM person_tbl WHERE NAME NOT REGEXP ''^sa''";
            rs = st.executeQuery(sql);
            System.out.println("Table records: ");
            while(rs.next()) {
                String id = rs.getString("id");
                String name = rs.getString("Name");
                String age = rs.getString("AGE");
                String address = rs.getString("ADDRESS");
                System.out.println("Id: " + id + ", Name: " + name + ", Age: " + age + ", Address: " + address);
            }
        }catch(Exception e) {
            e.printStackTrace();
        }
    }
}

Table records:
Id: 1, Name: John, Age: 33, Address: New York
Id: 2, Name: Ram, Age: 29, Address: Pune
Id: 4, Name: Tanya, Age: 26, Address: Paris
Id: 5, Name: Anmol, Age: 28, Address: Surat
Id: 6, Name: Ramesh, Age: 40, Address: Mumbai

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
   host=''localhost'',
   user=''root'',
   password=''password'',
   database=''tut''
)
#Creating a cursor object
cursorObj = connection.cursor()
notregexp_operator_query = f"SELECT * FROM person_tbl WHERE NAME NOT REGEXP ''^sa''"
cursorObj.execute(notregexp_operator_query)
# Fetching all the results
results = cursorObj.fetchall()
# display the results
print("Persons whose name does not start with ''sa'' using NOT REGEXP:")
for row in results:
    print(row)
cursorObj.close()
connection.close()

Persons whose name does not start with ''sa'' using NOT REGEXP:
(1, ''John'', 33, ''New York'')
(2, ''Ram'', 29, ''Pune'')
(4, ''Tanya'', 26, ''Paris'')
(5, ''Anmol'', 28, ''Surat'')
(6, ''Ramesh'', 40, ''Mumbai'')

MySQL supports various types of pattern matching operations to retrieve filtered result-sets from huge database tables. But, pattern matching with regular expressions is a powerful way to perform a complex search.

As we have seen in the previous chapter, the MySQL regexp_instr() function is used to return the position of the pattern found. But if you want to just detect whether the pattern is present in the data or not, you can use the regexp_like() function.

MySQL REGEXP_LIKE() Function

The MySQL regexp_like() function is also used to search for a string that is matched with specified patterns. This function returns 1 if this string matches the specified pattern, 0 if there is no match, or NULL if the string or the pattern is NULL. The pattern used in this function can be an extended regular expression and not just an ordinary string.

Syntax

Following is the syntax of the MySQL regexp_like() function −

REGEXP_LIKE(expr, pattern[, match_type])

Parameters

The regexp_like() function takes following parameter values −

• expr: The string in which search is performed

• pattern: The pattern that is searched in the string

• match_type: (Optional argument) A string that specifies how to perform matching; includes case-sensitive matching(c), case-insensitive matching(i), multiple-line mode(m), matching line terminators(n), matching Unix-only line endings(u).

Example

In this example, we are performing a search operation on a simple string using the MySQL REGEXP_LIKE() function −

SELECT REGEXP_LIKE(''Welcome To Tutorialspoint!'', ''To'')
AS RESULT;

The search pattern ”To” is present in the string, so it returned 1 as output.

Now, if there is no match found in the string, the result will be obtained as ”0” as shown below −

SELECT REGEXP_LIKE(''Welcome To Tutorialspoint!'', ''Hello'')
AS RESULT;

Following is the output −

Let us also pass the optional arguments to this function as case-sensitive matching(c) and observe the result −

SELECT REGEXP_LIKE(''Welcome To Tutorialspoint!'', ''t'', ''c'')
AS RESULT;

Executing the query above will produce the following output −

Example

If either of the first two arguments passed to this function is NULL, this function returns NULL. In the below query, we are passing NULL to the string parameter.

SELECT REGEXP_LIKE(NULL, ''value'') AS Result;

Following is the output −

Here, we are passing NULL as the search pattern −

SELECT REGEXP_LIKE(''Welcome to Tutorialspoint'', NULL)
AS Result;

Executing the query above will produce the following output −

Example

In another example, let us perform a search operation on a database table named CUSTOMERS using the REGEXP_LIKE() function. Firstly, let us create the table using the following query −

CREATE TABLE CUSTOMERS (
   ID INT AUTO_INCREMENT,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

The following query inserts 7 records into the above created table −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES
(1, ''Ramesh'', 32, ''Ahmedabad'', 2000.00 ),
(2, ''Khilan'', 25, ''Delhi'', 1500.00 ),
(3, ''Kaushik'', 23, ''Kota'', 2000.00 ),
(4, ''Chaitali'', 25, ''Mumbai'', 6500.00 ),
(5, ''Hardik'', 27, ''Bhopal'', 8500.00 ),
(6, ''Komal'', 22, ''Hyderabad'', 4500.00 ),
(7, ''Muffy'', 24, ''Indore'', 10000.00 );

Execute the following query to display all the records of CUSTOMERS table −

Select * from CUSTOMERS;

Following is the CUSTOMERS table −

The following query selects records from the CUSTOMERS table where the NAME column starts with the letter ”K” −

SELECT REGEXP_LIKE(NAME, ''^K'')
AS RESULT FROM CUSTOMERS;

If there is a name that starts with letter ”K” it gives 1 as output, else 0 −

The following query checks whether the ”ADDRESS” column in the ”CUSTOMERS” table contains the letter ”K” (case-insensitive). If the address contains ”K” or ”k,” the result is 1; otherwise, it”s 0.

SELECT REGEXP_LIKE(ADDRESS, ''R'', ''i'')
AS RESULT FROM CUSTOMERS;

As we can see in the output table, 6th and 7th row in ADDRESS column contains a letter ”K” (case-insensitive) −

REGEXP_LIKE() Function Using a Client Program

Besides using MySQL queries to perform the REGEXP_Like() function, we can also use client programs such as PHP, Node.js, Java, and Python to achieve the same result.

Syntax

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

$sql = "SELECT REGEXP_LIKE(''Welcome To Tutorialspoint!'', ''To'') AS RESULT";
$mysqli->query($sql);

sql = "SELECT REGEXP_LIKE(''Welcome To Tutorialspoint!'', ''To'') AS RESULT";
con.query(sql);

String sql = "SELECT REGEXP_LIKE(''Welcome To Tutorialspoint!'', ''To'') AS RESULT";
statement.executeQuery(sql);

sql = "SELECT REGEXP_LIKE(''Welcome To Tutorialspoint!'', ''To'') AS RESULT"
cursorObj.execute(sql)

Example

Following are the programs −

$dbhost = ''localhost
$dbuser = ''root
$dbpass = ''password
$db = ''TUTORIALS
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $db);
if ($mysqli->connect_errno) {
    printf("Connect failed: %s
", $mysqli->connect_error);
    exit();
}
//printf(''Connected successfully.
'');
$sql = "SELECT REGEXP_LIKE(''Welcome To Tutorialspoint!'', ''To'') AS RESULT";
if($result = $mysqli->query($sql)){
    while($row = mysqli_fetch_array($result)){
        printf("Result: %d", $row[''RESULT'']);
    }
}
if($mysqli->error){
    printf("Error message: ", $mysqli->error);
}
$mysqli->close();

Result: 1

var mysql = require(''mysql2'');
var con = mysql.createConnection({
host:"localhost",
user:"root",
password:"password"
});
 //Connecting to MySQL
 con.connect(function(err) {
 if (err) throw err;
  //console.log("Connected successfully...!");
  //console.log("--------------------------");
 sql = "USE TUTORIALS";
 con.query(sql);
 sql = "SELECT REGEXP_LIKE(''Welcome To Tutorialspoint!'', ''To'') AS RESULT";
 console.log("Select query executed successfully..!");
 console.log("Table records: ");
 con.query(sql);
 con.query(sql, function(err, result){
 if (err) throw err;
 console.log(result);
 });
});

Select query executed successfully..!
Table records:
[ { RESULT: 1 } ]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class regexp_like {
    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...!");
            String sql = "SELECT REGEXP_LIKE(''Welcome To Tutorialspoint!'', ''To'') AS RESULT";
            rs = st.executeQuery(sql);
            while(rs.next()) {
                String result = rs.getString("RESULT");
                System.out.println("Result: " + result);
            }
        }catch(Exception e) {
            e.printStackTrace();
        }
    }
}

Result: 1

import mysql.connector
# Establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
# Creating a cursor object
cursorObj = connection.cursor()
regexp_like_query = f"SELECT REGEXP_LIKE(''Welcome To Tutorialspoint!'', ''To'') AS RESULT"
cursorObj.execute(regexp_like_query)
# Fetching all the results
results = cursorObj.fetchall()
# Display the result
print("Result of REGEXP_LIKE() Function:")
for row in results:
    result = row[0]
    if result:
        print("The pattern ''To'' is found in the given string.")
    else:
        print("The pattern ''To'' is not found in the given string.")
cursorObj.close()
connection.close()

Result of REGEXP_LIKE() Function:
The pattern ''To'' is found in the given string.

Data types are referred as the type of data, like string, integer, date-time etc., that can be stored in a column (field) of a table. Properly defining the fields in a table is important to the overall optimization of your database. You should use only the type and size of field you really need to use.

When defining columns in a database, choose a data type that matches the actual size and type of data you wish to store, rather than unnecessarily allocating more space than needed.

MySQL Data Types

In MySQL, data types is categorized into three main groups −

• Numeric
• Date and Time
• String Types.

Let us now discuss them in detail.

Numeric Data Types

Numeric data types are used to store numeric values, including integers and decimal numbers. MySQL uses all the standard ANSI SQL numeric data types.

The following list shows the common MySQL numeric data types and their descriptions −

• INT − A normal-sized integer that can be signed or unsigned. If signed, the allowable range is from -2147483648 to 2147483647. If unsigned, the allowable range is from 0 to 4294967295. You can specify a width of up to 11 digits.

• TINYINT − A very small integer that can be signed or unsigned. If signed, the allowable range is from -128 to 127. If unsigned, the allowable range is from 0 to 255. You can specify a width of up to 4 digits.

• SMALLINT − A small integer that can be signed or unsigned. If signed, the allowable range is from -32768 to 32767. If unsigned, the allowable range is from 0 to 65535. You can specify a width of up to 5 digits.

• MEDIUMINT − A medium-sized integer that can be signed or unsigned. If signed, the allowable range is from -8388608 to 8388607. If unsigned, the allowable range is from 0 to 16777215. You can specify a width of up to 9 digits.

• BIGINT − A large integer that can be signed or unsigned. If signed, the allowable range is from -9223372036854775808 to 9223372036854775807. If unsigned, the allowable range is from 0 to 18446744073709551615. You can specify a width of up to 20 digits.

• FLOAT(M,D) − A floating-point number that cannot be unsigned. You can define the display length (M) and the number of decimals (D). This is not required and will default to 10,2, where 2 is the number of decimals and 10 is the total number of digits (including decimals). Decimal precision can go to 24 places for a FLOAT.

• DOUBLE(M,D) − A double precision floating-point number that cannot be unsigned. You can define the display length (M) and the number of decimals (D). This is not required and will default to 16,4, where 4 is the number of decimals. Decimal precision can go to 53 places for a DOUBLE. REAL is a synonym for DOUBLE.

• DECIMAL(M,D) − An unpacked floating-point number that cannot be unsigned. In the unpacked decimals, each decimal corresponds to one byte. Defining the display length (M) and the number of decimals (D) is required. NUMERIC is a synonym for DECIMAL.

Date and Time Data Types

Date and time data types in MySQL are used to store temporal data, including dates, times, and combinations of both. These data types are essential for handling date and time-related information accurately.

The MySQL date and time data types are as follows −

• DATE − A date in YYYY-MM-DD format, between 1000-01-01 and 9999-12-31. For example, December 30^th, 1973 would be stored as 1973-12-30.

• DATETIME − A date and time combination in YYYY-MM-DD HH:MM:SS format, between 1000-01-01 00:00:00 and 9999-12-31 23:59:59. For example, 3:30 in the afternoon on December 30^th, 1973 would be stored as 1973-12-30 15:30:00.

• TIMESTAMP − A timestamp between midnight, January 1^st, 1970 and sometime in 2037. This looks like the previous DATETIME format, only without the hyphens between numbers; 3:30 in the afternoon on December 30^th, 1973 would be stored as 19731230153000 ( YYYYMMDDHHMMSS ).

• TIME − Stores the time in a HH:MM:SS format.

• YEAR(M) − Stores a year in a 2-digit or a 4-digit format. If the length is specified as 2 (for example YEAR(2)), YEAR can be between 1970 to 2069 (70 to 69). If the length is specified as 4, then YEAR can be 1901 to 2155. The default length is 4.

String Data Types

String data types in MySQL are used to store text and character-based information. These data types stores textual data of various lengths and formats.

The following list describes the common string data types in MySQL −

• CHAR(M) − A fixed-length string between 1 and 255 characters in length (for example CHAR(5)), right-padded with spaces to the specified length when stored. Defining a length is not required, but the default is 1.

• VARCHAR(M) − A variable-length string between 1 and 255 characters in length. For example, VARCHAR(25). You must define a length when creating a VARCHAR field.

• BLOB or TEXT − A field with a maximum length of 65535 characters. BLOBs are “Binary Large Objects” and are used to store large amounts of binary data, such as images or other types of files. Fields defined as TEXT also hold large amounts of data. The difference between the two is that the sorts and comparisons on the stored data are case sensitive on BLOBs and are not case sensitive in TEXT fields. You do not specify a length with BLOB or TEXT.

• TINYBLOB or TINYTEXT − A BLOB or TEXT column with a maximum length of 255 characters. You do not specify a length with TINYBLOB or TINYTEXT.

• MEDIUMBLOB or MEDIUMTEXT − A BLOB or TEXT column with a maximum length of 16777215 characters. You do not specify a length with MEDIUMBLOB or MEDIUMTEXT.

• LONGBLOB or LONGTEXT − A BLOB or TEXT column with a maximum length of 4294967295 characters. You do not specify a length with LONGBLOB or LONGTEXT.

• ENUM − An enumeration, which is a fancy term for list. When defining an ENUM, you are creating a list of items from which the value must be selected (or it can be NULL). For example, if you wanted your field to contain “A” or “B” or “C”, you would define your ENUM as ENUM (”A”, ”B”, ”C”) and only those values (or NULL) could ever populate that field.

The MySQL Varchar Data Type

The MySQL VARCHAR data type is used to store variable-length character strings, having a length up to 65,535 bytes.

In MySQL, when you store text in a VARCHAR column, it needs a little extra space to keep track of how long the text is. This extra space can be either 1 or 2 bytes, depending on the length of the text. If the text is short (less than 255 characters), it uses 1 byte for length. For longer text, it uses 2 bytes.

The total size of data plus the length info cannot exceed 65,535 bytes for a row in a table.

Example

In the following query, we are creating a new table named test_table that has two columns column1 and column2.

As we can see in the below code block, the columns (column1 = 32765 and column2 = 32766) makes 65531 bytes. These columns will take 2 bytes each as a length prefix. Therefore, the columns totally make 32765+2+32766+2 = 65535 bytes −

CREATE TABLE test_table (
   column1 VARCHAR(32765) NOT NULL,
   column2 VARCHAR(32766) NOT NULL
)CHARACTER SET ''latin1'' COLLATE LATIN1_DANISH_CI;

Output

Following is the output of the above code −

Query OK, 0 rows affected (0.03 sec)

Example

Now, let us create another table test_table2 and provide 32766 and 32766 to both the columns (column1 and column2) −

CREATE TABLE test_table2 (
   column1 VARCHAR(32766) NOT NULL, --error
   column2 VARCHAR(32766) NOT NULL
)CHARACTER SET ''latin1'' COLLATE LATIN1_DANISH_CI;

Output

As we can see in the output below, an error is generated because the row size (32766 +2 +32766 +2 = 65536) exceeds the maximum limit (65,535) −

ERROR 1118 (42000): Row size too large. The maximum row size for the used
table type, not counting BLOBs, is 65535. This includes storage overhead,
check the manual. You have to change some columns to TEXT or BLOBs

Example

Here, we are creating another table named CUSTOMERS using the following query −

CREATE TABLE CUSTOMERS (
   ID int PRIMARY KEY AUTO_INCREMENT,
   NAME VARCHAR(3)
);

Following is the output obtained −

Query OK, 0 rows affected (0.03 sec)

Now, we are inserting a string into NAME column where the length is greater than the length of VARCHAR column −

INSERT INTO CUSTOMERS (NAME) VALUES (''Rahul'');

Output

As a result, MySQL will generate an error given below −

ERROR 1406 (22001): Data too long for column ''NAME'' at row 1

Example

MySQL does not count the trailing spaces when inserting a value. Instead it truncates the trailing spaces.

Let us insert a value into the NAME column that has trailing spaces −

INSERT INTO CUSTOMERS (NAME) VALUES (''ABC '');

Output

As we can see in the output below, MySQL issued a warning −

Query OK, 1 row affected, 1 warning (0.02 sec)

Example

In the following query, we are trying to check the length of the values in NAME column −

SELECT ID, NAME, length(NAME) FROM CUSTOMERS;

The result produced is as follows −

Now, let us execute the below query to display the warnings that issued on the above insertion operation −

SHOW warnings;

The result produced is −

Varchar Datatypes Using a Client Program

In addition to performing datatypes using mysql query, we can also create column of the Varchar datatypes using the client program.

Syntax

$sql ="CREATE TABLE customers (cust_Name VARCHAR(30), cust_Address varchar(50)) ";
$mysqli->query($sql);

sql = "CREATE TABLE customers (cust_Name VARCHAR(30), cust_Address varchar(50))";
con.query(sql);

String sql = "CREATE TABLE customers (cust_Name VARCHAR(30), cust_Address varchar(50))";
statement.execute(sql);

sql = ''CREATE TABLE test_table (column1 VARCHAR(32765) NOT NULL,  column2 VARCHAR(32766) NOT NULL)CHARACTER SET ''latin1'' COLLATE LATIN1_DANISH_CI''
cursorObj.execute(sql)

Example

Following are the programs −

$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 a customer table and use varchar data type with differenet size
$sql = "CREATE TABLE customers (cust_Name VARCHAR(30), cust_Address varchar(50)) ";
if ($mysqli->query($sql)) {
    echo "Table created successfully with varchar data!n";
}
if ($mysqli->errno) {
    printf("table could not create table: %s
", $mysqli->error);
}
$mysqli->close();

Table created successfully with varchar data!

var mysql = require("mysql2");
var con = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "password",
}); //Connecting to MySQL

con.connect(function (err) {
  if (err) throw err;
  //   console.log("Connected successfully...!");
  //   console.log("--------------------------");
  sql = "USE TUTORIALS";
  con.query(sql);

  //create a customer table and use varchar data type with differenet size
  sql = "CREATE TABLE customers (cust_Name VARCHAR(30), cust_Address varchar(50)) ";
  con.query(sql);

  sql = `SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = ''customers'' AND COLUMN_NAME = ''cust_Name''`;
  con.query(sql, function (err, result) {
    if (err) throw err;
    console.log(result);
  });
});

[ { DATA_TYPE: ''varchar'' } ]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class Varchar {
   public static void main(String[] args) {
      String url = "jdbc:mysql://localhost:3306/TUTORIALS";
      String username = "root";
      String password = "password";
      try {
         Class.forName("com.mysql.cj.jdbc.Driver");
         Connection connection = DriverManager.getConnection(url, username, password);
         Statement statement = connection.createStatement();
         System.out.println("Connected successfully...!");

         //varchar data types...!;
         String sql = "CREATE TABLE customers (cust_Name VARCHAR(30), cust_Address varchar(50))";
         statement.execute(sql);
         System.out.println("column of a varchar type created successfully...!");
         ResultSet resultSet = statement.executeQuery("DESCRIBE customers");
         while (resultSet.next()){
            System.out.println(resultSet.getString(1)+" "+resultSet.getString(2));
         }
         connection.close();
      } catch (Exception e) {
         System.out.println(e);
      }
   }
}

Connected successfully...!
column of a varchar type created successfully...!
cust_Name varchar(30)
cust_Address varchar(50)

import mysql.connector
# Establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
# Creating a cursor object
cursorObj = connection.cursor()
# Create table with varchar column
sql = ''''''
CREATE TABLE test_table (
column1 VARCHAR(32765) NOT NULL,
column2 VARCHAR(32766) NOT NULL
)
CHARACTER SET ''latin1'' COLLATE LATIN1_DANISH_CI
''''''
cursorObj.execute(sql)
print("The table is created successfully!")
# Insert data into the created table
insert_query = "INSERT INTO test_table (column1, column2) VALUES (%s, %s)"
data_to_insert = ("History", "Geography")
cursorObj.execute(insert_query, data_to_insert)
# Commit the changes after the insert operation
connection.commit()
print("Rows inserted successfully.")
# Now display the table records
select_query = "SELECT * FROM test_table"
cursorObj.execute(select_query)
result = cursorObj.fetchall()
print("Table Data:")
for row in result:
    print(row)
cursorObj.close()
connection.close()

The table is created successfully!
Rows inserted successfully.
Table Data:
(''History'', ''Geography'')

ENUM (Enumerator) is a user defined datatype which stores a list of values as strings. These values are specified when you define the ENUM column. The user can choose values from this predefined list while inserting values into this column.

Each string value defined in an ENUM column is implicitly assigned a numerical value starting from 1. These numerical values are used internally by MySQL to represent the ENUM values.

The MySQL ENUM Data Type

The MySQL ENUM data type allow you to select one or more values from a predefined list during insertion or update operations. The selected values are stored as strings in the table, and when you retrieve data from the ENUM column, the values are presented in a human-readable format.

ENUM columns can accept values of various data types, including integers, floating-point numbers, decimals, and strings. However, internally, MySQL will convert these values to the closest matching ENUM value based on its predefined list.

Syntax

Following is the syntax to define the ENUM data type on a column −

CREATE TABLE table_name (
   Column1,
   Column2 ENUM (''value1'',''value2'',''value3'', ...),
   Column3...
);

Note: An enum column can have maximum 65,535 values.

Attributes of ENUM

The ENUM datatype in MySQL has three attributes. The same is described below −

• Default − The default value of enum data type is NULL. If no value is provided for the enum field at the time of insertion, Null value will be inserted.

• NULL − It works the same as the DEFAULT value if this attribute is set for the enum field. If it is set, the index value is always NULL.

• NOT NULL − MySQL will generate a warning message if this attribute is set for the enum field and no value is provided at the insertion time.

Example

First of all, let us create a table named STUDENTS. In this table, we are specifying ENUM string object in the BRANCH column using the following query −

CREATE TABLE STUDENTS (
   ID int NOT NULL AUTO_INCREMENT,
   NAME varchar(30) NOT NULL,
   BRANCH ENUM (''CSE'', ''ECE'', ''MECH''),
   FEES int NOT NULL,
   PRIMARY KEY (ID)
);

Following is the output obtained −

Query OK, 0 rows affected (0.04 sec)

Now, we retrieve the structure of the STUDENTS table, revealing that the “BRANCH” field has an enum data type −

DESCRIBE STUDENTS;

The output indicates that the BRANCH field”s data type is ENUM, which stores the values (”CSE”, ”ECE”, ”MECH”) −

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

INSERT INTO STUDENTS (NAME, BRANCH, FEES) VALUES
(''Anirudh'', ''CSE'', 500000),
(''Yuvan'', ''ECE'', 350000),
(''Harris'', ''MECH'', 400000);

In these insertion queries, we have used values (”CSE”, ”ECE”, and ”MECH”) for the “BRANCH” field, which are valid enum values. Hence, the queries executed without any errors −

Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

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

SELECT * FROM STUDENTS;

Following are the records of STUDENTS table −

Inserting Records with Numerical ENUM Values

We can insert the enum list values to the ENUM column of table using the respective numeric index. The numeric index starts from 1 but not from 0.

Example

In the query below, we are inserting the value ”CSE” from the ENUM list into the ”BRANCH” column using its numeric index. Since ”CSE” is located at position 1 in the ENUM list, we use 1 as the numeric index in the query.

INSERT INTO STUDENTS (NAME, BRANCH, FEES) VALUES
(''Devi'', 1, 380000);

Output

The insertion query executes without any errors −

Query OK, 1 row affected (0.01 sec)

Verification

Let us verify whether the above insertion is successful or not by retrieving all the records of the table using the below query −

SELECT * FROM STUDENTS;

The STUDENTS table displayed is as follows −

Inserting Invalid Records

In MySQL, if we try to insert a value into a column with an ENUM data type that does not match any of the specified enum values, it will result in an error.

Example

In the following query, we are referring to the 6^th value in enum list, which does not exist. So, the following query will generate an error −

INSERT INTO STUDENTS (NAME, BRANCH, FEES) VALUES
(''Thaman'', 6, 200000);

Output

As we can see the output, an error is generated, and no new record has been inserted −

ERROR 1265 (01000): Data truncated for column ''BRANCH'' at row 1

Filtering Records by Numeric ENUM Value

In MySQL, you can retrieve records from an ENUM column based on either the string value or the numeric index. The numeric index starts from 1, not 0.

Example

The numeric index of 1 in enum list is ”CSE”. So, the following query will fetch the records where the BRANCH column contains the value as ”CSE”.

SELECT * FROM STUDENTS WHERE BRANCH = 1;

Output

The resulting output displays records where the ”BRANCH” column contains the value ”CSE” −

Filtering Records by Human-Readable ENUM Value

There can be some instances where the enum list will have large number of values. It can be difficult to remember the numeric index for every value in the list. In such cases, it is more convenient to use the human-readable string value of the ENUM item in your query to retrieve records based on the ENUM field”s value.

Example

In the following query, we are filtering the records where the BRANCH column contains the value “Mech”.

SELECT * FROM STUDENTS WHERE BRANCH = "MECH";

Output

Following is the output obtained −

Disadvantages of ENUM Data Type

Following are the disadvantages of ENUM data type in MySQL −

• If we wish to modify the values in enum list, we need to re-create the complete table using the ALTER TABLE command, which is quite expensive in terms of used resources and time.

• It is very complex to get the complete enum list because we need to access the inform_schema database.

• Expressions cannot be used with enumeration values. For instance, the following CREATE statement will return an error because it used the CONCAT() function for creating enumeration value −

CREATE TABLE Students (
   ID int PRIMARY KEY AUTO_INCREMENT,
   NAME varchar(30),
   BRANCH ENUM(''CSE'', CONCAT(''ME'',''CH''))
);

User variables cannot be used for an enumeration value. For instance, look at the following query −

mysql> SET @mybranch = ''EEE
mysql> CREATE TABLE Students (
   ID int PRIMARY KEY AUTO_INCREMENT,
   NAME varchar(30),
   BRANCH ENUM(''CSE'', ''MECH'', @mybranch)
);

It is recommended to not use the numeric values as enum values.

Enum Datatypes Using a Client Program

We can also create column of the Enum datatypes using the client program.

Syntax

$sql = ''CREATE TABLE STUDENTS (ID int NOT NULL AUTO_INCREMENT, NAME varchar(30) NOT NULL, BRANCH ENUM ("CSE", "ECE", "MECH"), FEES int NOT NULL, PRIMARY KEY (ID))
$mysqli->query($sql);

sql = "CREATE TABLE STUDENTS (ID int NOT NULL AUTO_INCREMENT, NAME varchar(30) NOT NULL, BRANCH ENUM (''CSE'', ''ECE'', ''MECH''), FEES int NOT NULL, PRIMARY KEY (ID) )";
con.query(sql);

String sql = "CREATE TABLE STUDENTS (ID int NOT NULL AUTO_INCREMENT, NAME varchar(30) NOT NULL, BRANCH ENUM ("CSE", "ECE", "MECH"), FEES int NOT NULL, PRIMARY KEY (ID))";
statement.execute(sql);

sql = ''CREATE TABLE STUDENTS( ID int NOT NULL AUTO_INCREMENT,  NAME varchar(30) NOT NULL,  BRANCH ENUM (''CSE'', ''ECE'', ''MECH''),  FEES int NOT NULL,  PRIMARY KEY (ID)  )''
cursorObj.execute(sql)

Example

Following are the programs −

$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 STUDENTS (ID int NOT NULL AUTO_INCREMENT, NAME varchar(30) NOT NULL, BRANCH ENUM ("CSE", "ECE", "MECH"), FEES int NOT NULL, PRIMARY KEY (ID))
$result = $mysqli->query($sql);
if ($result) {
    printf("Table created successfully...!n");
}
//insert data into created table
$q = "INSERT INTO STUDENTS (NAME, BRANCH, FEES)
      VALUES (''Anirudh'', ''CSE'', 500000), (''Yuvan'', ''ECE'', 350000)";
if ($res = $mysqli->query($q)) {
    printf("Data inserted successfully...!n");
}
//now display the table records
$s = "SELECT BRANCH FROM STUDENTS";
if ($r = $mysqli->query($s)) {
    printf("Select query executed successfully...!n");
    printf("following records belongs to Enum datatypes: n");
    while ($row = $r->fetch_assoc()) {
        printf(" Branch Name: %s", $row["BRANCH"]);
        printf("n");
    }
} else {
    printf(''Failed'');
}
$mysqli->close();

Table created successfully...!
Data inserted successfully...!
Select query executed successfully...!
following records belongs to Enum datatypes:
 Branch Name: CSE
 Branch Name: ECE

var mysql = require("mysql2");
var con = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "password",
}); //Connecting to MySQL

con.connect(function (err) {
  if (err) throw err;
  //   console.log("Connected successfully...!");
  //   console.log("--------------------------");
  sql = "USE TUTORIALS";
  con.query(sql);

  //create a customers that accepts one column enum type.
  sql = "CREATE TABLE STUDENTS (ID int NOT NULL AUTO_INCREMENT, NAME varchar(30) NOT NULL, BRANCH ENUM (''CSE'', ''ECE'', ''MECH''), FEES int NOT NULL, PRIMARY KEY (ID) )";
  con.query(sql);

  //insert data into created table
  sql ="INSERT INTO STUDENTS (NAME, BRANCH, FEES) VALUES (''Anirudh'', ''CSE'', 500000),  (''Yuvan'', ''ECE'', 350000)";
  con.query(sql);
  //select datatypes of branch
  sql = `SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = ''STUDENTS'' AND COLUMN_NAME = ''BRANCH''`;
  con.query(sql, function (err, result) {
    if (err) throw err;
    console.log(result);
  });
});

[ { DATA_TYPE: ''enum'' } ]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class Enum {
   public static void main(String[] args) {
      String url = "jdbc:mysql://localhost:3306/TUTORIALS";
      String username = "root";
      String password = "password";
      try {
         Class.forName("com.mysql.cj.jdbc.Driver");
         Connection connection = DriverManager.getConnection(url, username, password);
         Statement statement = connection.createStatement();
         System.out.println("Connected successfully...!");

         //ENUM data types...!;
         String sql = "CREATE TABLE STUDENTS (ID int NOT NULL AUTO_INCREMENT, NAME varchar(30) NOT NULL, BRANCH ENUM ("CSE", "ECE", "MECH"), FEES int NOT NULL, PRIMARY KEY (ID))";
         statement.execute(sql);
         System.out.println("column of a ENUM type created successfully...!");
         ResultSet resultSet = statement.executeQuery("DESCRIBE STUDENTS");
         while (resultSet.next()){
            System.out.println(resultSet.getString(1)+" "+resultSet.getString(2));
         }
         connection.close();
      } catch (Exception e) {
         System.out.println(e);
      }
   }
}

Connected successfully...!
column of a ENUM type created successfully...!
ID int
NAME varchar(30)
BRANCH enum(''CSE'',''ECE'',''MECH'')
FEES int

import mysql.connector
# Establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
# Creating a cursor object
cursorObj = connection.cursor()
# Create table with enum column
sql = ''''''
CREATE TABLE STUDENTS (
ID int NOT NULL AUTO_INCREMENT,
NAME varchar(30) NOT NULL,
BRANCH ENUM (''CSE'', ''ECE'', ''MECH''),
FEES int NOT NULL,
PRIMARY KEY (ID)
);
''''''
cursorObj.execute(sql)
print("The table is created successfully!")
# Data to be inserted
data_to_insert = [
    (''Anirudh'', ''CSE'', 500000),
    (''Yuvan'', ''ECE'', 350000),
    (''Harris'', ''MECH'', 400000)
]
# Insert data into the created table
insert_query = "INSERT INTO STUDENTS (NAME, BRANCH, FEES) VALUES (%s, %s, %s)"
cursorObj.executemany(insert_query, data_to_insert)
# Commit the changes after the insert operation
connection.commit()
print("Rows inserted successfully.")
# Now display the table records
select_query = "SELECT * FROM STUDENTS"
cursorObj.execute(select_query)
result = cursorObj.fetchall()
print("Table Data:")
for row in result:
    print(row)
cursorObj.close()
connection.close()

The table is created successfully!
Rows inserted successfully.
Table Data:
(1, ''Anirudh'', ''CSE'', 500000)
(2, ''Yuvan'', ''ECE'', 350000)
(3, ''Harris'', ''MECH'', 400000)

The MySQL Decimal Data Type

The MySQL DECIMAL data type is used to store numeric values with decimal points. It allows for precise calculations and can be configured to store a specified number of digits before and after the decimal point.

We often use this datatype for the columns that require exact precision such as salaries of employees, employee PF balances, etc.

Internally, MySQL stores DECIMAL values using a binary format that allocates storage for the integer and fractional parts of the number separately. This binary format efficiently packs 9 digits into 4 bytes of storage.

Syntax

Following is the syntax to define a column whose data type is DECIMAL −

column_name  DECIMAL(P,D);

Where,

• P is called precision which specifies the total number of significant digits that can be stored in the column, both to the left and right of the decimal point. The range of P is 1 to 65.

• D is a scale that specifies the maximum number of digits that can be stored after the decimal point. The range of D should be between 0 and 30 and D is less than or equal to (<=) P.

For instance, if we define a column as DECIMAL(10,2), it can store numbers with up to 10 digits, and up to 2 digits to the right of the decimal point.

In MySQL, instead of the DECIMAL keyword, we can also use the “DEC”, “FIXED” and “NUMERIC” keywords because they are synonyms for DECIMAL.

Attributes

The DECIMAL keyword has two attributes: UNSIGNED and ZEROFILL.

• UNSIGNED − When used, it indicates that the column does not accept negative values.

• ZEROFILL − If used, it pads the number with zeros to the specified width.

Precision and Scale

In the following query, we define a SALARY column with the DECIMAL data type, specifying a precision of 5 and a scale of 3 −

SALARY decimal(5,3)

This definition means that the SALARY column can store values with up to 5 digits in total, including 3 digits to the right of the decimal point. The range for this column would be from 99.999 to -99.999.

No Decimal Places

In here, the SALARY column contains no fractional part or decimal point. The following two queries are same −

SALARY DECIMAL(5);
SALARY DECIMAL(5,0);

Both declarations indicate that the SALARY column can store values as whole numbers without decimal places.

Default Precision

If you omit the precision value, the default precision P will be 10 −

SALARY DECIMAL;

MySQL DECIMAL Storage

MySQL stores values of the “DECIMAL” data type using a binary format that optimizes storage. Specifically, MySQL packs 9 digits into 4 bytes. Storage is allocated separately for the integer and fractional parts, with 4 bytes used for each set of 9 digits. Any remaining digits require additional storage.

The storage required for remaining (leftover) digits is demonstrated in the following table:

Consider a DECIMAL(30,9) column, which has 9 digits for the fractional part and 30 – 9 = 21 digits for the integer part. In this case, the fractional part takes 4 bytes. The integer part takes 8 bytes for the first 18 digits, and for the leftover 3 digits, it requires an additional 2 bytes. Therefore, the DECIMAL(30,9) column requires a total of 14 bytes.

Example

To further understand this, let us create a table named EMPLOYEES using the following query −

CREATE TABLE EMPLOYEES (
   ID int NOT NULL AUTO_INCREMENT,
   NAME varchar(30) NOT NULL,
   SALARY decimal(14,4) NOT NULL,
   PRIMARY KEY (ID)
 );

Using the following query, we are inserting some records into the above created table −

INSERT INTO EMPLOYEES (NAME, SALARY) VALUES
("Krishna", 150050.34),
("Kalyan", 100000.65);

The EMPLOYEES table obtained is as follows −

Using the following query, we are including the ZEROFILL attribute in the “SALARY” column −

ALTER TABLE EMPLOYEES
MODIFY SALARY decimal(14, 4) zerofill;

Following is the output of the above query −

Query OK, 2 rows affected, 1 warning (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 1

Here, we are trying to fetch all the records from the EMPLOYEES tables after including the ZEROFILL attribute on SALARY column −

SELECT * FROM EMPLOYEES;

The records will display zeros padded based on the range specified in the “SALARY” column −

Decimal Datatype Using a Client Program

We can also create column of the decimal datatype using the client program.

Syntax

$sql = ''CREATE TABLE EMPLOYEES ( ID int NOT NULL AUTO_INCREMENT, NAME varchar(30) NOT NULL, SALARY decimal(14,4) NOT NULL, PRIMARY KEY (ID) )
$mysqli->query($sql);

sql = "CREATE TABLE EMPLOYEES ( ID int NOT NULL AUTO_INCREMENT, NAME varchar(30) NOT NULL, SALARY decimal(14,4) NOT NULL, PRIMARY KEY (ID) )";
con.query(sql);

String sql = "CREATE TABLE STUDENTS (ID int NOT NULL AUTO_INCREMENT, NAME varchar(30) NOT NULL, BRANCH ENUM ("CSE", "ECE", "MECH"), FEES int NOT NULL, PRIMARY KEY (ID))";
statement.execute(sql);

sql = ''CREATE TABLE EMPLOYEES (ID int NOT NULL AUTO_INCREMENT, NAME varchar(30) NOT NULL, SALARY decimal(14,4) NOT NULL, PRIMARY KEY (ID))''
cursorObj.execute(sql)

Example

Following are the programs −

$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 EMPLOYEES ( ID int NOT NULL AUTO_INCREMENT, NAME varchar(30) NOT NULL, SALARY decimal(14,4) NOT NULL, PRIMARY KEY (ID) )
$result = $mysqli->query($sql);
if ($result) {
    printf("Table created successfully...!n");
}
//insert data into created table
$q = "INSERT INTO EMPLOYEES (NAME, SALARY) VALUES (''Krishna'', 150050.34), (''Kalyan'', 100000.65)";
if ($res = $mysqli->query($q)) {
    printf("Data inserted successfully...!n");
}
//now display the table records
$s = "SELECT ID, Salary FROM EMPLOYEES";
if ($r = $mysqli->query($s)) {
    printf("Table Records: Where Salary is decimal type! n");
    while ($row = $r->fetch_assoc()) {
        printf(" ID: %d, Salary: %s", $row["ID"], $row["Salary"]);
        printf("n");
    }
} else {
    printf(''Failed'');
}
$mysqli->close();

Table created successfully...!
Data inserted successfully...!
Table Records: Where Salary is decimal type!
 ID: 1, Salary: 150050.3400
 ID: 2, Salary: 100000.6500

var mysql = require("mysql2");
var con = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "password",
}); //Connecting to MySQL

con.connect(function (err) {
  if (err) throw err;
  //   console.log("Connected successfully...!");
  //   console.log("--------------------------");
  sql = "USE TUTORIALS";
  con.query(sql);

  //create a employees table, that accepts one column of decimal type.
  sql = "CREATE TABLE EMPLOYEES ( ID int NOT NULL AUTO_INCREMENT, NAME varchar(30) NOT NULL, SALARY decimal(14,4) NOT NULL, PRIMARY KEY (ID) )";
  con.query(sql);

  //insert data into created table
  sql = "INSERT INTO EMPLOYEES (NAME, SALARY) VALUES (''Krishna'', 150050.34), (''Kalyan'', 100000.65)";
  con.query(sql);

  //select datatypes of salary
  sql = `SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = ''EMPLOYEES'' AND COLUMN_NAME = ''SALARY''`;
  con.query(sql, function (err, result) {
    if (err) throw err;
    console.log(result);
  });
});

[ { DATA_TYPE: ''decimal'' } ]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class Decimal {
   public static void main(String[] args) {
      String url = "jdbc:mysql://localhost:3306/TUTORIALS";
      String username = "root";
      String password = "password";
      try {
         Class.forName("com.mysql.cj.jdbc.Driver");
         Connection connection = DriverManager.getConnection(url, username, password);
         Statement statement = connection.createStatement();
         System.out.println("Connected successfully...!");

         //Decimal data types...!;
         String sql = "CREATE TABLE EMPLOYEES ( ID int NOT NULL AUTO_INCREMENT, NAME varchar(30) NOT NULL, SALARY decimal(14,4) NOT NULL, PRIMARY KEY (ID))";
         statement.execute(sql);
         System.out.println("column of a Decimal type created successfully...!");
         ResultSet resultSet = statement.executeQuery("DESCRIBE EMPLOYEES");
         while (resultSet.next()){
            System.out.println(resultSet.getString(1)+" "+resultSet.getString(2));
         }
         connection.close();
      } catch (Exception e) {
         System.out.println(e);
      }
   }
}

Connected successfully...!
column of a Decimal type created successfully...!
ID int
NAME varchar(30)
SALARY decimal(14,4)

import mysql.connector
# Establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
# Creating a cursor object
cursorObj = connection.cursor()
# Create table with decimal column
sql = ''''''
CREATE TABLE EMPLOYEES (
ID int NOT NULL AUTO_INCREMENT,
NAME varchar(30) NOT NULL,
SALARY decimal(14,4) NOT NULL,
PRIMARY KEY (ID)
)''''''
cursorObj.execute(sql)
print("The table is created successfully!")
# Data to be inserted
data_to_insert = [
    (''Krishna'', 150050.34),
    (''Kalyan'', 100000.65)
]
# Insert data into the created table
insert_query = "INSERT INTO EMPLOYEES (NAME, SALARY) VALUES (%s, %s)"
cursorObj.executemany(insert_query, data_to_insert)
# Commit the changes after the insert operation
connection.commit()
print("Rows inserted successfully.")
# Now display the table records
select_query = "SELECT * FROM EMPLOYEES"
cursorObj.execute(select_query)
result = cursorObj.fetchall()
print("Table Data:")
for row in result:
    print(row)
cursorObj.close()
connection.close()

The table is created successfully!
Rows inserted successfully.
Table Data:
(1, ''Krishna'', Decimal(''150050.3400''))
(2, ''Kalyan'', Decimal(''100000.6500''))

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 −

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 −

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 −

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 −

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. −

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 −

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 −

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

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

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 −

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

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

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

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

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

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

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

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

The FLOAT data type is a part of the numeric data type. Numeric data types are used to store numbers, and they can be categorized into various subtypes based on their characteristics, such as storage size and precision.

The MySQL FLOAT Data Type

The MySQL FLOAT datatype is a floating-point number type that stores approximate numeric values. It stores approximate numeric values in 4 bytes and represents single-precision values.

FLOAT is suitable for a wide range of numeric values but stores them in an approximate manner due to the IEEE 754 standard limitations.

FLOAT data type can represent both signed and unsigned attributes of a data value in versions prior to MySQL 8.0.17, but the unsigned FLOAT is deprecated in MySQL 8.0.17 and later versions.

Syntax

Following is the basic syntax to set the datatype of a field as FLOAT −

CREATE TABLE (column_name FLOAT, ...);

Example

In this example, let us create a new database table named ”datatype_demo” using CREATE TABLE statement with columns representing FLOAT values −

CREATE TABLE datatype_demo(
   ID INT,
   NAME VARCHAR(50),
   HEIGHT FLOAT,
   WEIGHT FLOAT
);

Following is the output obtained −

Query OK, 0 rows affected (0.03 sec)

Verification

Once the table is created, we can verify the data types of the ”HEIGHT” and ”WEIGHT” fields by retrieving the table”s definition as shown below −

DESC datatype_demo;

The result of the DESC command will show that the ”HEIGHT” and ”WEIGHT” fields have the FLOAT data type −

To verify further, let us insert some values into the table using the following INSERT statement −

INSERT INTO datatype_demo VALUES
(1, ''John'', 171.3, 65.7),
(2, ''Rob'', 45, 75),
(3, ''Salman'', 12.74839, 54.262),
(4, ''Arush'', NULL, NULL),
(5, ''James'', ''h'', ''w'');

Following is the output obtained −

ERROR 1265 (01000): Data truncated for column ''HEIGHT'' at row 1

As expected, the FLOAT fields accept single precision floating-point numbers without any issues. However, when attempting to insert non-numeric values into these fields, such as ”h” and ”w,” MySQL raises an error, indicating data truncation.

Finally, to view the data that has been inserted into the table, we can use the SELECT statement as shown below −

SELECT * FROM datatype_demo;

The resultant table is as follows −

Other Representations of MySQL FLOAT

MySQL has a provision to specify the range of precision (not the exponent) for the FLOAT datatype in the form of bits. These bits are specified within the parenthesis following the keyword FLOAT, i.e. FLOAT(p).

However, this precision value is only used to determine the storage size and only holds up to 7 decimal places, with the range from 0 to 23 bits. If the precision bit exceeds 23, the data type becomes DOUBLE.

Example

First, we will drop the existing ”datatype_demo” table −

DROP TABLE datatype_demo;

The output obtained is as follows −

Query OK, 0 rows affected (0.01 sec)

Then, we will create a new table ”datatype_demo” specifying a precision of 20 bits for the ”HEIGHT” column −

CREATE TABLE datatype_demo(
   ID INT,
   NAME VARCHAR(50),
   HEIGHT FLOAT(20)
);

Following is the output of the above code −

Query OK, 0 rows affected (0.02 sec)

Verification

Even though we specified a precision of 20 bits, the ”HEIGHT” column will still store float values within the single-precision range, holding up to 7 decimal places. To verify the table”s definition, we can use the DESC command as shown below −

DESC datatype_demo;

The table produced is as follows −

If the precision bit exceeds 23, the datatype becomes DOUBLE. Look at the query below −

CREATE TABLE datatype_demo1(
   ID INT,
   NAME VARCHAR(50),
   HEIGHT FLOAT(30)
);

we get the following output −

Query OK, 0 rows affected (0.02 sec)

Again, we can verify the table”s definition using the DESC command −

DESC datatype_demo1;

Following is the table obtained −

Float Datatype Using a Client Program

We can also create column of the Float datatype using the client program.

Syntax

$sql = ''CREATE TABLE temp(Id INT AUTO_INCREMENT, Celsius FLOAT, Fahrenheit FLOAT, PRIMARY KEY(Id))
$mysqli->query($sql);

sql = "CREATE TABLE temp(Id INT AUTO_INCREMENT, Celsius FLOAT, Fahrenheit FLOAT, PRIMARY KEY(Id))";
con.query(sql);

String sql = "CREATE TABLE temp(Id INT AUTO_INCREMENT, Celsius FLOAT, Fahrenheit FLOAT, PRIMARY KEY(Id))";
statement.execute(sql);

sql = ''CREATE TABLE datatype_demo(ID INT, NAME VARCHAR(50), HEIGHT FLOAT, WEIGHT FLOAT)''''
cursorObj.execute(sql)

Example

Following are the programs −

$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.
'');

//creating a table temp where we are inserting the celsius and Fahrenheit values in float
$sql = ''CREATE TABLE temp(Id INT AUTO_INCREMENT, Celsius FLOAT, Fahrenheit FLOAT, PRIMARY KEY(Id))
$result = $mysqli->query($sql);
if ($result) {
    printf("Table created successfully...!n");
}
// insert data into created table
$q = " INSERT INTO temp(Celsius, Fahrenheit) VALUES ( 36.2, 97.16), ( 35.8, 96.44), ( 37.32, 99.17), ( 35.89, 96.602);";
if ($res = $mysqli->query($q)) {
    printf("Data inserted successfully...!n");
}
//now display the table records
$s = "SELECT * FROM temp";
if ($r = $mysqli->query($s)) {
    printf("Table Records: n");
    while ($row = $r->fetch_assoc()) {
        printf(" ID: %d, Celsius: %f, Fahrenheit: %f", $row["Id"], $row["Celsius"], $row["Fahrenheit"]);
        printf("n");
    }
} else {
    printf(''Failed'');
}
$mysqli->close();

Table created successfully...!
Data inserted successfully...!
Table Records:
 ID: 1, Celsius: 36.200000, Fahrenheit: 97.160000
 ID: 2, Celsius: 35.800000, Fahrenheit: 96.440000
 ID: 3, Celsius: 37.320000, Fahrenheit: 99.170000
 ID: 4, Celsius: 35.890000, Fahrenheit: 96.602000

var mysql = require("mysql2");
var con = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "password",
}); //Connecting to MySQL

con.connect(function (err) {
  if (err) throw err;
  //   console.log("Connected successfully...!");
  //   console.log("--------------------------");
  sql = "USE TUTORIALS";
  con.query(sql);

  //create a student table, that accepts one column of float type.
  sql =
    "CREATE TABLE temp(Id INT AUTO_INCREMENT, Celsius FLOAT, Fahrenheit FLOAT, PRIMARY KEY(Id))";
  con.query(sql);

  //insert data into created table
  sql =
    "INSERT INTO temp(Celsius, Fahrenheit) VALUES ( 36.2, 97.16), ( 35.8, 96.44), ( 37.32, 99.17), ( 35.89, 96.602)";
  con.query(sql);
  //select datatypes of salary
  sql = `SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = ''temp'' AND COLUMN_NAME = ''Celsius''`;
  con.query(sql, function (err, result) {
    if (err) throw err;
    console.log(result);
  });
});

[ { DATA_TYPE: ''float'' } ]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class Float {
   public static void main(String[] args) {
      String url = "jdbc:mysql://localhost:3306/TUTORIALS";
      String username = "root";
      String password = "password";
      try {
         Class.forName("com.mysql.cj.jdbc.Driver");
         Connection connection = DriverManager.getConnection(url, username, password);
         Statement statement = connection.createStatement();
         System.out.println("Connected successfully...!");

         //Float data types...!;
         String sql = "CREATE TABLE temp(Id INT AUTO_INCREMENT, Celsius FLOAT, Fahrenheit FLOAT, PRIMARY KEY(Id))";
         statement.execute(sql);
         System.out.println("column of a Float type created successfully...!");
         ResultSet resultSet = statement.executeQuery("DESCRIBE temp");
         while (resultSet.next()){
            System.out.println(resultSet.getString(1)+" "+resultSet.getString(2));
         }
         connection.close();
      } catch (Exception e) {
         System.out.println(e);
      }
   }
}

Connected successfully...!
column of a Float type created successfully...!
Id int
Celsius float
Fahrenheit float

import mysql.connector
# Establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
# Creating a cursor object
cursorObj = connection.cursor()
# Create table with float column
sql = ''''''
CREATE TABLE datatype_demo(ID INT,
NAME VARCHAR(50),
HEIGHT FLOAT,
WEIGHT FLOAT
)
''''''
cursorObj.execute(sql)
print("The table is created successfully!")
# Data to be inserted
data_to_insert = [
    (1, ''John'', 171.3, 65.7),
    (2, ''Rob'', 45, 75),
    (3, ''Salman'', 12.74839, 54.262),
    (4, ''Arush'', None, None),
]
# Insert data into the created table
insert_query = "INSERT INTO datatype_demo (ID, NAME, HEIGHT, WEIGHT) VALUES (%s, %s, %s, %s)"
cursorObj.executemany(insert_query, data_to_insert)
# Commit the changes after the insert operation
connection.commit()
print("Rows inserted successfully.")
# Now display the table records
select_query = "SELECT * FROM datatype_demo"
cursorObj.execute(select_query)
result = cursorObj.fetchall()
print("Table Data:")
for row in result:
    print(row)
cursorObj.close()
connection.close()

The table is created successfully!
Rows inserted successfully.
Table Data:
(1, ''John'', 171.3, 65.7)
(2, ''Rob'', 45.0, 75.0)
(3, ''Salman'', 12.7484, 54.262)
(4, ''Arush'', None, None)