In MySQL, literals are fixed values (constants) that can be used in SQL statements such as SELECT, INSERT, UPDATE, and DELETE. We can use a literal in SQL statements without needing to be represented by a variable or an expression.

Following are some common MySQL literals:

• Numeric Literals

• String Literals

• Boolean Literals

• Date and Time Literals

• NULL Literals

Numeric Literals

The MySQL numeric literals are numeric values that can represent positive or negative numbers, including both integers and floating-point values.

If we do not specify any sign (i.e. positive (+) or negative (-)) to a numeric value, then a positive value is assumed.

Let us see some examples by using various numeric literals in SQL queries.

Example

Following example displays an integer literal with no sign (by default positive sign will be considered)

SELECT 100 AS ''numeric literal

Output

The output is obtained as follows −

Example

Following example displays an integer literal with positive sign (+) −

SELECT -100 AS ''numeric literal

Output

The output is obtained as follows −

Example

Following example displays an integer literal with negative sign (-) −

SELECT +493 AS ''numeric literal

Output

The output is obtained as follows −

Example

Following example displays a floating point literal −

SELECT 109e-06 AS ''numeric literal

Output

The output is obtained as follows −

Example

Following example displays a decimal literal −

SELECT 793.200 AS ''numeric literal

Output

The output is obtained as follows −

String Literals

The MySQL string literals are character strings that are enclosed within the single quotes (”) or double quotes (“).

Let us see some examples where string literals in SQL queries are used in different ways.

Example

In this example, we are displaying a string literal enclosed in single quotes −

SELECT ''tutorialspoint'' AS ''string literal

We can use double quotes to enclose a string literal as follows −

SELECT "tutorialspoint" AS ''string literal

Output

Following output is obtained in both cases −

Example

In this example, we are displaying a string literal with spaces enclosed in single quotes −

SELECT ''tutorials point india'' AS ''string literal

We can also enclose this string literal (spaces included) in double quotes −

SELECT "tutorials point india" AS ''string literal

Output

Following output is obtained with both queries −

Boolean Literals

The MySQL Boolean literals are logical values that evaluate to either 1 or 0. Let us see some example for a better understanding.

Example

There are various ways a boolean value is evaluated to true in MySQL. Here, we use the integer 1 as a boolean literal −

SELECT 1 AS ''boolean literal

We can also use the keyword TRUE to evaluate the boolean literal to 1.

SELECT TRUE AS ''boolean literal

We can also use the lowercase of the keyword TRUE, as true, to evaluate the boolean literal to 1.

SELECT true AS ''boolean literal

Output

Following output is obtained −

Example

Similarly, there are multiple ways a boolean value is evaluated to false in MySQL. Here, we use the integer 0 as a boolean literal −

SELECT 0 AS ''boolean literal

We can also use the keyword FALSE to evaluate the boolean literal to 0.

SELECT FALSE AS ''boolean literal

We can also use the lowercase of the keyword FALSE, as false, to evaluate the boolean literal to 0.

SELECT false AS ''boolean literal

Output

Following output is obtained −

Date and Time Literals

The MySQL date and time literals represent date and time values. Let us see examples to understand how date and time values are represented in various ways in MySQL.

Example

In this example, we will display a date literal formatted as ”YYYY-MM-DD”

SELECT ''2023-04-20'' AS ''Date literal

Output

Following output is obtained −

Example

In this example, we will display a date literal formatted as ”YYYYMMDD”

SELECT ''20230420'' AS ''Date literal

Output

Following output is obtained −

Example

In this example, we will display a date literal formatted as YYYYMMDD

SELECT 20230420 AS ''Date literal

Output

Following output is obtained −

Example

In this example, we will display a date literal formatted as ”YY-MM-DD”

SELECT ''23-04-20'' AS ''Date literal

Output

Following output is obtained −

Example

In this example, we will display a date literal formatted as ”YYMMDD”

SELECT ''230420'' AS ''Date literal

Output

Following output is obtained −

Example

In this example, we will display a date literal formatted as YYMMDD

SELECT 230420 AS ''Date literal

Output

Following output is obtained −

Example

In this example, we are displaying a time literal formatted as ”HH:MM:SS”.

SELECT ''10:45:50'' AS ''Time literal

Output

Following output is obtained −

Example

In this example, we are displaying a time literal formatted as HHMMSS.

SELECT 104550 AS ''Time literal

Output

Following output is obtained −

Example

In this example, we are displaying a time literal formatted as ”HH:MM”.

SELECT ''10:45'' AS ''Time literal

Output

Following output is obtained −

Example

In this example, we are displaying a time literal formatted as ”MMSS”.

SELECT ''4510'' AS ''Time literal

Output

Following output is obtained −

Example

In this example, we are displaying a time literal formatted as ”SS”.

SELECT ''10'' AS ''Time literal

Here, let us display a time literal formatted as SS.

SELECT 10 AS ''Time literal

Output

Following output is obtained −

Example

In this example, we are displaying a time literal formatted as ”D HH:MM:SS” where D can be a day value between 0 and 34.

SELECT ''4 09:30:12'' AS ''Time literal

Output

Following output is obtained −

Example

In this example, we are displaying a time literal formatted as ”D HH:MM” where D can be a day value between 0 and 34.

SELECT ''4 09:30'' AS ''Time literal

Output

Following output is obtained −

Example

In this example, we are displaying a time literal formatted as ”D HH” where D can be a day value between 0 and 34.

SELECT ''4 09'' AS ''Time literal

Output

Following output is obtained −

Example

In this example, we are displaying a Datetime literal formatted as ”YYYY-MM-DD HH:MM:SS”.

SELECT ''2023-04-20 09:45:10'' AS ''datetime literal

Output

Following output is obtained −

Example

In this example, we are displaying a Datetime literal formatted as ”YYYYMMDDHHMMSS”.

SELECT ''20230420094510'' AS ''datetime literal

Now, we are displaying a Datetime literal formatted as YYYYMMDDHHMMSS.

SELECT 20230420094510 AS ''datetime literal

Output

Both queries produce the same output as follows −

Example

In this example, we are displaying a Datetime literal formatted as ”YY-MM-DD HH:MM:SS”.

SELECT ''23-04-20 09:45:10'' AS ''datetime literal

Output

Following output is obtained −

Example

In this example, we are displaying a Datetime literal formatted as ”YYMMDDHHMMSS”.

SELECT ''230420094510'' AS ''datetime literal

Here, we are displaying a Datetime literal formatted as YYMMDDHHMMSS.

SELECT 230420094510 AS ''datetime literal

Output

Both queries give the same following output −

Null Literals

The MySQL Null literals represents the absence of a value. It is case in-sensitive.

Example

Following are some examples of valid NULL literals −

SELECT NULL AS ''NULL literals

In lowercase −

SELECT null AS ''NULL literals

Output

Following output is obtained −

Client Program

We can also use Literals in a MySQL database using a Client Program.

Syntax

$sql = "SELECT 100 AS ''Numerical_literal''";
$mysqli->query($sql);

sql = "SELECT 100 AS ''numeric literal''";
con.query(sql)

String sql  "SELECT 100 AS ''Numerical_literal''";
statement.executeQuery(sql);

literal_query = "SELECT 100 AS ''numeric literal''"
cursorObj.execute(literal_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 = "SELECT 100 AS ''Numerical_literal''";
If($result = $mysqli->query($sql)){
    printf("Select query executed successfully...!n");
    while($row = mysqli_fetch_array($result)){
        printf("Numerical literal: %d", $row["Numerical_literal"]);
    }
printf("n");
}
$sql = "SELECT ''Tutorialspoint'' AS ''String_literal''";
If($result = $mysqli->query($sql)){
    printf("Select query executed successfully...!n");
    while($row = mysqli_fetch_array($result)){
        printf("String Literal: %s", $row["String_literal"]);
    }
}
printf("n");
$sql = "SELECT 1 AS ''Boolean_literal''";
If($result = $mysqli->query($sql)){
    printf("Select query executed successfully...!n");
    while($row = mysqli_fetch_array($result)){
        printf("Boolean literal: %s", $row["Boolean_literal"]);
    }
}
if($mysqli->error){
    printf("Error message: ", $mysqli->error);
}
$mysqli->close();

Select query executed successfully...!
Numerical literal: 100
Select query executed successfully...!
String Literal: Tutorialspoint
Select query executed successfully...!
Boolean literal: 1

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

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

    // Create a new database
    sql = "Create Database TUTORIALS";
    con.query(sql);

    sql = "USE TUTORIALS";
    con.query(sql);

    //integer literal with no sign (by default positive sign will be considered)
    sql = "SELECT 100 AS ''numeric literal''";
    con.query(sql, function(err, result){
      if (err) throw err
      console.log(result);
    });

    //string with single quotes
    sql = "SELECT ''tutorialspoint'' AS ''string literal"
    con.query(sql, function(err, result){
      if (err) throw err
      console.log(result);
    });

    //Boolean Literals
    sql = "SELECT 1 AS ''boolean literal"
    con.query(sql, function(err, result){
      if (err) throw err
      console.log(result);
    });

    //date literal formatted as ''YYYY-MM-DD''
    sql = "SELECT ''2023-04-20'' AS ''Date literal"
    con.query(sql, function(err, result){
      if (err) throw err
      console.log(result);
    });
});

 Connected!
 --------------------------
 [ { ''numeric literal'': 100 } ]
 [ { ''string literal'': ''tutorialspoint'' } ]
 [ { ''boolean literal'': 1 } ]
 [ { ''Date literal'': ''2023-04-20'' } ]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class Literals {
  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...!");
            //numerical literal
            String sql = "SELECT 100 AS ''Numerical_literal''";
            rs = st.executeQuery(sql);
            System.out.println("Numerical literal: ");
            while(rs.next()) {
              String nl = rs.getString("Numerical_literal");
              System.out.println(nl);
            }
          //String literal
            String sql1 = "SELECT ''Tutorialspoint'' AS ''String_literal''";
            rs = st.executeQuery(sql1);
            System.out.println("String literal: ");
            while(rs.next()) {
              String nl = rs.getString("String_literal");
              System.out.println(nl);
            }
          //Boolean literal
            String sql2 = "SELECT 1 AS ''Boolean_literal''";
            rs = st.executeQuery(sql2);
            System.out.println("Boolean literal: ");
            while(rs.next()) {
              String nl = rs.getString("Boolean_literal");
              System.out.println(nl);
            }
    }catch(Exception e) {
      e.printStackTrace();
    }
  }
}

Numerical literal:
100
String literal:
Tutorialspoint
Boolean literal:
1

import mysql.connector
# Establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
# Creating a cursor object
cursorObj = connection.cursor()
# Integer literal with no sign (by default positive sign will be considered)
literal_query = "SELECT 100 AS ''numeric literal''"
cursorObj.execute(literal_query)
result_numeric = cursorObj.fetchone()
print("Numeric Literal:")
print(result_numeric[0])
# String literal with single quotes
string_literal = "SELECT ''tutorialspoint'' AS ''string literal"
cursorObj.execute(string_literal)
result_string = cursorObj.fetchone()
print("nString Literal:")
print(result_string[0])
# Boolean literal, evaluates to 1 (true)
boolean_literal = "SELECT 1 AS ''boolean literal"
cursorObj.execute(boolean_literal)
result_boolean = cursorObj.fetchone()
print("nBoolean Literal:")
print(result_boolean[0])
# Date literal formatted as ''YYYY-MM-DD''
date_time_literal = "SELECT ''2023-04-20'' AS ''Date literal"
cursorObj.execute(date_time_literal)
result_date = cursorObj.fetchone()
print("nDate Literal:")
print(result_date[0])
# Closing the cursor and connection
cursorObj.close()
connection.close()

Numeric Literal:
100

String Literal:
tutorialspoint

Boolean Literal:
1

Date Literal:
2023-04-20

In general, aggregation is a consideration of a collection of objects that are bound together as a single entity. MySQL provides a set of aggregate functions that perform operations on all the entities of the column of a table considering them as a single unit. Following are the MySQL aggregate functions −

MySQL – String Functions

MySQL string functions are used to manipulate the string values. The following table details the string functions that are available in the MySQL.

MySQL – Numeric Functions

MySQL numeric functions are used primarily for numeric manipulation and/or mathematical calculations. The following table details the numeric functions that are available in the MySQL.

The MySQL NULL Values

MySQL uses the term “NULL” to represent a non-existent data value in the database. These values are different from an empty string or zero and do not occupy any storage space in the database. They are used to indicate the absence of a value or an unknown value in a data field.

There are some common reasons why a value may be NULL −

• The value may not be provided during data entry.

• The value is not yet known.

Since NULL values are non-existent, you cannot use standard comparison operators such as “=”, “<,” or “>” with them. Instead, you can use the “IS NULL,” “IS NOT NULL,” or “NOT NULL” operators to check if a value is NULL.

Creating a Table without NULL Values

To create a table without NULL values, you can use the “NOT NULL” keyword while defining the columns. If a column is specified as “NOT NULL,” an error will occur when attempting to insert NULL values into that specific column.

Syntax

The basic syntax for creating a table with “NOT NULL” columns is as follows −

CREATE TABLE table_name (
   column1 datatype NOT NULL,
   column2 datatype NOT NULL,
   ...
   columnN datatype
);

Where, “NOT NULL” indicates that a column must always contain a specific value of the defined data type. Columns marked as “NOT NULL” cannot accept NULL values. On the other hand, you can insert NULL values into the columns without the “NOT NULL” constraint.

Example

Let us create a table named “CUSTOMERS” using the following query −

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

Now, let us insert some records into the above-created table −

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, ''Hyderabad'', NULL),
(7, ''Muffy'', 24, ''Indore'', NULL);

The CUSTOMERS table obtained is as follows −

Now, to retrieve records that are not NULL, you can use the “IS NOT NULL” operator as shown below−

SELECT ID, NAME, AGE, ADDRESS, SALARY
FROM CUSTOMERS
WHERE SALARY IS NOT NULL;

Following is the output of the above code −

To retrieve records that are NULL, you can use the “IS NULL” operator as shown below −

SELECT ID, NAME, AGE, ADDRESS, SALARY
FROM CUSTOMERS
WHERE SALARY IS NULL;

The output produced is as follows −

Updating NULL Values in a Table

To update NULL values in a table, you can use the “UPDATE” statement with the “IS NULL” operator. This filter the rows containing NULL values and set new values using the “SET” keyword.

Example

Here, we are updating the NULL values in the SALARY column of the CUSTOMERS table as shown below −

UPDATE CUSTOMERS SET SALARY = 9000 WHERE SALARY IS NULL;

Output

After executing the above code, we get the following output −

Query OK, 2 rows affected (0.01 sec)
Rows matched: 2  Changed: 2  Warnings: 0

Verification

You can verify whether the records in the table are updated or not using the following query −

SELECT * FROM CUSTOMERS;

The output displayed is as follows −

Deleting Records with NULL Values

To delete records with NULL values from a table, you can use the “DELETE FROM” statement with the “IS NULL” operator in the “WHERE” clause.

Example

Now, we are deleting records with NULL values in the SALARY column as shown below −

DELETE FROM CUSTOMERS WHERE SALARY IS NULL;

Output

Output of the above code is as shown below −

Query OK, 2 rows affected (0.01 sec)

Verification

You can verify whether the records in the table is deleted or not using the SELECT statement as follows −

SELECT * FROM CUSTOMERS;

The table produced is as shown below −

NULL Value Using a Client Program

We can also execute NULL value using the client program.

Syntax

$sql = "SELECT * from tcount_tbl WHERE tutorial_count IS NULL";
$mysqli->query($sql);

sql = "SELECT * from tcount_tbl WHERE tutorial_count IS NOT NULL";
con.query(sql);

String sql = "SELECT * FROM tcount_tbl WHERE tutorial_count IS NULL";
statement.executeQuery(sql);

null_values_query = "SELECT * FROM tcount_tbl WHERE tutorial_count IS NULL"
cursorObj.execute(null_values_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 = "SELECT * from tcount_tbl WHERE tutorial_count IS NULL";
if($result = $mysqli->query($sql)){
    printf("Table record: n");
    while($row = mysqli_fetch_array($result)){
        printf("Tutorial_author %s, Tutorial_count %d",
        $row[''tutorial_author''],
        $row[''tutorial_count'']);
        printf("n");
    }
}
if($mysqli->error){
    printf("Error message: ", $mysqli->error);
}
$mysqli->close();

Table record:
Tutorial_author mahnaz, Tutorial_count 0
Tutorial_author Jen, Tutorial_count 0

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 tcount_tbl WHERE tutorial_count IS NOT NULL";
 if(con.query(sql)){
    console.log("Select query executed successfully....!");
 }
 else{
    console.log("Error");
 }
 console.log("Table records: ");
 con.query(sql, function(err, result){
 if (err) throw err;
 console.log(result);
 });
});

Select query executed successfully....!
Table records:
[
  { tutorial_author: ''mahran'', tutorial_count: 20 },
  { tutorial_author: ''Gill'', tutorial_count: 20 }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class NullValues {
   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 tcount_tbl WHERE tutorial_count = NULL";
            rs = st.executeQuery(sql);
            System.out.println("Table records(tutorial_count = null): ");
            while(rs.next()) {
               String tutorial_author = rs.getString("tutorial_author");
               String tutorial_count = rs.getString("tutorial_count");
               System.out.println("Author: " + tutorial_author + ", Tutorial_count: " + tutorial_count);
            }
            String sql1 = "SELECT * FROM tcount_tbl WHERE tutorial_count IS NULL";
            rs = st.executeQuery(sql1);
            System.out.println("Table records(tutorial_count is null): ");
            while(rs.next()) {
               String tutorial_author = rs.getString("tutorial_author");
               String tutorial_count = rs.getString("tutorial_count");
               System.out.println("Author: " + tutorial_author + ", Tutorial_count: " + tutorial_count);
            }
      }catch(Exception e) {
         e.printStackTrace();
      }
   }
}

This will generate the following output -
Table records(tutorial_count = null):
Table records(tutorial_count is null):
Author: mahnaz, Tutorial_count: null
Author: Jen, Tutorial_count: null

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
cursorObj = connection.cursor()
null_values_query = f"""
SELECT * FROM tcount_tbl
WHERE tutorial_count IS NULL
"""
cursorObj.execute(null_values_query)
# Fetching all the rows that meet the criteria
filtered_rows = cursorObj.fetchall()
for row in filtered_rows:
    print(row)
cursorObj.close()
connection.close()

(''mahnaz'', None)
(''Jen'', None)

A sequence is a series of integers, starting from 1 and incrementing by 1 with each successive value. These sequences are usually used in databases, as many applications require each row in a table to have a unique identifier, and sequences provide an easy way to generate such values.

Sequences in MySQL

MySQL does not have a built-in sequence feature but provides an alternative in the form of the AUTO_INCREMENT column, which serves a similar purpose.

In MySQL, the AUTO_INCREMENT attribute is used to automatically generate unique integer values (sequences) for a column. By default, this sequence begins with an initial value of 1 and increments by 1 for each new row that is added.

Syntax

Following is the syntax of AUTO_INCREMENT attribute in MySQL −

CREATE TABLE table_name (
   column1 datatype AUTO_INCREMENT,
   column2 datatype,
   column3 datatype,
   ...
   columnN datatype
);

Example

In the following example, we are creating a table named “CUSTOMERS” and, in addition, defining the AUTO_INCREMENT attribute for the “ID” column of the table −

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, let us insert some records into the above-created table −

INSERT INTO CUSTOMERS VALUES
(NULL, ''Ramesh'', 32, ''Ahmedabad'', 2000.00),
(NULL, ''Khilan'', 25, ''Delhi'', 1500.00),
(NULL, ''Kaushik'', 23, ''Kota'', 2000.00),
(NULL, ''Chaitali'', 25, ''Mumbai'', 6500.00),
(NULL, ''Hardik'', 27, ''Bhopal'', 8500.00),
(NULL, ''Komal'', 22, ''Hyderabad'', 4500.00),
(NULL, ''Muffy'', 24, ''Indore'', 10000.00);

Output

We can see in the table displayed below that the values in the “ID” column are automatically incremented −

Retrieving AUTO_INCREMENT Values

To obtain AUTO_INCREMENT values in MySQL, you can use the LAST_INSERT_ID() SQL function. This function can be used in any client that can issue SQL statements. Alternatively, in PERL and PHP scripts, specific functions are available to retrieve the auto-incremented value of the last record.

PERL Example

You can access the AUTO_INCREMENT value generated by a query using the mysql_insertid attribute. This attribute can be accessed either through a database handle or a statement handle, depending on how you execute the query.

The following example references it through the database handle −

$dbh->do ("INSERT INTO insect (name,date,origin)
VALUES(''moth'',''2001-09-14'',''windowsill'')");
my $seq = $dbh->{mysql_insertid};

PHP Example

After executing a query that generates an AUTO_INCREMENT value, you can retrieve the value using the mysql_insert_id( ) command −

mysql_query ("INSERT INTO insect (name,date,origin)
VALUES(''moth'',''2001-09-14'',''windowsill'')", $conn_id);
$seq = mysql_insert_id ($conn_id);

Renumbering an Existing Sequence

In some cases, you may need to re-sequence records in a table, especially if you have deleted many records. Be careful when resequencing if your table is related to other tables through joins.

If you determine that the resequencing of an AUTO_INCREMENT column is unavoidable, the way to do it is to drop the AUTO_INCREMENT column from the table, then add it again.

Example

The following example shows how to renumber the id values in the table using this technique.

ALTER TABLE CUSTOMERS DROP id;
ALTER TABLE CUSTOMERS
ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST,
ADD PRIMARY KEY (id);

Output

Following is the output obtained −

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

Starting a Sequence at a Specific Value

By default, MySQL starts sequences from 1, but you can specify a different initial value when creating the table.

Example

The following example demonstrates how to start the sequence from 100 during table creation −

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

Output

Output of the above code is as shown below −

Query OK, 0 rows affected (0.04 sec)

Alternatively, you can create the table first and then set the initial sequence value using the ALTER TABLE command as shown below −

ALTER TABLE CUSTOMERS AUTO_INCREMENT = 100;

Sequence Using a Client Program

We can also create a sequence using the client program.

Syntax

$sql = "CREATE TABLE insect (id INT UNSIGNED NOT NULL AUTO_INCREMENT,PRIMARY KEY (id),name VARCHAR(30) NOT NULL,date DATE NOT NULL,origin VARCHAR(30) NOT NULL)";
$mysqli->query($sql);

sql = "CREATE TABLE insect (id INT UNSIGNED NOT NULL AUTO_INCREMENT,PRIMARY KEY (id),name VARCHAR(30) NOT NULL,date DATE NOT NULL,origin VARCHAR(30) NOT NULL)";
con.query(sql);

String sql = "CREATE TABLE insect (id INT UNSIGNED NOT NULL AUTO_INCREMENT,PRIMARY KEY (id),name VARCHAR(30) NOT NULL,date DATE NOT NULL,origin VARCHAR(30) NOT NULL)";
statement.execute(sql);

create_table_query = "CREATE TABLE insect (id INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (id), name VARCHAR(30) NOT NULL, date DATE NOT NULL, origin VARCHAR(30) NOT NULL)"
cursorObj.execute(create_table_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.
'');
//create table with autoincrement sequene
$sql = "CREATE TABLE insect (id INT UNSIGNED NOT NULL AUTO_INCREMENT,PRIMARY KEY (id),name VARCHAR(30) NOT NULL,date DATE NOT NULL,origin VARCHAR(30) NOT NULL)";
if($mysqli->query($sql)){
    printf("Table created successfully....!n");
}
//let''s insert some record
$sql = "INSERT INTO insect (id,name,date,origin) VALUES(NULL,''housefly'',''2001-09-10'',''kitchen'')";
if($mysqli->query($sql)){
    printf("First record inserted successfully...!n");
}
$sql = "INSERT INTO insect (id,name,date,origin) VALUES(NULL,''millipede'',''2001-09-10'',''driveway'')";
if($mysqli->query($sql)){
    printf("Second record inserted successfully...!n");
}
$sql = "INSERT INTO insect (id,name,date,origin) VALUES(NULL,''grasshopper'',''2001-09-10'',''front yard'')";
if($mysqli->query($sql)){
    printf("Third record inserted successfully...!n");
}
printf("Table records: n");
$sql = "SELECT * FROM insect";
if($result = $mysqli->query($sql)){
    printf("Table record: n");
    while($row = mysqli_fetch_array($result)){
        printf("Id: %d, Name: %s, Date: %s, Origin %s",
        $row[''id''],
        $row[''name''],
        $row[''date''],
        $row[''origin'']);
        printf("n");
    }
}
if($mysqli->error){
    printf("Error message: ", $mysqli->error);
}
$mysqli->close();

Table created successfully....!
First record inserted successfully...!
Second record inserted successfully...!
Third record inserted successfully...!
Table records:
Table record:
Id: 1, Name: housefly, Date: 2001-09-10, Origin kitchen
Id: 2, Name: millipede, Date: 2001-09-10, Origin driveway
Id: 3, Name: grasshopper, Date: 2001-09-10, Origin front yard

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 table
 sql = "CREATE TABLE insect (id INT UNSIGNED NOT NULL AUTO_INCREMENT,PRIMARY KEY (id),name VARCHAR(30) NOT NULL,date DATE NOT NULL,origin VARCHAR(30) NOT NULL)";
 con.query(sql, function(err, result){
    if (err) throw err;
    console.log("Table created successfully....!");
    });
//now let''s insert some record
sql = "INSERT INTO insect (id,name,date,origin) VALUES(NULL,''housefly'',''2001-09-10'',''kitchen'')";
con.query(sql, function(err, result){
    if (err) throw err;
    console.log("First record inserted successfully...!");
    });
sql = "INSERT INTO insect (id,name,date,origin) VALUES(NULL,''millipede'',''2001-09-10'',''driveway'')";
con.query(sql, function(err, result){
    if (err) throw err;
    console.log("Second record inserted successfully...!");
    });
sql = "INSERT INTO insect (id,name,date,origin) VALUES(NULL,''grasshopper'',''2001-09-10'',''front yard'')";
    con.query(sql, function(err, result){
    if (err) throw err;
    console.log("Third record inserted successfully...!");
    });
sql = "SELECT * FROM INSECT";
con.query(sql, function(err, result){
console.log("Table records(with ID auto_increment sequence).");
    if (err) throw err;
    console.log(result);
    });
});

Table created successfully....!
First record inserted successfully...!
Second record inserted successfully...!
Third record inserted successfully...!
Table records(with ID auto_increment sequence).
[
  {
    id: 1,
    name: ''housefly'',
    date: 2001-09-09T18:30:00.000Z,
    origin: ''kitchen''
  },
  {
    id: 2,
    name: ''millipede'',
    date: 2001-09-09T18:30:00.000Z,
    origin: ''driveway''
  },
  {
    id: 3,
    name: ''grasshopper'',
    date: 2001-09-09T18:30:00.000Z,
    origin: ''front yard''
  }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class Sequence {
   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 insect (id INT UNSIGNED NOT NULL AUTO_INCREMENT,PRIMARY KEY (id),name VARCHAR(30) NOT NULL,date DATE NOT NULL,origin VARCHAR(30) NOT NULL)";
            st.execute(sql);
            System.out.println("Table ''insect'' created successfully...!");
            //lets insert some records into the table
            String sql1 = "INSERT INTO insect (id,name,date,origin) VALUES(NULL,''housefly'',''2001-09-10'',''kitchen''), (NULL,''millipede'',''2001-09-10'',''driveway''), (NULL,''grasshopper'',''2001-09-10'',''front yard'')";
            st.execute(sql1);
            System.out.println("Records inserted successfully...!");
            //lets print table records
            String sql2 = "SELECT * FROM INSECT";
            rs = st.executeQuery(sql2);
            System.out.println("Table records: ");
            while(rs.next()) {
               String id = rs.getString("id");
               String name = rs.getString("name");
               String date = rs.getString("date");
               String origin = rs.getString("origin");
               System.out.println("Id: " + id + ", Name: " + name + ", Date: " + date + ", Origin: " + origin);
            }
      }catch(Exception e) {
         e.printStackTrace();
      }
   }
}

Table ''insect'' created successfully...!
Records inserted successfully...!
Table records:
Id: 1, Name: housefly, Date: 2001-09-10, Origin: kitchen
Id: 2, Name: millipede, Date: 2001-09-10, Origin: driveway
Id: 3, Name: grasshopper, Date: 2001-09-10, Origin: front yard

import mysql.connector
# Connecting to MySQL
con = mysql.connector.connect(
    host="localhost",
    user="root",
    password="password",
    database="tut"
)
# Creating a cursor object
cursorObj = con.cursor()
# Creating the table
create_table_query = """
CREATE TABLE insect (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id),
name VARCHAR(30) NOT NULL,
date DATE NOT NULL,
origin VARCHAR(30) NOT NULL
)
"""
cursorObj.execute(create_table_query)
print("Table ''insect'' is created successfully!")
# Inserting records
sql = "INSERT INTO insect (name, date, origin) VALUES (%s, %s, %s)"
values = [
    (''housefly'', ''2001-09-10'', ''kitchen''),
    (''millipede'', ''2001-09-10'', ''driveway''),
    (''grasshopper'', ''2001-09-10'', ''front yard'')
]
cursorObj.executemany(sql, values)
print("Data inserted into the ''insect'' table.")
con.commit()
# Retrieving records
cursorObj.execute("SELECT * FROM insect")
records = cursorObj.fetchall()
# Printing the records
print("Table records(with ID auto_increment sequence).")
for record in records:
    print(record)
# Closing the connection
cursorObj.close()
con.close()

Table ''insect'' is created successfully!
Data inserted into the ''insect'' table.
Table records(with ID auto_increment sequence).
(1, ''housefly'', datetime.date(2001, 9, 10), ''kitchen'')
(2, ''millipede'', datetime.date(2001, 9, 10), ''driveway'')
(3, ''grasshopper'', datetime.date(2001, 9, 10), ''front yard'')

The MySQL Transactions

The MySQL transaction is a sequential group of database manipulation operations, which is performed as if it were one single work unit. In other words, a transaction will never be complete unless each individual operation within the group is successful. If any operation within the transaction fails, the entire transaction will fail.

Practically, you will club many SQL queries into a group and you will execute all of them together as a part of a transaction. This will ensure no data losses or failed executions of SQL queries.

Properties of Transactions

There are four standard properties of transactions, often referred to by the acronym ACID −

• Atomicity − This ensures that all operations within a transaction are treated as a single unit. Either all the operations within the transaction are completed successfully, or none of them are. If any part of the transaction fails, the entire transaction is rolled back, and the database is left in its original state.

• Consistency − This ensures that the database properly changes states upon a successfully committed transaction.

• Isolation − This enables transactions to operate independently and transparent to each other.

• Durability − This ensures that once a transaction is committed, its effects on the database are permanent and will survive system failures (e.g., power outages, hardware failures).

Transactional Statements in MySQL

In MySQL, the transactions begin with either START TRANSACTION, BEGIN or BEGIN WORK statements, and end with either a COMMIT or a ROLLBACK statement. The MySQL commands executed between the beginning and ending statements forms the main body of the transaction.

To enable or disable the auto-commit option in a transaction, you can use the SET AUTOCOMMIT command. To enable auto-commit, set the command to ”1” or ”ON,” and to disable it, set the command to ”0” or ”OFF.”

The COMMIT Command

The COMMIT command is a transaction control command in MySQL. When issued, it finalizes the changes made to a database table up to that point in a transaction, making those changes permanent. As a result, these changes become visible to other active sessions in MySQL.

Syntax

Following is the syntax to execute the COMMIT command in MySQL −

COMMIT;

Example

Let us create a table names CUSTOMERS using the following query −

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

We are inserting some records into the above-created table −

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, ''Hyderabad'', 4500.00),
(7, ''Muffy'', 24, ''Indore'', 10000.00);

The CUSTOMERS table displayed is as follows −

Using the following query, start a transaction and delete the records from the CUSTOMERS table whose AGE is 25, then COMMIT the changes in the database −

START TRANSACTION;
DELETE FROM CUSTOMERS WHERE AGE = 25;
COMMIT;

Verification

Two rows from the table would be deleted. To verify, display the modified CUSTOMERS table using the following SELECT statement −

SELECT * FROM CUSTOMERS;

Following is the output obtained −

The AUTOCOMMIT Command

You can control the behaviour of a transaction by setting session variable called AUTOCOMMIT. If AUTOCOMMIT is set to 1 (the default), then each SQL statement (within a transaction or not) is considered a complete transaction and committed by default when it finishes.

When AUTOCOMMIT is set to 0, by issuing the SET AUTOCOMMIT = 0 command, the subsequent series of statements acts like a transaction and no activities are committed until an explicit COMMIT statement is issued.

The ROLLBACK Command

The ROLLBACK command is a transactional command used to undo changes made in a transaction that have not been saved (committed) to the database. This command can only reverse the effects of transactions made since the last COMMIT or ROLLBACK statement was executed.

Syntax

Following is the syntax for ROLLBACK command in MySQL −

ROLLBACK;

Example

Using the following query, delete the records from the CUSTOMERS table whose AGE is 25, then ROLLBACK the changes in the database −

DELETE FROM CUSTOMERS WHERE AGE = 25;
ROLLBACK;

Verification

The table will not be affected. To verify, display the modified CUSTOMERS table using the following SELECT statement −

SELECT * FROM CUSTOMERS;

Following is the table obtained −

You must remember that ROLLBACK only works inside a transaction. If you try to execute it without starting a transaction, the changes would not be revoked.

The SAVEPOINT Command

A SAVEPOINT is a logical rollback point within a transaction in MySQL.

When you execute the ROLLBACK command, it reverts the changes made in the transaction back to the last COMMIT or the beginning of the transaction if there haven”t been any COMMITs. However, by creating save points within the transaction, you can establish specific points to which you can partially roll back the transaction. You can create multiple save points within a transaction to have multiple rollback options between two commits.

Syntax

The syntax for creating a SAVEPOINT command among transactions is as shown below −

SAVEPOINT SAVEPOINT_NAME;

The syntax for rolling back to the SAVEPOINT created is as follows −

ROLLBACK TO SAVEPOINT_NAME;

Example

In the following example, you plan to delete three different records from the CUSTOMERS table while creating SAVEPOINTs before each delete. This allows you to ROLLBACK to any SAVEPOINT at any time to restore the appropriate data to its original state −

SAVEPOINT SP1;
Query OK, 0 rows affected (0.00 sec)

DELETE FROM CUSTOMERS WHERE ID=1;
Query OK, 1 row affected (0.01 sec)

SAVEPOINT SP2;
Query OK, 0 rows affected (0.00 sec)

DELETE FROM CUSTOMERS WHERE ID=2;
Query OK, 0 rows affected (0.00 sec)

SAVEPOINT SP3;
Query OK, 0 rows affected (0.00 sec)

DELETE FROM CUSTOMERS WHERE ID=3;
Query OK, 1 row affected (0.01 sec)

Now that the three deletions have taken place, let us assume that you have changed your mind and decided to ROLLBACK to the SAVEPOINT identified as SP2. Since SP2 was created after the first deletion, this action undoes the last two deletions −

ROLLBACK TO SP2;

Verification

If you display the CUSTOMERS table using the following SELECT statement, you will notice that only the first deletion took place since you rolled back to SP2 −

SELECT * FROM CUSTOMERS;

The result obtained is as shown below −

Transaction-Safe Table Types in MySQL

In MySQL, not all table types natively support transactions. To use transactions effectively, you should create your tables in a specific way. Although various table types are available, the most commonly used transaction-safe table type is InnoDB.

To enable InnoDB table support, you may need a specific compilation parameter during the MySQL source compilation. If your MySQL version doesn”t include InnoDB support, you can request your Internet Service Provider (ISP) to provide a MySQL version with InnoDB support, or you can download and install MySQL-Max Binary Distribution for Windows or Linux/UNIX to work with InnoDB tables in a development environment.

If your MySQL installation supports InnoDB tables, you can create an InnoDB table as shown below −

CREATE TABLE tcount_tbl (
   tutorial_author varchar(40) NOT NULL,
   tutorial_count  INT
) ENGINE = InnoDB;

Following is the output obtained −

Query OK, 0 rows affected (0.05 sec)

You can also use other table types like GEMINI or BDB, but it depends on your installation, whether it supports these two table types or not.

Transactions Using a Client Program

We can also perform transactions using the client program.

Syntax

$sql = "START TRANSACTION";
$mysqli->query($sql);
...
$sql = "COMMIT";
$mysqli->query($sql);
...
$sql = "ROLLBACK";
$mysqli->query($sql);
...

sql = "START TRANSACTION";
con.query(sql);
...
sql = "COMMIT";
con.query(sql);
...
sql = "ROLLBACK";
con.query(sql);
...

String sql = "START TRANSACTION";
statement.execute(sql);
...
String sql = "COMMIT";
statement.execute(sql);
...
String sql = "ROLLBACK";
statement.execute(sql);
...

connection.start_transaction()
...
connection.commit()
...
connection.rollback()
...

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.
'');
 //start transaction
 $sql = "START TRANSACTION";
if($mysqli->query($sql)){
 printf("Transaction started....!n");
}
//print table record
 $sql = "SELECT * FROM CUSTOMERS";
 if($result = $mysqli->query($sql)){
     printf("Table records after transaction...!n");
     while($row = mysqli_fetch_array($result)){
         printf("ID %d, NAME %s, AGE %d, ADDRESS %s, SALARY %f",
         $row[''ID''],
         $row[''NAME''],
         $row[''AGE''],
         $row[''ADDRESS''],
         $row[''SALARY'']);
         printf("n");
     }
 }
 //let''s delete some records
 $sql = "DELETE FROM CUSTOMERS WHERE AGE  = 25";
 if($mysqli->query($sql)){
     printf("Records with age = 25 are deleted successfully....!n");
 }
 //lets delete some more records..
 $sql = "DELETE FROM CUSTOMERS WHERE SALARY  = 2000";
 if($mysqli->query($sql)){
     printf("Records with salary = 2000 are deleted successfully....!n");
 }
 printf("Table data after second delete (before rollback)...!n");
 $sql = "SELECT * FROM CUSTOMERS";
 if($result = $mysqli->query($sql)){
     while($row = mysqli_fetch_array($result)){
         printf("ID %d, NAME %s, AGE %d, ADDRESS %s, SALARY %f",
         $row[''ID''],
         $row[''NAME''],
         $row[''AGE''],
         $row[''ADDRESS''],
         $row[''SALARY'']);
         printf("n");
     }
 }
 $sql = "ROLLBACK";
 if($mysqli->query($sql)){
     printf("Transaction rollbacked successfully..!n");
 }
 printf("Table data after rollback: n");
 $sql = "SELECT * FROM CUSTOMERS";
 if($result = $mysqli->query($sql)){
     while($row = mysqli_fetch_array($result)){
         printf("ID %d, NAME %s, AGE %d, ADDRESS %s, SALARY %f",
         $row[''ID''],
         $row[''NAME''],
         $row[''AGE''],
         $row[''ADDRESS''],
         $row[''SALARY'']);
         printf("n");
     }
 }
 if($mysqli->error){
     printf("Error message: ", $mysqli->error);
 }
 $mysqli->close();

Transaction started....!
Table records after transaction...!
ID 1, NAME Ramesh, AGE 32, ADDRESS Ahmedabad, SALARY 2000.000000
ID 2, NAME Khilan, AGE 25, ADDRESS Delhi, SALARY 1500.000000
ID 3, NAME kaushik, AGE 23, ADDRESS Kota, SALARY 2000.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
Records with age = 25 are deleted successfully....!
Records with salary = 2000 are deleted successfully....!
Table data after second delete (before rollback)...!
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
Transaction rollbacked successfully..!
Table data after rollback:
ID 1, NAME Ramesh, AGE 32, ADDRESS Ahmedabad, SALARY 2000.000000
ID 2, NAME Khilan, AGE 25, ADDRESS Delhi, SALARY 1500.000000
ID 3, NAME kaushik, AGE 23, ADDRESS Kota, SALARY 2000.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

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 = "START TRANSACTION";
con.query(sql, function(err, result){
    if (err) throw err;
    console.log("Transaction started....!");
});
 sql = "SELECT * FROM CUSTOMERS";
 con.query(sql, function(err, result){
    console.log("Table records after transaction...!");
    if (err) throw err;
    console.log(result);
    });
//delete record
sql = "DELETE FROM CUSTOMERS WHERE AGE  = 25";
con.query(sql, function(err, result){
    if (err) throw err;
    console.log("Records with age = 25 are deleted successfully....!");
});
//now lets delete more records
sql = "DELETE FROM CUSTOMERS WHERE SALARY  = 2000";
con.query(sql, function(err, result){
    if (err) throw err;
    console.log("Records with salary = 2000 are deleted successfully....!");
});
//print table records before rollback;
sql = "SELECT * FROM CUSTOMERS";
con.query(sql, function(err, result){
   console.log("Table Data After Second Delete (Before Rollback)");
   if (err) throw err;
   console.log(result);
   });
//rollback the transaction
sql = "ROLLBACK";
con.query(sql, function(err, result){
    if (err) throw err;
    console.log("Transaction rollbacked successfully..!");
});
//print table data after rollback;
sql = "SELECT * FROM CUSTOMERS";
 con.query(sql, function(err, result){
    console.log("Table records after rollback...!");
    if (err) throw err;
    console.log(result);
    });
});

Transaction started....!
Table records after transaction...!
[
  {
    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''
  }
]
Records with age = 25 are deleted successfully....!
Records with salary = 2000 are deleted successfully....!
Table Data After Second Delete (Before Rollback)
[
  {
    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''
  }
]
Transaction rollbacked successfully..!
Table records after rollback...!
[
  {
    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''
  }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class Transaction {
   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...!");
            //start transaction
            String sql = "START TRANSACTION";
            st.execute(sql);
            System.out.println("Transaction started....!");
            //print customers record after starting transaction
            String sql1 = "SELECT * FROM CUSTOMERS";
            rs = st.executeQuery(sql1);
            System.out.println("Table records after starting transaction: ");
            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 delete some records
            String sql2 = "DELETE FROM CUSTOMERS WHERE AGE = 25";
            st.execute(sql2);
            System.out.println("Customer with age 25 deleted successfully...!");
            String sql4 = "DELETE FROM CUSTOMERS WHERE SALARY  = 2000";
            st.execute(sql4);
            System.out.println("Customer with age 2000 deleted successfully...!");
            String sql5 = "SELECT * FROM CUSTOMERS";
            rs = st.executeQuery(sql5);
            System.out.println("Table records before rollback: ");
            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 roll-back the transaction
            String r = "ROLLBACK";
            st.execute(r);
            System.out.println("Transaction rollbacked successfully...!");
            String sql6 = "SELECT * FROM CUSTOMERS";
            rs = st.executeQuery(sql6);
            System.out.println("Table records after rollback: ");
            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();
      }
   }
}

Transaction started....!
Table records after starting transaction:
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
Customer with age 25 deleted successfully...!
Customer with age 2000 deleted successfully...!
Table records before rollback:
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
Transaction rollbacked successfully...!
Table records after rollback:
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

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()
# Start the transaction
connection.start_transaction()
# Displaying the table before deleting
select_query = "SELECT * FROM customers"
cursorObj.execute(select_query)
print("Table Data after starting Transaction:")
for row in cursorObj.fetchall():
    print(row)
# Execute the DELETE statement
delete_query = "DELETE FROM customers WHERE AGE = 25"
cursorObj.execute(delete_query)
print("Rows with AGE = 25 are deleted.")
# Commit the transaction
connection.commit()
print("Transaction committed successfully.")
# Displaying the table after deleting (changes are permanent)
cursorObj.execute(select_query)
print("Table Data After Transaction:")
for row in cursorObj.fetchall():
    print(row)
# Now, let us delete more records
delete_query1 = "DELETE FROM customers WHERE SALARY = 2000"
cursorObj.execute(delete_query1)
print("Rows with SALARY = 2000 are deleted.")
# Display the table after the second delete operation (changes are not committed yet)
cursorObj.execute(select_query)
print("Table Data After Second Delete (Before Rollback):")
for row in cursorObj.fetchall():
    print(row)
# Rollback the transaction
connection.rollback()
print("Transaction rollbacked successfully.")
# Displaying the table after rollback (changes are reverted)
cursorObj.execute(select_query)
print("Table Data After Rollback:")
for row in cursorObj.fetchall():
    print(row)
# Closing the cursor and connection
cursorObj.close()
connection.close()

Table Data after starting Transaction:
(1, ''Ramesh'', 32, ''Ahmedabad'', Decimal(''2000.00''))
(2, ''Khilan'', 25, ''Delhi'', Decimal(''1500.00''))
(3, ''kaushik'', 23, ''Kota'', Decimal(''2000.00''))
(4, ''Chaitali'', 25, ''Mumbai'', Decimal(''6500.00''))
(5, ''Hardik'', 27, ''Bhopal'', Decimal(''8500.00''))
(6, ''Komal'', 22, ''MP'', Decimal(''4500.00''))
(7, ''Muffy'', 24, ''Indore'', Decimal(''10000.00''))
Rows with AGE = 25 are deleted.
Transaction committed successfully.
Table Data After Transaction:
(1, ''Ramesh'', 32, ''Ahmedabad'', Decimal(''2000.00''))
(3, ''kaushik'', 23, ''Kota'', Decimal(''2000.00''))
(5, ''Hardik'', 27, ''Bhopal'', Decimal(''8500.00''))
(6, ''Komal'', 22, ''MP'', Decimal(''4500.00''))
(7, ''Muffy'', 24, ''Indore'', Decimal(''10000.00''))
Rows with SALARY = 2000 are deleted.
Table Data After Second Delete (Before Rollback):
(5, ''Hardik'', 27, ''Bhopal'', Decimal(''8500.00''))
(6, ''Komal'', 22, ''MP'', Decimal(''4500.00''))
(7, ''Muffy'', 24, ''Indore'', Decimal(''10000.00''))
Transaction rollbacked successfully.
Table Data After Rollback:
(1, ''Ramesh'', 32, ''Ahmedabad'', Decimal(''2000.00''))
(3, ''kaushik'', 23, ''Kota'', Decimal(''2000.00''))
(5, ''Hardik'', 27, ''Bhopal'', Decimal(''8500.00''))
(6, ''Komal'', 22, ''MP'', Decimal(''4500.00''))
(7, ''Muffy'', 24, ''Indore'', Decimal(''10000.00''))

Tables or result sets in a database usually contain duplicate records. While duplicates are generally allowed, there are situations where it is necessary to prevent them. In such cases, it becomes essential to identify and remove duplicate records from a database table.

Importance of Handling MySQL Duplicates

There are various reasons why handling duplicates in a database becomes necessary. One of the main reasons is that the existence of duplicates in an organizational database will lead to logical errors. In addition to it, we need to handle redundant data to prevent the following consequences −

• Duplicate data occupies storage space, reducing the efficiency of database usage and increasing storage costs.
• Dealing with duplicate records consumes additional resources, driving up the overall cost of maintaining the database.
• Duplicates in a database can lead to logical errors in data, affecting the integrity and reliability of the information stored.

Preventing Duplicate Entries

You can use a PRIMARY KEY or a UNIQUE Index on a table with the appropriate fields to prevent duplicate record entries into a table.

Example

The following table contains no such index or primary key, so it would allow duplicate records for first_name and last_name.

CREATE TABLE CUSTOMERS (
   first_name CHAR(20),
   last_name CHAR(20),
   sex CHAR(10)
);

To prevent multiple records with the same first and last name values from being created in this table, add a PRIMARY KEY to its definition. When you do this, it is also necessary to declare the indexed columns to be NOT NULL, because a PRIMARY KEY does not allow NULL values −

CREATE TABLE CUSTOMERS (
   first_name CHAR(20) NOT NULL,
   last_name CHAR(20) NOT NULL,
   sex CHAR(10),
   PRIMARY KEY (last_name, first_name)
);

Using INSERT IGNORE Query −

The existence of a unique index in a table normally causes an error when attempting to insert a record that duplicates an existing record in the indexed column(s).

To handle this situation without generating an error, you can use the “INSERT IGNORE” command. When a record is not a duplicate, MySQL inserts it as usual. However, if the record is duplicate, the “IGNORE” keyword instructs MySQL to discard it without producing an error.

The provided example does not result in an error, and it also ensures that duplicate records are not inserted −

INSERT IGNORE INTO CUSTOMERS (LAST_NAME, FIRST_NAME)
VALUES (''Jay'', ''Thomas''), (''Jay'', ''Thomas'');

We get the following output −

Query OK, 1 row affected, 1 warning (0.01 sec)
Records: 2  Duplicates: 1  Warnings: 1

Using REPLACE Query −

Instead of using the INSERT command, consider using the REPLACE command. When dealing with a new record, it is inserted just as with INSERT. However, if it is a duplicate, the new record replaces the old one.

REPLACE INTO CUSTOMERS (LAST_NAME, FIRST_NAME)
VALUES ( ''Ajay'', ''Kumar''), ( ''Ajay'', ''Kumar'');

Following is the output of the above code −

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

Your choice between the INSERT IGNORE and REPLACE commands should depend on the specific duplicate-handling behaviour you wish to achieve. The INSERT IGNORE command retains the first set of duplicated records and discards the remaining. On the other hand, the REPLACE command keeps the last set of duplicates and removes any earlier instances.

Using UNIQUE Constraint −

Another way to enforce uniqueness is to add a UNIQUE index rather than a PRIMARY KEY to a table −

CREATE TABLE CUSTOMERS (
   first_name CHAR(20) NOT NULL,
   last_name CHAR(20) NOT NULL,
   sex CHAR(10),
   UNIQUE (last_name, first_name)
);

Counting and Identifying Duplicates

You can use the COUNT function and GROUP BY clause to count and identify duplicate records based on specific columns.

Example

Following is the query to count duplicate records with first_name and last_name in a table −

SELECT COUNT(*) as repetitions, last_name, first_name
FROM CUSTOMERS
GROUP BY last_name, first_name
HAVING repetitions > 1;

This query will return a list of all the duplicate records in the CUSTOMERS table. In general, to identify sets of values that are duplicated, follow the steps given below.

• Determine which columns may contain duplicated values.

• Include those columns in the column selection list, along with COUNT(*).

• List the columns in the GROUP BY clause as well.

• Apply a HAVING clause to filter unique values by requiring the group counts to be greater than one.

Eliminating Duplicates from a Query Result

You can use the DISTINCT command along with the SELECT statement to find out unique records available in a table.

SELECT DISTINCT last_name, first_name
FROM CUSTOMERS
ORDER BY last_name;

An alternative to the DISTINCT command is to add a GROUP BY clause that specifies the columns you are selecting. This approach eliminates duplicates and retrieves only the unique combinations of values from the specified columns.

SELECT last_name, first_name
FROM CUSTOMERS
GROUP BY (last_name, first_name);

Removing Duplicates Using Table Replacement

If you have duplicate records in a table and you want to remove all the duplicate records from that table, then follow the procedure given below −

CREATE TABLE tmp AS
SELECT DISTINCT last_name, first_name, sex
FROM CUSTOMERS;

DROP TABLE CUSTOMERS;
ALTER TABLE tmp RENAME TO CUSTOMERS;

Handling Duplicates Using a Client Program

We can also handle duplicate using the client program.

Syntax

$sql = "INSERT IGNORE INTO person_tbl (last_name, first_name) VALUES( ''Jay'', ''Thomas'')";
$mysqli->query($sql);

sql = "INSERT IGNORE INTO person_tbl (last_name, first_name) VALUES( ''Jay'', ''Thomas'')";
con.query(sql);

String sql = "INSERT IGNORE INTO person_tbl (last_name, first_name) VALUES( ''Jay'', ''Thomas'')";
statement.execute(sql);

sql = "INSERT IGNORE INTO person_tbl (last_name, first_name) VALUES( ''Jay'', ''Thomas'')"
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 = "CREATE TABLE person_tbl (first_name CHAR(20) NOT NULL, last_name CHAR(20) NOT NULL, sex CHAR(10), PRIMARY KEY (last_name, first_name))";
if($mysqli->query($sql)){
    printf("Table created successfully...!n");
}
//let''s insert some records
$sql = "INSERT INTO person_tbl (last_name, first_name) VALUES( ''Jay'', ''Thomas'')";
if($mysqli->query($sql)){
    printf("First record inserted successfully...!n");
}
$sql = "INSERT INTO person_tbl (last_name, first_name) VALUES( ''John'', ''Smith'')";
if($mysqli->query($sql)){
    printf("Second record inserted successfully...!n");
}
//now lets insert duplicate record with IGNORE keyword
$sql = "INSERT IGNORE INTO person_tbl (last_name, first_name) VALUES( ''Jay'', ''Thomas'')";
if($mysqli->query($sql)){
    printf("Duplicate record inserted successfully using IGNORE keyword...!n");
}
$sql = "SELECT * from person_tbl";
if($result = $mysqli->query($sql)){
    printf("Table records: n");
    while($row = mysqli_fetch_array($result)){
        printf("First Name: %s, Last name: %s, Sex: %s",
        $row[''first_name''],
        $row[''last_name''],
        $row[''sex'']);
        printf("n");
    }
}
//lets insert a duplicate record
$sql = "INSERT INTO person_tbl (last_name, first_name) VALUES( ''John'', ''Smith'')";
if(!$mysqli->query($sql)){
    printf("You can''t insert any duplicate records...!n");
}
if($mysqli->error){
    printf("Error message: ", $mysqli->error);
}
$mysqli->close();

Table created successfully...!
First record inserted successfully...!
Second record inserted successfully...!
Duplicate record inserted successfully using IGNORE keyword...!
Table records:
First Name: Thomas, Last name: Jay, Sex:
First Name: Smith, Last name: John, Sex:
PHP Fatal error:  Uncaught mysqli_sql_exception: Duplicate entry ''John-Smith'' for key ''person_tbl.PRIMARY'' in D:testhandlingduplicates.php:48

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 table
 sql = "CREATE TABLE person_tbl (first_name CHAR(20) NOT NULL, last_name CHAR(20) NOT NULL, sex CHAR(10), PRIMARY KEY (last_name, first_name))";
 con.query(sql, function(err, result){
    if (err) throw err;
    console.log("Table created successfully....!");
    });
//now let''s insert some records
sql = "INSERT INTO person_tbl (last_name, first_name) VALUES( ''Jay'', ''Thomas'')";
con.query(sql, function(err, result){
    if (err) throw err;
    console.log("First record inserted successfully...!");
    });
sql = "INSERT INTO person_tbl (last_name, first_name) VALUES( ''John'', ''Smith'')";
con.query(sql, function(err, result){
    if (err) throw err;
    console.log("Second record inserted successfully...!");
    });
//now lets insert duplicate record with IGNORE keyword
sql = "INSERT IGNORE INTO person_tbl (last_name, first_name) VALUES( ''Jay'', ''Thomas'')";
    con.query(sql, function(err, result){
        console.log("Insert duplicate record with IGNORE keyword")
    if (err) throw err;
    console.log("Duplicate record inserted successfully with the help of IGNORE keyword");
    });
//lets insert a duplicate record
sql = "INSERT INTO person_tbl (last_name, first_name) VALUES( ''John'', ''Smith'')";
    con.query(sql, function(err, result){
        console.log("Insert duplicate record");
    if (err) throw err;
    console.log("You can''t insert the duplicate record because columns are primary key");
    });
sql = "SELECT * FROM person_tbl";
con.query(sql, function(err, result){
    console.log("Table records(with ID auto_increment sequence).");
    if (err) throw err;
    console.log(result);
    });
});

Table created successfully....!
First record inserted successfully...!
Second record inserted successfully...!
Insert duplicate record with IGNORE keyword
Duplicate record inserted successfully with the help of IGNORE keyword
Insert duplicate record
Insert duplicate record
D:test1duplicate.js:43
    if (err) throw err;              ^

Error: Duplicate entry ''John-Smith'' for key ''person_tbl.PRIMARY''

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class HandleDuplicates {
   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 person_tbl ( first_name CHAR(20) NOT NULL, last_name CHAR(20) NOT NULL, sex CHAR(10), PRIMARY KEY (last_name, first_name))";
            st.execute(sql);
            System.out.println("Table created successfully....!");
            //let''s insert some records
            String sql1 = "INSERT IGNORE INTO person_tbl (last_name, first_name) VALUES( ''Jay'', ''Thomas'')";
            st.execute(sql1);
            System.out.println("Record inserted successfully...!");
            String sql2 = "INSERT IGNORE INTO person_tbl (last_name, first_name) VALUES( ''Jay'', ''Thomas'')";
            st.execute(sql2);
            System.out.println("Duplicate record inserted successfully...!");
            //lets print the table records
            String sql3 = "SELECT * FROM PERSON_TBL";
            rs = st.executeQuery(sql3);
            System.out.println("Table records: ");
            while(rs.next()) {
               String fname = rs.getString("first_name");
               String lname = rs.getString("last_name");
               System.out.println("First name: " + fname + ", Last name: " + lname);
            }
      }catch(Exception e) {
         e.printStackTrace();
      }
   }
}

Table created successfully....!
Record inserted successfully...!
Duplicate record inserted successfully...!
Table records:
First name: Thomas, Last name: Jay

import mysql.connector
# Connecting to MySQL
con = mysql.connector.connect(
    host="localhost",
    user="root",
    password="password",
    database="tut"
)
# Creating a cursor object
cursorObj = con.cursor()
# Creating the table
create_table_query = """
CREATE TABLE person_tbl (
    first_name CHAR(20) NOT NULL,
    last_name CHAR(20) NOT NULL,
    sex CHAR(10),
    PRIMARY KEY (last_name, first_name)
)
"""
cursorObj.execute(create_table_query)
print("Table ''person_tbl'' is created successfully!")
# Inserting some records
first_record = "INSERT INTO person_tbl (last_name, first_name) VALUES( ''Jay'', ''Thomas'')"
print("First record inserted successfully!")
cursorObj.execute(first_record)

second_record = "INSERT INTO person_tbl (last_name, first_name) VALUES( ''John'', ''Smith'')"
print("Second record inserted successfully!")
cursorObj.execute(second_record)
# Insert duplicate record with IGNORE keyword
sql = "INSERT IGNORE INTO person_tbl (last_name, first_name) VALUES( ''Jay'', ''Thomas'')"
print("Duplicate record inserted successfully with the help of IGNORE keyword")
cursorObj.execute(sql)
# Insert a duplicate record (this will throw an error)
try:
    cursorObj.execute("INSERT INTO person_tbl (last_name, first_name) VALUES( ''John'', ''Smith'')")
    print("Insert duplicate record")
except mysql.connector.Error as err:
    print("Insert duplicate record error:", err)
con.commit()
# Retrieving records
cursorObj.execute("SELECT * FROM person_tbl")
records = cursorObj.fetchall()
# Printing the records
print("Table records.")
for record in records:
    print(record)
# Closing the connection
cursorObj.close()
con.close()

First record inserted successfully!
Second record inserted successfully!
Duplicate record inserted successfully with the help of IGNORE keyword
Insert duplicate record error: 1062 (23000): Duplicate entry ''John-Smith'' for key ''person_tbl.PRIMARY''
Table records.
(''Thomas'', ''Jay'', None)
(''Smith'', ''John'', None)

The SQL Injection in MySQL is a harmful approach where an attacker inserts or “injects” harmful SQL code into a database query. This can be done through user inputs such as forms, URL parameters, or cookies. The attacker takes advantage of weaknesses in the software to steal information from the database.

How SQL Injection Works

Imagine you have a web application with a login page. When a user enters their username and password, the application checks these credentials against a MySQL database. The SQL query might look like as given below −

SELECT * FROM users
WHERE username = ''user'' AND password = ''password

In a secure application, the ”user” and ”password” would be the actual values entered by the user. However, in an SQL Injection attack, an attacker can manipulate the input fields to inject malicious SQL code.

For example, they might enter the following as the username −

'' OR ''1'' = ''1

Now, the SQL query becomes −

SELECT * FROM users
WHERE username = '''' OR ''1'' = ''1'' AND password = ''password

Because ”1” always equals ”1”, this condition is always true, and the attacker gains unauthorized access to the application. In this way, they trick the application into granting access without a valid password.

Preventing SQL Injection

To prevent SQL injection, it is important to handle escape characters properly when using scripting languages like PERL and PHP. When working with PHP and MySQL, you can use the mysql_real_escape_string() function to escape input characters that have special meaning in MySQL. Following is an example of how to do this −

if (get_magic_quotes_gpc()) {
   $name = stripslashes($name);
}
// escape input characters
$name = mysql_real_escape_string($name);

// Perform the MySQL query with the escaped ''name''
mysqli_query("SELECT * FROM CUSTOMERS WHERE name=''{$name}''");

The LIKE Quandary

Now, let us address the issue with the LIKE clause. When dealing with user-provided data that may include ”%” and ”_” characters, it is important to create a custom escaping mechanism to treat them as literals. You can achieve this by combining “mysql_real_escape_string()” function with “addcslashes()” function, which allows you to specify a character range to escape. Following is an example of how you can do it −

// Escape and convert ''%'' and ''_'' in the user-provided string
$sub = addcslashes(mysql_real_escape_string("%str"), "%_");

// $sub will be equal to %str_

// Use the escaped string in the LIKE query
mysqli_query("SELECT * FROM messages
   WHERE subject LIKE ''{$sub}%''");

In this way, you ensure that the ”%” and ”_” characters in the user input are treated as literal characters in the SQL query, preventing SQL injection and maintaining the integrity of your database operations.

The MySQL subquery, also known as an inner query or nested query, is a query inside another query. It allows you to retrieve data from one or more tables based on the results of another query. Subqueries can be used in various parts of SQL statements, including SELECT, INSERT, UPDATE, and DELETE.

Subquery with the SELECT Statement

A subquery within a SELECT statement is used to filter the results of the main query based on the values retrieved from the subquery.

Syntax

Following is the basic syntax of a subquery within a SELECT statement −

SELECT column1, column2, ...
FROM table1
WHERE columnN operator
(SELECT column_name FROM table2 WHERE condition);

Example

First, let us create a table with the name CUSTOMERS using the following query −

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

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

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, ''Hyderabad'', 4500.00 ),
(7, ''Muffy'', 24, ''Indore'', 10000.00 );

The CUSTOMERS table displayed is as shown below −

The following query retrieves the salaries of all customers from the CUSTOMERS table whose ID”s match with the ID”s in the same table −

SELECT SALARY FROM CUSTOMERS
WHERE ID IN
(SELECT ID FROM CUSTOMERS);

Output

The output for the query above is produced as given below −

Subquery with the INSERT Statement

We can also use the subqueries with the INSERT statements in MySQL. The INSERT statement will use the data returned from the subquery to insert into another table.

Syntax

Following is the basic syntax of a subquery within an INSERT statement −

INSERT INTO target_table (column1, column2, ...)
SELECT source_column1, source_column2, ...
FROM source_table
WHERE condition;

Example

Before performing the subqueries with INSERT statement, let us create a table named “CUSTOMERS_BKP” with a similar structure as CUSTOMERS table −

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

Now, let us insert all records from CUSTOMERS table into the CUSTOMERS_BKP table using the following query −

INSERT INTO CUSTOMERS_BKP
SELECT * FROM CUSTOMERS
WHERE ID IN (SELECT ID FROM CUSTOMERS);

Output

The records of CUSTOMERS table has successfully inserted into CUSTOMERS_BKP table −

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

Verification

Let us verify whether the CUSTOMERS_BKP table have records using the following SELECT statement −

SELECT * FROM CUSTOMERS_BKP;

As we can see in the table below, all the records in CUSTOMERS table is inserted into CUSTOMERS_BKP table −

Subquery with Comparison Operator

The MySQL Subquery with comparison operator allows us to use a query inside another query and compare its result with the outer query using comparison operators.

Syntax

Following is the basic syntax of a subquery with comparison operators −

SELECT column_name [, column_name ]
FROM   table1 [, table2 ]
WHERE  column_name OPERATOR
(SELECT column_name [, column_name ]
FROM table1 [, table2 ]
[WHERE] .....)

Example

The following query retrieves all the CUSTOMERS from the table CUSTOMERS_BKP with an AGE greater than 23 and returns their IDs.

SELECT * FROM CUSTOMERS_BKP
WHERE ID IN (SELECT ID FROM CUSTOMERS_BKP
WHERE AGE > 23);

Output

The output for the query above is produced as given below −

Subquery with IN or NOT-IN Operator

The MySQL subqueries with IN/NOT-IN operators are used to filter data based on whether values from one query match or do not match values from another query −

• IN matches any value from the list

• NOT-IN excludes any value from the list.

Example

The following query retrieves all the records from the CUSTOMERS table where the ADDRESS is not “Hyderabad” by comparing it to addresses in the CUSTOMERS_BKP table −

SELECT * FROM CUSTOMERS
WHERE ADDRESS NOT IN (
SELECT ADDRESS FROM CUSTOMERS_BKP WHERE ADDRESS = "Hyderabad");

Output

Following is the output of the above query −

Example

Now, the following query retrieves all the rows from the CUSTOMERS table where the ADDRESS is “Hyderabad” by using a subquery to fetch all addresses that match “Hyderabad” from the CUSTOMERS_BKP table −

SELECT * FROM CUSTOMERS
WHERE ADDRESS IN (
SELECT ADDRESS FROM CUSTOMERS_BKP WHERE ADDRESS = "Hyderabad");

Output

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

Subquery Using a Client Program

We can also perform Subquery using the client program.

Syntax

$sql = "SELECT ID, NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS WHERE ID IN (SELECT ID FROM CUSTOMERS WHERE SALARY > 2000)";
$mysqli->query($sql);

sql = "SELECT NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS WHERE ID IN (SELECT ID FROM CUSTOMERS WHERE SALARY > 2000)";
con.query(sql);

String sql = "SELECT ID, NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS WHERE ID IN (SELECT ID FROM CUSTOMERS WHERE SALARY > 2000)";
statement.executeQuery(sql);

sub_query = "SELECT SALARY FROM CUSTOMERS WHERE ID IN (SELECT ID FROM CUSTOMERS)"
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 ID, NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS WHERE ID IN (SELECT ID FROM CUSTOMERS WHERE SALARY > 2000)";
printf("Table records: n");
if($result = $mysqli->query($sql)){
    while($row = mysqli_fetch_array($result)){
        printf("Id: %d, NAME: %s, AGE: %d, ADDRESS: %s, SALARY: %f",
        $row[''ID''],
        $row[''NAME''],
        $row[''AGE''],
        $row[''ADDRESS''],
        $row[''SALARY'']);
        printf("n");
    }
}
if($mysqli->error){
    printf("Error message: ", $mysqli->error);
}
$mysqli->close();

Table records:
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: Hyderabad, SALARY: 4500.000000
Id: 7, NAME: Muffy, AGE: 24, ADDRESS: Indore, SALARY: 10000.000000

NodeJS program
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 table
 sql = "SELECT NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS WHERE ID IN (SELECT ID FROM CUSTOMERS WHERE SALARY > 2000)";
 con.query(sql, function(err, result){
    console.log("Subquery executed successfully...!");
    console.log("Table records: ")
    if (err) throw err;
    console.log(result);
    });
});

Subquery executed successfully...!
Table records:
[
  { NAME: ''Chaitali'', AGE: 25, ADDRESS: ''Mumbai'', SALARY: ''6500.00'' },
  { NAME: ''Hardik'', AGE: 27, ADDRESS: ''Bhopal'', SALARY: ''8500.00'' },
  { NAME: ''Komal'', AGE: 22, ADDRESS: ''Hyderabad'', SALARY: ''4500.00'' },
  { NAME: ''Muffy'', AGE: 24, ADDRESS: ''Indore'', SALARY: ''10000.00'' }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class SubQuery {
  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 = "SELECT ID, NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS WHERE ID IN (SELECT ID FROM CUSTOMERS WHERE SALARY > 2000)";
            rs = st.executeQuery(sql);
            System.out.println("Table records: ");
            while(rs.next()) {
              String id = rs.getString("id");
              String name = rs.getString("name");
              String age = rs.getString("age");
              String address = rs.getString("address");
              String salary = rs.getString("salary");
              System.out.println("Id: " + id + ", Name: " + name + ", Age: " + age + ", Address: " + address + ", Salary: " + salary);
            }
    }catch(Exception e) {
      e.printStackTrace();
    }
  }
}

Table records:
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: Hyderabad, Salary: 4500.00
Id: 7, Name: Muffy, Age: 24, Address: Indore, Salary: 10000.00

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
cursorObj = connection.cursor()
# Subquery to fetch the salaries of all customers whose ID is present in the same table
sub_query = f"""
SELECT SALARY FROM CUSTOMERS
WHERE ID IN
(SELECT ID FROM CUSTOMERS);
"""
cursorObj.execute(sub_query)
# Fetching all the rows that meet the criteria
filtered_rows = cursorObj.fetchall()
for row in filtered_rows:
    print(row)
cursorObj.close()
connection.close()

(Decimal(''2000.00''),)
(Decimal(''1500.00''),)
(Decimal(''2000.00''),)
(Decimal(''6500.00''),)
(Decimal(''8500.00''),)
(Decimal(''4500.00''),)
(Decimal(''10000.00''),)