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

The MySQL Alias

The MySQL Alias is used to assign a temporary name, called an Alias to a table or a column in SQL.

Aliases are created using the AS keyword and are used to refer to a specific table or a column without changing its original name. They are used to make the query easily readable when working tables or columns with similar names.

Aliasing Column Names

Aliasing column names is used to assign a different name to a column of a table.

Syntax

The basic syntax of a column alias is as follows −

SELECT column_name
AS alias_name
FROM table_name;

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

Now, let us insert values into the table created above using the INSERT INTO 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 obtained is as follows −

Example

In the following query, we are creating two aliases, one for the ID column and one for the AGE column −

SELECT ID AS CUST_ID, AGE
AS CUST_AGE
FROM CUSTOMERS;

Output

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

Example

If we want the alias name to contain spaces, we can use the double quotation marks as shown in the query below −

SELECT ID AS "CUST ID", AGE
AS "CUST AGE"
FROM CUSTOMERS;

Output

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

Example

In the query below, we are creating an alias named ”INFORMATION” that combines two columns (AGE, ADDRESS) −

SELECT ID, CONCAT(AGE, '', '', ADDRESS)
AS INFORMATION
FROM CUSTOMERS;

Output

When we execute the above query, the output is obtained as follows −

Aliasing Table Names

Aliasing table names is used to assign a different name to a table.

Syntax

Following is the syntax of a table alias −

SELECT column1, column2....
FROM table_name AS alias_name

Example

Let us create another table with the name ORDERS using the following query −

CREATE TABLE ORDERS (
   OID INT NOT NULL,
   DATES DATETIME NOT NULL,
   CUSTOMER_ID INT NOT NULL,
   AMOUNT INT NOT NULL,
   PRIMARY KEY (OID)
 );

Now, let us insert values into the table created above using the INSERT INTO statement as follows −

INSERT INTO ORDERS VALUES
(102, ''2009-10-08 00:00:00'', 3, 3000),
(100, ''2009-10-08 00:00:00'', 3, 1500),
(101, ''2009-11-20 00:00:00'', 2, 1560),
(103, ''2008-05-20 00:00:00'', 4, 2060);

The ORDERS table obtained is as follows −

In the following query, the CUSTOMERS table is aliased as ”C” and the ORDERS table is aliased as ”O” −

SELECT C.ID, C.NAME, C.AGE, O.AMOUNT
FROM CUSTOMERS AS C, ORDERS AS O
WHERE  C.ID = O.CUSTOMER_ID;

Output

This would produce the following result −

Aliasing with Self Join

The MySQL Self Join is used to join a table to itself as if it were two separate tables. Aliasing in self join is used to temporarily rename the table in the SQL statement to prevent confusion.

Syntax

Following is the syntax for performing a self-join with aliases −

SELECT column_name(s)
FROM my_table a, my_table b
ON a.join_column = b.join_column

Example

Now, let us join the CUSTOMERS table to itself using the self join to establish a relationship among the customers on the basis of their earnings.

Here, we are aliasing column names and table names to create a more meaningful resultant table.

SELECT a.ID, b.NAME
AS EARNS_HIGHER, a.NAME
AS EARNS_LESS, a.SALARY
AS LOWER_SALARY
FROM CUSTOMERS a, CUSTOMERS b
WHERE a.SALARY < b.SALARY;

Output

Output of the above query is as follows −

The MySQL ROLLUP Clause

The MySQL ROLLUP Clause is an extension of the GROUP BY Clause. It is used with aggregate functions in MySQL to find the grand total or a summary of a column”s values (also known as super-aggregate of a column), in an extra row within a table.

Consider a manufacturing factory that tracks monthly production data in a table. To determine the annual product production, you can use the SUM() aggregate function along with ROLLUP. However, if you need to find out the number of months where production falls below a specific threshold, ROLLUP will allow you to count such months as well using the COUNT() function.

Syntax

Following is the syntax of ROLLUP clause in MySQL −

SELECT AggregateFunction(column_name(s)), column_name(s)
FROM table_name
GROUP BY column_name(s)
WITH ROLLUP;

Example

First, we will create a table named “PRODUCT” containing production information such as product ID, product name, product count, and manufacturing month within an organization −

CREATE TABLE PRODUCT (
   PRODUCT_ID INT,
   PRODUCT_NAME VARCHAR(50),
   PRODUCT_COUNT INT,
   MONTH VARCHAR(20)
);

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

INSERT INTO PRODUCT VALUES
(101, ''Comb'', 2345, ''January''),
(102, ''Coffee Mugs'', 1242, ''January''),
(103, ''Cutlery'', 124, ''January''),
(101, ''Comb'', 3263, ''February''),
(102, ''Coffee Mugs'', 10982, ''February''),
(103, ''Cutlery'', 435, ''February'');

The PRODUCT table obtained is as follows −

Now, let us to find the sum of products manufactured each MONTH using ROLLUP as shown below −

SELECT SUM(PRODUCT_COUNT), MONTH
FROM PRODUCT
GROUP BY MONTH WITH ROLLUP;

Output

you can observe in the output below that the individual product counts for both January and February are calculated, and the grand total of total production is displayed in the third row using ROLLUP −

ROLLUP on Multiple Columns

You can also use ROLLUP on multiple columns by grouping them together using GROUP BY clause.

Example

Here, we are applying the GROUP BY clause on columns ”PRODUCT_ID” and ”PRODUCT_NAME” of the PRODUCT table −

SELECT PRODUCT_ID,
COUNT(PRODUCT_ID) AS PROD_ID_COUNT,
PRODUCT_NAME,
COUNT(PRODUCT_NAME) AS PROD_ID_NAME
FROM PRODUCT
GROUP BY PRODUCT_ID, PRODUCT_NAME;

We get the following output −

Now, calculate the summary of these two rows using ROLLUP as shown in the following query −

SELECT PRODUCT_ID,
COUNT(PRODUCT_ID) AS PROD_ID_COUNT,
PRODUCT_NAME,
COUNT(PRODUCT_NAME) AS PROD_ID_NAME
FROM PRODUCT
GROUP BY PRODUCT_ID, PRODUCT_NAME
WITH ROLLUP;

You can see in the output below that the summary is calculated not only at the final level but also at two levels. For every product name, a column summary is displayed −

Rollup Using Client Program

We can also perform rollup Using Client Program.

Syntax

$sql = "SELECT SUM(PRODUCT_COUNT), MONTH FROM PRODUCT GROUP BY MONTH WITH ROLLUP";
$mysqli->query($sql);

sql = "SELECT SUM(PRODUCT_COUNT), MONTH FROM PRODUCT GROUP BY MONTH WITH ROLLUP";
con.query(sql);

String sql = "SELECT SUM(PRODUCT_COUNT), MONTH FROM PRODUCT GROUP BY MONTH WITH ROLLUP";
statement.executeQuery(sql);

rollup_query = "SELECT SUM(PRODUCT_COUNT), MONTH FROM PRODUCT GROUP BY MONTH WITH ROLLUP"
cursorObj.execute(rollup_query)

Example

Following are the programs −

$dbhost = ''localhost
$dbuser = ''root
$dbpass = ''password
$db = ''TUTORIALS
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $db);
if ($mysqli->connect_errno) {
    printf("Connect failed: %s
", $mysqli->connect_error);
    exit();
}
//printf(''Connected successfully.
'');
$sql = "CREATE TABLE PRODUCT ( PRODUCT_ID INT, PRODUCT_NAME VARCHAR(50), PRODUCT_COUNT INT, MONTH VARCHAR(20) )";
if($mysqli->query($sql)){
    printf("Product table created successfully....!");
}
//now let''s insert some records into the table
$sql = "INSERT INTO PRODUCT VALUES(101, ''Comb'', 2345, ''January'')";
if($mysqli->query($sql)){
    printf("First record inserted successfully...!n");
}
$sql = "INSERT INTO PRODUCT VALUES(102, ''Coffee Mugs'', 1242, ''January'')";
if($mysqli->query($sql)){
    printf("Second record inserted successfully...!n");
}
$sql = "INSERT INTO PRODUCT VALUES(103, ''Cutlery'', 124, ''January'')";
if($mysqli->query($sql)){
    printf("Third record inserted successfully...!n");
}
$sql = "INSERT INTO PRODUCT VALUES(101, ''Comb'', 3263, ''February'')";
if($mysqli->query($sql)){
    printf("Fourth record inserted successfully...!n");
}
//display the table records
$sql = "SELECT * FROM PRODUCT";
if($result = $mysqli->query($sql)){
    printf("Table records: n");
while($row = mysqli_fetch_array($result)){
    printf("PRODUCT_ID: %d, PRODUCT_NAME: %s, PRODUCT_COUNT: %d, MONTH: %s",
    $row[''PRODUCT_ID''],
    $row[''PRODUCT_NAME''],
    $row[''PRODUCT_COUNT''],
    $row[''MONTH'']);
    printf("n");
}}
//let''s find the sum of product
$sql = "SELECT SUM(PRODUCT_COUNT), MONTH FROM PRODUCT GROUP BY MONTH WITH ROLLUP";
if($result = $mysqli->query($sql)){
    printf("Sum of product: n");
    while($row = mysqli_fetch_array($result)){
        printf("Sum of product: %d, MONTH: %s", $row[''SUM(PRODUCT_COUNT)''], $row[''MONTH'']);
        printf("n");
    }
}
if($mysqli->error){
    printf("Error message: ", $mysqli->error);
}
$mysqli->close();

Product table created successfully....!
First record inserted successfully...!
Second record inserted successfully...!
Third record inserted successfully...!
Fourth record inserted successfully...!
Table records:
PRODUCT_ID: 101, PRODUCT_NAME: Comb, PRODUCT_COUNT: 2345, MONTH: January
PRODUCT_ID: 102, PRODUCT_NAME: Coffee Mugs, PRODUCT_COUNT: 1242, MONTH: January
PRODUCT_ID: 103, PRODUCT_NAME: Cutlery, PRODUCT_COUNT: 124, MONTH: January
PRODUCT_ID: 101, PRODUCT_NAME: Comb, PRODUCT_COUNT: 3263, MONTH: February
Sum of product:
Sum of product: 3263, MONTH: February
Sum of product: 3711, MONTH: January
Sum of product: 6974, MONTH:

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

    sql = "CREATE TABLE PRODUCT (PRODUCT_ID INT,PRODUCT_NAME VARCHAR(50),PRODUCT_COUNT INT,MONTH VARCHAR(20));"
    con.query(sql);

    sql = "INSERT INTO PRODUCT VALUES(101, ''Comb'', 2345, ''January''),(102, ''Coffee Mugs'', 1242, ''January''),(103, ''Cutlery'', 124, ''January''),(101, ''Comb'', 3263, ''February''),(102, ''Coffee Mugs'', 10982, ''February''),(103, ''Cutlery'', 435, ''February'');"
    con.query(sql);

    sql = "SELECT SUM(PRODUCT_COUNT), MONTH FROM PRODUCT GROUP BY MONTH WITH ROLLUP";
    con.query(sql, function(err, result){
      if (err) throw err
      console.log(result);
    });
});

Connected!
--------------------------
[
  { ''SUM(PRODUCT_COUNT)'': ''14680'', MONTH: ''February'' },
  { ''SUM(PRODUCT_COUNT)'': ''3711'', MONTH: ''January'' },
  { ''SUM(PRODUCT_COUNT)'': ''18391'', MONTH: null }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class RollUp {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/TUTORIALS";
        String user = "root";
        String password = "password";
        ResultSet rs;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection(url, user, password);
            Statement st = con.createStatement();
            //System.out.println("Database connected successfully...!");
            //create a table with name Product
            String sql = "CREATE TABLE PRODUCT ( PRODUCT_ID INT, PRODUCT_NAME VARCHAR(50), PRODUCT_COUNT INT, MONTH VARCHAR(20) )";
            st.execute(sql);
            System.out.println("Product table created successfully....!");
            //let''s insert some records into it...
            String sql1 = "INSERT INTO PRODUCT VALUES(101, ''Comb'', 2345, ''January''),  (102, ''Coffee Mugs'', 1242, ''January''), (103, ''Cutlery'', 124, ''January''), (101, ''Comb'', 3263, ''February'')";
            st.execute(sql1);
            System.out.println("Records inserted successfully...!");
            //print table records
            String sql2 = "SELECT * FROM PRODUCT";
            rs = st.executeQuery(sql2);
            System.out.println("Table records: ");
            while(rs.next()) {
                String PRODUCT_ID = rs.getString("PRODUCT_ID");
                String PRODUCT_NAME = rs.getString("PRODUCT_NAME");
                String PRODUCT_COUNT = rs.getString("PRODUCT_COUNT");
                String MONTH = rs.getString("MONTH");
                System.out.println("PRODUCT_ID: " + PRODUCT_ID + ", PRODUCT_NAME: " + PRODUCT_NAME + ", PRODUCT_COUNT: " + PRODUCT_COUNT + ", MONTH: " + MONTH);
            }
            //let''s calculate the sum of product with RollUp
            String sql3 = "SELECT SUM(PRODUCT_COUNT), MONTH FROM PRODUCT GROUP BY MONTH WITH ROLLUP";
            rs = st.executeQuery(sql3);
            System.out.println("Sum of product: ");
            while(rs.next()) {
                String sum = rs.getString("SUM(PRODUCT_COUNT)");
                String MONTH = rs.getString("MONTH");
                System.out.println("Sum: " + sum + ", MONTH: " + MONTH);
            }
        }catch(Exception e) {
            e.printStackTrace();
        }
    }
}

Product table created successfully....!
Records inserted successfully...!
Table records:
PRODUCT_ID: 101, PRODUCT_NAME: Comb, PRODUCT_COUNT: 2345, MONTH: January
PRODUCT_ID: 102, PRODUCT_NAME: Coffee Mugs, PRODUCT_COUNT: 1242, MONTH: January
PRODUCT_ID: 103, PRODUCT_NAME: Cutlery, PRODUCT_COUNT: 124, MONTH: January
PRODUCT_ID: 101, PRODUCT_NAME: Comb, PRODUCT_COUNT: 3263, MONTH: February
Sum of product:
Sum: 3263, MONTH: February
Sum: 3711, MONTH: January
Sum: 6974, MONTH: null

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()
# providing rollup query
rollup_query = """SELECT SUM(PRODUCT_COUNT), MONTH FROM PRODUCT GROUP BY MONTH WITH ROLLUP"""
cursorObj.execute(rollup_query)
# Fetching and printing the results
results = cursorObj.fetchall()
print("Rollup Results:")
for row in results:
    print(f"Product Count: {row[0]}, MONTH: {row[1]}")
# Closing the cursor and connection
cursorObj.close()
connection.close()

Rollup Results:
Product Count: 14680, MONTH: February
Product Count: 3711, MONTH: January
Product Count: 18391, MONTH: None