The MySQL UPSERT Operation

The MySQL UPSERT operation combines INSERT and UPDATE into a single statement, allowing you to insert a new row into a table or update an existing row if it already exists. We can understand in the name (UPSERT) itself, where UP stands for UPDATE and SERT stands for INSERT.

This tutorial covers three common methods to perform UPSERT operations in MySQL: INSERT IGNORE, REPLACE, and INSERT with ON DUPLICATE KEY UPDATE.

UPSERT Using INSERT IGNORE

The INSERT IGNORE statement in MySQL allows you to insert a new record into a table. If a record with the same primary key already exists, it ignores the error and doesn”t insert the new record.

Example

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

CREATE TABLE COURSES(
   ID int,
   COURSE varchar(50) primary key,
   COST int
);

Here, we are inserting records into the COURSES table −

INSERT INTO COURSES VALUES
(1, "HTML", 3000),
(2, "CSS", 4000),
(3, "JavaScript", 6000),
(4, "Node.js", 10000),
(5, "React.js", 12000),
(6, "Angular", 8000),
(7, "Php", 9000);

The COURSES table obtained is as follows −

Now, we attempt to insert a duplicate record using the INSERT INTO statement in the following query −

INSERT INTO COURSES VALUES (6, ''Angular'', 9000);

This results in an error because a duplicate record cannot be inserted −

ERROR 1062 (23000): Duplicate entry ''Angular'' for key ''courses.PRIMARY''

Using INSERT IGNORE −

Now, let us perform the same operation using INSERT IGNORE statement −

INSERT IGNORE INTO COURSES VALUES (6, ''Angular'', 9000);

Output

As we can see in the output below, the INSERT IGNORE statement ignores the error −

Query OK, 0 rows affected, 1 warning (0.00 sec)

Verification

We can verify the COURSES table to see that the error was ignored using the following SELECT query −

SELECT * FROM COURSES;

The table obtained is as follows −

UPSERT Using REPLACE

The MySQL REPLACE statement first attempts to delete the existing row if it exists and then inserts the new row with the same primary key. If the row does not exist, it simply inserts the new row.

Example

Let us replace or update a row in the COURSES table. If a row with COURSE “Angular” already exists, it will update its values for ID and COST with the new values provided. Else, a new row will be inserted with the specified values in the query −

REPLACE INTO COURSES VALUES (6, ''Angular'', 9000);

Output

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

Query OK, 2 rows affected (0.01 sec)

Verification

Now, let us verify the COURSES table using the following SELECT query −

SELECT * FROM COURSES;

We can see in the following table, the REPLACE statement added a new row after deleting the duplicate row −

UPSERT Using INSERT with ON DUPLICATE KEY UPDATE

The INSERT … ON DUPLICATE KEY UPDATE statement in MySQL attempts to insert a new row. If the row already exists, it updates the existing row with the new values specified in the statement.

Example

Here, we are updating the duplicate record using the following query −

INSERT INTO COURSES VALUES (6, ''Angular'', 9000)
ON DUPLICATE KEY UPDATE
ID = 6, COURSE = ''Angular'', COST = 20000;

Output

As we can see in the output below, no error is generated and the duplicate row gets updated.

Query OK, 2 rows affected (0.01 sec)

Verification

Let us verify the COURSES table using the following SELECT query −

SELECT * FROM COURSES;

As we can see the table below, the INSERT INTO… ON DUPLICATE KEY UPDATE statement updated the duplicate record −

MySQL provides a native JSON (JavaScript Object Notation) datatype that enables efficient access to the data in JSON documents. This datatype is introduced in MySQL versions 5.7.8 and later.

Before it was introduced, the JSON-format strings were stored in the string columns of a table. However, the JSON datatype proves to be more advantageous than strings due to the following reasons −

• It automatically validates the JSON documents, displaying an error whenever an invalid document is stored.
• It stores the JSON documents in an internal format allowing easy read access to the document elements. Hence, when the MySQL server later reads the stored JSON values in a binary format, it just enables the server to look up subobjects or nested values directly by key or array index without reading all values before or after them in the document.

The storage requirements for JSON documents are similar to those of LONGBLOB or LONGTEXT data types.

MySQL JSON

To define a table column with JSON datatype, we use the keyword JSON in the CREATE TABLE statement.

We can create two types of JSON values in MySQL:

• JSON array: It is a list of values separated by commas and enclosed within square brackets ([]).

• JSON object: An object with a set of key-value pairs separated by commas and enclosed within curly brackets ({}).

Syntax

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

CREATE TABLE table_name (
   ...
   column_name JSON,
   ...
);

Example

Let us see an example demonstrating the usage of JSON datatype in a MySQL table. Here, we are creating a table named MOBILES using the following query −

CREATE TABLE MOBILES(
   ID INT NOT NULL,
   NAME VARCHAR(25) NOT NULL,
   PRICE DECIMAL(18,2),
   FEATURES JSON,
   PRIMARY KEY(ID)
);

Now, let us insert values into this table using the INSERT statement. In the FEATURES column, we use key-value pairs as a JSON value.

INSERT INTO MOBILES VALUES
(121, ''iPhone 15'', 90000.00, ''{"OS": "iOS", "Storage": "128GB", "Display": "15.54cm"}''),
(122, ''Samsung S23'', 79000.00, ''{"OS": "Android", "Storage": "128GB", "Display": "15.49cm"}''),
(123, ''Google Pixel 7'', 59000.00, ''{"OS": "Android", "Storage": "128GB", "Display": "16cm"}'');

Output

The table will be created as −

Retrieving Data From JSON Column

As JSON datatype provides an easier read access to all JSON elements, we can also retrieve each element directly from the JSON column. MySQL provides a JSON_EXTRACT() function to do so.

Syntax

Following is the syntax of the JSON_EXTRACT() function −

JSON_EXTRACT(json_doc, path)

In a JSON array, we can retrieve a particular element by specifying its index (starting from 0). And in a JSON object, we specify the key from key-value pairs.

Example

In this example, from the previously created MOBILES table we are retrieving the OS name of each mobile using the following query −

SELECT NAME, JSON_EXTRACT(FEATURES,''$.OS'')
AS OS FROM MOBILES;

Instead of calling the function, we can also use -> as a shortcut for JSON_EXTRACT. Look at the query below −

SELECT NAME, FEATURES->''$.OS''
AS OS FROM MOBILES;

Output

Both queries display the same following output −

The JSON_UNQUOTE() Function

The JSON_UNQUOTE() function is used to remove the quotes while retrieving the JSON string. Following is the syntax −

JSON_UNQUOTE(JSON_EXTRACT(json_doc, path))

Example

In this example, let us display the OS name of each mobile without the quotes −

SELECT NAME, JSON_UNQUOTE(JSON_EXTRACT(FEATURES,''$.OS''))
AS OS FROM MOBILES;

Or, we can use ->> as a shortcut for JSON_UNQUOTE(JSON_EXTRACT(…)).

SELECT NAME, FEATURES->>''$.OS''
AS OS FROM MOBILES;

Output

Both queries display the same following output −

We cannot use chained -> or ->> to extract data from nested JSON object or JSON array. These two can only be used for the top level.

The JSON_TYPE() Function

As we know, the JSON field can hold values in the form of arrays and objects. To identify which type of values are stored in the field, we use the JSON_TYPE() function. Following is the syntax −

JSON_TYPE(json_doc)

Example

In this example, let us check the type of the FEATURES column of MOBILES table using JSON_TYPE() function.

SELECT JSON_TYPE(FEATURES) FROM MOBILES;

Output

As we can see in the output, the songs column type is OBJECT.

The JSON_ARRAY_APPEND() Function

If we want to add another element to the JSON field in MySQL, we can use the JSON_ARRAY_APPEND() function. However, the new element will only be appended as an array. Following is the syntax −

JSON_ARRAY_APPEND(json_doc, path, new_value);

Example

Let us see an example where we are adding a new element at the end of the JSON object using the JSON_ARRAY_APPEND() function −

UPDATE MOBILES
SET FEATURES = JSON_ARRAY_APPEND(FEATURES,''$'',"Resolution:2400x1080 Pixels");

We can verify whether the value is added or not using a SELECT query −

SELECT NAME, FEATURES FROM MOBILES;

Output

The table will be updated as −

The JSON_ARRAY_INSERT() Function

We can only insert a JSON value at the end of the array using the JSON_ARRAY_APPEND() function. But, we can also choose a position to insert a new value into the JSON field using the JSON_ARRAY_INSERT() function. Following is the syntax −

JSON_ARRAY_INSERT(json_doc, pos, new_value);

Example

Here, we are adding a new element in the index=1 of the array using the JSON_ARRAY_INSERT() function −

UPDATE MOBILES
SET FEATURES = JSON_ARRAY_INSERT(
   FEATURES, ''$[1]'', "Charging: USB-C"
);

To verify whether the value is added or not, display the updated table using the SELECT query −

SELECT NAME, FEATURES FROM MOBILES;

Output

The table will be updated as −

JSON Using Client Program

We can also define a MySQL table column with the JSON datatype using Client Program.

Syntax

$sql = ''CREATE TABLE Blackpink (ID int AUTO_INCREMENT PRIMARY KEY NOT NULL, SONGS JSON)
$mysqli->query($sql);

sql = "CREATE TABLE Blackpink (ID int AUTO_INCREMENT PRIMARY KEY NOT NULL,SONGS JSON)";
con.query(sql)

String sql = "CREATE TABLE Blackpink (ID int AUTO_INCREMENT PRIMARY KEY NOT NULL, SONGS JSON)";
statement.execute(sql);

create_table_query = ''CREATE TABLE Blackpink (ID int AUTO_INCREMENT PRIMARY KEY NOT NULL, SONGS JSON)''
cursorObj.execute(create_table_query)

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();
  }
  // Create table Blackpink
  $sql = ''CREATE TABLE Blackpink (ID int AUTO_INCREMENT PRIMARY KEY NOT NULL, SONGS JSON)
  $result = $mysqli->query($sql);
  if ($result) {
      echo "Table created successfully...!
";
  }
  // Insert data into the created table
  $q = "INSERT INTO Blackpink (SONGS)
      VALUES (JSON_ARRAY(''Pink venom'', ''Shutdown'', ''Kill this love'', ''Stay'', ''BOOMBAYAH'', ''Pretty Savage'', ''PLAYING WITH FIRE''))";
  if ($res = $mysqli->query($q)) {
      echo "Data inserted successfully...!
";
  }
  // Now display the JSON type
  $s = "SELECT JSON_TYPE(SONGS) FROM Blackpink";
  if ($res = $mysqli->query($s)) {
      while ($row = mysqli_fetch_array($res)) {
          echo $row[0] . "n";
      }
  } else {
      echo ''Failed
  }
  // JSON_EXTRACT function to fetch the element
  $sql = "SELECT JSON_EXTRACT(SONGS, ''$[2]'') FROM Blackpink";
  if ($r = $mysqli->query($sql)) {
      while ($row = mysqli_fetch_array($r)) {
          echo $row[0] . "n";
      }
  } else {
      echo ''Failed
  }
  $mysqli->close();

ARRAY
"Kill this love"

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

    //Creating Blackpink table
    sql = "CREATE TABLE Blackpink (ID int AUTO_INCREMENT PRIMARY KEY NOT NULL,SONGS JSON)";
    con.query(sql);

    sql = "INSERT INTO Blackpink (ID, SONGS) VALUES (ID, JSON_ARRAY(''Pink venom'',''Shutdown'', ''Kill this love'', ''Stay'', ''BOOMBAYAH'', ''Pretty Savage'', ''PLAYING WITH FIRE''));"
    con.query(sql);

    sql = "select * from blackpink;"
    con.query(sql, function(err, result){
      if (err) throw err
      console.log("Records in Blackpink Table");
      console.log(result);
      console.log("--------------------------");
    });

    sql = "SELECT JSON_TYPE(songs) FROM Blackpink;"
    con.query(sql, function(err, result){
      if (err) throw err
      console.log("Type of the column");
      console.log(result);
      console.log("--------------------------");
    });

    sql = "SELECT JSON_EXTRACT(songs, ''$[2]'') FROM Blackpink;"
    con.query(sql, function(err, result){
      console.log("fetching the third element in the songs array ");
      if (err) throw err
      console.log(result);
    });
});

Connected!
--------------------------
Records in Blackpink Table
[
  {
    ID: 1,
    SONGS: [
      ''Pink venom'',
      ''Shutdown'',
      ''Kill this love'',
      ''Stay'',
      ''BOOMBAYAH'',
      ''Pretty Savage'',
      ''PLAYING WITH FIRE''
    ]
  }
]
--------------------------
Type of the column
[ { ''JSON_TYPE(songs)'': ''ARRAY'' } ]
--------------------------
fetching the third element in the songs array
[ { "JSON_EXTRACT(songs, ''$[2]'')": ''Kill this love'' } ]

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

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

         //create a table that takes a column of Json...!
         String sql = "CREATE TABLE Blackpink (ID int AUTO_INCREMENT PRIMARY KEY NOT NULL, SONGS JSON)";
         statement.execute(sql);
         System.out.println("Table created successfully...!");

         String sql1 = "INSERT INTO Blackpink (SONGS) VALUES (JSON_ARRAY(''Pink venom'', ''Shutdown'', ''Kill this love'', ''Stay'', ''BOOMBAYAH'', ''Pretty Savage'', ''PLAYING WITH FIRE''))";
         statement.execute(sql1);
         System.out.println("Json data inserted successfully...!");

         // Now display the JSON type
         String sql2 = "SELECT JSON_TYPE(SONGS) FROM Blackpink";
         ResultSet resultSet = statement.executeQuery(sql2);
         while (resultSet.next()){
            System.out.println("Json_type:"+" "+resultSet.getNString(1));
         }

         // JSON_EXTRACT function to fetch the element
         String sql3 = "SELECT JSON_EXTRACT(SONGS, ''$[2]'') FROM Blackpink";
         ResultSet resultSet1 = statement.executeQuery(sql3);
         while (resultSet1.next()){
            System.out.println("Song Name:"+" "+resultSet1.getNString(1));
         }

         connection.close();
      } catch (Exception e) {
         e.printStackTrace();
      }
   }
}

Connected successfully...!
Table created successfully...!
Json data inserted successfully...!
Json_type: ARRAY
Song Name: "Kill this love"

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()
# Creating the table ''Blackpink'' with JSON column
create_table_query = ''''''
CREATE TABLE Blackpink (
ID int AUTO_INCREMENT PRIMARY KEY NOT NULL,
SONGS JSON
)''''''
cursorObj.execute(create_table_query)
print("Table ''Blackpink'' is created successfully!")
# Adding values into the above-created table
insert = """
INSERT INTO Blackpink (SONGS) VALUES
(JSON_ARRAY(''Pink venom'', ''Shutdown'', ''Kill this love'', ''Stay'', ''BOOMBAYAH'', ''Pretty Savage'', ''PLAYING WITH FIRE''));
"""
cursorObj.execute(insert)
print("Values inserted successfully!")
# Display table
display_table = "SELECT * FROM Blackpink;"
cursorObj.execute(display_table)
# Printing the table ''Blackpink''
results = cursorObj.fetchall()
print("nBlackpink Table:")
for result in results:
    print(result)
# Checking the type of the ''SONGS'' column
type_query = "SELECT JSON_TYPE(SONGS) FROM Blackpink;"
cursorObj.execute(type_query)
song_type = cursorObj.fetchone()
print("nType of the ''SONGS'' column:")
print(song_type[0])
# Fetching the third element in the ''SONGS'' array
fetch_query = "SELECT JSON_EXTRACT(SONGS, ''$[2]'') FROM Blackpink;"
cursorObj.execute(fetch_query)
third_element = cursorObj.fetchone()
print("nThird element in the ''SONGS'' array:")
print(third_element[0])
# Closing the cursor and connection
cursorObj.close()
connection.close()

Table ''Blackpink'' is created successfully!
Values inserted successfully!

Blackpink Table:
(1, ''["Pink venom", "Shutdown", "Kill this love", "Stay", "BOOMBAYAH", "Pretty Savage", "PLAYING WITH FIRE"]'')

Type of the ''SONGS'' column:
ARRAY

Third element in the ''SONGS'' array:
"Kill this love"

The MySQL Partitioning is a technique that can be used to divide a database table into smaller tables i.e. partitions. These smaller tables are stored in different physical locations and are treated as separate tables. Thus, the data in these smaller tables can be accessed and managed individually.

But note that, even if the data smaller tables is managed separately, they are not independent tables; i.e., they are still a part of main table.

There are two forms of partitioning in MySQL: Horizontal Partitioning and Vertical Partitioning.

MySQL Horizontal Partitioning

The MySQL Horizontal partitioning is used to divide the table rows into multiple partitions. Since it divides the rows, all the columns will be present in each partition. All the partitions can be accessed individually or collectively.

There are several types of MySQL horizontal partitioning methods −

MySQL Range Partitioning

The MySQL RANGE partitioning is used to divide a table into partitions based on a specific range of column values. Each table partition contains rows with column values falling within that defined range.

Example

Let us create a table named CUSTOMERS and partition it by the AGE column into four partitions: P1, P2, P3, and P4 using the “PARTITION BY RANGE” clause −

CREATE TABLE CUSTOMERS(
   ID int not null,
   NAME varchar(40) not null,
   AGE int not null,
   ADDRESS char(25) not null,
   SALARY decimal(18, 2)
   )
   PARTITION BY RANGE (AGE) (
   PARTITION P1 VALUES LESS THAN (20),
   PARTITION P2 VALUES LESS THAN (30),
   PARTITION P3 VALUES LESS THAN (40),
   PARTITION P4 VALUES LESS THAN (50)
);

Here, we are inserting rows into the above created table −

INSERT INTO CUSTOMERS VALUES
(1, ''Ramesh'', 19, ''Ahmedabad'', 2000.00 ),
(2, ''Khilan'', 25, ''Delhi'', 1500.00 ),
(3, ''kaushik'', 23, ''Kota'', 2000.00 ),
(4, ''Chaitali'', 31, ''Mumbai'', 6500.00 ),
(5, ''Hardik'', 35, ''Bhopal'', 8500.00 ),
(6, ''Komal'', 47, ''MP'', 4500.00 ),
(7, ''Muffy'', 43, ''Indore'', 10000.00 );

Following is the CUSTOMERS table obtained −

Now that we have some data in the CUSTOMERS table, we can display the partition status to see how the data is distributed among the partitions using the following query −

SELECT PARTITION_NAME, TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME=''CUSTOMERS

The above query will show us the number of rows in each partition. For example, P1 has 1 row, P2 has 2 rows, P3 has 2 rows, and P4 has 2 rows as shown below −

Displaying Partitions −

We can also display data from specific partitions using the PARTITION clause. For instance, to retrieve data from partition P1, we use the following query −

SELECT * FROM CUSTOMERS PARTITION (p1);

It will display all the records in partition P1 −

Similarly, we can display other partitions using the same syntax.

Handling Data Outside the Range −

If we attempt to insert a value into the AGE column that doesn”t fall within any of the defined partitions, it will fail with an error, as shown below −

INSERT INTO CUSTOMERS VALUES
(8, ''Brahmi'', 70, ''Hyderabad'', 19000.00 );

Following is the error obtained −

ERROR 1526 (HY000): Table has no partition for value 70

Truncating Partitions −

We can also manage partitions by truncating them if needed. For example, to empty partition P2, we can use the following query −

ALTER TABLE CUSTOMERS TRUNCATE PARTITION p2;

The output obtained is as shown below −

Query OK, 0 rows affected (0.03 sec)

This will remove all data from partition P2, making it empty as shown below −

SELECT * FROM CUSTOMERS PARTITION (p2);

Following is the output produced −

Empty set (0.00 sec)

We can verify the CUSTOMERS table using the following SELECT query −

SELECT * FROM CUSTOMERS;

We can see in the table below that the rows belonging to p2 partition are deleted −

MySQL List Partitioning

The MySQL List Partitioning is used to divide the table into partitions based on a discrete set of values for a specific column. Each partition contains rows that match a particular value within the defined set.

Example

In this example, we will create a table named STUDENTS and divide it into four partitions (P1, P2, P3, and P4) based on the “DEPARTMENT_ID” column using the “PARTITION BY LIST” clause −

CREATE TABLE STUDENTS(
   ID int,
   NAME varchar(50),
   DEPARTMENT varchar(50),
   DEPARTMENT_ID int
   )
   PARTITION BY LIST(DEPARTMENT_ID)(
   PARTITION P1 VALUES IN (3, 5, 6, 7, 9),
   PARTITION P2 VALUES IN (13, 15, 16, 17, 20),
   PARTITION P3 VALUES IN (23, 25, 26, 27, 30),
   PARTITION P4 VALUES IN (33, 35, 36, 37, 40)
);

Here, we are inserting rows into the above-created table −

INSERT INTO STUDENTS VALUES
(1, ''Ramesh'', "cse", 5),
(2, ''Khilan'', "mech", 20),
(3, ''kaushik'', "ece", 17),
(4, ''Chaitali'', "eee", 33),
(5, ''Hardik'', "IT", 36),
(6, ''Komal'', "Hotel management", 40),
(7, ''Muffy'', "Fashion", 23);

Following is the STUDENTS table obtained −

We can display the partition status of the STUDENTS table to see how the data is distributed among partitions using the following query −

SELECT PARTITION_NAME, TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME=''STUDENTS

The output of this query will show the number of rows in each partition. For instance, P1 has 1 row, P2 has 2 rows, P3 has 1 row, and P4 has 3 rows −

MySQL Hash Partitioning

The MySQL HASH partitioning is used to divide the table data into partitions using a hash function based on a specific column(s). The data will be evenly distributed among the partitions.

Example

In the following query, we are creating a table with the name EMPLOYEES with four partitions based on the “id” column using the PARTITION BY HASH clause −

CREATE TABLE EMPLOYEES (
   id INT NOT NULL,
   name VARCHAR(50) NOT NULL,
   department VARCHAR(50) NOT NULL,
   salary INT NOT NULL
 )
   PARTITION BY HASH(id)
   PARTITIONS 4;

Here, we are inserting rows into the above-created table −

INSERT INTO EMPLOYEES VALUES
(1, ''Varun'', ''Sales'', 50000),
(2, ''Aarohi'', ''Marketing'', 60000),
(3, ''Paul'', ''IT'', 70000),
(4, ''Vaidhya'', ''Finance'', 80000),
(5, ''Nikhil'', ''Sales'', 55000),
(6, ''Sarah'', ''Marketing'', 65000),
(7, ''Tim'', ''IT'', 75000),
(8, ''Priya'', ''Finance'', 85000);

The EMPLOYEES table obtained is as follows −

The records are evenly distributed among four partitions based on the “id” column. You can verify the partition status using the following SELECT query −

SELECT PARTITION_NAME, TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME=''EMPLOYEES

The table obtained is as follows −

Key Partitioning

The MySQL key partitioning is used to divide the table data into partitions based on the values of the primary key or a unique key.

Example

In the following query, we are creating a table with the name PERSON with Key partitioning on the “id” column. We have divided the table into four partitions, and the primary key is “id” −

CREATE TABLE PERSON (
   id INT NOT NULL,
   name VARCHAR(50) NOT NULL,
   email VARCHAR(50) NOT NULL,
   address VARCHAR(100) NOT NULL,
   PRIMARY KEY (id)
   )
   PARTITION BY KEY(id)
   PARTITIONS 4;

Here, we are inserting rows into the above-created table −

INSERT INTO PERSON VALUES
(1, ''Krishna'', ''Krishna@tutorialspoint.com'', ''Ayodhya''),
(2, ''Kasyap'', ''Kasyap@tutorialspoint.com'', ''Ayodhya''),
(3, ''Radha'', ''Radha@tutorialspoint.com'', ''Ayodhya''),
(4, ''Sarah'', ''Sarah@tutorialspoint.com'', ''Sri Lanka''),
(5, ''Sita'', ''Sita@tutorialspoint.com'', ''Sri Lanka''),
(6, ''Arjun'', ''Arjun@tutorialspoint.com'', ''India''),
(7, ''Hanuman'', ''Hanuman@tutorialspoint.com'', ''Sri Lanka''),
(8, ''Lakshman'', ''Lakshman@tutorialspoint.com'', ''Sri Lanka'');

Following is the PERSON table obtained −

Again, the data is evenly distributed among partitions based on the “id” column, and you can verify the partition status using the query given below −

SELECT PARTITION_NAME, TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME=''PERSON

The output obtained is as shown below −

MySQL Sub-partitioning

The MySQL subpartitioning is used to further divide partitions based on another column, often used in conjunction with other partitioning methods like RANGE or HASH.

Example

Let us create a CUSTOMER_ORDERS table with RANGE partitioning on the “order_date” column, and then we will subpartition by hashing on the month of “order_date” −

CREATE TABLE CUSTOMER_ORDERS (
   order_id INT NOT NULL,
   customer_name VARCHAR(50) NOT NULL,
   order_date DATE NOT NULL,
   order_status VARCHAR(20) NOT NULL
   )
   PARTITION BY RANGE (YEAR(order_date))
   SUBPARTITION BY HASH(MONTH(order_date))
   SUBPARTITIONS 2(
   PARTITION p0 VALUES LESS THAN (2022),
   PARTITION p1 VALUES LESS THAN (2023),
   PARTITION p2 VALUES LESS THAN (2024)
);

Here, we are inserting rows into the above-created table −

INSERT INTO CUSTOMER_ORDERS VALUES
(1, ''John'', ''2021-03-15'', ''Shipped''),
(2, ''Bob'', ''2019-01-10'', ''Delivered''),
(3, ''Johnson'', ''2023-01-10'', ''Delivered''),
(4, ''Jake'', ''2020-01-10'', ''Delivered''),
(5, ''Smith'', ''2022-05-01'', ''Pending''),
(6, ''Rob'', ''2023-01-10'', ''Delivered'');

Following is the CUSTOMERS_ORDERS table obtained −

You can display the CUSTOMER_ORDERS table and verify the partition status using the following query −

SELECT PARTITION_NAME, TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME=''CUSTOMER_ORDERS

Following is the table obtained −

The MySQL Cursors

A MySQL cursor is a pointer that is used to iterate through a table”s records. They are used within stored programs such as procedures and functions and have the following features −

• READ ONLY − Cursors only allow you to read data; you can”t make changes to it.

• Non-Scrollable − Cursors move through records in one direction, from the top to the bottom.

• Asensitive − Cursors are sensitive to the changes made in the table. Any modification done in the table will be reflected in the cursor.

The following four operations are used to manage cursors in MySQL:

• Declare Cursor

• Open Cursor

• Fetch Cursor

• Close Cursor

Let us now see each of these operations in detail.

Declare Cursor

The DECLARE statement is used to declare a cursor in a MySQL. Once declared, it is then associated with a SELECT statement to retrieve the records from a table.

Following is the syntax to declare a cursor −

DECLARE cursor_name CURSOR FOR select_statement;

Open Cursor

The OPEN statement is used to initialize the cursor to retrieve the data after it has been declared.

Following is the syntax to open a cursor −

OPEN cursor_name;

Fetch Cursor

The FETCH statement is then used to retrieve the record pointed by the cursor. Once retrieved, the cursor moves to the next record.

Following is the syntax to fetch a cursor −

FETCH cursor_name INTO variable_list;

Close Cursor

The CLOSE statement is used to release the memory associated with the cursor after all the records have been retrieved.

Following is the syntax to close a cursor −

CLOSE cursor_name;

Example

In this example, we see how to manage a cursor in a stored procedure.

Assume two tables, CUSTOMERS and BACKUP, are created using the CREATE TABLE statement. The CUSTOMERS table holds information like ID, name, age, address, and salary as shown below −

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, we are creating the BACKUP table, having the same structure as the CUSTOMERS table to store a copy of the records from the CUSTOMERS table −

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

Now let us insert some records into the CUSTOMERS table using the INSERT statement as follows −

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 following SQL query creates a cursor on the CUSTOMERS table −

DECLARE MY_CURSOR CURSOR FOR SELECT * FROM CUSTOMERS;

Now, we are creating a stored procedure named ExampleProc to manage the cursor from declaration to closure −

DELIMITER //
CREATE PROCEDURE ExampleProc()
   BEGIN
      -- Variable declarations
      DECLARE done INT DEFAULT 0;
      DECLARE cust_id, cust_age INTEGER;
      DECLARE cust_name VARCHAR(20);
      DECLARE cust_address CHAR(25);
	  DECLARE cust_salary DECIMAL(18,2);

	  -- Cursor declaration
      DECLARE cur CURSOR FOR SELECT * FROM CUSTOMERS;

	  -- Handler for no more records
      DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

	  -- Open the cursor
      OPEN cur;

      -- Loop to fetch and insert records
      label: LOOP
      FETCH cur INTO cust_id, cust_name, cust_age, cust_address, cust_salary;
      INSERT INTO backup VALUES(cust_id, cust_name, cust_age, cust_address, cust_salary);
      IF done = 1 THEN LEAVE label;
      END IF;
      END LOOP;

	  -- Close the cursor
      CLOSE cur;
   END//
DELIMITER ;

Output

Finally, if we call the procedure using CALL ExampleProc(); and check the contents of the BACKUP table, it will contain the same records as CUSTOMERS −

SELECT * FROM BACKUP;

The BACKUP table obtained is as follows −

The MySQL Partitioning is used to divide large tables into smaller partitions that are stored in different physical locations and are treated as separate tables. Even though the smaller partitions are managed individually, they are still part of the main table.

There are two forms of partitioning in MySQL: Horizontal Partitioning and Vertical Partitioning.

The MySQL Vertical Partitioning

The MySQL Vertical partitioning divides the table into multiple tables based on columns, rather than rows.

There are two main types of vertical partitioning in MySQL, each serving specific purposes −

• RANGE Columns Partitioning
• LIST Columns Partitioning

Both Range Columns Partitioning and List Columns Partitioning support various data types, including integer types (TINYINT, SMALLINT, MEDIUMINT, INT, and BIGINT), string types (CHAR, VARCHAR, BINARY, and VARBINARY), as well as DATE and DATETIME data types.

Range Columns Partitioning

The MySQL Range Columns partitioning uses one or more columns as partition keys to divide the data into partitions based on a defined range of column values.

The values in these columns are compared to predefined ranges, and each row is assigned to the partition that encompasses the range containing its column values.

Example

In the following query, we are creating a table named INVENTORY and dividing it into three partitions based on “product_quantity” and “product_price” columns. Rows with specific values in these columns are stored in their corresponding partitions −

CREATE TABLE INVENTORY (
   id INT,
   product_name VARCHAR(50),
   product_quantity INT,
   product_price int
)
   PARTITION BY RANGE COLUMNS(product_quantity, product_price) (
   PARTITION P_low_stock VALUES LESS THAN (10, 100),
   PARTITION P_medium_stock VALUES LESS THAN (50, 500),
   PARTITION P_high_stock VALUES LESS THAN (200, 1200)
);

Here, we are inserting rows into the above-created table −

INSERT INTO INVENTORY VALUES
(1, ''Headphones'', 5, 50),
(2, ''Mouse'', 15, 200),
(3, ''Monitor'', 30, 300),
(4, ''Keyboard'', 60, 600),
(5, ''CPU'', 100, 1000);

Following is the INVENTORY table obtained −

Now that we have some data in the INVENTORY table, we can display the partition status to see how the data is distributed among the partitions using the following query −

SELECT PARTITION_NAME, TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME=''inventory

You will see in the output below that the respective columns are assigned to their respective partitions based on the defined range values −

Displaying Partitions −

We can also display data from specific partitions using the PARTITION clause. For instance, to retrieve data from partition P_high_stock, we use the following query −

SELECT * FROM inventory PARTITION (P_high_stock);

It will display all the records in partition P_high_stock −

Similarly, we can display other partitions using the same syntax.

List Columns Partitioning

The MySQL List columns partitioning uses one or more columns as partition keys and assigns records to partitions based on specific values in those columns. This method is handy when you want to group data into partitions based on discrete values or categories.

Example

Let us create a table named “EMPLOYEES” and partition it using LIST COLUMNS partitioning based on the “department” column −

CREATE TABLE EMPLOYEES (
   id INT,
   first_name VARCHAR(50),
   last_name VARCHAR(50),
   hiring_date DATE,
   department VARCHAR(50)
)
   PARTITION BY LIST COLUMNS(department) (
   PARTITION p_sales VALUES IN (''Sales'', ''Marketing''),
   PARTITION p_engineering VALUES IN (''Engineering'', ''Research''),
   PARTITION p_operations VALUES IN (''Operations'')
);

Here, we are inserting records into above-created table −

INSERT INTO EMPLOYEES VALUES
(1, ''John'', ''Doe'', ''2020-01-01'', ''Sales''),
(2, ''Jane'', ''Doe'', ''2020-02-01'', ''Marketing''),
(3, ''Bob'', ''Smith'', ''2020-03-01'', ''Engineering''),
(4, ''Alice'', ''Johnson'', ''2020-04-01'', ''Research''),
(5, ''Mike'', ''Brown'', ''2020-05-01'', ''Operations'');

Following is the EMPLOYEES table obtained −

We can display the partition status of the EMPLOYEES table to see how the data is distributed among partitions using the following query −

SELECT PARTITION_NAME, TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME=''EMPLOYEES

It will display the partitions and the number of rows in each partition based on the department values −

MySQL Stored Functions

A Stored Function is a set of SQL statements that perform a specific operation and then return a single value. Similar to built-in functions in MySQL, a stored function can be called from within any MySQL statement. The MySQL CREATE FUNCTION statement is used to create both stored functions and user-defined functions.

By default, a stored function is associated with the default database. In order to use the CREATE FUNCTION statement, the user must have the CREATE ROUTINE database privilege.

Syntax

Following is the syntax for creating a new stored function −

CREATE FUNCTION function_name(
   parameters...
)
RETURN datatype [characteristics]
func_body;

where,

• function_name: It is the name of the function that we are creating. The name must not be same as the MySQL built-in function names.

• parameters: These are the list of all parameters for the function. All the parameters are IN parameters by default. We cannot specify the IN, OUT or INOUT modifiers to the parameters.

• datatype: This is the datatype of the value returned by the function.

• characteristics: The CREATE FUNCTION statement will only be accepted if at least one of the characteristics (DETERMINISTIC, NO SQL, or READS SQL DATA) are specified in it”s declaration.

• fun_body: This contains set of MySQL statements that defines the behaviour of the function between the BEGIN and END commands.

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),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY(ID)
);

Here, we are inserting rows into the CUSTOMERS 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 table is displayed as −

Creating a Function −

With the following CREATE FUNCTION query, we are creating a function that returns the year of birth of the customers based on their AGE −

DELIMITER $$
CREATE FUNCTION DATE_OF_BIRTH(AGE INT) RETURNS INT DETERMINISTIC
BEGIN
   DECLARE currentdate DATE;
   SELECT CURDATE() INTO currentdate;
   RETURN year(currentdate)-AGE;
END $$
DELIMITER ;

Now, we are calling the DATE_OF_BIRTH function using the following query −

SELECT ID, NAME, DATE_OF_BIRTH(AGE)
AS ''YEAR_OF_BIRTH''
FROM CUSTOMERS;

Output

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

Calling Stored Function From Stored Procedure

In MySQL, we can call a stored function from a stored procedure. The following statement creates a stored procedure with the name StudentDetails() that calls the DATE_OF_BIRTH() stored function.

DELIMITER $$
CREATE PROCEDURE CustomerDetails()
BEGIN
SELECT ID, NAME, DATE_OF_BIRTH(AGE) AS ''YEAR_OF_BIRTH''
FROM CUSTOMERS;
END $$
DELIMITER ;

Here, we are calling the CustomerDetails() stored procedure using CALL keyword −

CALL CustomerDetails();

Output

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

When working with MySQL stored procedures, managing exceptions is important to prevent abrupt termination of procedures and to provide meaningful error information. This is achieved using the MySQL SIGNAL statement and the DECLARE … HANDLER statement.

The MySQL SIGNAL Statement

The MySQL SIGNAL statement is used to convey error information in stored procedures. It ensures that exceptions are properly handled, preventing sudden procedure termination.

Exception Handling with DECLARE … HANDLER

You can use the DECLARE … HANDLER statement to effectively manage exceptions in MySQL. It allows you to specify how different types of exceptions should be handled within a stored procedure. By using this statement in conjunction with SIGNAL, you can enable precise control over error handling.

Customizing Error Messages

The SIGNAL statement allows for the customization of error messages using the SET MESSAGE_TEXT command. This is helpful for modifying error messages to provide more meaningful feedback to handlers, applications, or clients.

Syntax

Following is the syntax of the MySQL SIGNAL Statement −

SIGNAL condition_value [SET signal_information_item]

Where,

• condition_value represents the error value to be returned, which can be either a “sqlstate_value” or a “condition_name”.

• signal_information_item allows you to set additional information related to the error condition. You can specify various signal information items like CLASS_ORIGIN, SUBCLASS_ORIGIN, MESSAGE_TEXT, MYSQL_ERRNO, CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME, CATALOG_NAME, SCHEMA_NAME, TABLE_NAME, COLUMN_NAME, or CURSOR_NAME.

Example

In this example, we create a procedure that accepts the short form of degrees and returns their full forms. If we provide an invalid degree i.e. value other than B-Tech, M-Tech, BSC and MSC, an error message is generated using the SIGNAL statement −

DELIMITER //
CREATE PROCEDURE example(IN degree VARCHAR(20), OUT full_form Varchar(50))
BEGIN
IF degree=''B-Tech'' THEN SET full_form = ''Bachelor of Technology
ELSEIF degree=''M-Tech'' THEN SET full_form = ''Master of Technology
ELSEIF degree=''BSC'' THEN SET full_form = ''Bachelor of Science
ELSEIF degree=''MSC'' THEN SET full_form = ''Master of Science
ELSE
SIGNAL SQLSTATE ''01000''
SET MESSAGE_TEXT = ''Choose from the existing values'', MYSQL_ERRNO = 12121;
SIGNAL SQLSTATE ''45000''
SET MESSAGE_TEXT = ''Given degree is not valid'', MYSQL_ERRNO = 1001;
END IF;
END //
DELIMITER ;

You can call the above procedure to retrieve the result as shown below −

CALL example(''BSC'', @fullform);

You can retrieve the value of the variable using the following SELECT statement −

SELECT @fullform;

Following is the output obtained −

If you pass an invalid value to the procedure, it will generate an error message as shown below −

CALL example (''BBC'', @fullform);

The output obtained is as follows −

ERROR 1001 (45000): Given degree is not valid

Example

Following is another example demonstrating exception handling with the SIGNAL statement. Here, we declare a condition and use SIGNAL to trigger exceptions based on certain conditions −

DELIMITER //
CREATE PROCEDURE example (num INT)
BEGIN
DECLARE testCondition CONDITION FOR SQLSTATE ''45000
IF num < 0 THEN
SIGNAL SQLSTATE ''01000
ELSEIF num > 150 THEN
SIGNAL SQLSTATE ''45000
END IF;
END //
DELIMITER ;

You can call the above procedure by passing two values as shown below −

CALL example(15);

Following is the output obtained −

Query OK, 0 rows affected (0.00 sec)

Calling the procedure by passing the second value −

CALL example(160);

The result produced is as shown below −

ERROR 1644 (45000): Unhandled user-defined exception condition

Example

You can customize error messages using SET MESSAGE_TEXT with the SIGNAL statement as shown in this modified example −

DELIMITER //
CREATE PROCEDURE example (num INT)
BEGIN
DECLARE testCondition CONDITION FOR SQLSTATE ''45000
IF num < 0 THEN
SIGNAL SQLSTATE ''01000
ELSEIF num > 150 THEN
SIGNAL SQLSTATE ''45000'' SET MESSAGE_TEXT = ''Number higher than the limit set
END IF;
END //
DELIMITER ;

We get the following output −

Query OK, 0 rows affected (0.01 sec)

You can call the above procedure by passing two values as shown below −

CALL example(20);

Following is the output obtained −

Query OK, 0 rows affected (0.00 sec)

Calling the procedure by passing the second value −

CALL example(160);

You can observe in the output below, the error message displayed is customized according to the user −

ERROR 1644 (45000): Number higher than the limit set

When working with stored procedures in MySQL, it is important to manage exceptions that may arise during their execution. These exceptions could otherwise lead to an abrupt termination of the procedure.

To address this issue, MySQL offers a way to handle exceptions through error handlers. These handlers can be declared using the DECLARE … HANDLER statement.

The MySQL RESIGNAL Statement

The MySQL RESIGNAL statement is used to provide error information to handlers, applications, or clients when an exception occurs within a stored procedure.

Customizing Error Messages

The RESIGNAL statement allows you to customize error messages using the SET MESSAGE_TEXT command, ensuring smoother procedure execution.

Syntax

Following is the syntax of the MySQL RESIGNAL Statement −

RESIGNAL condition_value [SET signal_information_item]

Where,

• condition_value represents the error value to be returned, which can be either a “sqlstate_value” or a “condition_name”.

• signal_information_item allows you to set additional information related to the error condition. You can specify various signal information items like CLASS_ORIGIN, SUBCLASS_ORIGIN, MESSAGE_TEXT, MYSQL_ERRNO, CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME, CATALOG_NAME, SCHEMA_NAME, TABLE_NAME, COLUMN_NAME, or CURSOR_NAME.

Example

In this example, we create a procedure that accepts the short form of degrees and returns their full forms. If we provide an invalid degree i.e. value other than BBA, BCA, MD and ITI, an error message is generated using the RESIGNAL statement −

DELIMITER //
CREATE PROCEDURE example(IN degree VARCHAR(20), OUT full_form VARCHAR(50))
BEGIN
DECLARE wrong_choice CONDITION FOR SQLSTATE ''45000
DECLARE EXIT HANDLER FOR wrong_choice
RESIGNAL SET MESSAGE_TEXT = ''Given degree is not valid'', MYSQL_ERRNO = 1001;
IF degree=''BBA'' THEN SET full_form = ''Bachelor of Business Administration
ELSEIF degree=''BCA'' THEN SET full_form = ''Bachelor of Computer Applications
ELSEIF degree=''MD'' THEN SET full_form = ''Doctor of Medicine
ELSEIF degree=''ITI'' THEN SET full_form = ''Industrial Training Institute
ELSE
SIGNAL wrong_choice;
END IF;
END //
DELIMITER ;

You can call the above procedure to retrieve the result as shown below −

CALL example(''MD'', @fullform);

You can retrieve the value of the variable using the following SELECT statement −

SELECT @fullform;

Following is the output obtained −

If you pass an invalid value to the procedure, it will generate an error message as follows −

CALL example (''IIT'', @fullform);

The output obtained is as follows −

ERROR 1001 (45000): Given degree is not valid

Handling Warnings with RESIGNAL

Let us see another example where we do not pass optional attributes to the RESIGNAL statement −

DELIMITER //
CREATE PROCEDURE testexample (num INT)
BEGIN
DECLARE testCondition1 CONDITION FOR SQLSTATE ''01000
DECLARE EXIT HANDLER FOR testCondition1 RESIGNAL;
IF num < 0 THEN
SIGNAL testCondition1;
END IF;
END //
DELIMITER ;

You can call the above procedure by passing two values. But, any SQLSTATE value that starts with ”01” refers to a warning, so the query is executed with a warning as shown below −

CALL testexample(-15);

The output obtained is as follows −

Query OK, 0 rows affected, 1 warning (0.00 sec)

Resignal Statement Using Client Program

We can also perform resignal Using Client Program.

Syntax

$sql = "CREATE PROCEDURE example_new1(IN degree VARCHAR(20), OUT full_form Varchar(50))
BEGIN
   IF degree=''B-Tech'' THEN SET full_form = ''Bachelor of Technology ELSEIF degree=''M-Tech'' THEN SET full_form = ''Master of Technology ELSEIF degree=''BSC'' THEN SET full_form = ''Bachelor of Science
   ELSEIF degree=''MSC'' THEN SET full_form = ''Master of Science
   ELSE
      RESIGNAL SQLSTATE ''01000''
   SET MESSAGE_TEXT = ''Choose from the existing values'', MYSQL_ERRNO = 12121;
      RESIGNAL SQLSTATE ''45000''
   SET MESSAGE_TEXT = ''Given degree is not valid'', MYSQL_ERRNO = 1001;
   END IF;
END";
$mysqli->query($sql);

var createProcedureSql = `
CREATE PROCEDURE example(IN degree VARCHAR(20), OUT full_form Varchar(50))
BEGIN
    IF degree=''B-Tech'' THEN SET full_form = ''Bachelor of Technology
    ELSEIF degree=''M-Tech'' THEN SET full_form = ''Master of Technology
    ELSEIF degree=''BSC'' THEN SET full_form = ''Bachelor of Science
    ELSEIF degree=''MSC'' THEN SET full_form = ''Master of Science
    ELSE
        RESIGNAL SQLSTATE ''01000'' -- Raise a warning
        SET MESSAGE_TEXT = ''Choose from the existing values'', MYSQL_ERRNO = 12121;
        RESIGNAL SQLSTATE ''45000'' -- Raise an error
        SET MESSAGE_TEXT = ''Given degree is not valid'', MYSQL_ERRNO = 1001;
    END IF;
END`;
con.query(createProcedureSql);

String sql = "CREATE PROCEDURE example(IN degree VARCHAR(20), OUT full_form Varchar(50))
BEGIN IF degree=''B-Tech'' THEN SET full_form = ''Bachelor of Technology
ELSEIF degree=''M-Tech'' THEN SET full_form = ''Master of Technology
ELSEIF degree=''BSC'' THEN SET full_form = ''Bachelor of Science
ELSEIF degree=''MSC'' THEN SET full_form = ''Master of Science
ELSE RESIGNAL SQLSTATE ''01000'' SET MESSAGE_TEXT = ''Choose from the existing values'', MYSQL_ERRNO = 12121;
RESIGNAL SQLSTATE ''45000'' SET MESSAGE_TEXT = ''Given degree is not valid'', MYSQL_ERRNO = 1001;
END IF;
END";
statement.execute(sql);

resignal_statement = ''CREATE PROCEDURE example(IN degree VARCHAR(20), OUT full_form VARCHAR(50))
BEGIN
IF degree=''B-Tech'' THEN SET full_form = ''Bachelor of Technology
ELSEIF degree=''M-Tech'' THEN SET full_form = ''Master of Technology
ELSEIF degree=''BSC'' THEN SET full_form = ''Bachelor of Science
ELSEIF degree=''MSC'' THEN SET full_form = ''Master of Science
ELSE
RESIGNAL SQLSTATE ''01000''
SET MESSAGE_TEXT = ''Choose from the existing values'', MYSQL_ERRNO = 12121;
RESIGNAL SQLSTATE ''45000''
SET MESSAGE_TEXT = ''Given degree is not valid'', MYSQL_ERRNO = 1001;
END IF;
END;''
cursorObj.execute(resignal_statement)

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.
'');
//lets generate error messege using RESIGNAL statement
$sql = "
CREATE PROCEDURE example_new1(IN degree VARCHAR(20), OUT full_form Varchar(50))
   BEGIN
      IF degree=''B-Tech'' THEN SET full_form = ''Bachelor of Technology ELSEIF degree=''M-Tech'' THEN SET full_form = ''Master of Technology ELSEIF degree=''BSC'' THEN SET full_form = ''Bachelor of Science
      ELSEIF degree=''MSC'' THEN SET full_form = ''Master of Science
      ELSE
         RESIGNAL SQLSTATE ''01000''
      SET MESSAGE_TEXT = ''Choose from the existing values'', MYSQL_ERRNO = 12121;
         RESIGNAL SQLSTATE ''45000''
      SET MESSAGE_TEXT = ''Given degree is not valid'', MYSQL_ERRNO = 1001;
      END IF;
   END";
if($mysqli->query($sql)){
    printf("Resignal statement created successfully....!n");
}
//lets call the above procedure
$sql = "CALL example_new(''BSC'', @fullform)";
if($mysqli->query($sql)){
    printf("Procedure called successfully...!n");
}
//lets retirve the value variable using SELECT statement...
$sql = "SELECT @fullform";
if($result = $mysqli->query($sql)){
    printf("Variable value is: n");
    while($row = mysqli_fetch_array($result)){
        print_r($row);
    }
}
if($mysqli->error){
    printf("Error message: ", $mysqli->error);
}
$mysqli->close();

Resignal statement created successfully....!
Procedure called successfully...!
Variable value is:
Array
(
   [0] => Bachelor of Science
   [@fullform] => Bachelor of Science
)

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);
   // Create the example procedure
   var createProcedureSql = `
       CREATE PROCEDURE example(IN degree VARCHAR(20), OUT full_form Varchar(50))
       BEGIN
           IF degree=''B-Tech'' THEN SET full_form = ''Bachelor of Technology
           ELSEIF degree=''M-Tech'' THEN SET full_form = ''Master of Technology
           ELSEIF degree=''BSC'' THEN SET full_form = ''Bachelor of Science
           ELSEIF degree=''MSC'' THEN SET full_form = ''Master of Science
           ELSE
               RESIGNAL SQLSTATE ''01000'' -- Raise a warning
               SET MESSAGE_TEXT = ''Choose from the existing values'', MYSQL_ERRNO = 12121;
               RESIGNAL SQLSTATE ''45000'' -- Raise an error
               SET MESSAGE_TEXT = ''Given degree is not valid'', MYSQL_ERRNO = 1001;
           END IF;
       END;
   `;
   con.query(createProcedureSql, function (err) {
       if (err) throw err;
       console.log("Procedure example created!");
       console.log("--------------------------");
     });
   //Passing BSC value to the procedure to get the fullform
   callExampleProcedureSql = "CALL example(''BSC'', @fullform);";
   con.query(callExampleProcedureSql)
   selectFullFormSql = ''SELECT @fullform;
   con.query(selectFullFormSql, function (err, result) {
       if (err) throw err;
       console.log("Full form of degree:");
       console.log(result);
       console.log("--------------------------");
      });

   //Passing an invalid value to the procedure
   callNonExistingProcedureSql = "CALL procedureEx (''BBC'', @fullform);";
   con.query(callNonExistingProcedureSql);
   con.query(selectFullFormSql, function (err, result) {
         if (err) throw err;
         console.log("Full form of BBC will leads to an error:");
         console.log(result);
         con.end();
      });
});

Connected!
--------------------------
Procedure example created!
--------------------------
Full form of degree:
[ { ''@fullform'': ''Bachelor of Science'' } ]
--------------------------
C:UsersLenovodesktopJavaScriptconnectDB.js:61
          if (err) throw err;
                             ^

Error: PROCEDURE tutorials.procedureEx does not exist
    at Packet.asError (C:UsersLenovodesktopJavaScriptnode_modulesmysql2libpacketspacket.js:728:17)
    at Query.execute (C:UsersLenovodesktopJavaScriptnode_modulesmysql2libcommandscommand.js:29:26)
    at Connection.handlePacket (C:UsersLenovodesktopJavaScriptnode_modulesmysql2libconnection.js:478:34)
    at PacketParser.onPacket (C:UsersLenovodesktopJavaScriptnode_modulesmysql2libconnection.js:97:12)
    at PacketParser.executeStart (C:UsersLenovodesktopJavaScriptnode_modulesmysql2libpacket_parser.js:75:16)
    at Socket. (C:UsersLenovodesktopJavaScriptnode_modulesmysql2libconnection.js:104:25)
    at Socket.emit (node:events:513:28)
    at addChunk (node:internal/streams/readable:315:12)
    at readableAddChunk (node:internal/streams/readable:289:9)
    at Socket.Readable.push (node:internal/streams/readable:228:10)
Emitted ''error'' event on Query instance at:
    at Query.execute (C:UsersLenovodesktopJavaScriptnode_modulesmysql2libcommandscommand.js:39:14)
    at Connection.handlePacket (C:UsersLenovodesktopJavaScriptnode_modulesmysql2libconnection.js:478:34)
    [... lines matching original stack trace ...]
    at Socket.Readable.push (node:internal/streams/readable:228:10)
    at TCP.onStreamRead (node:internal/stream_base_commons:190:23) {
  code: ''ER_SP_DOES_NOT_EXIST'',
  errno: 1305,
  sqlState: ''42000'',
  sqlMessage: ''PROCEDURE tutorials.procedureEx does not exist'',
  sql: "CALL procedureEx (''BBC'', @fullform);",
  fatal: true
}

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class Resignal {
   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...!");
            //lets generate error message using RESIGNAL statement
            String sql = "CREATE PROCEDURE example(IN degree VARCHAR(20), OUT full_form Varchar(50))
            BEGIN IF degree=''B-Tech'' THEN SET full_form = ''Bachelor of Technology
            ELSEIF degree=''M-Tech'' THEN SET full_form = ''Master of Technology
            ELSEIF degree=''BSC'' THEN SET full_form = ''Bachelor of Science
            ELSEIF degree=''MSC'' THEN SET full_form = ''Master of Science
            ELSE RESIGNAL SQLSTATE ''01000'' SET MESSAGE_TEXT = ''Choose from the existing values'', MYSQL_ERRNO = 12121;
            RESIGNAL SQLSTATE ''45000'' SET MESSAGE_TEXT = ''Given degree is not valid'', MYSQL_ERRNO = 1001;
            END IF;
            END";
            st.execute(sql);
            System.out.println("Resignal statement created successfully....!");
            //lets call the above procedure
            String sql1 = "CALL example(''BSC'', @fullform)";
            st.execute(sql1);
            System.out.println("Procedure called successfully....!");
            //lets retrieve the value variable using SELECT statement...
            String sql2 = "SELECT @fullform";
            rs = st.executeQuery(sql2);
            System.out.println("variable value is: ");
            while(rs.next()) {
               String var = rs.getNString(1);
               System.out.println(var);
            }
      }catch(Exception e) {
         e.printStackTrace();
      }
   }
}

Resignal statement created successfully....!
Procedure called successfully....!
variable value is:
Bachelor of Science

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()
# Using the RESIGNAL Statement to generate an error message
resignal_statement = ''''''
CREATE PROCEDURE example(IN degree VARCHAR(20), OUT full_form VARCHAR(50))
BEGIN
IF degree=''B-Tech'' THEN SET full_form = ''Bachelor of Technology
ELSEIF degree=''M-Tech'' THEN SET full_form = ''Master of Technology
ELSEIF degree=''BSC'' THEN SET full_form = ''Bachelor of Science
ELSEIF degree=''MSC'' THEN SET full_form = ''Master of Science
ELSE
RESIGNAL SQLSTATE ''01000''
SET MESSAGE_TEXT = ''Choose from the existing values'', MYSQL_ERRNO = 12121;
RESIGNAL SQLSTATE ''45000''
SET MESSAGE_TEXT = ''Given degree is not valid'', MYSQL_ERRNO = 1001;
END IF;
END;
''''''
cursorObj.execute(resignal_statement)
print("Stored procedure ''example'' created successfully!")
# Call the above procedure
call = "CALL example(''BSC'', @fullform);"
cursorObj.execute(call)
print("Procedure ''example'' called successfully!")
# You can retrieve the value of the variable using SELECT statement
retrieve = "SELECT @fullform;"
cursorObj.execute(retrieve)
result = cursorObj.fetchone()
print("Retrieved full_form value:", result[0])
# If you pass an invalid value to the procedure, it will generate an error message
pass_invalid = "CALL procedureEx (''BBC'', @fullform);"
try:
    cursorObj.execute(pass_invalid)
except mysql.connector.Error as err:
    print("Error occurred:", err)
# Closing the cursor and connection
cursorObj.close()
connection.close()

Stored procedure ''example'' created successfully!
Procedure ''example'' called successfully!
Retrieved full_form value: Bachelor of Science
Error occurred: 1305 (42000): PROCEDURE tut.procedureEx does not exist

The MySQL Character Set

The MySQL Character set is used to determine the set of permissible characters within a string. It allows for the storage of data in various character encodings. By default, MySQL uses the “utf8mb4” character set.

These character sets provides several functionalities −

• Storage Variety − MySQL allows the storage of strings in various character sets.
• String Comparison − Collations help in comparing strings based on the chosen character set.
• Mixed Character Sets − It is possible to combine strings with different character sets or collations within the same server, database, or table.
• Specifying Character Set and Collation − You can define the character set and collation at different levels of the database structure.

The MySQL Show Character-Set

You can use the MySQL SHOW CHARACTER SET statement to view the list of all the available character sets.

Syntax

Following is the syntax of the SHOW CHARACTER SET statement −

SHOW CHARACTER SET [LIKE ''pattern'' | WHERE expr]

Example

The following query retrieves a list of available character sets, along with their descriptions, default collations, and maximum lengths in a MySQL database −

SHOW CHARACTER SET;

Output

Following is the output obtained −

The MySQL Set Character-set

The MySQL SET CHARACTER SET Statement is used to assign a value to the character set attribute. It maps all the strings between the server and the current client with the specified mapping set. This statement changes values of the “character_set_client” and “character_set_results” variables.

Syntax

Following is the syntax of the MySQL SET CHARACTER SET Statement −

SET {CHARACTER SET | CHARSET} {''charset_name'' | DEFAULT}

Where, ”charset_name” is the name of the character set.

Example

The query given below sets the character set to “macroman” −

SET CHARACTER SET macroman;

Output

The output produced is as shown below −

Query OK, 0 rows affected (0.10 sec)

Verification

You can verify the character set values using the SHOW VARIABLES LIKE statement as shown below −

SHOW VARIABLES LIKE "character_set_client";

Following is the output obtained −

Now verifying the current value of the “character_set_results” variable −

SHOW VARIABLES LIKE "character_set_results";

The result produced is as shown below −

MySQL Collation is a set of rules used to decide how to compare and sort various characters of a character set. MySQL supports multiple character sets including ASCII, Unicode System, Binary, etc.

Every character of these character sets is subjected to a weight. A MySQL collation orders the characters based on their respective weights. For instance, when comparing two characters in a character set, if one character holds heavier weight than the other, it is greater; and vice-versa. If both characters have equal weights, they are equal.

Each character set must have at least one collation (or more) and no two character sets can have the same collation.

Implementing MySQL Collations

MySQL implements various types of collations in order to compare character strings −

• Simple Collations for 8-bit Character Sets

• Complex Collations for 8-bit Character Sets

• Collations for Non-Unicode Multibyte Character Sets

• Collations for Unicode Multibyte Character Sets

• Miscellaneous Collations

Every character set has a built-in binary collation, so they need not be redefined. Built-in collations like these must not be modified in any case to avoid unexpected server behaviour.

Simple Collations for 8-bit Character Sets

• As the 8-bit character sets can only hold up to 256 characters, this type of collation is implemented by using a weights array of length 256.
• Each character in the character set is one-to-one mapped to the weights.
• It is a case-insensitive collation, so the uppercase and lowercase of same character hold the same weight.

Complex Collations for 8-bit Character Sets

• For complex 8-bit character sets, collations are implemented by defining the order of characters using functions.
• Here, we create a C source file that specifies the character set properties and defines the necessary support routines to perform operations on that character set properly.

Collations for Non-Unicode Multibyte Character Sets

• Unlike single-byte (8-bit) characters, there are two types of relationships between codes and weights of multi-byte characters.
• Weight of a character is equal to its code.
• Character codes are mapped one-to-one with weights, where weights are not necessarily equal to codes.

Collations for Unicode Multibyte Character Sets

Some collations are based on the Unicode Collation Algorithm (UCA). They hold the following properties −

• If a character has weight, each weight uses 2 bytes.
• If a character has no weight, then the character is ignorable.
• A single character can have many weights. This is called Expansion. For example, the German letter (SHARP S) has a weight of 0x0FEA0FEA.
• Multiple characters together can have only one weight. This is called Contraction. For example, ”ch” is a single letter in Czech and has a weight of 0x0EE2.

Miscellaneous Collations

• Collations that do not fall into any previous categories are termed as Miscellaneous Collations.

Set Character Set and Collation

MySQL allows us to set the character sets and collations at three different levels. The same is described below:

• At Server level

• At Database level

• At Table level

At Server Level

In MySQL, the character set latin1 will be used as the default character set. So, the default collation will be latin1_swedish_ci. MySQL allows us to change these default settings at the server startup level.

When starting up a MySQL server, if we specify a character set, it will use the default collation of that set. But if we explicitly specify both a character set and collation, MySQL will use that combination for all databases created further.

Example

In the following query, we will set the character set as utf8 and the collation as utf8_unicode_cs for the sever.

mysqld --character-set-server=utf8 --collation-server=utf8_unicode_cs

A warning is issued if –collation-server is set to a user-defined collation name.

At Database Level

When we create a database and if we do not provide any character set and collation, the database will use the default character set and collation of the server.

We can override the default character set and collation at the database level using the CREATE DATABASE statement.

If we want to override default settings for existing database, we can use the ALTER DATABASE statement.

Syntax

Following is the basic syntax to override the default settings at database level −

[CREATE | ALTER] DATABASE database_name
CHARACTER SET character_set_name
COLLATE collation_name;

Example

Here, we are creating a database and specifying the character set as utf8 and collation as utf8_unicode_ci using the following query −

CREATE DATABASE testdb
CHARACTER SET utf8
COLLATE utf8_unicode_ci;

At Table Level

In MySQL, a database may contain tables with different characters sets and collations than the database”s character set and collation.

We can specify the default character set and collation at the while creating the table using the CREATE TABLE statement.

If we want to override default settings for existing table, we can use the ALTER TABLE statement.

Syntax

Following is the syntax for specifying default character set and collation for a table using the CREATE TABLE statement −

[CREATE | ALTER] TABLE table_name
column_name datatype (length)
CHARACTER SET character_set_name
COLLATE collation_name

Example

In the following query, we are creating a table without any character set and collation. So, it uses the database”s character set and collation.

CREATE TABLE CUSTOMERS(
   ID VARCHAR(45),
   NAME VARCHAR(45),
   AGE INT
);

Now, we are using the ALTER TABLE statement to modify the character set as ”latin1” and collation as ”latin_german_ci”.

ALTER TABLE CUSTOMERS
CHARACTER SET latin1
COLLATE latin1_german1_ci;

Displaying Default Collations

We can display all the default collations of character sets in MySQL database server using the SHOW CHARACTER SET query.

SHOW CHARACTER SET;

User-defined collations are deprecated in the latest versions of MySQL. Thus, the server issues a warning if they are used in any SQL statement.

A collation string for every character set starts with the character set name and ends with _ci (case insensitive), _cs(case sensitive) or _bin(binary).

The MySQL LIKE Clause

In MySQL, using the LIKE clause with the SHOW COLLATION statement, we can specify a pattern to fetch the names and other information of the collations that match the given pattern.

SHOW COLLATION LIKE ''greek%

Output

The above query returns all the collations with the name greek in it.

The MySQL WHERE Clause

We can use the WHERE clause with the SHOW COLLATION statement to retrieve collation names that match the specified condition.

SHOW COLLATION WHERE Charset = ''cp1251

Output

The above query returns all the collations where the charset id equal to ”cp1251”.