The MySQL SET data type

The MySQL SET data type is used to store a set of values chosen from a predefined list of values. Each column of the SET datatype can have zero or more values chosen from its list of values. These values are specified as a comma-separated list when inserting or updating data.

It is important to note that the list of values that can be stored in a SET column is defined at the time the table is created, and any values outside this list are not allowed.

For example, if we define a SET column like this −

test_col SET(''one'', ''two'') NOT NULL

The possible values for this column are −

• An empty string (””)
• ”one”
• ”two”
• ”one,two”

Storage of SET Data Type

A MySQL SET column can hold a maximum of 64 distinct members, which means that duplicate values are not allowed. If duplicates exist, MySQL will generate an error or a warning when strict SQL mode is enabled. Additionally, MySQL automatically removes trailing spaces from SET values when creating a table.

In MySQL, when you store a number in a SET column, the bits set in the binary representation of that number determine which set members are included in the column value. Consider the following query for a better understanding −

Create table test_table (
   ID int auto_increment primary key ,
   COL1 set(''Goa'', ''Assam'', ''Delhi'', ''Kerala'')
);

In the above query, each set member is assigned a single bit with corresponding decimal and binary values −

So, if a value of 3 is assigned to the column (binary: 0011), it selects the first two SET members, resulting in ”Goa,Assam”.

Example

First of all, let us create a table with the name test_table using the following query −

Create table test_table (
   ID int auto_increment primary key ,
   COL1 set(''Goa'', ''Assam'', ''Delhi'', ''Kerala'')
);

Following is the output obtained −

Query OK, 0 rows affected (0.02 sec)

When inserting values into a SET column, there is no specific order required for listing the elements. Even if a particular element is listed multiple times, when retrieved later, each element will appear only once, following the order specified during table creation.

Here, we are inserting the values into the set −

INSERT INTO test_table (COL1) VALUES
(''Goa,Assam''),
(''Assam,Goa''),
(''Goa,Assam,Goa''),
(''Goa,Assam,Assam''),
(''Assam,Goa,Assam'');

Output

The output produced is as shown below −

Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

Verification

Let us display all the records of the ”test_table” using the SELECT statement as follows −

SELECT * FROM test_table;

As we can see the output below, all the values in ”COL1” will appear as ”Goa,Assam” −

Example

In the following query, we are searching for the SET values in the table using the MySQL LIKE operator. It finds rows where ”COL1” contains ”GOA” anywhere, even as a substring −

SELECT * FROM test_table WHERE COL1 LIKE ''%Goa%

Output

On executing the given query, the output is displayed as follows −

Example

In here, we are fetching the rows where the values are exactly ”Goa,Assam” and in the same order as listed in the ”COL1” definition −

SELECT * FROM test_table WHERE COL1 = ''Goa,Assam

Output

The output for the above query is as given below −

Updating the SET Values

In MySQL, you can update SET elements in various ways: by replacing elements, adding elements, or removing elements from the SET data. Here are examples of each method −

Replacing SET Data

In the following query, we replace the value in the 5^th row with the number 11, which corresponds to Goa + Assam + Kerala (8 + 2 + 1) −

UPDATE test_table SET COL1 = 11 WHERE Id = 5;

Output

The query executes successfully and produces the following output −

Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Verification

To verify the changes done in the test_table, use the following SELECT query −

SELECT * FROM test_table;

Following is the output produced −

Adding Data to SET

You can add elements to an existing SET column using the CONCAT() function. In this example, we add “Kerala” to the value in the 3rd row −

UPDATE test_table SET COL1 = CONCAT(COL1, ",Kerala")
WHERE Id = 3;

Output

The output for this query is as follows −

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Verification

To verify the changes done in the test_table, use the following SELECT query −

SELECT * FROM test_table;

The result shows the updated value −

Removing Data from SET

To remove a specific SET element, you can use the & ~ bitwise operation. In this example, we remove the “Assam” element from the value in the 4th row −

UPDATE test_table SET COL1 = COL1 & ~2 WHERE ID = 4;

Output

The output for this query is as follows −

Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Verification

Let us verify the test_table using the below query −

SELECT * FROM test_table;

Following is the table obtained −

SET Datatype Using a Client Program

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

Syntax

$sql = "CREATE TABLE test_table (ID INT auto_increment primary key, COL1 set(''Goa'', ''Assam'', ''Delhi'', ''Kerala'') )";
$mysqli->query($sql);

sql = "CREATE TABLE test_table (ID INT auto_increment primary key, COL1 set(''Goa'', ''Assam'', ''Delhi'', ''Kerala'') )";
con.query(sql);

String sql = "CREATE TABLE test_table (ID INT auto_increment primary key, COL1 set("Goa", "Assam", "Delhi", "Kerala"))";
statement.execute(sql);

sql = ''CREATE TABLE test_table (ID INT AUTO_INCREMENT PRIMARY KEY,  COL1 SET(''Goa'', ''Assam'', ''Delhi'', ''Kerala''))''
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.
'');

$sql = ''Create table test_table (ID INT auto_increment primary key, COL1 set("Goa", "Assam", "Delhi", "Kerala") )
$result = $mysqli->query($sql);
if ($result) {
    printf("Table created successfully...!n");
}
// insert data into created table
$q ="INSERT INTO test_table (COL1) VALUES (''Goa,Assam''), (''Assam,Goa''), (''Goa,Assam,Goa''), (''Goa,Assam,Assam''), (''Assam,Goa,Assam'')";
if ($res = $mysqli->query($q)) {
    printf("Data inserted successfully...!n");
}
//now display the table records
$s = "SELECT * FROM test_table";
if ($r = $mysqli->query($s)) {
    printf("Table Records: n");
    while ($row = $r->fetch_assoc()) {
        printf(" ID: %d, Col_1: %s", $row["ID"], $row["COL1"]);
        printf("n");
    }
} else {
    printf(''Failed'');
}
$mysqli->close();

Table created successfully...!
Data inserted successfully...!
Table Records:
 ID: 1, Col_1: Goa,Assam
 ID: 2, Col_1: Goa,Assam
 ID: 3, Col_1: Goa,Assam
 ID: 4, Col_1: Goa,Assam
 ID: 5, Col_1: Goa,Assam

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 test_table table, that accepts one column of set type.
   sql = "Create table test_table (ID INT auto_increment primary key, COL1 set(''Goa'', ''Assam'', ''Delhi'', ''Kerala'') )";
   con.query(sql);

   //insert data into created table
   sql = "INSERT INTO test_table (COL1) VALUES (''Goa,Assam''), (''Assam,Goa''), (''Goa,Assam,Goa''), (''Goa,Assam,Assam''), (''Assam,Goa,Assam'')";
   con.query(sql);

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

[ { DATA_TYPE: ''set'' } ]

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

public class Set {
   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...!");

         //Set data types...!;
         String sql = "Create table test_table (ID INT auto_increment primary key, COL1 set("Goa", "Assam", "Delhi", "Kerala"))";
         statement.execute(sql);
         System.out.println("column of a SET type created successfully...!");
         ResultSet resultSet = statement.executeQuery("DESCRIBE test_table");
         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 SET type created successfully...!
ID int
COL1 set(''Goa'',''Assam'',''Delhi'',''Kerala'')

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 Set column
sql = ''''''
CREATE TABLE test_table (
ID INT AUTO_INCREMENT PRIMARY KEY,
COL1 SET(''Goa'', ''Assam'', ''Delhi'', ''Kerala'')
)
''''''
cursorObj.execute(sql)
print("The table is created successfully!")
# Data to be inserted
data_to_insert = [
    (''Goa,Assam'',),
    (''Assam,Goa'',),
    (''Goa,Assam,Goa'',),
    (''Goa,Assam,Assam'',),
    (''Assam,Goa,Assam'',)
]
# Insert data into the created table
insert_query = "INSERT INTO test_table (COL1) VALUES (%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 test_table"
cursorObj.execute(select_query)
result = cursorObj.fetchall()
print("Test Table Data:")
for row in result:
    print(row)
cursorObj.close()
connection.close()

The table is created successfully!
Rows inserted successfully.
Test Table Data:
(1, {''Assam'', ''Goa''})
(2, {''Assam'', ''Goa''})
(3, {''Assam'', ''Goa''})
(4, {''Assam'', ''Goa''})
(5, {''Assam'', ''Goa''})

MySQL supports various types of pattern matching operations to retrieve filtered result-sets from huge database tables. In previous chapters, we have already learned about the LIKE operator for pattern matching. In this chapter, we will see another pattern matching operation based on regular expressions.

MySQL Regular Expressions

A regular expression is loosely 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.

MySQL implements regular expression support using International Components for Unicode (ICU), which provides full Unicode support and is multi-byte safe.

In MySQL, it is a powerful way to perform a complex search operations in a database to retrieve desired content. And unlike the LIKE operator, the regular expressions are not restricted on search patterns (like % and _) as they use several other meta characters to expand the flexibility and control during pattern matching. This is performed using the REGEXP operator.

Syntax

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

expression REGEXP pattern

Patterns used with REGEXP

Following is the table of pattern, which can be used along with the REGEXP operator.

Examples

The following example demonstrates the usage of some patterns mentioned in the table above, along with the REGEXP operator. For that, we are first creating a database table to perform the search on.

Assume we are creating a table called 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 it 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 above created table −

SELECT * FROM CUSTOMERS;

Following are the records present in CUSTOMERS table −

REGEXP with Patterns −

Now, we are finding all the records in the CUSTOMERS table whose name starts with ”k” −

SELECT * FROM CUSTOMERS WHERE NAME REGEXP ''^k

Executing the query above will produce the following output −

The following query retrieves all records in CUSTOMERS table whose name ends with ”sh” −

SELECT * FROM CUSTOMERS WHERE NAME REGEXP ''sh$

Executing the query above will produce the following output −

Here, we are retrieving all the records whose name contain ”sh” −

SELECT * FROM CUSTOMERS WHERE NAME REGEXP ''sh

As we can see the output, there are only two names that contain ”sh”.

In the following query, we are finding all the names starting with a vowel and ending with ”ol” −

SELECT * FROM CUSTOMERS WHERE NAME REGEXP ''^[aeiou].*ol$

It returned an empty set because the CUSTOMERS table do not have any names who starts with vowel and ends with ”ol”

Empty set (0.00 sec)

The following query finds all the names in the CUSTOMERS table whose name starts with a consonant −

SELECT * FROM CUSTOMERS WHERE NAME REGEXP ''^[^aeiou]

Executing the query above will produce the following output −

Regular Expression Functions and Operators

Following is the table of functions and operators of regular expressions.

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

In MySQL, a trigger is defined a special stored procedure that resides in the system catalogue, and is executed whenever an event is performed. It is called a special stored procedure as it does not require to be invoked explicitly like other stored procedures. The trigger acts automatically whenever the desired event is fired. Triggers are categorized into two types −

• Before Triggers

• After Triggers

These triggers can be a response to either insertion operation on a table, update operation or deletion operation. Thus, these special stored procedures respond whenever INSERT, UPDATE or DELETE statements are executed.

MySQL Before Delete Trigger

The Before Delete Trigger is a row-level trigger supported by the MySQL database. The Before Delete Trigger is executed right before a value is deleted from a row of a database table.

A row-level trigger is a type of trigger that goes off every time a row is modified. Simply, for every single transaction made in a table (like insertion, deletion, update), one trigger acts automatically.

With this trigger, whenever a DELETE statement is executed in the database, the value is deleted from a table first followed by execution of the trigger set.

Syntax

Following is the syntax to create the BEFORE DELETE trigger in MySQL −

CREATE TRIGGER trigger_name
BEFORE DELETE ON table_name FOR EACH ROW
BEGIN
   -- trigger body
END;

Example

In this example, we are creating a table named ”CUSTOMERS”, to demonstrate the BEFORE DELETE trigger on, using the following query −

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

Insert values into this table created using the following INSERT statements −

INSERT INTO CUSTOMERS 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, ''MP'', 4500.00 ),
(7, ''Muffy'', 24, ''Indore'', 10000.00 );

Creating Another Table

Now, let us create another empty table to store all former customers after being deleted from the main table ”CUSTOMERS” −

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

Using the following CREATE TRIGGER statement, create a new trigger ”before_delete_trigger” on the CUSTOMERS table to delete the customer details from CUSTOMERS table and insert them into another table “OLD_CUSTOMERS” −

DELIMITER //
CREATE TRIGGER before_delete_trigger
BEFORE DELETE ON CUSTOMERS
FOR EACH ROW
BEGIN
 INSERT INTO OLD_CUSTOMERS VALUES
 (OLD.ID, OLD.NAME, OLD.AGE, OLD.ADDRESS, OLD.SALARY);
END //
DELIMITER ;

Delete details of ”old” customers from the CUSTOMERS table using the regular DELETE statement as shown below −

DELETE FROM CUSTOMERS WHERE ID = 3;

Verification

To verify whether the details are deleted from the OCUSTOMERS table and added onto the OLD_CUSTOMERS table, let us try to retrieve both of their result-sets using SELECT queries.

The records in CUSTOMERS table are as follows −

The records in OLD_CUSTOMERS table are as follows −

As you can in the tables above, the data has been deleted from the CUSTOMERS table and added to the OLD_CUSTOMERS table.

Before Delete Trigger Using Client Program

We can also execute the Before Delete trigger statement using a client program, instead of SQL queries.

Syntax

$sql = "CREATE TRIGGER before_delete_trigger BEFORE DELETE ON CUSTOMERS FOR EACH ROW
BEGIN
INSERT INTO OLD_CUSTOMERS VALUES (OLD.ID, OLD.NAME, OLD.AGE, OLD.ADDRESS, OLD.SALARY);
END";
$mysqli->query($sql);

sql = `CREATE TRIGGER before_delete_trigger BEFORE DELETE ON CUSTOMERS FOR EACH ROW
BEGIN
INSERT INTO OLD_CUSTOMERS VALUES (OLD.ID, OLD.NAME, OLD.AGE, OLD.ADDRESS, OLD.SALARY);
END`;
con.query(sql);

String sql = "CREATE TRIGGER before_delete_trigger BEFORE DELETE ON CUSTOMERS FOR EACH ROW BEGIN INSERT INTO OLD_CUSTOMERS VALUES (OLD.ID, OLD.NAME, OLD.AGE, OLD.ADDRESS, OLD.SALARY);
END";
statement.execute(sql);

beforeDelete_trigger_query = ''CREATE TRIGGER {trigger_name}
BEFORE DELETE ON {table_name}
FOR EACH ROW
BEGIN
INSERT INTO {another_table} VALUES (OLD.ID, OLD.NAME, OLD.AGE, OLD.ADDRESS, OLD.SALARY);
END''
cursorObj.execute(beforeDelete_trigger_query)

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 = "CREATE TRIGGER before_delete_trigger BEFORE DELETE ON CUSTOMERS FOR EACH ROW
BEGIN
INSERT INTO OLD_CUSTOMERS VALUES (OLD.ID, OLD.NAME, OLD.AGE, OLD.ADDRESS, OLD.SALARY);
END";
if ($mysqli->query($sql)) {
    printf("Trigger created successfully...!n");
}
$q = "DELETE FROM CUSTOMERS WHERE ID = 3";
$result = $mysqli->query($q);
if ($result == true) {
    printf("Delete query executed successfully ...!n");
}
$q1 = "SELECT * FROM CUSTOMERS";
$res1 = $mysqli->query($q1);
if ($res1->num_rows > 0) {
    printf("SELECT * FROM CUSTOMERS(verification): n");
    while($r1 = $res1->fetch_assoc()){
        printf("Id %d, Name: %s, Age: %d, Address %s, Salary %f",
           $r1[''ID''],
           $r1["NAME"],
           $r1[''AGE''],
           $r1["ADDRESS"],
           $r1["SALARY"],);
           printf("n");
        }
}
$q2 = "SELECT * FROM OLD_CUSTOMERS";
$res2 = $mysqli->query($q2);
if ($res2->num_rows > 0) {
    printf("SELECT * FROM OLD_CUSTOMER(verification): n");
    while($r1 = $res2->fetch_assoc()){
        printf("Id %d, Name: %s, Age: %d, Address %s, Salary %f",
           $r1[''ID''],
           $r1["NAME"],
           $r1[''AGE''],
           $r1["ADDRESS"],
           $r1["SALARY"],);
           printf("n");
        }
}
if ($mysqli->error) {
    printf("Error message: ", $mysqli->error);
}
$mysqli->close();

Trigger created successfully...!
Delete query executed successfully ...!
SELECT * FROM CUSTOMERS(verification):
Id 1, Name: Ramesh, Age: 32, Address Ahmedabad, Salary 2000.000000
Id 2, Name: Khilan, Age: 25, Address Delhi, Salary 1500.000000
Id 4, Name: Chaitali, Age: 25, Address Mumbai, Salary 6500.000000
Id 5, Name: Hardik, Age: 27, Address Bhopal, Salary 8500.000000
Id 6, Name: Komal, Age: 22, Address MP, Salary 4500.000000
Id 7, Name: Muffy, Age: 24, Address Indore, Salary 10000.000000
SELECT * FROM OLD_CUSTOMER(verification):
Id 3, Name: kaushik, Age: 23, Address Kota, Salary 2000.000000

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 = `CREATE TRIGGER before_delete_trigger BEFORE DELETE ON CUSTOMERS FOR EACH ROW
 BEGIN
 INSERT INTO OLD_CUSTOMERS VALUES (OLD.ID, OLD.NAME, OLD.AGE, OLD.ADDRESS, OLD.SALARY);
 END`;
 con.query(sql);
 console.log("Before delete query executed successfully..!");
 sql = "DELETE FROM CUSTOMERS WHERE ID = 3";
 console.log("Delete query executed successfully...!");
 con.query(sql);
 console.log("Customers table records: ")
 sql = "SELECT * FROM CUSTOMERS";
 con.query(sql, function(err, result){
 if (err) throw err;
 console.log(result);
 console.log(''---------------------------------'');
 console.log(''OLD_CUSTOMERS table records: '')
 });
 sql = "SELECT * FROM OLD_CUSTOMERS";
 con.query(sql, function(err, result){
 if (err) throw err;
 console.log(result);
 });
});

After delete query executed successfully..!
Delete query executed successfully...!
Customers table records:
[
  {
    ID: 1,
    NAME: ''Ramesh'',
    AGE: 32,
    ADDRESS: ''Ahmedabad'',
    SALARY: ''2000.00''
  },
  {
    ID: 2,
    NAME: ''Khilan'',
    AGE: 25,
    ADDRESS: ''Delhi'',
    SALARY: ''1500.00''
  },
  {
    ID: 4,
    NAME: ''Chaitali'',
    AGE: 25,
    ADDRESS: ''Mumbai'',
    SALARY: ''6500.00''
  },
  {
    ID: 5,
    NAME: ''Hardik'',
    AGE: 27,
    ADDRESS: ''Bhopal'',
    SALARY: ''8500.00''
  },
  { ID: 6, NAME: ''Komal'', AGE: 22, ADDRESS: ''MP'', SALARY: ''4500.00'' },
  {
    ID: 7,
    NAME: ''Muffy'',
    AGE: 24,
    ADDRESS: ''Indore'',
    SALARY: ''10000.00''
  }
]
---------------------------------
OLD_CUSTOMERS table records:
[
  {
    ID: 3,
    NAME: ''kaushik'',
    AGE: 23,
    ADDRESS: ''Kota'',
    SALARY: ''2000.00''
  }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class BeforeDeleteTrigger {
    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 table
            String sql = "CREATE TABLE CUSTOMERS (ID INT NOT NULL, NAME VARCHAR(20) NOT NULL, AGE INT NOT NULL, ADDRESS VARCHAR(25), SALARY DECIMAL(18, 2), PRIMARY KEY(ID))";
            st.execute(sql);
            System.out.println("Customers table created successfully...!");
            //lets insert some records into customers table
            String sql1 = "INSERT INTO CUSTOMERS 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, ''MP'', 4500.00 ), (7, ''Muffy'', 24, ''Indore'', 10000.00 )";
            st.execute(sql1);
            System.out.println("Records inserted successfully...!");
            //print table records
            String sql2 = "SELECT * FROM CUSTOMERS";
            rs = st.executeQuery(sql2);
            System.out.println("Customers 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");
                String salary = rs.getString("salary");
                System.out.println("Id: " + id + ", Name: " + name + ", Age: " + age + ", Address: " + address + ", Salary: " + salary);
            }
            //let create one more table named Old_customers
            String sql3 = "CREATE TABLE OLD_CUSTOMERS (ID INT NOT NULL, NAME VARCHAR(20) NOT NULL, AGE INT NOT NULL, ADDRESS VARCHAR(25), SALARY DECIMAL(18, 2), PRIMARY KEY(ID))";
            st.execute(sql3);
            System.out.println("OLD_CUSTOMERS table created successully...!");
            //print the records
            String sql4 = "SELECT * FROM OLD_CUSTOMERS";
            rs = st.executeQuery(sql4);
            System.out.println("OLD_CUSTOMERS table records before delete trigger: ");
            while(rs.next()) {
                String id = rs.getString("id");
                String name = rs.getString("name");
                String age = rs.getString("age");
                String address = rs.getString("address");
                String salary = rs.getString("salary");
                System.out.println("Id: " + id + ", Name: " + name + ", Age: " + age + ", Address: " + address + ", Salary: " + salary);
            }
            //lets create trigger on student table
            String sql5 = "CREATE TRIGGER before_delete_trigger BEFORE DELETE ON CUSTOMERS FOR EACH ROW BEGIN INSERT INTO OLD_CUSTOMERS VALUES (OLD.ID, OLD.NAME, OLD.AGE, OLD.ADDRESS, OLD.SALARY); END";
            st.execute(sql5);
            System.out.println("Triggerd Created successfully...!");
            //lets delete one record from customers table;
            String d_sql = "DELETE FROM CUSTOMERS WHERE ID = 3";
            st.execute(d_sql);
            System.out.println("Record with id = 3 deleted successfully....!");
            //let print OLD_CUSTOMERS table records
            String sql6 = "SELECT * FROM OLD_CUSTOMERS";
            rs = st.executeQuery(sql6);
            System.out.println("OLD_CUSTOMERS records: ");
            while(rs.next()) {
                String id = rs.getString("id");
                String name = rs.getString("name");
                String age = rs.getString("age");
                String address = rs.getString("address");
                String salary = rs.getString("salary");
                System.out.println("Id: " + id + ", Name: " + name + ", Age: " + age + ", Address: " + address + ", Salary: " + salary);
            }
        }catch(Exception e) {
            e.printStackTrace();
        }
    }
}

Customers table created successfully...!
Records inserted successfully...!
Customers table records:
Id: 1, Name: Ramesh, Age: 32, Address: Ahmedabad, Salary: 2000.00
Id: 2, Name: Khilan, Age: 25, Address: Delhi, Salary: 1500.00
Id: 3, Name: kaushik, Age: 23, Address: Kota, Salary: 2000.00
Id: 4, Name: Chaitali, Age: 25, Address: Mumbai, Salary: 6500.00
Id: 5, Name: Hardik, Age: 27, Address: Bhopal, Salary: 8500.00
Id: 6, Name: Komal, Age: 22, Address: MP, Salary: 4500.00
Id: 7, Name: Muffy, Age: 24, Address: Indore, Salary: 10000.00
OLD_CUSTOMERS table created successully...!
OLD_CUSTOMERS table records before delete trigger:
Triggerd Created successfully...!
Record with id = 3 deleted successfully....!
OLD_CUSTOMERS records:
Id: 3, Name: kaushik, Age: 23, Address: Kota, Salary: 2000.00

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()
table_name = ''Customers''
another_table = ''OLD_CUSTOMERS''
trigger_name = ''before_delete_trigger''
beforeDelete_trigger_query = f''''''
CREATE TRIGGER {trigger_name}
BEFORE DELETE ON {table_name}
FOR EACH ROW
BEGIN
INSERT INTO {another_table} VALUES (OLD.ID, OLD.NAME, OLD.AGE, OLD.ADDRESS, OLD.SALARY);
END
''''''
cursorObj.execute(beforeDelete_trigger_query)
print(f"BEFORE DELETE Trigger ''{trigger_name}'' is created successfully.")
connection.commit()
# Delete details of old customer
delete_query = "DELETE FROM Customers WHERE ID = 3;"
cursorObj.execute(delete_query)
print("Delete query executed successfully.")
# close the cursor and connection
connection.commit()
cursorObj.close()
connection.close()

BEFORE DELETE Trigger ''before_delete_trigger'' is created successfully.
Delete query executed successfully.

In general, a Trigger is defined as a response to an event. In MySQL, a trigger is called a special stored procedure as it does not require to be invoked explicitly like other stored procedures. The trigger acts automatically whenever the desired event is fired. Triggers are categorized into two types: Before Triggers and After Triggers.

These triggers can be a response to either insertion operation on a table, update operation or deletion operation. Thus, these special stored procedures respond whenever INSERT, UPDATE or DELETE statements are executed.

MySQL After Delete Trigger

The After Delete Trigger is a row-level trigger supported by the MySQL database. This trigger is executed right after a value is deleted from a row of a database table.

A row-level trigger is a type of trigger that is executed every time a row is modified. For every single transaction made in a table (like insertion, deletion, update operation), one trigger acts automatically.

When a DELETE statement is executed in the database, the trigger is performed first and then the said value is deleted from the table.

Syntax

Following is the syntax to create the AFTER DELETE trigger in MySQL −

CREATE TRIGGER trigger_name
AFTER DELETE ON table_name FOR EACH ROW
BEGIN
   -- trigger body
END;

Example

In this example, we are creating a table named ”CUSTOMERS”, to demonstrate the AFTER DELETE trigger on, using the following query −

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

Insert values into this table created using the following INSERT statements −

INSERT INTO CUSTOMERS 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, ''MP'', 4500.00 ),
(7, ''Muffy'', 24, ''Indore'', 10000.00 );

Creating Another Table:

Now, let us create another empty table to store all former customers after being deleted from the main table ”CUSTOMERS” −

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

Using the following CREATE TRIGGER statement, create a new trigger ”after_delete_trigger” on the CUSTOMERS table to delete the customer details from CUSTOMERS table and insert them into another table “OLD_CUSTOMERS” −

DELIMITER //
CREATE TRIGGER after_delete_trigger
AFTER DELETE ON CUSTOMERS
FOR EACH ROW
BEGIN
 INSERT INTO OLD_CUSTOMERS VALUES
 (OLD.ID, OLD.NAME, OLD.AGE, OLD.ADDRESS, OLD.SALARY);
END //
DELIMITER ;

Delete details of ”old” customers from the CUSTOMERS table using the regular DELETE statement as shown below −

DELETE FROM CUSTOMERS WHERE ID = 3;

Verification

To verify whether the details are deleted from the CUSTOMERS table and added onto the OLD_CUSTOMERS table, let us try to retrieve both of their result-sets using the SELECT queries.

The records in CUSTOMERS table are as follows −

The records in OLD_CUSTOMERS table are as follows −

As you can in the tables above, the data has been deleted from the CUSTOMERS table and added to the OLD_CUSTOMERS table. However, the only difference that is not visible on the application level is that the trigger is performed after the deletion is done, in contrast to the BEFORE DELETE trigger.

After Delete Trigger Using a Client Program

We can also execute the After Delete trigger statement using a client program, instead of SQL queries.

Syntax

$sql = "CREATE TRIGGER after_delete_trigger AFTER DELETE ON CUSTOMERS FOR EACH ROW
BEGIN
INSERT INTO OLD_CUSTOMERS VALUES (OLD.ID, OLD.NAME, OLD.AGE, OLD.ADDRESS, OLD.SALARY);
END";
$mysqli->query($sql);

sql = `CREATE TRIGGER after_delete_trigger AFTER DELETE ON CUSTOMERS FOR EACH ROW
BEGIN
INSERT INTO OLD_CUSTOMERS VALUES (OLD.ID, OLD.NAME, OLD.AGE, OLD.ADDRESS, OLD.SALARY);
END`;
con.query(sql);

String sql = "CREATE TRIGGER after_delete_trigger AFTER DELETE ON CUSTOMERS FOR EACH ROW BEGIN INSERT INTO OLD_CUSTOMERS VALUES (OLD.ID, OLD.NAME, OLD.AGE, OLD.ADDRESS, OLD.SALARY);
END";
statement.execute(sql);

afterDelete_trigger_query = ''CREATE TRIGGER {trigger_name}
AFTER DELETE ON {table_name}
FOR EACH ROW
BEGIN
INSERT INTO {another_table} VALUES (OLD.ID, OLD.NAME, OLD.AGE, OLD.ADDRESS, OLD.SALARY);
END''
cursorObj.execute(afterDelete_trigger_query)

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 = "CREATE TRIGGER after_delete_trigger AFTER DELETE ON CUSTOMERS FOR EACH ROW
BEGIN
INSERT INTO OLD_CUSTOMERS VALUES (OLD.ID, OLD.NAME, OLD.AGE, OLD.ADDRESS, OLD.SALARY);
END";
if ($mysqli->query($sql)) {
    printf("Trigger created successfully...!n");
}
$q = "DELETE FROM CUSTOMERS WHERE ID = 3";
$result = $mysqli->query($q);
if ($result == true) {
    printf("Delete query executed successfully ...!n");
}
$q1 = "SELECT * FROM CUSTOMERS";
$res1 = $mysqli->query($q1);
if ($res1->num_rows > 0) {
    printf("SELECT * FROM CUSTOMERS(verification): n");
    while ($r1 = $res1->fetch_assoc()) {
        printf(
            "Id %d, Name: %s, Age: %d, Address %s, Salary %f",
            $r1[''ID''],
            $r1["NAME"],
            $r1[''AGE''],
            $r1["ADDRESS"],
            $r1["SALARY"],
        );
        printf("n");
    }
}
$q2 = "SELECT * FROM OLD_CUSTOMERS";
$res2 = $mysqli->query($q2);
if ($res2->num_rows > 0) {
    printf("SELECT * FROM OLD_CUSTOMER(verification): n");
    while ($r1 = $res2->fetch_assoc()) {
        printf(
            "Id %d, Name: %s, Age: %d, Address %s, Salary %f",
            $r1[''ID''],
            $r1["NAME"],
            $r1[''AGE''],
            $r1["ADDRESS"],
            $r1["SALARY"],
        );
        printf("n");
    }
}
if ($mysqli->error) {
    printf("Error message: ", $mysqli->error);
}
$mysqli->close();

Trigger created successfully...!
Delete query executed successfully ...!
SELECT * FROM CUSTOMERS(verification):
Id 1, Name: Ramesh, Age: 32, Address Ahmedabad, Salary 2000.000000
Id 2, Name: Khilan, Age: 25, Address Delhi, Salary 1500.000000
Id 4, Name: Chaitali, Age: 25, Address Mumbai, Salary 6500.000000
Id 5, Name: Hardik, Age: 27, Address Bhopal, Salary 8500.000000
Id 6, Name: Komal, Age: 22, Address MP, Salary 4500.000000
Id 7, Name: Muffy, Age: 24, Address Indore, Salary 10000.000000
SELECT * FROM OLD_CUSTOMER(verification):
Id 3, Name: Kaushik, Age: 23, Address Kota, Salary 2000.000000

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 = `CREATE TRIGGER after_delete_trigger AFTER DELETE ON CUSTOMERS FOR EACH ROW
 BEGIN
 INSERT INTO OLD_CUSTOMERS VALUES (OLD.ID, OLD.NAME, OLD.AGE, OLD.ADDRESS, OLD.SALARY);
 END`;
 con.query(sql);
 console.log("After delete query executed successfully..!");
 sql = "DELETE FROM CUSTOMERS WHERE ID = 3";
 con.query(sql);
 console.log("Customers table records: ")
 sql = "SELECT * FROM CUSTOMERS";
 con.query(sql, function(err, result){
 if (err) throw err;
 console.log(result);
 console.log(''---------------------------------'');
 console.log(''OLD_CUSTOMERS table records: '')
 });
 sql = "SELECT * FROM OLD_CUSTOMERS";
 con.query(sql, function(err, result){
 if (err) throw err;
 console.log(result);
 });
});

After delete query executed successfully..!
Customers table records:
[
  {
    ID: 1,
    NAME: ''Ramesh'',
    AGE: 32,
    ADDRESS: ''Ahmedabad'',
    SALARY: ''2000.00''
  },
  {
    ID: 2,
    NAME: ''Khilan'',
    AGE: 25,
    ADDRESS: ''Delhi'',
    SALARY: ''1500.00''
  },
  {
    ID: 4,
    NAME: ''Chaitali'',
    AGE: 25,
    ADDRESS: ''Mumbai'',
    SALARY: ''6500.00''
  },
  {
    ID: 5,
    NAME: ''Hardik'',
    AGE: 27,
    ADDRESS: ''Bhopal'',
    SALARY: ''8500.00''
  },
  { ID: 6, NAME: ''Komal'', AGE: 22, ADDRESS: ''MP'', SALARY: ''4500.00'' },
  {
    ID: 7,
    NAME: ''Muffy'',
    AGE: 24,
    ADDRESS: ''Indore'',
    SALARY: ''10000.00''
  }
]
---------------------------------
OLD_CUSTOMERS table records:
[
  {
    ID: 3,
    NAME: ''Kaushik'',
    AGE: 23,
    ADDRESS: ''Kota'',
    SALARY: ''2000.00''
  }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class AfterDeleteTrigger {
    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 table
            String sql = "CREATE TABLE CUSTOMERS (ID INT NOT NULL, NAME VARCHAR(20) NOT NULL, AGE INT NOT NULL, ADDRESS VARCHAR(25), SALARY DECIMAL(18, 2), PRIMARY KEY(ID))";
            st.execute(sql);
            System.out.println("Customers table created successfully...!");
            //lets insert some records into customers table
            String sql1 = "INSERT INTO CUSTOMERS 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, ''MP'', 4500.00 ), (7, ''Muffy'', 24, ''Indore'', 10000.00 )";
            st.execute(sql1);
            System.out.println("Records inserted successfully...!");
            //print table records
            String sql2 = "SELECT * FROM CUSTOMERS";
            rs = st.executeQuery(sql2);
            System.out.println("Customers 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");
                String salary = rs.getString("salary");
                System.out.println("Id: " + id + ", Name: " + name + ", Age: " + age + ", Address: " + address + ", Salary: " + salary);
            }
            //let create one more table named Old_customers
            String sql3 = "CREATE TABLE OLD_CUSTOMERS (ID INT NOT NULL, NAME VARCHAR(20) NOT NULL, AGE INT NOT NULL, ADDRESS VARCHAR(25), SALARY DECIMAL(18, 2), PRIMARY KEY(ID))";
            st.execute(sql3);
            System.out.println("OLD_CUSTOMERS table created successully...!");
            //print the records
            String sql4 = "SELECT * FROM OLD_CUSTOMERS";
            rs = st.executeQuery(sql4);
            System.out.println("OLD_CUSTOMERS table records before delete trigger: ");
            while(rs.next()) {
                String id = rs.getString("id");
                String name = rs.getString("name");
                String age = rs.getString("age");
                String address = rs.getString("address");
                String salary = rs.getString("salary");
                System.out.println("Id: " + id + ", Name: " + name + ", Age: " + age + ", Address: " + address + ", Salary: " + salary);
            }
            //lets create trigger on student table
            String sql5 = "CREATE TRIGGER after_delete_trigger AFTER DELETE ON CUSTOMERS FOR EACH ROW BEGIN INSERT INTO OLD_CUSTOMERS VALUES (OLD.ID, OLD.NAME, OLD.AGE, OLD.ADDRESS, OLD.SALARY);
            END";
            st.execute(sql5);
            System.out.println("Triggerd Created successfully...!");
            //lets delete one record from customers table;
            String d_sql = "DELETE FROM CUSTOMERS WHERE ID = 3";
            st.execute(d_sql);
            System.out.println("Record with id = 3 deleted successfully....!");
            //let print OLD_CUSTOMERS table records
            String sql6 = "SELECT * FROM OLD_CUSTOMERS";
            rs = st.executeQuery(sql6);
            System.out.println("OLD_CUSTOMERS records: ");
            while(rs.next()) {
                String id = rs.getString("id");
                String name = rs.getString("name");
                String age = rs.getString("age");
                String address = rs.getString("address");
                String salary = rs.getString("salary");
                System.out.println("Id: " + id + ", Name: " + name + ", Age: " + age + ", Address: " + address + ", Salary: " + salary);
            }
        }catch(Exception e) {
            e.printStackTrace();
        }
    }
}

Customers table created successfully...!
Records inserted successfully...!
Customers table records:
Id: 1, Name: Ramesh, Age: 32, Address: Ahmedabad, Salary: 2000.00
Id: 2, Name: Khilan, Age: 25, Address: Delhi, Salary: 1500.00
Id: 3, Name: Kaushik, Age: 23, Address: Kota, Salary: 2000.00
Id: 4, Name: Chaitali, Age: 25, Address: Mumbai, Salary: 6500.00
Id: 5, Name: Hardik, Age: 27, Address: Bhopal, Salary: 8500.00
Id: 6, Name: Komal, Age: 22, Address: MP, Salary: 4500.00
Id: 7, Name: Muffy, Age: 24, Address: Indore, Salary: 10000.00
OLD_CUSTOMERS table created successully...!
OLD_CUSTOMERS table records before delete trigger:
Triggerd Created successfully...!
Record with id = 3 deleted successfully....!
OLD_CUSTOMERS records:
Id: 3, Name: Kaushik, Age: 23, Address: Kota, Salary: 2000.00

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()
table_name = ''Customers''
another_table = ''OLD_CUSTOMERS''
trigger_name = ''after_delete_trigger''
afterDelete_trigger_query = f''''''
CREATE TRIGGER {trigger_name}
AFTER DELETE ON {table_name}
FOR EACH ROW
BEGIN
INSERT INTO {another_table} VALUES (OLD.ID, OLD.NAME, OLD.AGE, OLD.ADDRESS, OLD.SALARY);
END
''''''
cursorObj.execute(afterDelete_trigger_query)
print(f"AFTER DELETE Trigger ''{trigger_name}'' is created successfully.")
connection.commit()
# Delete details of old customer
delete_query = "DELETE FROM Customers WHERE ID = 3;"
cursorObj.execute(delete_query)
print("Delete query executed successfully.")
# close the cursor and connection
connection.commit()
cursorObj.close()
connection.close()

AFTER DELETE Trigger ''after_delete_trigger'' is created successfully.
Delete query executed successfully.

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