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

The MySQL ON DELETE CASCADE Constraint

The MySQL ON DELETE CASCADE constraint ensures that when a row in the parent table is deleted, all related rows in the child table are automatically deleted as well. This constraint helps maintain referential integrity between two tables that are connected through a foreign key relationship.

If we do not use this constraint, the database server will, by default, prevent us from deleting data in a table if it is referenced by other tables.

Example

Creating the Parent Table −

First, let us create the parent table with the name PERSONS using the following query −

CREATE TABLE PERSONS(
   P_ID int primary key,
   P_NAME varchar(40),
   P_AGE int
);

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

INSERT INTO PERSONS VALUES
(1, "Priya", 29),
(2, "Sarah", 20),
(3, "Varun", 26),
(4, "Dev", 25),
(5, "Ram", 31),
(6, "Aarohi", 34);

The PERSONS table obtained is as shown below −

Creating the Child Table −

Now, let us create a child table named Films_watched with the ON DELETE CASCADE constraint. In this table, the P_ID column is a foreign key referencing the P_ID column in the Persons table −

CREATE TABLE Films_watched (
   P_ID INT,
   F_NO INT,
   F_NAME varchar(40),
   PRIMARY KEY(P_ID,F_NO),
   FOREIGN KEY(P_ID)
   REFERENCES PERSONS(P_ID)
   ON DELETE CASCADE
);

Now, we are inserting rows into the Films_watched table −

INSERT INTO Films_watched VALUES
(1, 130, "RRR"),
(2, 131, "Bahubali"),
(3, 132, "Pushpa"),
(3, 133, "KGF"),
(3, 134, "Salaar"),
(6, 135, "Karthikeya");

The Films_watched table produced is as follows −

Deleting a Record from the Parent Table −

As we can see in the above table, we have three films that has been watched by the person with P_ID = 3. Here, we are deleting the person with P_ID = 3 from the PERSONS (parent) table −

DELETE FROM PERSONS WHERE P_ID = 3;

Following is the output obtained −

Query OK, 1 row affected (0.01 sec)

After this deletion, let us check the data in both the Persons and Films_watched tables.

Querying Data from the Persons Table −

To see the remaining records in the Persons table, use the following SELECT query −

SELECT * FROM PERSONS;

We can see in the table below, the row with P_ID = 3 is deleted −

Querying Data from the Films_watched Table −

Lastly, you can check the data in the Films_watched table −

SELECT * FROM Films_watched;

Output

We can see in the output below that all related records with P_ID = 3 have been automatically deleted −

The MySQL UUID function

The MySQL UUID() function is used to generate “Universal Unique Identifiers” (UUIDs) in accordance with RFC 4122. UUIDs are designed to be universally unique, even when generated on different servers. The UUID is generated using a combination of the current timestamp, the unique identifier of the server, and a random number.

UUID Format

The UUID value is represented as a UTF-8 string and is a 128-bit number. The format of the UUID value is in hexadecimal number, and it consists of five segments which are separated by hyphens.

The general format of the UUID value is: aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee, where each segment represents a hexadecimal value.

Generating a UUID

Following is the basic example to generate a UUID using the UUID() function in MySQL −

SELECT UUID();

Output

It will display a universal unique identifier as shown below −

Generating Multiple UUIDs

You can generate multiple UUIDs in a single query, and each UUID will be different as shown below −

SELECT UUID() AS ID1, UUID() AS ID2;

Output

The output will show two different UUIDs, with differences generally in the first segment −

UUIDs in a Database Table

You can use UUIDs as unique identifiers in a database table. Following is an example of how to create a table with a UUID column and insert data −

Here, we are first creating a table with the name “ORDERS”, with an ORDER_ID column of type VARCHAR using the following query −

CREATE TABLE ORDERS(
   ID int auto_increment primary key,
   NAME varchar(40),
   PRODUCT varchar(40),
   ORDER_ID varchar(100)
);

Now, we are inserting data into the ORDERS table, using the UUID() function to generate unique values for the ORDER_ID column −

INSERT INTO ORDERS (NAME, PRODUCT, ORDER_ID) VALUES ("Varun", "Headphones", UUID());
INSERT INTO ORDERS (NAME, PRODUCT, ORDER_ID) VALUES ("Priya", "Mouse", UUID());
INSERT INTO ORDERS (NAME, PRODUCT, ORDER_ID) VALUES ("Nikhil", "Monitor", UUID());
INSERT INTO ORDERS (NAME, PRODUCT, ORDER_ID) VALUES ("Sarah", "Keyboard", UUID());
INSERT INTO ORDERS (NAME, PRODUCT, ORDER_ID) VALUES ("Vaidhya", "Printer", UUID());

Following is the ORDERS table obtained −

Modifying UUIDs

You can modify UUIDs without losing their uniqueness. For example, you can remove hyphens or convert them to base64 notation using functions like REPLACE() and TO_BASE64().

Example

Here, we are updating the UUID value for the record where ID = 1 using the following query −

UPDATE ORDERS SET ORDER_ID = UUID() WHERE ID=1;

Output

Following is the output of the above code −

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

Verification

To verify the modified UUID values, we can use the following SELECT query −

SELECT * FROM ORDERS;

As we can see in the output below, every time we execute the UUID() function, we get a different UUID value −

Example

Assume the previously created table and let us remove hyphens from the UUID of the row with ID = 2 using the REPLACE() function as shown below −

UPDATE ORDERS
SET ORDER_ID = REPLACE(UUID(), ''-'', '''')
WHERE ID = 2;

Output

Output of the above code is as follows −

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

Verification

To verify the modified UUID value, we can use the following SELECT query −

SELECT * FROM ORDERS;

As we can see in the output below, the UUID of row = 2 is modified without disturbing the “unique” part of it −

Example

In the following query, we are converting the UUID of ID = 4 to base64 notation using the TO_BASE64() function −

UPDATE ORDERS
SET ORDER_ID = TO_BASE64(UNHEX(REPLACE(UUID(),''-'','''')))
WHERE ID=4;

Output

The result produced is as follows −

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

Verification

Let us verify the modified UUID value using the following SELECT query −

SELECT * FROM ORDERS;

The output produced is as given below −

The MySQL Common Table Expression

The MySQL Common Table Expression (CTE) is a temporary result-set or table that exists for the duration of a single query. We can use the CTEs to reference the result-set within the context of a single SELECT, UPDATE, INSERT, DELETE, CREATE, VIEW, or MERGE statement.

The scope of a CTE is limited to that specific query. It makes complex queries simple by breaking them into simple blocks.

The MySQL WITH Clause

The MySQL WITH clause is used to create CTEs by having one or more comma-separated subclauses. The subclauses provide a subquery that generates the result-set.

WITH clause cannot be used in MySQL versions before 8.0.

Syntax

Following is the syntax of the MySQL WITH clause −

WITH name_for_summary_data AS (SELECT Statement)
SELECT columns
FROM name_for_summary_data
WHERE conditions  (
   SELECT column
   FROM name_for_summary_data
)
[ORDER BY columns]

Example

Assume we have created a table named DATA containing details such as id, name, age and salary −

CREATE TABLE DATA(
   ID INT,
   NAME CHAR(20),
   AGE INT,
   SALARY INT
);

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

INSERT INTO DATA VALUES
(101, ''John'', 25, 55452),
(102, ''Jane'', 29, 66458),
(103, ''Arub'', 35, 36944);

In the following example, the WITH clause is used to create a CTE named CTE, which is then queried to retrieve the data from the DATA table −

WITH CTE AS
(Select ID, NAME, AGE, SALARY FROM DATA)
SELECT * FROM CTE;

After executing the above code, we get the output as follows −

CTE from Multiple Tables

We can create CTEs from multiple tables by separating each CTE subclause using a comma (”,”).

Example

Suppose we have created a table with name EMPLOYEE and populated data into it as shown below −

CREATE TABLE EMPLOYEE(
   ID INT NOT NULL,
   FIRST_NAME CHAR(20) NOT NULL,
   LAST_NAME CHAR(20),
   AGE INT,
   SEX CHAR(1),
   INCOME FLOAT,
   CONTACT INT
);

Here, we are inserting records into the EMPLOYEE table −

INSERT INTO EMPLOYEE VALUES
(101, ''Serena'', ''Williams'', 27, ''F'', 9000, 101),
(102, ''Virat'', ''Kohli'', 20, ''M'', 6000, 102);

The EMPLOYEE table obtained is as follows −

Now, we create another table CONTACT −

CREATE TABLE CONTACT(
   ID INT NOT NULL,
   EMAIL CHAR(20) NOT NULL,
   PHONE LONG,
   CITY CHAR(20)
);

Let us insert some records in the CONTACT table −

INSERT INTO CONTACT (ID, EMAIL, CITY) VALUES
(101, ''serena@mymail.com'', ''Hyderabad''),
(102, ''virat@mymail.com'', ''Vishakhapatnam'');

The CONTACT table produced is as follows −

Following example uses Common Table Expressions (CTEs) named “exp1” and “exp2” to separately select specific columns from the EMPLOYEE and CONTACT tables. The final SELECT statement joins these CTEs, combining the chosen columns from each −

WITH
exp1 AS (SELECT ID, FIRST_NAME, LAST_NAME FROM EMPLOYEE),
exp2 AS (SELECT EMAIL, PHONE FROM CONTACT)
SELECT * FROM exp1 JOIN exp2;

Following is the output of the above code −

MySQL is an open-source relational database management system that allows us to store and manage large volume of data. One of its key feature is to export data from a table into various formats and CSV is one of it. CSV stands for “Comma Separated Values” file.

Export MySQL Table into CSV File

To export the MySQL table data into a CSV file, we can use the MySQL “SELECT INTO … OUTFILE” statement. Before exporting any table data into CSV files in the database server, we must ensure the following things −

• The MySQL server”s process must have the read/write privileges to the specified target folder, where CSV file will be created.

• The specified CSV file should be already present in the system (No duplicate file).

The exported CSV file can contain data from one or more tables, and it can be modified to include only particular columns or rows.

Syntax

Following is the syntax of SELECT INTO … OUTFILE statement −

SELECT column_name1, column_name2,...
INTO OUTFILE ''C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/file_name.csv''
FIELDS TERMINATED BY '',''
OPTIONALLY ENCLOSED BY ''"''
LINES TERMINATED BY ''rn

Where,

• INTO OUTFILE is the path and name of the CSV file that we want to export the table data to.

• FIELDS TERMINATED BY is the delimiter that separates the fields in the exported CSV file.

• LINES TERMINATED is the line terminator character for exported CSV file.

Storage Location of Exported .csv File

In MySQL, when you export a file, such as a .csv file, the default storage location for the exported file is determined by the “secure_file_priv” variable.

To find out the default path for exported files, you can use the following SQL query −

SHOW VARIABLES LIKE "secure_file_priv";

We get the following output −

Before exporting data to a .csv file, you will need to have at least one table in your MySQL database. Let us create a table named “CUSTOMERS” using the following SQL query −

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

Now, we are inserting data into the above created table as shown below −

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

The CUSTOMERS table obtained is as shown below −

Exporting MySQL Data in CSV Format

You can export MySQL data in CSV file using the SELECT INTO … OUTFILE statement. Here, we are exporting the data of CUSTOMERS table into a CSV file named “CUSTOMERS_BACKUP” using the following query −

SELECT ID, NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS
INTO OUTFILE ''C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/CUSTOMERS_BACKUP.csv''
FIELDS ENCLOSED BY ''"''
TERMINATED BY ''
ESCAPED BY ''"''
LINES TERMINATED BY ''rn

After executing the above query, the CSV format file will be created at the specified path. Following is the output obtained after executing the above query −

Query OK, 7 rows affected (0.01 sec)

Following is the image of “CUSTOMERS_BACKUP.csv” file when we opened it −

Handling File Already Exists Error −

If you attempt to export data into a file that already exists, MySQL will generate an error −

SELECT ID, NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS
INTO OUTFILE ''C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/CUSTOMERS_BACKUP.csv''
FIELDS ENCLOSED BY ''"''
TERMINATED BY ''
ESCAPED BY ''"''
LINES TERMINATED BY ''rn

Following is the error obtained −

ERROR 1086 (HY000): File ''C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/CUSTOMERS_BACKUP.csv'' already exists

To avoid this error, you can either choose a different filename or delete the existing file before executing the export query.

Removing Enclosing Quotes for Numeric Records −

By default, all records in the CSV file will be enclosed in double quotes, including numeric values. If you want to remove the quotes for numeric records, you can use the OPTIONALLY clause before the ENCLOSED BY clause, as shown below −

SELECT ID, NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS
INTO OUTFILE ''C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/CUSTOMERS_BACKUP.csv''
FIELDS TERMINATED BY '',''
OPTIONALLY ENCLOSED BY ''"''
LINES TERMINATED BY ''rn

Following is the output obtained after executing the above query −

Query OK, 7 rows affected (0.00 sec)

As we can see the image of CSV file below, the double quotes (“”) are removed for the numeric records.

Exporting Table Data Along with Column Headings

To export table data along with their respective column headings, you can use the UNION ALL statement. This allows you to create a row with column names and then append the data rows. Here is an example query −

SELECT ''ID'', ''NAME'', ''EMAIL'', ''PHONE'', ''CITY''
UNION ALL
SELECT ID, NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS
INTO OUTFILE ''C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/CUSTOMERS_BACKUP.csv''
FIELDS TERMINATED BY '',''
OPTIONALLY ENCLOSED BY ''"''
LINES TERMINATED BY ''rn

Output

The output obtained is as follows −

Query OK, 8 rows affected (0.01 sec)

Verification

If we verify the .csv file, we can see that the respective column names are added −

Exporting Table Data Without Specifying Column Names

You can also export table data into a CSV file without specifying column names.

Syntax

Following is the syntax to export table data into a CSV file without specifying column names −

TABLE table_name ORDER BY column_name LIMIT 100
INTO OUTFILE ''/path/filename.txt''
FIELDS ENCLOSED BY ''"''
TERMINATED BY ''
ESCAPED BY ''"''
LINES TERMINATED BY ''rn;

Example

In the following query, we are exporting the CUSTOMERS table data into “CUSTOMERS_BACKUP.csv” file without specifying their column names −

TABLE CUSTOMERS ORDER BY NAME LIMIT 100
INTO OUTFILE ''C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/CUSTOMERS_BACKUP.csv''
FIELDS ENCLOSED BY ''"''
TERMINATED BY ''
ESCAPED BY ''"''
LINES TERMINATED BY ''rn

Output

The result produced is as follows −

Query OK, 7 rows affected (0.01 sec)

Verification

As we can see in the “CUSTOMERS_BACKUP.csv” file, the table data got exported −

Replacing NULL Values

If your table contains NULL values, you can use the IFNULL() function to replace them with specific values before exporting the data to a CSV file.

Example

In the following query, the IFNULL() function is used to replace NULL values in the “ADDRESS” column with “NULL_VALUE” before exporting the data as shown below −

SELECT ID, NAME, AGE, IFNULL(ADDRESS, ''NULL_VALUE'') FROM CUSTOMERS
INTO OUTFILE ''C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/CUSTOMERS_BACKUP.csv''
FIELDS ENCLOSED BY ''"''
TERMINATED BY ''
ESCAPED BY ''"''
LINES TERMINATED BY ''rn

Output

The result obtained is as shown below −

Query OK, 7 rows affected (0.00 sec)

Verification

The NULL values (N) are replaced with “NULL_VALUE” −

Using Client Program

We can also export table into CSV file using Client Program.

Syntax

$sql = "SELECT ID, NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS INTO OUTFILE ''C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/CUSTOMERS_BACKUP_new.csv'' FIELDS ENCLOSED BY '''' TERMINATED BY
'' ESCAPED BY '''' LINES TERMINATED BY ''rn''";
$mysqli->query($sql);

sql = `SELECT ID, NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS INTO OUTFILE ''C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/CUSTOMERS_BACKUP_new1.csv'' FIELDS ENCLOSED BY ''"'' TERMINATED BY
'' ESCAPED BY ''"'' LINES TERMINATED BY ''rn''`;
con.query(sql);

String sql = "SELECT ID, NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS INTO OUTFILE ''C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/CUSTOMERS_BACKUP_new.csv'' FIELDS ENCLOSED BY '''' TERMINATED BY
'' ESCAPED BY '''' LINES TERMINATED BY ''\r\n''";
statement.execute(sql);

sql = "SELECT ID, NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS INTO OUTFILE ''C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/CUSTOMERS_BACKUP.csv''FIELDS ENCLOSED BY ''"''TERMINATED BY ''
'' ESCAPED BY ''"''LINES TERMINATED BY ''rn''"
cursorObj.execute(sql)

Example

Following are the programs −

$dbhost = ''localhost
$dbuser = ''root
$dbpass = ''password
$db = ''TUTORIALS
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $db);
if ($mysqli->connect_errno) {
    printf("Connect failed: %s
", $mysqli->connect_error);
    exit();
}
//printf(''Connected successfully.
'');
$sql = "SELECT ID, NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS INTO OUTFILE ''C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/CUSTOMERS_BACKUP_new.csv'' FIELDS ENCLOSED BY '''' TERMINATED BY '' ESCAPED BY '''' LINES TERMINATED BY ''rn''";
if($result = $mysqli->query($sql)){
   printf("Table data exported successfully....!n");
   print_r($result);
}
if($mysqli->error){
    printf("Error message: ", $mysqli->error);
}
$mysqli->close();

Table data exported successfully....!
1

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

 //Connecting to MySQL
 con.connect(function(err) {
 if (err) throw err;
//   console.log("Connected successfully...!");
//   console.log("--------------------------");
 sql = "USE TUTORIALS";
 con.query(sql);
 sql = `SELECT ID, NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS INTO OUTFILE ''C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/CUSTOMERS_BACKUP_new1.csv'' FIELDS ENCLOSED BY ''"'' TERMINATED BY '' ESCAPED BY ''"'' LINES TERMINATED BY ''rn''`;
 con.query(sql, function(err, result){
    console.log("Table data exported successfully...!");
    console.log("Data: ")
    if (err) throw err;
    console.log(result);
    });
});

Table data exported successfully...!
Data:
ResultSetHeader {
  fieldCount: 0,
  affectedRows: 7,
  insertId: 0,
  info: '''',
  serverStatus: 34,
  warningStatus: 0,
  changedRows: 0
}

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class ExportTableToCSVFile {
  public static void main(String[] args) {
    String url = "jdbc:mysql://localhost:3306/TUTORIALS";
    String user = "root";
    String password = "password";
    ResultSet rs;
    try {
      Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection(url, user, password);
            Statement st = con.createStatement();
            //System.out.println("Database connected successfully...!");
            String sql = "SELECT ID, NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS INTO OUTFILE ''C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/CUSTOMERS_BACKUP_new.csv'' FIELDS ENCLOSED BY '''' TERMINATED BY '' ESCAPED BY '''' LINES TERMINATED BY ''\r\n''";
            st.execute(sql);
            System.out.println("Successfully...! table exported into CSV file..");
    }catch(Exception e) {
      e.printStackTrace();
    }
  }
}

Successfully...! table exported into CSV file..

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
cursorObj = connection.cursor()
# Query to export table into csv file
sql = """
SELECT ID, NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS
INTO OUTFILE ''C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/CUSTOMERS_BACKUP.csv''
FIELDS ENCLOSED BY ''"''
TERMINATED BY ''
ESCAPED BY ''"''
LINES TERMINATED BY ''rn
"""
cursorObj.execute(sql)
print("Table data expoted successfully")
# Reading and displaying the exported CSV file
with open(''C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/CUSTOMERS_BACKUP.csv'', ''r'') as csvfile:
    for row in csvfile:
        # Use strip() to remove extra newlines
        print(row.strip())
# Closing the cursor and connection
cursorObj.close()
connection.close()

Table data expoted successfully
"1";"Ramesh";"32";"Ahmedabad";"2000.00"
"2";"Khilan";"25";"Delhi";"1500.00"
"3";"kaushik";"23";"Kota";"2000.00"
"4";"Chaitali";"25";"Mumbai";"6500.00"
"5";"Hardik";"27";"Bhopal";"8500.00"
"6";"Komal";"22";"MP";"4500.00"
"7";"Muffy";"24";"Indore";"10000.00"

Import MySQL CSV into Database

Before importing the CSV file into the database server, we must ensure the following things −

• Database Table − Ensure you have a database table already set up to receive the incoming data.
• CSV File − You need a CSV file containing the data to be imported.
• User Privileges − Ensure your account has the necessary privileges, specifically FILE and INSERT, to perform this operation.
• Matching Columns − The target table and the CSV file should have matching columns with the same data types.
• CSV Format − The CSV file should be in a comma-separated format, with each row representing a record.

Syntax

Following is the syntax of the LOAD DATA INFILE statement in MySQL −

LOAD DATA INFILE file_path
INTO TABLE table_name
FIELDS TERMINATED BY delimiter
ENCLOSED BY enclosure
LINES TERMINATED BY line_separator
IGNORE number_of_lines_to_skip;

Where,

• file_path is the path to the CSV file that contains the data to be imported.

• table_name is the name of the target table, where the data will be imported.

• delimiter is a character that separates each record in the CSV file.

• Enclosure is a character that encloses string records in the CSV file.

• line_seperator is a character that marks the end of a line in the CSV file.

• number_of_lines_to_skip is the number of lines to ignore at the beginning of the CSV file.

Example

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

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

Now, let us insert rows into the above created table −

INSERT INTO EMPLOYEES VALUES
(1, ''Ramesh'', 32, ''Ahmedabad'', 2000.00 ),
(2, ''Khilan'', 25, ''Delhi'', 1500.00 ),
(3, ''kaushik'', 23, ''Kota'', 2000.00 ),
(4, ''Chaitali'', 25, ''Mumbai'', 6500.00 ),
(5, ''Hardik'', 27, ''Bhopal'', 8500.00 ),
(6, ''Komal'', 22, ''MP'', 4500.00 ),
(7, ''Muffy'', 24, ''Indore'', 10000.00 );

The EMPLOYEES table obtained is as shown below −

Export Data to CSV −

Now, we export the data from the EMPLOYEES table into a CSV file named “EMPLOYEES_BACKUP” using the following query −

SELECT * FROM EMPLOYEES
INTO OUTFILE ''C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/EMPLOYEES_BACKUP.csv''
FIELDS TERMINATED BY '',''
OPTIONALLY ENCLOSED BY ''"''
LINES TERMINATED BY ''rn

After executing the above query, the CSV format file will be created at the specified path. Following is the output obtained after executing the above query −

Query OK, 7 rows affected (0.00 sec)

Following is the image of “EMPLOYEES_BACKUP.csv” file when we opened it −

Create Another Table −

Now, let us create another table named “CUSTOMERS” with the same columns and data types as EMPLOYEES table −

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

Following is the output of the above code −

Query OK, 0 rows affected (0.03 sec)

Import Data from CSV −

Now, we import all the data from “EMPLOYEES_BACKUP.csv” file into the CUSTOMERS table using the following query −

LOAD DATA INFILE ''C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/EMPLOYEES_BACKUP.csv''
INTO TABLE CUSTOMERS
FIELDS TERMINATED BY '',''
OPTIONALLY ENCLOSED BY ''"''
LINES TERMINATED BY ''n

The result obtained is as follows −

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

Verify Data Import −

To verify that the data has been successfully imported into the CUSTOMERS table, we can use the following SELECT statement −

SELECT * FROM CUSTOMERS;

As we can see the output below, the CUSTOMERS table contains the same data as the EMPLOYEES table, as the CSV data has been imported successfully −

Importing a CSV File Using Client Program

We can also import CSV file into database using Client Program.

Syntax

$sql = "LOAD DATA INFILE ''C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/EMPLOYEES_BACKUP.csv'' INTO TABLE CUSTOMERS FIELDS TERMINATED BY '' OPTIONALLY ENCLOSED BY '''' LINES TERMINATED BY ''n''";
$mysqli->query($sql);

sql = `LOAD DATA INFILE ''C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/EMPLOYEES_BACKUP.csv'' INTO TABLE CUSTOMERS FIELDS TERMINATED BY '' OPTIONALLY ENCLOSED BY '''' LINES TERMINATED BY ''n''`;
con.query(sql);

String sql = "LOAD DATA INFILE ''C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/EMPLOYEES_BACKUP.csv'' INTO TABLE CUSTOMERS FIELDS TERMINATED BY '' OPTIONALLY ENCLOSED BY '''' LINES TERMINATED BY ''\n''";
statement.execute(sql);

import_sql = "LOAD DATA INFILE ''C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/EMPLOYEE_BACKUP.csv'' INTO TABLE CUSTOMERS FIELDS TERMINATED BY '',''
OPTIONALLY ENCLOSED BY ''"'' LINES TERMINATED BY ''rn''"
cursorObj.execute(import_sql)

Example

Following are the programs −

$dbhost = ''localhost
$dbuser = ''root
$dbpass = ''password
$db = ''TUTORIALS
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $db);
if ($mysqli->connect_errno) {
    printf("Connect failed: %s
", $mysqli->connect_error);
    exit();
}
//printf(''Connected successfully.
'');
$sql = "CREATE TABLE EMPLOYEES( ID INT NOT NULL, NAME VARCHAR(20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(25) NOT NULL, SALARY DECIMAL(18, 2), PRIMARY KEY(ID) )";
if($mysqli->query($sql)){
    printf("Employees table created successfully...!n");
}
//now lets insert some data into it..
$sql = "INSERT INTO EMPLOYEES (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, ''Ramesh'',
32, ''Ahmedabad'', 2000.00 )";
if($mysqli->query($sql)){
    printf("First record inserted successfully...!n");
}
$sql = "INSERT INTO EMPLOYEES (ID,NAME,AGE,ADDRESS,SALARY) VALUES (2, ''Khilan'',
25, ''Delhi'', 1500.00 )";
 if($mysqli->query($sql)){
    printf("Second record inserted successfully...!n");
}
$sql = "INSERT INTO EMPLOYEES (ID,NAME,AGE,ADDRESS,SALARY) VALUES (3, ''kaushik'',
23, ''Kota'', 2000.00 )";
if($mysqli->query($sql)){
    printf("Third record inserted successfully...!n");
}
//display table record
$sql = "SELECT * FROM EMPLOYEES";
if($result = $mysqli->query($sql)){
    printf("Table records: n");
    while($row = mysqli_fetch_array($result)){
        printf("ID: %d, NAME: %s, AGE: %d, ADDRESS: %s, SALARY: %f",
            $row[''ID''],
            $row[''NAME''],
            $row[''AGE''],
            $row[''ADDRESS''],
            $row[''SALARY'']);
            printf("n");
    }
}
//now let''s export the table data into csv file
$sql = "SELECT ID, NAME, AGE, ADDRESS, SALARY FROM EMPLOYEES INTO OUTFILE ''C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/EMPLOYEES_BACKUP.csv'' FIELDS ENCLOSED BY '''' TERMINATED BY '' ESCAPED BY '''' LINES TERMINATED BY ''rn''";
if($result = $mysqli->query($sql)){
   printf("Table data exported successfully....!n");
   print_r($result , "n");
}
//now let''s create a customers table
$sql = "CREATE TABLE CUSTOMERS( ID INT NOT NULL, NAME VARCHAR(20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(25) NOT NULL, SALARY DECIMAL(18, 2), PRIMARY KEY(ID) )";
if($mysqli->query($sql)){
    printf("Customers table created successfully...!n");
}
//now lets import employees table data csv file into customers table
$sql = "LOAD DATA INFILE ''C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/EMPLOYEES_BACKUP.csv'' INTO TABLE CUSTOMERS FIELDS TERMINATED BY '' OPTIONALLY ENCLOSED BY '''' LINES TERMINATED BY ''n''";
if($mysqli->query($sql)){
    printf("CSV file data imported successfully into Customers table....!n");
}
//print customers table records
$sql = "SELECT * FROM CUSTOMERS";
if($result = $mysqli->query($sql)){
    printf("Customers table records after importing csv file data into it: n");
    while($row = mysqli_fetch_array($result)){
        printf("ID: %d, NAME: %s, AGE: %d, ADDRESS: %s, SALARY: %f",
            $row[''ID''],
            $row[''NAME''],
            $row[''AGE''],
            $row[''ADDRESS''],
            $row[''SALARY'']);
            printf("n");
    }
}
if($mysqli->error){
    printf("Error message: ", $mysqli->error);
}
$mysqli->close();

Employees table created successfully...!
First record inserted successfully...!
Second record inserted successfully...!
Third record inserted successfully...!
Table records:
ID: 1, NAME: Ramesh, AGE: 32, ADDRESS: Ahmedabad, SALARY: 2000.000000
ID: 2, NAME: Khilan, AGE: 25, ADDRESS: Delhi, SALARY: 1500.000000
ID: 3, NAME: kaushik, AGE: 23, ADDRESS: Kota, SALARY: 2000.000000
Table data exported successfully....!
Customers table created successfully...!
CSV file data imported successfully into Customers table....!
Customers table records after importing csv file data into it:
ID: 1, NAME: Ramesh, AGE: 32, ADDRESS: Ahmedabad, SALARY: 2000.000000
ID: 2, NAME: Khilan, AGE: 25, ADDRESS: Delhi, SALARY: 1500.000000
ID: 3, NAME: kaushik, AGE: 23, ADDRESS: Kota, SALARY: 2000.000000

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

 //Connecting to MySQL
 con.connect(function(err) {
 if (err) throw err;
//   console.log("Connected successfully...!");
//   console.log("--------------------------");
 sql = "USE TUTORIALS";
 con.query(sql);
 //create employees table
 sql = "CREATE TABLE EMPLOYEES( ID INT NOT NULL, NAME VARCHAR(20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(25) NOT NULL, SALARY DECIMAL(18, 2), PRIMARY KEY(ID) )";
 if(con.query(sql)){
    console.log("Employees table created successfully...!");
 }
//now let''s insert some values
sql = `INSERT INTO EMPLOYEES (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, ''Ramesh'',
32, ''Ahmedabad'', 2000.00 )`;
if(con.query(sql)){
    console.log("First record inserted successfully...!");
}
sql = `INSERT INTO EMPLOYEES (ID,NAME,AGE,ADDRESS,SALARY) VALUES (2, ''Khilan'',
    25, ''Delhi'', 1500.00 )`;
if(con.query(sql)){
    console.log("Second record inserted successfully...!");
}
sql = `INSERT INTO EMPLOYEES (ID,NAME,AGE,ADDRESS,SALARY) VALUES (3, ''kaushik'',
    23, ''Kota'', 2000.00 )`;
if(con.query(sql)){
    console.log("Third record inserted successfully...!");
}
sql = "SELECT * FROM EMPLOYEES";
con.query(sql, function(err, result){
    console.log("Employees table records: ");
    if (err) throw err;
    console.log(result);
    });
sql = `SELECT ID, NAME, AGE, ADDRESS, SALARY FROM EMPLOYEES INTO OUTFILE ''C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/EMPLOYEES_BACKUP.csv'' FIELDS ENCLOSED BY '''' TERMINATED BY '' ESCAPED BY '''' LINES TERMINATED BY ''rn''`;
con.query(sql, function(err, result){
    if (err) throw err;
    console.log("Table data exported successfully....!");
    });
sql = `CREATE TABLE CUSTOMERS( ID INT NOT NULL, NAME VARCHAR(20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(25) NOT NULL, SALARY DECIMAL(18, 2), PRIMARY KEY(ID) )`;
con.query(sql, function(err, result){
    if (err) throw err;
    console.log("Customers table created successfully...!");
    });
sql = `LOAD DATA INFILE ''C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/EMPLOYEES_BACKUP.csv'' INTO TABLE CUSTOMERS FIELDS TERMINATED BY '' OPTIONALLY ENCLOSED BY '''' LINES TERMINATED BY ''n''`;
con.query(sql, function(err, result){
    if (err) throw err;
    console.log("CSV file data imported successfully into Customers table....!");
    });
sql = "SELECT * FROM CUSTOMERS";
con.query(sql, function(err, result){
    console.log("Customers table records after importing csv file data into it: ")
    if (err) throw err;
    console.log(result);
});
});

Employees table created successfully...!
First record inserted successfully...!
Second record inserted successfully...!
Third record inserted successfully...!
Employees table records:
[
  {
    ID: 1,
    NAME: ''Ramesh'',
    AGE: 32,
    ADDRESS: ''Ahmedabad'',
    SALARY: ''2000.00''
  },
  {
    ID: 2,
    NAME: ''Khilan'',
    AGE: 25,
    ADDRESS: ''Delhi'',
    SALARY: ''1500.00''
  },
  {
    ID: 3,
    NAME: ''kaushik'',
    AGE: 23,
    ADDRESS: ''Kota'',
    SALARY: ''2000.00''
  }
]
Table data exported successfully....!
Customers table created successfully...!
CSV file data imported successfully into Customers table....!
Customers table records after importing csv file data into it:
[
  {
    ID: 1,
    NAME: ''Ramesh'',
    AGE: 32,
    ADDRESS: ''Ahmedabad'',
    SALARY: ''2000.00''
  },
  {
    ID: 2,
    NAME: ''Khilan'',
    AGE: 25,
    ADDRESS: ''Delhi'',
    SALARY: ''1500.00''
  },
  {
    ID: 3,
    NAME: ''kaushik'',
    AGE: 23,
    ADDRESS: ''Kota'',
    SALARY: ''2000.00''
  }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class ImportCSVToDatabase {
  public static void main(String[] args) {
    String url = "jdbc:mysql://localhost:3306/TUTORIALS";
    String user = "root";
    String password = "password";
    ResultSet rs;
    try {
      Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection(url, user, password);
            Statement st = con.createStatement();
            //System.out.println("Database connected successfully...!");
            String sql = "CREATE TABLE EMPLOYEES( ID INT NOT NULL, NAME VARCHAR(20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(25) NOT NULL, SALARY DECIMAL(18, 2), PRIMARY KEY(ID))";
            st.execute(sql);
            System.out.println("Table EMPLOYEES created successfully....!");
            //let''s insert some records into it...
            String sql1 = "INSERT INTO EMPLOYEES (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, ''Ramesh'', 32, ''Ahmedabad'', 2000.00 ), (2, ''Khilan'', 25, ''Delhi'', 1500.00 ), (3, ''kaushik'', 23, ''Kota'', 2000.00 )";
            st.execute(sql1);
            System.out.println("Records inserted successfully...!");
            //lets print table records
            String sql2 = "SELECT * FROM  EMPLOYEES";
            rs = st.executeQuery(sql2);
            System.out.println("Table records: ");
            while(rs.next()) {
              String id = rs.getString("id");
              String name = rs.getString("name");
              String age = rs.getString("age");
              String address = rs.getString("address");
              String salary = rs.getString("salary");
              System.out.println("Id: " + id + ", Name: " + name + ", Age: " + age + ", Address: " + address + ", Salary: " + salary);
            }
            //lets export the table records into csv file
            String sql3 = "SELECT ID, NAME, AGE, ADDRESS, SALARY FROM EMPLOYEES INTO OUTFILE ''C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/EMPLOYEES_BACKUP.csv'' FIELDS ENCLOSED BY '''' TERMINATED BY '' ESCAPED BY '''' LINES TERMINATED BY ''\r\n''";
            st.execute(sql3);
            System.out.println("Table records exported successfully into CSV file....!");
            //let''s create one more table name Customers;
            String sql4 = "CREATE TABLE CUSTOMERS( ID INT NOT NULL, NAME VARCHAR(20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(25) NOT NULL, SALARY DECIMAL(18, 2), PRIMARY KEY(ID))";
            st.execute(sql4);
            System.out.println("Table Customers created successfully...!");
            //lets import csv file into database
            String sql5 = "LOAD DATA INFILE ''C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/EMPLOYEES_BACKUP.csv'' INTO TABLE CUSTOMERS FIELDS TERMINATED BY '' OPTIONALLY ENCLOSED BY '''' LINES TERMINATED BY ''\n''";
            st.execute(sql5);
            System.out.println("Data imported successfully...!");
            //now lets print customers table records
            String sql6 = "SELECT * FROM  CUSTOMERS";
            rs = st.executeQuery(sql6);
            System.out.println("Table records: ");
            while(rs.next()) {
              String id = rs.getString("id");
              String name = rs.getString("name");
              String age = rs.getString("age");
              String address = rs.getString("address");
              String salary = rs.getString("salary");
              System.out.println("Id: " + id + ", Name: " + name + ", Age: " + age + ", Address: " + address + ", Salary: " + salary);
            }
    }catch(Exception e) {
      e.printStackTrace();
    }
  }
}

Table EMPLOYEES created successfully....!
Records inserted successfully...!
Table records:
Id: 1, Name: Ramesh, Age: 32, Address: Ahmedabad, Salary: 2000.00
Id: 2, Name: Khilan, Age: 25, Address: Delhi, Salary: 1500.00
Id: 3, Name: kaushik, Age: 23, Address: Kota, Salary: 2000.00
Table records exported successfully into CSV file....!
Table Customers created successfully...!
Data imported successfully...!
Table records:
Id: 1, Name: Ramesh, Age: 32, Address: Ahmedabad, Salary: 2000.00
Id: 2, Name: Khilan, Age: 25, Address: Delhi, Salary: 1500.00
Id: 3, Name: kaushik, Age: 23, Address: Kota, Salary: 2000.00

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 ''EMPLOYEES''
create_table_query = ''''''
CREATE TABLE EMPLOYEES(
ID INT NOT NULL,
NAME VARCHAR(20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(25) NOT NULL,
SALARY DECIMAL(18, 2),
PRIMARY KEY(ID)
);
''''''
cursorObj.execute(create_table_query)
print("Table ''EMPLOYEES'' is created successfully!")
# Inserting records into ''EMPLOYEES'' table
sql = "INSERT INTO EMPLOYEES (ID, NAME, AGE, ADDRESS, SALARY) VALUES (%s, %s, %s, %s, %s);"
values = [
    (1, ''Ramesh'', 32, ''Ahmedabad'', 2000.00),
    (2, ''Khilan'', 25, ''Delhi'', 1500.00),
    (3, ''Kaushik'', 23, ''Kota'', 2000.00),
    (4, ''Chaitali'', 25, ''Mumbai'', 6500.00),
    (5, ''Hardik'', 27, ''Bhopal'', 8500.00),
    (6, ''Komal'', 22, ''MP'', 4500.00),
    (7, ''Muffy'', 24, ''Indore'', 10000.00)
]
cursorObj.executemany(sql, values)
print("Data inserted into the ''EMPLOYEES'' table.")
# Export the table data into a CSV file
export_sql = """
SELECT ID, NAME, AGE, ADDRESS, SALARY FROM EMPLOYEES
INTO OUTFILE ''C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/EMPLOYEE_BACKUP.csv''
FIELDS ENCLOSED BY ''"''
TERMINATED BY '',''
ESCAPED BY ''"''
LINES TERMINATED BY ''rn
"""
cursorObj.execute(export_sql)
print("Table data exported successfully!")
# Now let''s create a ''CUSTOMERS'' table
create_another_table = ''''''
CREATE TABLE CUSTOMERS(
ID INT NOT NULL,
NAME VARCHAR(20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(25) NOT NULL,
SALARY DECIMAL(18, 2),
PRIMARY KEY(ID)
);
''''''
cursorObj.execute(create_another_table)
print("Table ''CUSTOMERS'' is created successfully!")
# Now let''s import ''EMPLOYEES'' table data from the CSV file into ''CUSTOMERS'' table
import_sql = """
LOAD DATA INFILE ''C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/EMPLOYEE_BACKUP.csv''
INTO TABLE CUSTOMERS
FIELDS TERMINATED BY '',''
OPTIONALLY ENCLOSED BY ''"''
LINES TERMINATED BY ''rn''
"""
cursorObj.execute(import_sql)
print("CSV file data imported successfully into Customers table....!n")
# Print ''CUSTOMERS'' table records
cursorObj.execute("SELECT * FROM CUSTOMERS")
records = cursorObj.fetchall()
# Printing the records
print("Customers table records after importing CSV file data into it: n")
for record in records:
    print(record)

# Closing the cursor and connection
cursorObj.close()
connection.close()

Table ''EMPLOYEES'' is created successfully!
Data inserted into the ''EMPLOYEES'' table.
Table data exported successfully!
Table ''CUSTOMERS'' is created successfully!
CSV file data imported successfully into Customers table...!

Customers table records after importing CSV file data into it:

(1, ''Ramesh'', 32, ''Ahmedabad'', Decimal(''2000.00''))
(2, ''Khilan'', 25, ''Delhi'', Decimal(''1500.00''))
(3, ''Kaushik'', 23, ''Kota'', Decimal(''2000.00''))
(4, ''Chaitali'', 25, ''Mumbai'', Decimal(''6500.00''))
(5, ''Hardik'', 27, ''Bhopal'', Decimal(''8500.00''))
(6, ''Komal'', 22, ''MP'', Decimal(''4500.00''))
(7, ''Muffy'', 24, ''Indore'', Decimal(''10000.00''))

The MySQL Check Constraint

The MySQL Check Constraint is a condition that can be applied to a column to ensure that the inserted or updated data in that column meets the specified condition. The database rejects the operation if the condition is not met to maintain data integrity.

Check Constraint with a Trigger

A trigger in MySQL is used to automatically execute a set of SQL statements in response to specific events in the database, such as an INSERT, UPDATE, or DELETE operation.

A check constraint with a trigger allows us to perform actions automatically based on data changes.

Example

Assume we have created a table with name CUSTOMERS in the MySQL database using CREATE TABLE statement 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)
);

Following query inserts values into CUSTOMERS table using the INSERT statement −

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 obtained is as shown below −

Now, we will add a check constraint to ensure that the age of customers in the CUSTOMERS table should be greater than or equal to 18. Additionally, we will create a trigger that, when an attempt is made to insert a record with an age less than 18, it will raise an error and prevent the insertion −

-- Creating a Trigger
DELIMITER //
CREATE TRIGGER check_age_trigger
BEFORE INSERT ON CUSTOMERS
FOR EACH ROW
BEGIN
   IF NEW.AGE < 18 THEN
      SIGNAL SQLSTATE ''45000''
      SET MESSAGE_TEXT = ''Age must be 18 or older
   END IF;
END;
//
DELIMITER ;

-- Adding a Check Constraint
ALTER TABLE CUSTOMERS
ADD CONSTRAINT check_age_constraint CHECK (AGE >= 18);

Output

We get the output as shown below −

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

Adding Check Constraint on Single Column

We can apply a check constraint on a column by specifying the check constraint after the column name at the time of table creation.

Syntax

Following is the syntax to specify the check constraint on column −

CREATE TABLE table_name (
   column1 datatype(size),
   column datatype(size) constraint constraintName
   CHECK Check(columnName condition value),..., column datatype (size)
);

Example

In this example, we are creating a table named EMPLOYEES and specifying a column-level check constraint on one column −

CREATE TABLE EMPLOYEES(
   EID INT NOT NULL,
   NAME VARCHAR(40),
   AGE INT NOT NULL CHECK(AGE>=20),
   CITY VARCHAR(30),
   C_Phone VARCHAR(12) NOT NULL UNIQUE
);

We can verify if the check constraint is working correctly by inserting a value into the EMPLOYEES table which does not satisfy the condition −

INSERT INTO EMPLOYEES
VALUES (1, ''John'', 19, ''New York'', ''09182829109'');

Output

The output obtained is as follows −

ERROR 3819 (HY000): Check constraint ''employees_chk_1'' is violated.

Adding Check Constraint on Multiple Columns

We can add check constraint on multiple columns of a table by specifying the constraints for each column after the column name.

Example

In the following example, we are creating a table named STUDENTS and specifying a column-level check constraint on multiple columns (AGE and FEE) −

CREATE TABLE STUDENTS(
   SID INT NOT NULL,
   NAME VARCHAR(20),
   AGE INT NOT NULL CHECK(AGE<=24),
   CITY VARCHAR(30),
   FEE NUMERIC NOT NULL CHECK(FEE>=15000)
);

Now, we can insert records, but if we attempt to insert a record that violates these constraints, the database will reject it.

Here, we are inserting a valid record −

INSERT INTO STUDENTS
VALUES (001, ''Robert'', 21, ''LA'', 17000);

We can see in the output below that the insertion is successful because the age is within the allowed range, and the fee meets the specified condition −

Query OK, 1 row affected (0.01 sec)

In here, we are attempting to insert a record violating constraints −

INSERT INTO STUDENTS
VALUES (002, ''James'', 25, ''Barcelona'', 10000);

We can see that the insertion fails since the age exceeds 24, violating the constraint.

ERROR 3819 (HY000): Check constraint ''students_chk_1'' is violated.

Adding Check Constraint on an Existing Table

We can also add a check constraint on an existing table in MySQL by using the ALTER statement. We must ensure that the constraint satisfy for the existing records in the table.

Syntax

ALTER TABLE table_name
ADD CONSTRAINT ConstraintName
CHECK(ColumnName condition Value);

Example

In the following example, we are adding a check constraint to the AGE column of the CUSTOMERS table created above −

ALTER TABLE CUSTOMERS
ADD CONSTRAINT Constraint_Age
CHECK (AGE >= 21);

Output

Following is the output of the above code −

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

Dropping Check Constraint

We can remove an existing constraint by using the ALTER statement with the DROP statement.

Syntax

Following is the syntax to remove a constraint from the table −

ALTER TABLE table_name
DROP CONSTRAINT constraint_set;

Example

Following example removes an existing constraint from the AGE column in the CUSTOMERS table created above −

ALTER TABLE CUSTOMERS
DROP CONSTRAINT Constraint_Age;

Output

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

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

Check-Constraints Using a Client Program

We can also perform check-constraints using the client program.

Syntax

$sql = "CREATE TABLE EMPLOYEES(EID INT NOT NULL,NAME VARCHAR(40),AGE INT NOT NULL CHECK(AGE>=20),CITY VARCHAR(30),C_Phone VARCHAR(12) NOT NULL UNIQUE)"
$mysqli->query($sql);

sql = "CREATE TABLE EMPLOYEES(EID INT NOT NULL,NAME VARCHAR(40),AGE INT NOT NULL CHECK(AGE>=20),CITY VARCHAR(30),C_Phone VARCHAR(12) NOT NULL UNIQUE)";
con.query(sql);

String sql = "CREATE TABLE EMPLOYEES( EID INT NOT NULL, NAME VARCHAR(40), AGE INT NOT NULL CHECK(AGE>=20), CITY VARCHAR(30), C_Phone VARCHAR(12) NOT NULL UNIQUE)";
statement.execute(sql);

create_table_query = ''CREATE TABLE EMPLOYEES(EID INT NOT NULL, NAME VARCHAR(40), AGE INT NOT NULL CHECK(AGE>=20), CITY VARCHAR(30), C_Phone VARCHAR(12) NOT NULL UNIQUE);
cursorObj.execute(create_table_query)

Example

Following are the programs −

$dbhost = ''localhost
$dbuser = ''root
$dbpass = ''password
$db = ''TUTORIALS
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $db);
if ($mysqli->connect_errno) {
    printf("Connect failed: %s
", $mysqli->connect_error);
    exit();
}
//printf(''Connected successfully.
'');
$sql = "CREATE TABLE EMPLOYEES(EID INT NOT NULL,NAME VARCHAR(40),AGE INT NOT NULL CHECK(AGE>=20),CITY VARCHAR(30),C_Phone VARCHAR(12) NOT NULL UNIQUE)";
if($mysqli->query($sql)){
    printf("Table created successfully...!n");
}
//let''s insert some records... whose age is greater than 20
$sql = "INSERT INTO EMPLOYEES VALUES(1, ''Jay'', 30, ''Hyderabad'', ''223233'')";
if($mysqli->query($sql)){
    printf("First record(age>20) inserted successfully...!n");
}
$sql = "INSERT INTO EMPLOYEES VALUES(2, ''John'', 35, ''Lucknow'', ''213032'')";
if($mysqli->query($sql)){
    printf("Second record(age>20) inserted successfully...!n");
}
//table record before inserting employee record whose age is less than 20;
$sql = "SELECT * FROM EMPLOYEES";
printf("Table records(before inserting emp record agequery($sql)){
    while($row = mysqli_fetch_array($result)){
        printf("EId: %d, NAME: %s, AGE: %d, CITY %s, C_Phone %d",
        $row[''EID''],
        $row[''NAME''],
        $row[''AGE''],
        $row[''CITY''],
        $row[''C_Phone'']);
        printf("n");
    }
}
//let''s insert some records... whose age is less than 20
$sql = "INSERT INTO EMPLOYEES VALUES(3, ''Vinnet'', 18, ''Hyderabad'', ''228151'')";
if($mysqli->query($sql)){
    printf("Third record(agequery($sql)){
    while($row = mysqli_fetch_array($result)){
        printf("EId: %d, NAME: %s, AGE: %d, CITY %s, C_Phone %d",
        $row[''EID''],
        $row[''NAME''],
        $row[''AGE''],
        $row[''CITY''],
        $row[''C_Phone'']);
        printf("n");
    }
}
if($mysqli->error){
    printf("Error message: ", $mysqli->error);
}
$mysqli->close();

Table created successfully...!
First record(age>20) inserted successfully...!
Second record(age>20) inserted successfully...!
Table records(before inserting emp record age

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

 //Connecting to MySQL
 con.connect(function(err) {
 if (err) throw err;
//   console.log("Connected successfully...!");
//   console.log("--------------------------");
 sql = "USE TUTORIALS";
 con.query(sql);
 //create table with check constraints
 sql = "CREATE TABLE EMPLOYEES(EID INT NOT NULL,NAME VARCHAR(40),AGE INT NOT NULL CHECK(AGE>=20),CITY VARCHAR(30),C_Phone VARCHAR(12) NOT NULL UNIQUE)";
 con.query(sql, function(err, result){
    if (err) throw err;
    console.log("Table created successfully....!");
    });
//now let''s insert some records(age greater than 20)
sql = "INSERT INTO EMPLOYEES VALUES(1, ''Jay'', 30, ''Hyderabad'', ''223233'')";
con.query(sql, function(err, result){
    if (err) throw err;
    console.log("First record inserted successfully...!");
    });
sql = "INSERT INTO EMPLOYEES VALUES(2, ''John'', 35, ''Lucknow'', ''213032'')";
con.query(sql, function(err, result){
    if (err) throw err;
    console.log("Second record inserted successfully...!");
    });
sql = "INSERT INTO EMPLOYEES VALUES(3, ''Vinnet'', 18, ''Hyderabad'', ''228151'')";
    con.query(sql, function(err, result){
        if (err) throw err;
        console.log(result);
        });
sql = "SELECT * FROM EMPLOYEES";
con.query(sql, function(err, result){
    console.log("Table records(with ID auto_increment sequence).");
    if (err) throw err;
    console.log(result);
    });
});

Table created successfully....!
First record inserted successfully...!
Second record inserted successfully...!
D:test1checkcons.js:34
        if (err) throw err;                 ^
Error: Check constraint ''employees_chk_1'' is violated.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class CheckConstraints {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/TUTORIALS";
        String user = "root";
        String password = "password";
        ResultSet rs;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection(url, user, password);
            Statement st = con.createStatement();
            //System.out.println("Database connected successfully...!");
            //create table
            String sql = "CREATE TABLE EMPLOYEES( EID INT NOT NULL, NAME VARCHAR(40), AGE INT NOT NULL CHECK(AGE>=20), CITY VARCHAR(30), C_Phone VARCHAR(12) NOT NULL UNIQUE)";
            st.execute(sql);
            System.out.println("Employees table created successfully...!");
            //let''s insert some records
            String sql1 = "INSERT INTO EMPLOYEES VALUES (1, ''John'', 19, ''New York'', ''09182829109'')";
            st.execute(sql1);
            System.out.println("Record inserted successfully....!");
            //lets print table records
            String sql2 = "SELECT * FROM EMPLOYEES";
            rs = st.executeQuery(sql2);
            while(rs.next()) {
                String id = rs.getString("id");
                String name = rs.getString("name");
                String age = rs.getString("age");
                String city = rs.getString("city");
                String c_phone = rs.getString("C_Phone");
                System.out.println("Id: " + id + ", Name: " + name + ", Age: " + age + ", City: " + city + ", C_phone: " + c_phone);
            }
        }catch(Exception e) {
            e.printStackTrace();
        }
    }
}

Employees table created successfully...!
java.sql.SQLException: Check constraint ''employees_chk_1'' is violated.

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
create_table_query = ''''''
CREATE TABLE EMPLOYEES(
    EID INT NOT NULL,
    NAME VARCHAR(40),
    AGE INT NOT NULL CHECK(AGE>=20),
    CITY VARCHAR(30),
    C_Phone VARCHAR(12) NOT NULL UNIQUE
)
''''''
cursorObj.execute(create_table_query)
print("Table ''EMPLOYEES'' is created successfully!")
# Describing the EMPLOYEES table
describe = "DESC EMPLOYEES"
cursorObj.execute(describe)
print("Table Description:")
for column_info in cursorObj.fetchall():
    print(column_info)
# Inserting the first record
try:
    sql = "INSERT INTO EMPLOYEES VALUES(1, ''Jay'', 30, ''Hyderabad'', ''223233'')"
    cursorObj.execute(sql)
    connection.commit()
    print("First record inserted successfully!")
except mysql.connector.Error as err:
    connection.rollback()
    print(f"Error: {err}")

# Inserting the second record
try:
    sql = "INSERT INTO EMPLOYEES VALUES(2, ''John'', 35, ''Lucknow'', ''213032'')"
    cursorObj.execute(sql)
    connection.commit()
    print("Second record inserted successfully!")
except mysql.connector.Error as err:
    connection.rollback()
    print(f"Error: {err}")

# Inserting the third record with age less than 20 (this will raise an error)
try:
    sql = "INSERT INTO EMPLOYEES VALUES(3, ''Vinnet'', 18, ''Hyderabad'', ''228151'')"
    cursorObj.execute(sql)
    connection.commit()
    print("Third record inserted successfully!")
except mysql.connector.Error as err:
    connection.rollback()
    print(f"Error: {err}")
# Retrieving records
cursorObj.execute("SELECT * FROM person_tbl")
records = cursorObj.fetchall()
# Printing the records
print("Table records.")
for record in records:
    print(record)
# Closing the cursor and connection
cursorObj.close()
connection.close()

Table ''EMPLOYEES'' is created successfully!
Table Description:
(''EID'', b''int'', ''NO'', '''', None, '''')
(''NAME'', b''varchar(40)'', ''YES'', '''', None, '''')
(''AGE'', b''int'', ''NO'', '''', None, '''')
(''CITY'', b''varchar(30)'', ''YES'', '''', None, '''')
(''C_Phone'', b''varchar(12)'', ''NO'', ''PRI'', None, '''')
First record inserted successfully!
Second record inserted successfully!
Error: 3819 (HY000): Check constraint ''employees_chk_1'' is violated.
Table records.
(''Thomas'', ''Jay'', None)
(''Smith'', ''John'', None)

The MySQL Storage Engines

As we already know, a MySQL database is used to store data in the form of rows and columns. The MySQL storage engine is a component that is used to handle the SQL operations performed to manage this data. They work with simple tasks like creating a table, renaming it, updating or deleting it; which is necessary to increase the database performance.

There are two categories of storage engines used: transactional engines and non-transactional engines. Many common storage engines fall into either type of these categories. In MySQL, however, the default storage engine is InnoDB.

Common Storage Engines

Various common storage engines that are used to work with MySQL are as follows −

InnoDB Storage Engine

• ACID Compliant − InnoDB is the default storage engine in MySQL 5.5 and later versions. It is a transactional database engine, ensuring ACID compliance, which means it supports operations like commit and rollback.
• Crash-Recovery − InnoDB offers crash-recovery capabilities to protect user data.
• Row-Level Locking − It supports row-level locking, which enhances multi-user concurrency and performance.
• Referential Integrity − It also enforces FOREIGN KEY referential-integrity constraints.

ISAM Storage Engine

• Deprecated − ISAM, which stands for Indexed Sequential Access Method, was supported by earlier MySQL versions but has been deprecated and removed from recent versions.
• Limited Size − ISAM tables were limited to a size of 4GB.

MyISAM Storage Engine

• Portability − MyISAM is designed for portability, addressing ISAM”s non-portable nature.
• Performance − It offers faster performance compared to ISAM and was the default storage engine before MySQL 5.x.
• Memory Efficiency − MyISAM tables have a small memory footprint, making them suitable for read-only or read-mostly workloads.

MERGE Storage Engine

• Logical Combination − MERGE table enables a MySQL developer to logically combine multiple identical MyISAM tables and reference them as one object.
• Limited Operations − Only INSERT, SELECT, DELETE, and UPDATE operations are allowed on MERGE tables. If DROP query is used, only the storage engine specification gets reset while the table remains unchanged.

MEMORY Storage Engine

• In-Memory Storage − MEMORY tables store data entirely in RAM, optimizing access speed for quick lookups.
• Hash Indexes − It uses hash indexes for faster data retrieval.
• Decreasing Use − Its use cases are decreasing; other engines, like InnoDB”s buffer pool memory area provide better memory management.

CSV Storage Engine

• CSV Format − CSV tables are text files with comma-separated values, useful for data exchange with scripts and applications.
• No Indexing − They are not indexed, and generally used during data import or export alongside InnoDB tables.

NDBCLUSTER Storage Engine

• Clustering − NDBCLUSTER, also known as NDB, is a clustered database engine suitable for applications that require the highest possible degree of uptime and availability.

ARCHIVE Storage Engine

• Historical Data − ARCHIVE tables are ideal for storing and retrieving large amounts of historical, archived, or secure data. The ARCHIVE storage engines support supports non-indexed tables

BLACKHOLE Storage Engine

• Data Discard − BLACKHOLE tables accept data but do not store it, always returning an empty set.
• Usage − Used in replication configurations, where DML statements are sent to replica servers, but the source server does not keep its own copy of the data.

FEDERATED Storage Engine

• Distributed Databases − FEDERATED allows linking separate MySQL servers to create a logical database from multiple physical servers, useful in distributed environments.

EXAMPLE Storage Engine

• Development Tool − EXAMPLE is a tool in the MySQL source code that serves as an example for developers to start writing new storage engines. You can create tables with this engine, but it doesn”t store or retrieve data.

Even though there are so many storage engines that can be used with databases, there is no such thing called a perfect storage engine. In some situations, one storage engine could be a better fit to use whereas in other situations, other engines perform better. Therefore, one must carefully choose what Storage engine to use while working in certain environments.

To choose an engine, you can use the SHOW ENGINES statement.

SHOW ENGINES Statement

The SHOW ENGINES statement in MySQL will list out all the storage engines. It can be taken into consideration while choosing an engine that are supported by the database and are easy to work with.

Syntax

Following is the syntax of the SHOW ENGINES statement −

SHOW ENGINESG

where, the ”G” delimiter is used to vertically align the result-set obtained from executing this statement.

Example

Let us observe the result-set obtained by executing the SHOW ENGINES statement in a MySQL database using the following query −

SHOW ENGINESG

Output

Following is the result-set obtained. Here, you can check which storage engines are supported by the MySQL database and where they can be best used −

*************************** 1. row ************************
      Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 2. row ************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 3. row ************************
      Engine: CSV
     Support: YES
     Comment: CSV storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 4. row ************************
      Engine: FEDERATED
     Support: NO
     Comment: Federated MySQL storage engine
Transactions: NULL
          XA: NULL
  Savepoints: NULL
*************************** 5. row ************************
      Engine: PERFORMANCE_SCHEMA
     Support: YES
     Comment: Performance Schema
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 6. row ************************
      Engine: MyISAM
     Support: YES
     Comment: MyISAM storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 7. row ************************
      Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
*************************** 8. row ************************
      Engine: ndbinfo
     Support: NO
     Comment: MySQL Cluster system information storage engine
Transactions: NULL
          XA: NULL
  Savepoints: NULL
*************************** 9. row ************************
      Engine: BLACKHOLE
     Support: YES
     Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 10. row ************************
      Engine: ARCHIVE
     Support: YES
     Comment: Archive storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 11. row ************************
      Engine: ndbcluster
     Support: NO
     Comment: Clustered, fault-tolerant tables
Transactions: NULL
          XA: NULL
  Savepoints: NULL
11 rows in set (0.00 sec)

Setting a Storage Engine

Once a storage engine is chosen to be used on a table, you might want to set it while creating the database table. This is done by specifying the type of engine you want to use by adding its name in the CREATE TABLE statement.

If you do not specify the engine type, the default engine (InnoDB for MySQL) will be used automatically.

Syntax

Following is the syntax to set a storage engine in CREATE TABLE statement −

CREATE TABLE table_name (
   column_name1 datatype,
   column_name2 datatype,
   .
   .
   .
) ENGINE = engine_name;

Example

In this example, let us create a new table ”TEST” on MyISAM storage engine using the following query −

CREATE TABLE TEST (
   ROLL INT,
   NAME VARCHAR(25),
   MARKS DECIMAL(20, 2)
) ENGINE = MyISAM;

The result obtained is as shown below −

Query OK, 0 rows affected (0.01 sec)

But if we create a table on an engine that is not supported by MySQL, say FEDERATED, an error is raised −

CREATE TABLE TEST (
   ROLL INT,
   NAME VARCHAR(25),
   MARKS DECIMAL(20, 2)
) ENGINE = FEDERATED;

We get the following error −

ERROR 1286 (42000): Unknown storage engine ''FEDERATED''

Changing Default Storage Engine

MySQL also has provisions to change the default storage engine option in three ways −

• Using ”–default-storage-engine=name” server startup option.

• Setting ”default-storage-engine” option in ”my.cnf” configuration file.

• Using SET statement

Syntax

Let us see the syntax of using SET statement to change the default storage engine in a database −

SET default_storage_engine = engine_name;

Note − The storage engine for temporary tables, which were created with the CREATE TEMPORARY TABLE statement, can be set separately by setting the ”default_tmp_storage_engine”, either at startup or at runtime.

Example

In this example, we are changing the default storage engine to MyISAM using SET statement given as follows −

SET default_storage_engine = MyISAM;

The result obtained is as follows −

Query OK, 0 rows affected (0.00 sec)

Now, let us list the storage engines using SHOW ENGINES statement below. The support column for MyISAM storage engine is changed to default −

SHOW ENGINESG

Output

Following is the result-set produced. Here, note that we are not displaying the entire result-set and only the MyISAM row for simpler understandability. The actual result-set has 11 total rows −

*************************** 6. row ************************
      Engine: MyISAM
     Support: DEFAULT
     Comment: MyISAM storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
11 rows in set (0.00 sec)

Altering Storage Engine

You can also alter the existing storage engine of a table to another storage engine using the ALTER TABLE command in MySQL. However, the storage engine must be changed to one that is supported by MySQL only.

Syntax

Following is the basic syntax to change the existing storage engine to another −

ALTER TABLE table_name ENGINE = engine_name;

Example

Consider the previously created table TEST on MyISAM database engine. In this example, using the following ALTER TABLE command, we are changing it to InnoDB engine.

ALTER TABLE TEST ENGINE = InnoDB;

Output

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

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

Verification

To verify whether the storage engine is changed or not, use the following query −

SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES
WHERE TABLE_SCHEMA = ''testDB

The table produced is as shown below −

Storage Engines Using a Client Program

We can also perform storage Engines using the client program.

Syntax

$sql = "SHOW ENGINES";
$mysqli->query($sql);

sql = "SHOW ENGINES";
con.query(sql);

String sql = "SHOW ENGINES";
statement.executeQuery(sql);

sql = "SHOW ENGINES"
cursorObj.execute(sql)

Example

Following are the programs −

$dbhost = ''localhost
$dbuser = ''root
$dbpass = ''password
$db = ''TUTORIALS
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $db);
if ($mysqli->connect_errno) {
    printf("Connect failed: %s
", $mysqli->connect_error);
    exit();
}
//printf(''Connected successfully.
'');
$sql = "SHOW ENGINES";
if($mysqli->query($sql)){
    printf("Show query executed successfully....!n");
}
printf("Storage engines: n");
if($result = $mysqli->query($sql)){
   print_r($result);
}
if($mysqli->error){
    printf("Error message: ", $mysqli->error);
}
$mysqli->close();

Show query executed successfully....!
Storage engines:
mysqli_result Object
(
    [current_field] => 0
    [field_count] => 6
    [lengths] =>
    [num_rows] => 11
    [type] => 0
)

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

 //Connecting to MySQL
 con.connect(function(err) {
 if (err) throw err;
//   console.log("Connected successfully...!");
//   console.log("--------------------------");
 sql = "USE TUTORIALS";
 con.query(sql);
 //create table
 sql = "SHOW ENGINES";
 con.query(sql, function(err, result){
    console.log("Show query executed successfully....!");
    console.log("Storage engines: ")
    if (err) throw err;
    console.log(result);
    });
});

Show query executed successfully....!
Storage engines:
[
  {
    Engine: ''MEMORY'',
    Support: ''YES'',
    Comment: ''Hash based, stored in memory, useful for temporary tables'',
    Transactions: ''NO'',
    XA: ''NO'',
    Savepoints: ''NO''
  },
  {
    Engine: ''MRG_MYISAM'',
    Support: ''YES'',
    Comment: ''Collection of identical MyISAM tables'',
    Transactions: ''NO'',
    XA: ''NO'',
    Savepoints: ''NO''
  },
  {
    Engine: ''CSV'',
    Support: ''YES'',
    Comment: ''CSV storage engine'',
    Transactions: ''NO'',
    XA: ''NO'',
    Savepoints: ''NO''
  },
  {
    Engine: ''FEDERATED'',
    Support: ''NO'',
    Comment: ''Federated MySQL storage engine'',
    Transactions: null,
    XA: null,
    Savepoints: null
  }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class StorageEngine {
  public static void main(String[] args) {
    String url = "jdbc:mysql://localhost:3306/TUTORIALS";
    String user = "root";
    String password = "password";
    ResultSet rs;
    try {
      Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection(url, user, password);
            Statement st = con.createStatement();
            //System.out.println("Database connected successfully...!");
            //create table
            String sql = "SHOW ENGINES";
            rs = st.executeQuery(sql);
            System.out.println("Storage engines: ");
            while(rs.next()) {
              String engines = rs.getNString(1);
              System.out.println(engines);
            }
    }catch(Exception e) {
      e.printStackTrace();
    }
  }
}

Storage engines:
MEMORY
MRG_MYISAM
CSV
FEDERATED
PERFORMANCE_SCHEMA
MyISAM
InnoDB
ndbinfo
BLACKHOLE
ARCHIVE
ndbcluster

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
cursorObj = connection.cursor()
# Query to get information about storage engines
storage_engines_query = "SHOW ENGINES"
cursorObj.execute(storage_engines_query)
# Fetching all records about storage engines
all_storage_engines = cursorObj.fetchall()
for row in all_storage_engines:
    print(row)
# Closing the cursor and connection
cursorObj.close()
connection.close()

(''MEMORY'', ''YES'', ''Hash based, stored in memory, useful for temporary tables'', ''NO'', ''NO'', ''NO'')
(''MRG_MYISAM'', ''YES'', ''Collection of identical MyISAM tables'', ''NO'', ''NO'', ''NO'')
(''CSV'', ''YES'', ''CSV storage engine'', ''NO'', ''NO'', ''NO'')
(''FEDERATED'', ''NO'', ''Federated MySQL storage engine'', None, None, None)
(''PERFORMANCE_SCHEMA'', ''YES'', ''Performance Schema'', ''NO'', ''NO'', ''NO'')
(''MyISAM'', ''YES'', ''MyISAM storage engine'', ''NO'', ''NO'', ''NO'')
(''InnoDB'', ''DEFAULT'', ''Supports transactions, row-level locking, and foreign keys'', ''YES'', ''YES'', ''YES'')
(''BLACKHOLE'', ''YES'', ''/dev/null storage engine (anything you write to it disappears)'', ''NO'', ''NO'', ''NO'')
(''ARCHIVE'', ''YES'', ''Archive storage engine'', ''NO'', ''NO'', ''NO'')

The MySQL Comments

The MySQL Comment is a textual explanation added to a piece of code to provide additional information about the code. Comments are not meant to be executed as part of the code. It serve as notes for readers, including developers, to understand the purpose of the code, functionality, or any other relevant details.

There are two types of comments in MySQL: Single-line comments and Multi-line comments

The MySQL Single Line Comments

Single-line comments are used for brief explanations on a single line. To create a single-line comment in MySQL, use two hyphens (–) followed by your comment text.

Example

In the following query, we are using a single line comment to write a text.

SELECT * FROM customers; -- This is a comment

The MySQL Multi-line Comments

Multi-line comments in MySQL are used for longer explanations or to comment out multiple lines of code. These comments start with /* and end with */. Everything between them is considered a comment.

Example

The following example uses multi-line comment as an explanation of the query −

/*
This is a multi-line comment.
You can use it to explain complex queries or comment out multiple lines of code.

SELECT *
FROM products
WHERE price > 50;
*/

Where to Place Comments

You can place comments almost anywhere in your SQL code. Common places include −

• Before or after a SQL statement.

• Within a SQL statement to explain a specific part of it.

• At the beginning of a script or stored procedure to describe its purpose.

-- This is a comment before a query
SELECT * FROM orders;

SELECT /* This is an inline comment */ customer_name
FROM customers;

/* This is a comment block at the beginning of a script */
DELIMITER //
CREATE PROCEDURE CalculateDiscount(IN product_id INT)
BEGIN
    -- Calculate discount logic here
END //
DELIMITER ;

Comments Using a Client Program

We can also comment any value using the client program.

Syntax

single line comment
--
multiline comment
/**/

(using Query)

$sql = "SELECT ID /*NAME, ADDRESS*/ FROM CUSTOMERS WHERE ADDRESS = ''Mumbai''";
$mysqli->query($sql);

single line comment
--
multiline comment
/**/

(using Query)

sql = "SELECT ID /*NAME, ADDRESS*/ FROM CUSTOMERS WHERE ADDRESS = ''Mumbai''";
con.query(sql);

single line comment
--
multiline comment
/**/

(using Query)

String sql = "SELECT ID /*NAME, ADDRESS*/ FROM CUSTOMERS WHERE ADDRESS = ''Mumbai''";
statement.executeQuery(sql);

single line comment
--
multiline comment
/**/

(using Query)

comments_query = "SELECT ID /*NAME, ADDRESS*/ FROM CUSTOMERS WHERE ADDRESS = ''Mumbai''"
cursorObj.execute(comments_query)

Example

Following are the programs −

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

Select query executed successfully...!
Table records:
Id: 4

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

 //Connecting to MySQL
 con.connect(function(err) {
 if (err) throw err;
//   console.log("Connected successfully...!");
//   console.log("--------------------------");
 sql = "USE TUTORIALS";
 con.query(sql);
 //create table
 sql = "SELECT ID /*NAME, ADDRESS*/ FROM CUSTOMERS WHERE ADDRESS = ''Mumbai''";
 con.query(sql, function(err, result){
    console.log("Select query executed successfully(where we commented the name and address column)...!");
    console.log("Table records: ")
    if (err) throw err;
    console.log(result);
    });
});

Select query executed successfully(where we commented the name and address column)...!
Table records:
[ { ID: 4 } ]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class Comments {
  public static void main(String[] args) {
    String url = "jdbc:mysql://localhost:3306/TUTORIALS";
    String user = "root";
    String password = "password";
    ResultSet rs;
    try {
      Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection(url, user, password);
            Statement st = con.createStatement();
            //System.out.println("Database connected successfully...!");
            //create table
            String sql = "SELECT ID /*NAME, ADDRESS*/ FROM CUSTOMERS WHERE ADDRESS = ''Mumbai''";
            rs = st.executeQuery(sql);
            System.out.println("Table records: ");
            while(rs.next()) {
              String id = rs.getString("id");
              System.out.println("Id: " + id);
            }
    }catch(Exception e) {
      e.printStackTrace();
    }
  }
}

Table records:
Id: 4

import mysql.connector
# Establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
cursorObj = connection.cursor()
# Query with comments
comments_query = """SELECT ID /*NAME, ADDRESS*/ FROM CUSTOMERS WHERE ADDRESS = ''Mumbai''"""
cursorObj.execute(comments_query)
# Fetching all the rows that meet the criteria
filtered_rows = cursorObj.fetchall()
# Printing the result
print("IDs of customers from Mumbai:")
for row in filtered_rows:
    print(row[0])
cursorObj.close()
connection.close()

IDs of customers from Mumbai:
4