MySQL Repair Table Statement

There can be scenarios where tables in databases can become corrupted due to various reasons such as hardware failures, software bugs, or unexpected server crashes. When this situation happens, we cannot be able to access or manipulate the data in those tables because of data inconsistencies or errors.

In such situations, to repair those currupted tables, we use the MySQL REPAIR TABLE statement. This statement works for only certain engines such as MyISAM, etc.

Syntax

Following is the syntax of MySQL REPAIR TABLE Statement −

REPAIR [NO_WRITE_TO_BINLOG | LOCAL]
   TABLE tbl_name [, tbl_name] ...
   [QUICK] [EXTENDED] [USE_FRM]

Example

Let us start by creating a table named CUSTOMERS using the following query −

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

Here, we are inserting 7 records into the above created table using the below INSERT statement −

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

Assume the above created table is corrupted and we are using the REPAIR TABLE statement to repair it.

REPAIR TABLE CUSTOMERS;

The above query displays an error as: “The storage engine for the table doesn”t support repair” because the REPAIR TABLE statement won”t work with the default InnoDB engine.

To repair the table, We need to change the table”s engine to MyISAM because it supports the REPAIR TABLE statement.

ALTER TABLE CUSTOMERS ENGINE = MyISAM;

Now, to repair the CUSTOMERS table, execute the following query −

REPAIR TABLE CUSTOMERS;

Output

We can see in the output below, it says OK which indicates that the table CUSTOMERS is in good condition, and there are no issues or corruption.

Repairing multiple tables

In MySQL, we can also repair multiple tables and get the results using the REPAIR TABLE Statement. To do this, we just need to list the names of the tables we want to repair, separating them with commas.

Example

Let us create three different tables with the names Test1, Test2, and Test3 using the following CREATE TABLE statements −

CREATE TABLE Test1(ID INT, Name VARCHAR(255));
CREATE TABLE Test2(ID INT, Name VARCHAR(255));
CREATE TABLE Test3(ID INT, Name VARCHAR(255));

Assume the above three tables are corrupted. Change the engine of these tables to MyISAM to repair them with REPAIR TABLE statement −

ALTER TABLE Test1 ENGINE = MyISAM;
ALTER TABLE Test2 ENGINE = MyISAM;
ALTER TABLE Test3 ENGINE = MyISAM;

Now, to repair these tables, execute the following query −

REPAIR TABLE Test1, Test2, Test3;

As we can see in the output below, all three tables are in good condition, and there are no issues or corruption.

Repair Table Options

We have various optional clauses to use with REPAIR TABLE such as QUICK, EXTENDED, and, USE_FRM clause. Let us discuss them one by one with suitable examples.

QUICK Clause

The QUICK clause is the is the default and it is most commonly used with REPAIR TABLE. If you specify the QUICK clause, MySQL will repair the table without re-creating it. −

In the following example, we are using the QUICK clause with the REPAIR TABLE statement to repair the CUSTOMERS table.

REPAIR TABLE CUSTOMERS QUICK;

Executing the query above will produce the following output −

EXTENDED Clause

If we specify the EXTENDED clause, MySQL not only repairs the table but also rebuilds the index and optimizes the table structure.

Note: The EXTENDED clause is a more time-consuming compared to QUICK clause.

In the following example, we are using the EXTENDED clause with the REPAIR TABLE statement to repair the CUSTOMERS table.

REPAIR TABLE CUSTOMERS EXTENDED;

Executing the query above will produce the following output −

USE_FRM clause

We can use the USE_FRM clause, in case the MYI index file is missing. If you provide this clause the .NYI file will be recreated using information from the data dictionary −

Here, we are using the USE_FRM clause with the REPAIR TABLE statement to repair the CUSTOMERS table.

REPAIR TABLE CUSTOMERS USE_FRM;

Executing the query above will produce the following output −

Repairing table Using a Client Program

Besides repairing a table in a MySQL database with a MySQL query, we can also use a client program to perform the REPAIR TABLE operation.

Syntax

Following are the syntaxes to repair a table in various programming languages −

$sql="Repair TABLE table_names";
$mysqli->query($sql);

sql=" REPAIR [NO_WRITE_TO_BINLOG | LOCAL] TABLE tbl_name [, tbl_name] ...
   [QUICK] [EXTENDED] [USE_FRM]";
con.query(sql);

String sql="Repair TABLE table_names";
statement.executeUpdate(sql);

sql="REPAIR TABLE table_name";
cursorObj.execute(sql);

Example

Following are the programs −

$dbhost = ''localhost
$dbuser = ''root
$dbpass = ''password
$dbname = ''TUTORIALS
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);

if ($mysqli->connect_errno) {
    printf("Connect failed: %s
", $mysqli->connect_error);
    exit();
}
// printf(''Connected successfully.
'');

$sql = " REPAIR TABLE SalesSummary ";
if ($mysqli->query($sql)) {
    printf(" Table repair successfully.
");
}
if ($mysqli->errno) {
    printf("table could not be repaired .
", $mysqli->error);
}
$mysqli->close();

Table repair successfully.

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

  sql = "Create Database TUTORIALS"
  con.query(sql);

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

  sql = "CREATE TABLE sales(ID INT, ProductName VARCHAR(255), CustomerName VARCHAR(255), DispatchDate date, DeliveryTime time, Price INT, Location VARCHAR(255));"
  con.query(sql);

  sql = "insert into sales values(1, ''Key-Board'', ''Raja'', DATE(''2019-09-01''), TIME(''11:00:00''), 7000, ''Hyderabad''),(2, ''Earphones'', ''Roja'', DATE(''2019-05-01''), TIME(''11:00:00''), 2000, ''Vishakhapatnam''),(3, ''Mouse'', ''Puja'', DATE(''2019-03-01''), TIME(''10:59:59''), 3000, ''Vijayawada''),(4, ''Mobile'', ''Vanaja'', DATE(''2019-03-01''), TIME(''10:10:52''), 9000, ''Chennai''),(5, ''Headset'', ''Jalaja'', DATE(''2019-04-06''), TIME(''11:08:59''), 6000, ''Goa'');"
  con.query(sql);

  sql = "ALTER TABLE Sales ENGINE = MyISAM;"
  con.query(sql);

  sql = "REPAIR TABLE Sales;"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log(result);
  });
});

Connected!
--------------------------
[
  {
    Table: ''tutorials.sales'',
    Op: ''repair'',
    Msg_type: ''status'',
    Msg_text: ''OK''
  }
]

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

public class RepairTable{
        public static void main(String[] args){
            String url = "jdbc:mysql://localhost:3306/TUTORIALS";
            String username = "root";
            String password = "password";
            try{
                Class.forName("com.mysql.cj.jdbc.Driver");
                Connection connection = DriverManager.getConnection(url, username, password);
                Statement statement = connection.createStatement();
                System.out.println("Connected successfully...!");

                //Repair tables...!
                String sql = "REPAIR TABLE customers";
                statement.executeUpdate(sql);
                System.out.println("Table repaired successfully...!");

                connection.close();
            }
            catch(Exception e){
                System.out.println(e);
            }
        }
}

Connected successfully...!
Table repaired successfully...!

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
table_name = ''tutorials_tbl_temp''
#Creating a cursor object
cursorObj = connection.cursor()
repair_table_query = f"REPAIR TABLE {table_name}"
cursorObj.execute(repair_table_query)
print(f"Table ''{table_name}'' is repaired successfully.")
# Fetch and consume any remaining results from the cursor
# ensuring that there are no unread results before closing the cursor.
for _ in cursorObj:
    pass
cursorObj.close()
connection.close()

Table ''tutorials_tbl_temp'' is repaired successfully.

What are Temporary Tables?

The Temporary Tables are the tables that are created in a database to store data temporarily. These tables will be automatically deleted once the current client session is terminated or ends. In addition to that, these tables can be deleted explicitly if the users decide to drop them manually. You can perform various SQL operations on temporary tables, just like you would with permanent tables, including CREATE, UPDATE, DELETE, INSERT, JOIN, etc.

Temporary tables were introduced in MySQL version 3.23. If you”re using an older version of MySQL that”s older than 3.23, you won”t be able to use temporary tables, instead you can use the Heap Tables.

As stated earlier, temporary tables will only last as long as the session is alive. If you run the code in a PHP script, the temporary table will be destroyed automatically when the script finishes executing. If you are connected to the MySQL database server through the MySQL client program, then the temporary table will exist until you close the client or manually destroy the table.

Creating Temporary Tables in MySQL

Creating a temporary table in MySQL is very similar to creating a regular database table. But, instead of using CREATE TABLE, we use CREATE TEMPORARY TABLE statement.

Syntax

Following is the syntax to create a temporary table in MySQL −

CREATE TEMPORARY TABLE table_name(
   column1 datatype,
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
   PRIMARY KEY( one or more columns )
);

Example

First of all, let us create a temporary table named CUSTOMERS using the below query −

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

Similar to normal tables, we can insert records into a temporary table using the INSERT statement. Here, we are inserting three records into the above created temporary table −

INSERT INTO CUSTOMERS VALUES
   (1, ''Ramesh'', 32, ''Ahmedabad'', 2000.00 ),
   (2, ''Khilan'', 25, ''Delhi'', 1500.00 ),
   (3, ''kaushik'', 23, ''Kota'', 2000.00 );

Execute the following query to display all the records of the temporary table CUSTOMERS.

SELECT * FROM CUSTOMERS;

Following are the records of CUSTOMERS table −

When we issue a SHOW TABLES command, our temporary table won”t be displayed in the list of tables. To verify if the temporary table exists, we need to use the SELECT statement to retrieve its data. Temporary tables will be deleted when we ends our session, so if we log out of MySQL and then try to issue the SELECT command, we won”t find the temporary table in the database.

Dropping Temporary Tables in MySQL

Though MySQL automatically removes temporary tables when your database connection ends, we can still delete them ourselves by using the DROP TEMPORARY TABLE command if we want to.

Syntax

Following is the syntax for dropping a temporary table in MySQL −

DROP TEMPORARY TABLE table_name;

Example

In the following query, we are dropping the temporary table CUSTOMERS that was created in the previous example −

DROP TEMPORARY TABLE CUSTOMERS;

Output

Executing the query above will produce the following output −

Query OK, 0 rows affected (0.00 sec)

Verification

Now, let us verify the temporary table CUSTOMERS by retrieving it”s records using the following query −

SELECT * FROM CUSTOMERS;

Since we removed the the temporary table CUSTOMERS, it will generate an error saying the table does not exist.

ERROR 1146: Table ''TUTORIALS.CUSTOMERS'' doesn''t exist

Creating Temporary table Using a Client Program

In addition to create a temporary table in MySQL Database using the MySQL query, we can also perform the “TEMPORARY TABLE” operation on a table using a client program.

Syntax

Following are the syntaxes to create a temporary table into MySQL Database in various programming languages −

$sql="CREATE temporary Table table_name(column_name, column_type, ...)";
$mysqli->query($sql);

sql="CREATE temporary Table table_name(column_name, column_type, ...)";
con.query(sql);

String sql="CREATE temporary Table table_name(column_name, column_type, ...)";
statement.executeQuery(sql);

sql="CREATE temporary Table table_name(column_name, column_type, ...)";
cursorObj.execute(sql);

Example

Following are the programs −

$dbhost = ''localhost
$dbuser = ''root
$dbpass = ''password
$dbname = ''TUTORIALS
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);

if ($mysqli->connect_errno) {
    printf("Connect failed: %s
", $mysqli->connect_error);
    exit();
}
// printf(''Connected successfully.
'');

$sql = "CREATE TEMPORARY TABLE SalesSummary (" . " product_name VARCHAR(50) NOT NULL, " . " total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00, " . " avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00, " . " total_units_sold INT UNSIGNED NOT NULL DEFAULT 0 )";
if ($mysqli->query($sql)) {
    printf("temporary table created successfully.
");
}
if ($mysqli->errno) {
    printf("temporary table could not be created: %s
", $mysqli->error);
}
$mysqli->close();

temporary table created successfully.

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

  //Creating a Database
  sql = "CREATE DATABASE testdb"
  con.query(sql);

  //Selecting a Database
  sql = "USE testdb"
  con.query(sql);

  //Creating table
  sql = "CREATE TEMPORARY TABLE SalesSummary (product_name VARCHAR(50) NOT NULL, total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00, avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00, total_units_sold INT UNSIGNED NOT NULL DEFAULT 0);"
  con.query(sql);

  sql = "INSERT INTO SalesSummary(product_name, total_sales, avg_unit_price, total_units_sold)VALUES(''cucumber'', 100.25, 90, 2);"
  con.query(sql);

  sql = "SELECT * FROM SalesSummary;"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log("**SalesSummary Table:**")
    console.log(result);
  });
});

Connected!
--------------------------
**SalesSummary Table:**
[
  {
    product_name: ''cucumber'',
    total_sales: ''100.25'',
    avg_unit_price: ''90.00'',
    total_units_sold: 2
  }
]

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

public class TemporaryTable {
    public static void main(String[] args){
        String url = "jdbc:mysql://localhost:3306/TUTORIALS";
        String username = "root";
        String password = "password";
        try{
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection connection = DriverManager.getConnection(url, username, password);
            Statement statement = connection.createStatement();
            System.out.println("Connected successfully...!");

            //Create temporary table...!
            String sql = "CREATE TEMPORARY TABLE SalesSummary (product_name VARCHAR(50) NOT NULL, total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00, avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00, total_units_sold INT UNSIGNED NOT NULL DEFAULT 0 )";
            statement.executeUpdate(sql);
            System.out.println("Temporary table created successfully...!");

            ResultSet resultSet = statement.executeQuery("DESCRIBE SalesSummary");
            while(resultSet.next()) {
                System.out.print(resultSet.getNString(1));
                System.out.println();
            }
            connection.close();
        }
        catch(Exception e){
            System.out.println(e);
        }
    }
}

Connected successfully...!
Temporary table created successfully...!
product_name
total_sales
avg_unit_price
total_units_sold

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
table_name = ''tutorials_tbl_temp''
#Creating a cursor object
cursorObj = connection.cursor()
create_table_query = f"CREATE TEMPORARY TABLE {table_name} (NAME VARCHAR(50), ID INT)"
cursorObj.execute(create_table_query)
print(f"Temporary table ''{table_name}'' is created successfully.")
cursorObj.close()
connection.close()

Temporary table ''tutorials_tbl_temp'' is created successfully.

MySQL TRUNCATE TABLE Statement

The MySQL TRUNCATE TABLE statement is used to delete only the data of an existing table, but not the table.

This command helps to TRUNCATE a table completely in one go instead of deleting table records one by one which will be very time consuming and hefty process.

You can delete a table using the DROP TABLE command, but be careful because it completely erases both data and the table”s structure from the database. If you want to store some data again, you would need to re-create this table once again.

Syntax

Following is the basic syntax of the TRUNCATE TABLE statement −

TRUNCATE TABLE table_name

Where, table_name is the name of the table you need to delete all the records from.

Example

First of all, let us create a table with 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, we are inserting 7 records into the above-created table using the following INSERT statement −

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

Using the following query, we are displaying the records of CUSTOMERS table −

SELECT * FROM CUSTOMERS;

Following are the records of CUSTOMERS table −

In the following query, we are using the TRUNCATE TABLE command to remove all the records in the CUSTOMERS table −

TRUNCATE TABLE CUSTOMERS;

Output

The records have been truncated from the CUSTOMERS table without any error.

Query OK, 0 rows affected (0.02 sec)

Verification

To verify whether the records have been truncated, let us retrieve the records using the following query −

SELECT * FROM CUSTOMERS;

As we can see the output below, there are no records present in the CUSTOMERS table. Thus, the records have been truncated.

Empty set (0.00 sec)

TRUNCATE vs DELETE

Following are some major differences between the TRUNCATE and DELETE commands, even though they work similar logically:

TRUNCATE vs DROP

The TRUNCATE and DROP are two different commands. TRUNCATE just deletes the table”s records, whereas DROP command deletes the table entirely from the database.

However, there are still some differences between these commands, which are summarized in the following table −

Truncating Table Using a Client Program

Besides truncating a table in a MySQL database with a MySQL query, we can also use a client program to perform the TRUNCATE TABLE operation.

Syntax

Following are the syntaxes to truncate a table from MySQL Database in various programming languages −

$sql = "TRUNCATE TABLE table_name";
$mysqli->query($sql);

sql = "TRUNCATE TABLE table_name";
con.query(sql);

String sql = "TRUNCATE TABLE table_name";
statement.executeUpdate(sql);

sql = "TRUNCATE TABLE table_name";
cursorObj.execute(sql);

Example

Following are the programs −

$dbhost = ''localhost
$dbuser = ''root
$dbpass = ''password
$dbname = ''TUTORIALS
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);

if ($mysqli->connect_errno) {
    printf("Connect failed: %s
", $mysqli->connect_error);
    exit();
}
// printf(''Connected successfully.
'');

$sql = " TRUNCATE TABLE clone_table ";
if ($mysqli->query($sql)) {
    printf("table truncated successfully.
");
}
if ($mysqli->errno) {
    printf("table could not be truncated: %s
", $mysqli->error);
}
$mysqli->close();

table truncated successfully.

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

  //Creating a Database
  sql = "CREATE DATABASE testdb"
  con.query(sql);

  //Selecting a Database
  sql = "USE testdb"
  con.query(sql);

  //Creating table
  sql = "CREATE TABLE MyPlayers(ID INT,First_Name VARCHAR(255),Last_Name VARCHAR(255),Date_Of_Birth date,Place_Of_Birth VARCHAR(255),Country VARCHAR(255),PRIMARY KEY (ID));"
  con.query(sql);

  sql = "insert into MyPlayers values(1, ''Shikhar'', ''Dhawan'', DATE(''1981-12-05''), ''Delhi'', ''India''),(2, ''Jonathan'', ''Trott'', DATE(''1981-04-22''), ''CapeTown'', ''SouthAfrica''),(3, ''Kumara'', ''Sangakkara'', DATE(''1977-10-27''), ''Matale'', ''Srilanka''),(4, ''Virat'', ''Kohli'', DATE(''1988-11-05''), ''Delhi'', ''India''),(5, ''Rohit'', ''Sharma'', DATE(''1987-04-30''), ''Nagpur'', ''India''),(6, ''Ravindra'', ''Jadeja'', DATE(''1988-12-06''), ''Nagpur'', ''India''),(7, ''James'', ''Anderson'', DATE(''1982-06-30''), ''Burnley'', ''England'');"
  con.query(sql);

  //List of tables
  sql = "select * from MyPlayers;"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log("**MyPlayers Table:**")
    console.log(result);
    console.log("--------------------------");
  });

  //Truncating the records of Myplayers table
  sql = "TRUNCATE TABLE MyPlayers;"
  con.query(sql);

  //fetching the records of table after truncating
  sql = "select * from MyPlayers;"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log("**Myplayers table after truncating:**");
    console.log(result);
  });
});

Connected!
--------------------------
**MyPlayers Table:**
[
  {
    ID: 1,
    First_Name: ''Shikhar'',
    Last_Name: ''Dhawan'',
    Date_Of_Birth: 1981-12-04T18:30:00.000Z,
    Place_Of_Birth: ''Delhi'',
    Country: ''India''
  },
  {
    ID: 2,
    First_Name: ''Jonathan'',
    Last_Name: ''Trott'',
    Date_Of_Birth: 1981-04-21T18:30:00.000Z,
    Place_Of_Birth: ''CapeTown'',
    Country: ''SouthAfrica''
  },
  {
    ID: 3,
    First_Name: ''Kumara'',
    Last_Name: ''Sangakkara'',
    Date_Of_Birth: 1977-10-26T18:30:00.000Z,
    Place_Of_Birth: ''Matale'',
    Country: ''Srilanka''
  },
  {
    ID: 4,
    First_Name: ''Virat'',
    Last_Name: ''Kohli'',
    Date_Of_Birth: 1988-11-04T18:30:00.000Z,
    Place_Of_Birth: ''Delhi'',
    Country: ''India''
  },
  {
    ID: 5,
    First_Name: ''Rohit'',
    Last_Name: ''Sharma'',
    Date_Of_Birth: 1987-04-29T18:30:00.000Z,
    Place_Of_Birth: ''Nagpur'',
    Country: ''India''
  },
  {
    ID: 6,
    First_Name: ''Ravindra'',
    Last_Name: ''Jadeja'',
    Date_Of_Birth: 1988-12-05T18:30:00.000Z,
    Place_Of_Birth: ''Nagpur'',
    Country: ''India''
  },
  {
    ID: 7,
    First_Name: ''James'',
    Last_Name: ''Anderson'',
    Date_Of_Birth: 1982-06-29T18:30:00.000Z,
    Place_Of_Birth: ''Burnley'',
    Country: ''England''
  }
]
--------------------------
**Myplayers table after truncating:**
[]

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

public class TruncateTable {
    public static void main(String[] args){
        String url = "jdbc:mysql://localhost:3306/TUTORIALS";
        String username = "root";
        String password = "password";
        try{
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection connection = DriverManager.getConnection(url, username, password);
            Statement statement = connection.createStatement();
            System.out.println("Connected successfully...!");

            //truncate tables...!
            String sql = "TRUNCATE TABLE clone_tbl";
            statement.executeUpdate(sql);
            System.out.println("Table Truncated successfully...!");

            ResultSet resultSet = statement.executeQuery("SELECT * FROM clone_tbl");
            while (resultSet.next()){
                System.out.println(resultSet.getInt(1)+" "+resultSet.getString(2)+" "+
                        resultSet.getInt(3)+" "+ resultSet.getString(4)+" "+resultSet.getFloat(5));
                System.out.println();
            }
            connection.close();
        }
        catch(Exception e){
            System.out.println(e);
        }
    }
}

Connected successfully...!
Table Truncated successfully...!

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
table_name = ''tutorials_tbl_cloned''
#Creating a cursor object
cursorObj = connection.cursor()
cursorObj.execute(f"TRUNCATE TABLE {table_name}")
print(f"Table ''{table_name}'' is truncated successfully.")
cursorObj.close()
connection.close()

Table ''tutorials_tbl_cloned'' is truncated successfully.

There may be a situation when you need an exact copy of a table with the same columns, attributes, indexes, default values and so forth. Instead of spending time on creating the exact same version of an existing table using the CREATE TABLE statement, you can clone the table without disturbing the original table.

Cloning operation in SQL allows the user to create the exact copy of an existing table along with its definition, that is completely independent from the original table. Thus, if any changes are made to the cloned table, they will not be reflected in the original table. This operation comes in handy during testing processes, where there is a need to perform sample testing using the existing database tables.

There are three types of cloning possible using SQL in MySQL RDBMS; they are listed below −

• Simple Cloning: Creates a new table containing same records of existing table but void of any constraints or indexes etc.

• Shallow Cloning: Creates a new empty table with the same table definition of an existing table.

• Deep Cloning: Creates a new table and copies the table structure and data of an existing table to the new table.

Cloning Tables in MySQL

You can handle this situation by following the steps given below −

• Use SHOW CREATE TABLE to get a CREATE TABLE statement that specifies the source table”s structure, indexes and all.

• Modify the statement to change the table name to that of the clone table and execute the statement. This way, you will have the exact clone table.

• Optionally, if you need the table contents copied as well, issue an INSERT INTO … SELECT statement, too.

Example

Try out the following example to create a clone table for CUSTOMERS.

Step 1 − First of all, create the CUSTOMERS using the CREATE TABLE statement.

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

Insert some records into it using the following INSERT INTO statement.

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

Now, execute the following SHOW CREATE TABLE command to display the source table”s (CUSTOMERS) structure.

SHOW CREATE TABLE CUSTOMERS;

Following is the CUSTOMERS table structure.

Table: CUSTOMERS
Create Table: CREATE TABLE `customers` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `NAME` varchar(20) NOT NULL,
  `AGE` int NOT NULL,
  `ADDRESS` char(25) DEFAULT NULL,
  `SALARY` decimal(18,2) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Step 2 − Now, copy the above source table structure and just change the name to copyCUSTOMERS.

CREATE TABLE `copyCUSTOMERS` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `NAME` varchar(20) NOT NULL,
  `AGE` int NOT NULL,
  `ADDRESS` char(25) DEFAULT NULL,
  `SALARY` decimal(18,2) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Step 3 − After executing step 2, if we want to copy data from the source table then we can do it by using INSERT INTO… SELECT statement.

INSERT INTO copyCUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)

SELECT ID,NAME,AGE,ADDRESS,SALARY FROM CUSTOMERS;

Let us verify whether the clone table copyCUSTOMERS has the same records as CUSTOMERS table or not using the following query −

SELECT * FROM copyCUSTOMERS;

As we observe the output, we have an exact clone table as CUSTOMERS.

But to make this process simpler, we can try to perform Simple Cloning, Shallow Cloning or Deep Cloning using CREATE TABLE and INSERT INTO statements. Let us discuss them one by one in detail with suitable examples further in this tutorial.

Simple Cloning in MySQL

Simple Cloning means making a new table that contains the same data as an existing one. First, a new table is created using the CREATE TABLE statement. Then, data from selected columns in the existing table is copied into the new table using a SELECT statement.

Syntax

Following is the basic syntax to perform simple cloning in MySQL RDBMS −

CREATE TABLE new_table SELECT * FROM original_table;

Example

To perform the simple cloning operation, let us first create a table named CUSTOMERS table using the below query −

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

The following query inserts 7 records into the above created table −

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

To display the records of the above created table, execute the following query −

SELECT * FROM CUSTOMERS;

Following is the CUSTOMERS table −

Now, with the following query, we are creating a new table CUSTOMERScopy by cloning the CUSTOMERS table.

CREATE TABLE CUSTOMERScopy SELECT * FROM CUSTOMERS;

Output

Executing the query above will produce the following output −

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

Verification

To verify whether the new table contains all the information from the existing table CUSTOMERS, we can use the following SELECT query −

SELECT * FROM CUSTOMERScopy;

Following is the CUSTOMERScopy table −

Shallow Cloning in MySQL

The Shallow Cloning operation only copies the structure of the existing table into the new table created, but it doesn”t copy any of the data. So, we end up with a new empty table that has the same structure as the existing table.

Syntax

Following is the basic syntax to perform shallow cloning in MySQL RDBMS −

CREATE TABLE testCUSTOMERS1 LIKE CUSTOMERS;

Example

In the following query, we are performing shallow cloning by creating a new table named CUSTOMERScopy1 by cloning the CUSTOMERS table.

CREATE TABLE CUSTOMERScopy1 LIKE CUSTOMERS;

Output

Executing the query above will produce the following output −

Query OK, 0 rows affected (0.03 sec)

Verification

To verify whether the new table is created or not, we can use the following SELECT query −

SELECT * FROM CUSTOMERScopy1;

As we can see in the output, the new table CUSTOMERScopy1 has been created with no data copied into it.

Empty set (0.00 sec)

Deep Cloning in MySQL

Deep Cloning operation is a combination of simple cloning and shallow cloning. It not only copies the structure of the existing table but also its data into the newly created table. Hence, the new table will have all the attributes of the existing table and also its contents.

Since it is a combination of shallow and simple cloning, this type of cloning will have two different queries to be executed: one with CREATE TABLE statement and one with INSERT INTO statement. The CREATE TABLE statement will create the new table by including all the attributes of existing table; and INSERT INTO statement will insert the data from existing table into new table.

Syntax

Following is the basic syntax to perform deep cloning in MySQL RDBMS −

CREATE TABLE new_table LIKE original_table;
INSERT INTO new_table SELECT * FROM original_table;

Example

In the following query, we are creating a new table CUSTOMERScopy2 by cloning the CUSTOMERS table, i.e. perform shallow cloning first.

CREATE TABLE CUSTOMERScopy2 LIKE CUSTOMERS;

Now using the following query, we are inserting data from CUSTOMERS table into new table CUSTOMERScopy2, i.e. performing simple cloning.

INSERT INTO CUSTOMERScopy2 SELECT * FROM CUSTOMERS;

Output

Executing the query above will produce the following output −

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

Verification

To verify whether the new table is created or not with all the data present in it, we can use the following SELECT query −

SELECT * FROM CUSTOMERScopy2;

As we observe the ouptut, the CUSTOMERScopy2 table has the same structure and data as CUSTOMERS table.

Cloning a table Using a Client Program

Besides using MySQL queries to clone an existing table, you can also perform the cloning operation on a table using a client program.

Syntax

Following are the syntaxes to clone a table various in programming languages −

$sql = "CREATE TABLE clone_table LIKE tut_tbl";
$mysqli->query($sql);

sql = "CREATE TABLE new_table SELECT * FROM original_table";
con.query(sql);

String sql = "CREATE TABLE clone_table LIKE tut_tbl";
statement.executeUpdate(sql);

sql = "CREATE TABLE new_table_name AS
   SELECT * FROM source_table_name";
cursorObj.execute(sql);

Example

Following are the programs −

$dbhost = ''localhost
$dbuser = ''root
$dbpass = ''password
$dbname = ''TUTORIALS
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);

if ($mysqli->connect_errno) {
    printf("Connect failed: %s
", $mysqli->connect_error);
    exit();
}
printf(''Connected successfully.
'');

// clone table
// To inherit all table definitions, use the create table...like syntax
$sql = "CREATE TABLE clone_table LIKE tut_tbl";
if ($mysqli->query($sql)) {
    printf("Table cloned successfully.
");
}
if ($mysqli->errno) {
    printf("table could not be cloned: %s
", $mysqli->error);
}

// To copy the data, you''ll need INSER...SELECT
$cpy_data = "INSERT INTO clone_table SELECT * FROM tut_tbl";
if ($mysqli->query($cpy_data)) {
    printf("fully cloned including data.
");
}
if ($mysqli->errno) {
    printf("table could not be cloned fully: %s
", $mysqli->error);
}
$mysqli->close();

Connected successfully.
Table cloned successfully.
fully cloned including data.

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

  sql = "CREATE DATABASE TUTORIALS;"
  con.query(sql);

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

  sql = "CREATE TABLE CUSTOMERS(ID int NOT NULL, NAME varchar(20) NOT NULL, AGE int NOT NULL, ADDRESS varchar(25), SALARY decimal(18, 2), PRIMARY KEY (ID) );"
  con.query(sql);

  sql = "INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, ''Ramesh'',32, ''Ahmedabad'', 2000.00 ),(2, ''Khilan'',25, ''Delhi'', 1500.00 ),(3, ''kaushik'',23, ''Kota'', 2000.00),(4,''Chaitali'', 25, ''Mumbai'', 6500.00 ),(5, ''Hardik'',27, ''Bhopal'', 8500.00 ),(6, ''Komal'',22, ''MP'', 4500.00 ),(7, ''Muffy'',24, ''Indore'', 10000.00 );"
  con.query(sql);

  sql = "CREATE TABLE testCUSTOMERS SELECT * FROM CUSTOMERS;"
  con.query(sql);

  sql = "SELECT * FROM testCUSTOMERS;"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log("**Records in Clone table**")
    console.log(result);
  });
});

Connected!
--------------------------
**Records in Clone table**
[
  {
    ID: 1,
    NAME: ''Ramesh'',
    AGE: 32,
    ADDRESS: ''Ahmedabad'',
    SALARY: ''2000.00''
  },
  {
    ID: 2,
    NAME: ''Khilan'',
    AGE: 25,
    ADDRESS: ''Delhi'',
    SALARY: ''1500.00''
  },
  {
    ID: 3,
    NAME: ''kaushik'',
    AGE: 23,
    ADDRESS: ''Kota'',
    SALARY: ''2000.00''
  },
  {
    ID: 4,
    NAME: ''Chaitali'',
    AGE: 25,
    ADDRESS: ''Mumbai'',
    SALARY: ''6500.00''
  },
  {
    ID: 5,
    NAME: ''Hardik'',
    AGE: 27,
    ADDRESS: ''Bhopal'',
    SALARY: ''8500.00''
  },
  { ID: 6, NAME: ''Komal'', AGE: 22, ADDRESS: ''MP'', SALARY: ''4500.00'' },
  {
    ID: 7,
    NAME: ''Muffy'',
    AGE: 24,
    ADDRESS: ''Indore'',
    SALARY: ''10000.00''
  }
]

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

public class CloneTable {
    public static void main(String[] args){
        String url = "jdbc:mysql://localhost:3306/TUTORIALS";
        String username = "root";
        String password = "password";
        try{
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection connection = DriverManager.getConnection(url, username, password);
            Statement statement = connection.createStatement();
            System.out.println("Connected successfully...!");

            //Creates clone tables...!
            String sql = "CREATE TABLE clone_tbl LIKE customers";
            statement.executeUpdate(sql);
            System.out.println("Clone table of customers has been created successfully...!");

            connection.close();
        }
        catch(Exception e){
            System.out.println(e);
        }
    }
}

Connected successfully...!
Clone table of customers has been created successfully...!

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
source_table_name = ''tutorials_tbl''
new_table_name = ''tutorials_tbl_cloned''
#Creating a cursor object
cursorObj = connection.cursor()
cursorObj.execute(f"CREATE TABLE {new_table_name} AS SELECT * FROM {source_table_name}")
print(f"Table ''{source_table_name}'' is cloned to ''{new_table_name}'' successfully.")
cursorObj.close()
connection.close()

Table ''tutorials_tbl'' is cloned to ''tutorials_tbl_cloned'' successfully.

MySQL Show Tables Statement

In MySQL, we use the SHOW TABLES command to retrieve the names of tables that are present in a specific database. This command is useful in various situations, such as:

• When we want to view names of tables present in a database to verify if a specific table exists or not.

• When we want to display additional information about each table present in a database, we use the SHOW TABLES command with the MySQL FULL modifier.

• Additionally, we can use the SHOW TABLES command with WILDCARDS to filter and display only the tables that match a specific pattern.

Syntax

Following is the syntax of MySQL SHOW TABLES command−

SHOW TABLES;

Before proceeding with the examples, assume that the following tables exist in two databases, testdb1 and testdb2:

Example

First of all, we are changing the database to testdb1 to perform the SHOW TABLES operation on it.

mysql> USE testdb1;
Database changed

Now, execute the following query to list down all the tables from testdb1 database.

SHOW TABLES;

Output

Following are the tables that are present in the testdb1 database −

SHOW TABLES with FULL modifier

In MySQL, we use the optional FULL modifier along with the SHOW TABLES command to display a second output column that contains additional information about the tables present in a database, such as their types: BASE TABLE for a table, VIEW for a view, or SYSTEM VIEW for an INFORMATION_SCHEMA table.

Example

In the following query, we are using the FULL modifier along with the SHOW TABLES command to list the tables and their types in the testdb1 database.

SHOW FULL TABLES;

Output

Following is the output of the above query −

SHOW TABLES in different Database

In MySQL, we can retrieve the list of tables present in another database. To do so, we need to use the IN operator or the FROM clause in conjunction with the SHOW TABLES statement.

Example

In the following query, we are fetching the list of tables that exist in another database testdb2, using the SHOW TABLES command with IN operator.

SHOW TABLES IN testdb2;

Output

Following are the names of the tables that are present in testdb2 database −

Example

We can also perform the above operation using the SHOW TABLES command with FROM clause.

SHOW TABLES FROM testdb2;

Output

As we can observe, both outputs are the same.

SHOW TABLES using Pattern Matching

In some scenarios where there are large amount of tables present in a database, and we want to retrieve only specific tables, we use the LIKE operator with WILDCARD characters such as ”%”. These wildcards will filter and display only the tables that match a specific pattern.”

Example

In the following query, we are using the LIKE operator with SHOW TABLES command to select all the tables (in testdb1 database) where the name starts with “stud”.

SHOW TABLES IN testdb1 LIKE "stud%";

Output

Following are the tables present in testdb1 database whose name starts with “stud” −

Example

Here, we are trying to retrieve the tables from testdb2 database where the name starts with “stud” −

SHOW TABLES IN testdb2 LIKE "stud%";

Output

This will produce following result −

Example

We are using the SHOW TABLES along with WHERE clause to check if there is a table named “employee_remarks” in testdb1 database −

SHOW TABLES FROM testdb1 WHERE Tables_in_testdb1 = "employee_remarks";

Output

This will produce following result −

Showing tables Using a Client Program

Besides showing the list of tables present in a MySQL database with a MySQL query, we can also use a client program to perform the SHOW TABLES operation.

Syntax

Following are the syntaxes to Show list of tables in MySQL Database in various programming languages −

$sql = "SHOW TABLES FROM DATABASE";
$mysqli->query($sql);

sql= "SHOW TABLES FROM DATABASE";
con.query(sql);

String sql = "SHOW TABLES FROM DATABASE";
statement.executeQuery(sql);

show_table_query = "SHOW TABLES FROM DATABASE"
cursorObj.execute(show_table_query);

Example

Following are the programs −

$dbhost = ''localhost
$dbuser = ''root
$dbpass = ''password
$dbname = ''TUTORIALS
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);

if ($mysqli->connect_errno) {
    printf("Connect failed: %s
", $mysqli->connect_error);
    exit();
}
// printf(''Connected successfully.
'');

$sql = "SHOW TABLES FROM TUTORIALS";
if ($result = $mysqli->query($sql)) {
    printf("Show table executed successfully.
");
    while ($row = mysqli_fetch_array($result)) {
        print_r($row);
    }
}
if ($mysqli->errno) {
    printf("Could not show table: %s
", $mysqli->error);
}
$mysqli->close();

Show table executed successfully.
Array
(
    [0] => articles
    [Tables_in_tutorials] => articles
)
Array
(
    [0] => courses
    [Tables_in_tutorials] => courses
)
Array
(
    [0] => customers
    [Tables_in_tutorials] => customers
)
Array
(
    [0] => customerss
    [Tables_in_tutorials] => customerss
)
Array
(
    [0] => demo_table
    [Tables_in_tutorials] => demo_table
)
Array
(
    [0] => employee
    [Tables_in_tutorials] => employee
)
Array
(
    [0] => films
    [Tables_in_tutorials] => films
)
Array
(
    [0] => films_watched
    [Tables_in_tutorials] => films_watched
)
Array
(
    [0] => myplayers
    [Tables_in_tutorials] => myplayers
)
Array
(
    [0] => new_tutorial_tbl
    [Tables_in_tutorials] => new_tutorial_tbl
)
Array
(
    [0] => orders
    [Tables_in_tutorials] => orders
)
Array
(
    [0] => persons
    [Tables_in_tutorials] => persons
)
Array
(
    [0] => products
    [Tables_in_tutorials] => products
)
Array
(
    [0] => sales
    [Tables_in_tutorials] => sales
)
Array
(
    [0] => students
    [Tables_in_tutorials] => students
)

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

  sql = "CREATE DATABASE TUTORIALS;"
  con.query(sql);

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

  sql = "CREATE TABLE CUSTOMERS (ID INT NOT NULL, NAME VARCHAR(20) NOT NULL);"
  con.query(sql);

  sql = "CREATE TABLE ORDERS (OID INT NOT NULL, CUSTOMER_ID INT);"
  con.query(sql);

  sql = "SHOW TABLES;"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log(result);
  });
});

Connected!
--------------------------
[
  { Tables_in_tutorials: ''customers'' },
  { Tables_in_tutorials: ''orders'' }
]

import java.sql.*;
public class JDBCconnection {
public static void main(String[] args){
    String url = "jdbc:mysql://localhost:3306/TUTORIALS";
    String username = "root";
    String password = "password";
    try{
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection connection = DriverManager.getConnection(url, username, password);
        Statement statement = connection.createStatement();
        System.out.println("Connected successfully...!");

        //show table statement...!
        String sql = "SHOW TABLES FROM TUTORIALS";
        ResultSet resultSet = statement.executeQuery(sql);
        System.out.println("Tables in the current database: ");
        while(resultSet.next()) {
            System.out.print(resultSet.getString(1));
            System.out.println();
        }
        connection.close();
    }
    catch(Exception e){
        System.out.println(e);
    }
}
}

Connected successfully...!
Tables in the current database:
articles
blackpink
blog
courses
customer
customers
data

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()
cursorObj.execute("SHOW TABLES")
tables = cursorObj.fetchall()
print("Tables in the database are:")
for table in tables:
    print(table[0])
cursorObj.close()
connection.close()

Tables in the database are:
books
novel
novels
tutorials_tbl

There can be a situation where both users and database administrators might want to change the name of a table in a relational database to make the table”s name more suitable for a specific situation.

MySQL provides two different ways to rename an MySQL table. We can use either the RENAME TABLE or ALTER TABLE statement. In this tutorial, we will understand them with suitable examples.

MySQL RENAME TABLE Statement

The MySQL RENAME TABLE statement is used to rename an existing table in a database with another name.

Syntax

Following is the basic syntax of the MySQL RENAME TABLE statement −

RENAME TABLE table_name TO new_name;

Where, table_name is the name of an existing table and new_name is the new name which you want to assign.

Example

Let us start by creating a table with name CUSTOMERS in MySQL database using CREATE statement as shown below −

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

Here, we are renaming the above-created CUSTOMERS table to BUYERS using the following query −

RENAME TABLE CUSTOMERS to BUYERS;

Output

The table has been renamed without any errors.

Query OK, 0 rows affected (0.01 sec)

Verification

Execute the following query to retrieve the description of the CUSTOMERS table −

DESC CUSTOMERS;

It display an error because, we have changed the CUSTOMERS table name to BUYERS and there is no CUSTOMERS table in our database.

ERROR 1146 (42S02): Table ''tutorials.customers'' doesn''t exist

Renaming Multiple Tables

Using the MySQL RENAME TABLE statement, we can also rename multiple tables in a single query.

Syntax

Following is the syntax for renaming multiple tables using MySQL RENAME TABLE statement −

RENAME TABLE old_table1 TO new_table1,
   old_table2 TO new_table2,
   old_table3 TO new_table3;

Example

In the following example, we are creating three different tables named Cust1, Cust2, and Cust3 −

CREATE TABLE Cust1(ID INT);
CREATE TABLE Cust2(ID INT);
CREATE TABLE Cust3(ID INT);

Here, we are verifying whether the above tables are created or not using the following query −

SHOW TABLES;

As we can see in the output below, the above tables have been successfully created.

Now, let us rename all the above-created tables using the following query −

RENAME TABLE Cust1 TO Buyer1, Cust2 TO Buyer2, Cust3 TO Buyer3;

Output

All three tables has been renamed without any errors.

Query OK, 0 rows affected (0.03 sec)

Verification

Let us verify the list of the tables again to find whether the table names have been changed or not −

SHOW TABLES;

As we can see the output below, all three tables have been successfully renamed.

Renaming a Table using ALTER TABLE statement

In MySQL, we can also use the RENAME with ALTER TABLE statement to modify the name of an existing table.

Syntax

Following is the syntax to rename a table with ALTER TABLE statement −

ALTER TABLE existing_table_name RENAME TO new_table_name

Example

In the following query, we are creating a table named PLAYERS.

CREATE TABLE PLAYERS (
	ID INT,
	NAME VARCHAR(20),
	AGE INT
);

Now, let us rename the above-created table with a new name TEAMS using the following query −

ALTER TABLE PLAYERS RENAME TO TEAMS;

Output

The table has been renamed without any errors.

Query OK, 0 rows affected (0.02 sec)

Verification

Execute the following query to retrieve the description of the PLAYERS table −

DESC PLAYERS;

It will display an error because, we have renamed the PLAYERS table to TEAMS and there is no PLAYERS table in our database.

ERROR 1146 (42S02): Table ''tutorials.players'' doesn''t exist

Renaming Table Using a Client Program

In addition to renaming a table in MySQL Database using MySQL query, we can also perform the RENAME TABLE operation on a table using a client program.

Syntax

Following are the syntaxes to rename table in MySQL database in various programming languages −

$sql = "RENAME TABLE old_table_name TO new_table_name";
$mysqli->query($sql);

sql = "RENAME TABLE table_name TO new_name";
con.query(sql);

String sql = "RENAME TABLE old_table_name TO new_table_name";
statement.executeUpdate(sql);

sql = "RENAME TABLE old_table_name TO new_table_name"
cursorObj.execute(sql);

Example

Following are the programs −

$dbhost = ''localhost
$dbuser = ''root
$dbpass = ''password
$dbname = ''TUTORIALS
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);

if ($mysqli->connect_errno) {
    printf("Connect failed: %s
", $mysqli->connect_error);
    exit();
}
// printf(''Connected successfully.
'');

$sql = "RENAME TABLE tutorials_table TO tutorials_tbl ";
if ($mysqli->query($sql)) {
    printf("table renamed successfully.
");
}
if ($mysqli->errno) {
    printf("table could not rename: %s
", $mysqli->error);
}
$mysqli->close();

table renamed successfully.

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

  //Creating a Database
  sql = "CREATE DATABASE tutorials"
  con.query(sql);

  //Selecting a Database
  sql = "USE tutorials"
  con.query(sql);

  //Creating DEMO table
  sql = "CREATE TABLE Demo(ID INT, First_Name VARCHAR(255), Last_Name VARCHAR(255));"
  con.query(sql);

  //Inserting records
  sql = "INSERT INTO Demo VALUES(1, ''Shikhar'', ''Dhawan''),(2, ''Jonathan'', ''Trott''),(3, ''Kumara'', ''Sangakkara'');"
  con.query(sql);

  //Fetching the DEMO table
  sql = "SELECT * FROM Demo;"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log("**Following is the DEMO table**");
    console.log(result);
    console.log("--------------------------");
  });

  //Renaming the DEMO table as PLAYERS
  sql = "RENAME TABLE Demo to Players;"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log("**Renamed the DEMO table as Players**");
    console.log(result);
    console.log("--------------------------");
  });

  //Trying to Retrieve the DEMO table, Leads to an error.
  sql = "SELECT * FROM Demo;"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log("Trying to retrieve DEMO table");
    console.log(result);
  });
});

Connected!
--------------------------
**Following is the DEMO table**
[
  { ID: 1, First_Name: ''Shikhar'', Last_Name: ''Dhawan'' },
  { ID: 2, First_Name: ''Jonathan'', Last_Name: ''Trott'' },
  { ID: 3, First_Name: ''Kumara'', Last_Name: ''Sangakkara'' }
]
--------------------------
**Renamed the DEMO table as Players**
ResultSetHeader {
  fieldCount: 0,
  affectedRows: 0,
  insertId: 0,
  info: '''',
  serverStatus: 2,
  warningStatus: 0,
  changedRows: 0
}
--------------------------
C:UsersLenovodesktopJavaScriptconnectDB.js:52
    if (err) throw err
             ^

Error: Table ''tutorials.demo'' doesn''t 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) {
  code: ''ER_NO_SUCH_TABLE'',
  errno: 1146,
  sqlState: ''42S02'',
  sqlMessage: "Table ''tutorials.demo'' doesn''t exist",
  sql: ''SELECT * FROM Demo;'',
  fatal: true
}

import java.sql.*;
public class RenameTable {
    public static void main(String[] args){
        String url = "jdbc:mysql://localhost:3306/TUTORIALS";
        String username = "root";
        String password = "password";
        try{
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection connection = DriverManager.getConnection(url, username, password);
            Statement statement = connection.createStatement();
            System.out.println("Connected successfully...!");

            //Rename tables...!
            String sql = "RENAME TABLE tutorials_tbl TO new_table";
            statement.executeUpdate(sql);
            System.out.println("Table renamed successfully successfully...!");
            connection.close();
        }
        catch(Exception e){
            System.out.println(e);
        }
    }
}

Table renamed successfully successfully...!

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
old_table_name = ''tutorials_tbl''
new_table_name = ''tutorials_table''
#Creating a cursor object
cursorObj = connection.cursor()
cursorObj.execute(f"RENAME TABLE {old_table_name} TO {new_table_name}")
print(f"Table ''{old_table_name}'' is renamed to ''{new_table_name}'' successfully.")
cursorObj.close()
connection.close()

Table ''tutorials_tbl'' is renamed to ''tutorials_table'' successfully.

MySQL ALTER Command

The MySQL ALTER command is used to modify the structure of an existing table. It allows you to make various changes, such as adding, deleting, or modify columns within the table.

Additionally, the ALTER command is also used to add and drop different constraints associated with an existing table.

Since this command modifies the structure of a table, it is a part of Data Definition Language in SQL. This is also where the ALTER command differs from UPDATE command; while ALTER interacts with the structure of a table to modify it, UPDATE only interacts with the data present in the table without disturbing its structure.

Syntax

Following is the syntax of ALTER command in MySQL −

ALTER TABLE table_name [alter_option ...];

Example

Let us begin with the creation of a table named CUSTOMERS.

CREATE TABLE CUSTOMERS (
   ID INT,
   NAME VARCHAR(20)
);

Now, execute the following query to display information about the columns in CUSTOMERS table.

SHOW COLUMNS FROM CUSTOMERS;

Output

Following are the details of the columns of the CUSTOMERS table −

Dropping a Column

To drop a column in an existing table, we use the ALTER TABLE command with DROP clause.

Example

In the following example, we are dropping an existing column named ID from the above-created CUSTOMERS table −

ALTER TABLE CUSTOMERS DROP ID;

Output

Executing the query above will produce the following output −

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

Verification

To verify whether the ID column has been dropped from the CUSTOMERS table, execute the following query −

SHOW COLUMNS FROM CUSTOMERS;

As we can see in the output below, there is no ID column present. Hence it is dropped.

Note: A DROP clause will not work if the column is the only one left in the table.

Adding a Column

To add a new column into an existing table, we use ADD keyword with the ALTER TABLE command.

Example

In the following query, we are adding a column named ID into an existing table CUSTOMERS.

ALTER TABLE CUSTOMERS ADD ID INT;

Output

Executing the query above will produce the following output −

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

Now, the CUSTOMERS table will contain the same two columns that it had when you first created the table. But the newly added ID column will be added at the end of the table by default. In this case, it will add after the NAME column.

Verification

Let us verify using the following query −

SHOW COLUMNS FROM CUSTOMERS;

As we can see in the output below, the newly added ID column is inserted at the end of the table.

Repositioning a Column

If we want a column to be placed at a specific position within the table, we can use FIRST to make it the first column or AFTER col_name to indicate that the new column should be positioned after the col_name.

Example

Consider the previously modified CUSTOMERS table, where the NAME is the first column and ID is the last column.

In the following query, we are removing the ID column from the table and then adding it back, positioning it as the first column in the table using FIRST keyword −

ALTER TABLE CUSTOMERS DROP ID;
ALTER TABLE CUSTOMERS ADD ID INT FIRST;

Output

Executing the query above will produce the following output −

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

Verification

Now, let us verify the positions of the column in the CUSTOMERS table −

SHOW COLUMNS FROM CUSTOMERS;

As we can see in the output below, the ID column is positioned first.

Example

Here, we are removing the ID column from the table and then adding it back, positioning it after the NAME column using the AFTER col_name keyword.

ALTER TABLE CUSTOMERS DROP ID;
ALTER TABLE CUSTOMERS ADD ID INT AFTER NAME;

Output

Executing the query above will produce the following output −

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

Verification

Now, let us verify the positions of the column in the CUSTOMERS table −

SHOW COLUMNS FROM CUSTOMERS;

As we can see in the output below, the ID column is positioned first.

Note: The FIRST and AFTER specifiers work only with the ADD clause. This means that if you want to reposition an existing column within a table, you first must DROP it and then ADD it at the new position.

Altering a Column Definition or a Name

In MySQL, to change a column”s definition, we use MODIFY or CHANGE clause in conjunction with the ALTER command.

Example

In the query below, we are changing the definition of column NAME from varchar(20) to INT using the MODIFY clause −

ALTER TABLE CUSTOMERS MODIFY NAME INT;

Output

Executing the query above will produce the following output −

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

Verification

Now, let us verify the definition of the NAME column in the CUSTOMERS table −

SHOW COLUMNS FROM CUSTOMERS;

As we can observe, the definition for NAME column has been changed to INT.

Example

We can also change the column definition using CHANGE, but the syntax is a bit different from MODIFY. After the CHANGE keyword, we specify the name of the column (twice) that we want to change, then specify the new definition.

Here, we are changing the definition of column ID from INT to varchar(20) using the CHANGE clause −

ALTER TABLE CUSTOMERS MODIFY ID VARCHAR(20);

Output

Executing the query above will produce the following output −

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

Verification

Now, let us verify the definition of the NAME column in the CUSTOMERS table −

SHOW COLUMNS FROM CUSTOMERS;

As we can observe, the definition for NAME column has been changed to INT.

Altering a Column”s Default Value

In MySQL, we can change a default value for any column by using the DEFAULT constraint with ALTER command.

Example

In the following example, we are changing the default value of NAME column.

ALTER TABLE CUSTOMERS ALTER NAME SET DEFAULT 1000;

Output

Executing the query above will produce the following output −

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

Verification

Now, let us verify the default value of the NAME column in the CUSTOMERS table −

SHOW COLUMNS FROM CUSTOMERS;

As we can observe, the default value for NAME column has been changed to 1000.

Example

We can remove the default constraint from any column by using DROP clause along with the ALTER command.

Here, we are removing the default constraint of NAME column.

ALTER TABLE CUSTOMERS ALTER NAME DROP DEFAULT;

Output

Executing the query above will produce the following output −

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

Verification

Now, let us verify the default value of the NAME column in the CUSTOMERS table −

SHOW COLUMNS FROM CUSTOMERS;

As we can observe, the default value for NAME column has been changed to NULL.

Altering (Renaming) a Table

To rename a table, use the RENAME option of the ALTER TABLE statement.

Example

The following query renames the table named CUSTOMERS to BUYERS.

ALTER TABLE CUSTOMERS RENAME TO BUYERS;

Output

Executing the query above will produce the following output −

Query OK, 0 rows affected (0.02 sec)

Verification

Now, let us verify the default value of the NAME column in the CUSTOMERS table −

SHOW COLUMNS FROM BUYERS;

The table has been renamed to BUYERS, as we can see from the columns within it.

Altering Table Using a Client Program

Besides altering an existing table in a MySQL database with a MySQL query, we can also use a client program to perform the ALTER TABLE operation.

Syntax

Following are the syntaxes of Altering a table from MySQL Database in various programming languages −

$sql = "ALTER TABLE table_name";
$mysqli->query($sql);

sql = "ALTER TABLE table_name";
con.query(sql);

String sql = "ALTER TABLE table_name";
statement.execute(sql);

sql = "ALTER TABLE table_name";
cursorObj.execute(sql);

Example

Following are the programs −

$dbhost = ''localhost
$dbuser = ''root
$dbpass = ''password
$dbname = ''TUTORIALS
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);

if ($mysqli->connect_errno) {
    printf("Connect failed: %s
", $mysqli->connect_error);
    exit();
}
// printf(''Connected successfully.
'');

$sql = "ALTER TABLE testalter_tbl  DROP i";
if ($mysqli->query($sql)) {
    printf("table altered successfully.
");
}
if ($mysqli->errno) {
    printf("table could not alter: %s
", $mysqli->error);
}
$mysqli->close();

table altered successfully.

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("--------------------------");
  sql = "USE TUTORIALS"
  con.query(sql);

  //Altering a table
  sql = "ALTER TABLE testalter_tbl  DROP i";
    con.query(sql, function(err){
    if (err) throw err
    console.log("Altered table successfully...");
  });
});

Connected!
--------------------------
Altered table successfully...

import java.sql.*;
public class AlterTable {
    public static void main(String[] args){
        String url = "jdbc:mysql://localhost:3306/TUTORIALS";
        String username = "root";
        String password = "password";
        try{
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection connection = DriverManager.getConnection(url, username, password);
            Statement statement = connection.createStatement();
            System.out.println("Connected successfully...!");

            //Alter table statement...!
            String sql = "ALTER TABLE testalter_tbl  DROP i";
            statement.executeUpdate(sql);
            System.out.println("Table altered successfully...!");
            connection.close();
        }
        catch(Exception e){
            System.out.println(e);
        }
    }
}

Connected successfully...!
Table altered successfully...!

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
table_name = ''testalter_tbl''
# ALTER TABLE statement
alter_statement = ''testalter_tbl  DROP i''
#Creating a cursor object
cursorObj = connection.cursor()
cursorObj.execute(f"ALTER TABLE {table_name} {alter_statement}")
print(f"Table ''{table_name}'' is altered successfully.")
cursorObj.close()
connection.close()

Table ''testalter_tbl'' is altered successfully.

Account Locking and Unlocking in MySQL is introduced to increase security of the database by preventing unauthorized transactions or suspicious activities.

MySQL Unlock User Account

To check whether an account is unlocked or not, MySQL provides the ”account_locked” attribute in the ”mysql.user” table that will hold either ”Y” or ”N” values respectively. If the attribute holds the ”N” value, then the account is said to be in the unlock mode.

By default, all the new user accounts created in MySQL are unlocked.

Unlocking New Accounts

You can use the CREATE USER… ACCOUNT UNLOCK statement to unlock new accounts created in MySQL. By default, the newly created accounts are always unlocked unless specified otherwise. However, the ACCOUNT UNLOCK clause is mostly used when an account is in the locked state.

Syntax

Following is the syntax of CREATE USER… ACCOUNT UNLOCK statement −

CREATE USER username@hostname
IDENTIFIED BY ''new_password'' ACCOUNT UNLOCK;

Example

In the following query, we are creating a new user account in MySQL using the CREATE USER statement −

CREATE USER testuser@localhost
IDENTIFIED BY ''qwerty'' ACCOUNT UNLOCK;

Output

Following is the output of the above code −

Query OK, 0 rows affected (0.02 sec)

Verification

We can verify whether the account of the ”testuser” is unlocked or not using the following SELECT statement −

SELECT User, Host, account_locked
FROM mysql.user WHERE User = ''testuser

Output of the above code is as shown below −

Example

As we have learned above, the newly created user accounts are unlocked by default. Look at the example below −

CREATE USER demo@localhost IDENTIFIED BY ''000000

Output

The result produced is as follows −

Query OK, 0 rows affected (0.02 sec)

Verification

We can verify whether the newly created account is unlocked by default using the following SELECT statement −

SELECT User, Host, account_locked
FROM mysql.user WHERE User = ''demo

The output obtained is as follows −

Unlocking Existing Accounts

We can use the ALTER USER… ACCOUNT UNLOCK statement unlock existing accounts in MySQL that are locked beforehand.

Syntax

Following is the syntax of ALTER USER… ACCOUNT UNLOCK statement −

ALTER USER username@hostname ACCOUNT UNLOCK;

Example

We are first retrieving the information of the existing account ”sample”, including its username, host, and the status of its account lock −

SELECT User, Host, account_locked
FROM mysql.user WHERE User = ''sample

We can see in the output below that the user account is locked −

Now, we will unlock the existing account ”sample” using the ALTER USER statement −

ALTER USER sample@localhost ACCOUNT UNLOCK;

Output

Following is the output of the above query −

Query OK, 0 rows affected (0.00 sec)

Verification

We can verify whether the account is unlocked or not using the following SELECT query −

SELECT User, Host, account_locked
FROM mysql.user WHERE User = ''sample

As we can see in the below output, the ”sample@localhost” account is now unlocked and can be accessed according to its privileges −

Unlock User Account Using a Client Program

Now, in this section let us discuss how to unlock a MySQL user using various client programs.

Syntax

Following are the syntaxes −

$sql = "ALTER USER user_name ACCOUNT UNLOCK";
$mysqli->query($sql);

sql= "CREATE USER username@hostname IDENTIFIED BY
  ''new_password'' ACCOUNT UNLOCK";
con.query(sql, function (err, result) {
   if (err) throw err;
      console.log(result);
});

String sql = "ALTER USER USER_NAME@LOCALHOST ACCOUNT UNLOCK";
statement.execute(sql);

sql = f"ALTER USER ''{username_to_unlock}''@''localhost'' ACCOUNT UNLOCK";
cursorObj.execute(sql);

Example

Following are the programs to unlock users in various programming languages −

$dbhost = ''localhost
$dbuser = ''root
$dbpass = ''password
$mysqli = new mysqli($dbhost, $dbuser, $dbpass);
   if($mysqli->connect_errno ) {
      printf("Connect failed: %s
", $mysqli->connect_error);
      exit();
}
//printf(''Connected successfully.
'');
$sql = "ALTER USER Sarika ACCOUNT UNLOCK";
if($mysqli->query($sql)){
   printf("User has been unlocked successfully..!");
}
if($mysqli->error){
   printf("Failed..!" , $mysqli->error);
}
$mysqli->close();

User has been unlocked successfully..!

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

  sql = "CREATE USER testuser@localhost IDENTIFIED BY ''qwerty'' ACCOUNT UNLOCK;"
  con.query(sql);

  sql = "SELECT User, Host, account_locked FROM mysql.user WHERE User = ''testuser";
  con.query(sql, function(err, result){
    if (err) throw err;
    console.log(result);
  });
});

Connected!
--------------------------
[ { User: ''testuser'', Host: ''localhost'', account_locked: ''N'' } ]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class UnlockUserAccount {
	public static void main(String[] args) {
		String url = "jdbc:mysql://localhost:3306/TUTORIALS";
		String user = "root";
		String password = "password";
		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 = "ALTER USER Vivek@localhost ACCOUNT UNLOCK";
            st.execute(sql);
            System.out.println("User ''Vivek'' account unlocked successfully...!");
		}catch(Exception e) {
			e.printStackTrace();
		}
	}
}

User ''Vivek'' account unlocked successfully...!

import mysql.connector
# creating the connection object
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password''
)
username_to_unlock = ''newUser''
# Create a cursor object for the connection
cursorObj = connection.cursor()
cursorObj.execute(f"ALTER USER ''{username_to_unlock}''@''localhost'' ACCOUNT UNLOCK")
print(f"User ''{username_to_unlock}'' account is unlocked successfully.")
cursorObj.close()
connection.close()

User ''newUser'' account is unlocked successfully.

Earlier, we discussed how a root user gets access to a MySQL server with default privileges after installation. These privileges are sufficient for performing basic operations on the data. However, in some special situations, users might need to request the server”s host to take away certain privileges. To do so, we use the MySQL REVOKE statement.

The MySQ REVOKE statement

The MySQL REVOKE statement is used to remove certain administrative privileges or roles from users. It revokes permissions that were previously granted.

Syntax

Following is the syntax of the MySQL REVOKE Statement −

REVOKE privileges
   ON database_name.table_name
   FROM ''user''@''host

Example

Assume we have created a user named ”test_user”@”localhost” in MySQL using the CREATE USER statement as shown below −

CREATE USER ''test_user''@''localhost'' IDENTIFIED BY ''testpassword

Following is the output produced −

Query OK, 0 rows affected (0.23 sec)

Now, let us create a database named ”test_database” −

CREATE DATABASE test_database;

The output produced is as follows −

Query OK, 1 row affected (0.56 sec)

Next, we will use the created database −

USE test_database;

We get the output as shown below −

Database changed

Now, let us create a table in the database −

CREATE TABLE MyTable(data VARCHAR(255));

The output obtained is as follows −

Query OK, 0 rows affected (0.67 sec)

Following query grants privileges on the table created above to the user ”test_user”@”localhost −

GRANT SELECT ON test_database.MyTable TO ''test_user''@''localhost

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

Query OK, 0 rows affected (0.31 sec)

You can verify the granted privileges using the SHOW GRANTS statements −

SHOW GRANTS FOR ''test_user''@''localhost

The output we get is as shown below −

Now, you can revoke the above granted privilege using the REVOKE statement as shown below −

REVOKE SELECT ON test_database.MyTable FROM ''test_user''@''localhost

We get the output as follows −

Query OK, 0 rows affected (0.25 sec)

Verification

We can verify whether the SELECT privilege has been revoked or not using the SHOW GRANTS statements as shown below −

SHOW GRANTS FOR ''test_user''@''localhost

We can see that the output no longer lists the SELECT privilege, indicating that it has been revoked −

Revoking All Privileges

If a user has multiple privileges with a user, you can revoke all those privileges at once using the REVOKE ALL statement in MySQL.

Syntax

Following is the syntax to revoke all privileges in MySQL −

REVOKE ALL PRIVILEGES ON *.* FROM ''user''@''host

Example

Assume we have created a user as follows −

CREATE USER ''sample_user''@''localhost

Following is the output produced −

Query OK, 0 rows affected (0.18 sec)

We also create a procedure as shown below −

DELIMITER //
CREATE PROCEDURE sample ()
   BEGIN
      SELECT ''This is a sample procedure
   END//
DELIMITER ;

The output obtained is as follows −

Query OK, 0 rows affected (0.29 sec)

Additionally, we create a table named ”sample” in a database −

CREATE TABLE sample(data INT);

We get the output as shown below −

Query OK, 0 rows affected (0.68 sec)

Now, the following queries grants ALTER ROUTINE, EXECUTE privileges on the above created procedure to the user named ”sample_user”@”localhost”.

GRANT ALTER ROUTINE, EXECUTE
ON PROCEDURE test_database.sample TO ''sample_user''@''localhost

Output of the above code is as shown below −

Query OK, 0 rows affected (0.20 sec)

Similarly, following query grants SELECT, INSERT and UPDATE privileges on the table ”sample” to the user ”sample_user”@”localhost −

GRANT SELECT, INSERT, UPDATE
ON test.sample TO ''sample_user''@''localhost

The result produced is −

Query OK, 0 rows affected (0.14 sec)

You can verify the list of all privileges granted for the user using the SHOW GRANTS statement −

SHOW GRANTS FOR ''sample_user''@''localhost

The result obtained is as follows −

Finally, to revoke all the privileges granted to ”sample_user”@”localhost”, you can use the following statement −

REVOKE ALL PRIVILEGES, GRANT OPTION FROM ''sample_user''@''localhost

The result produced is −

Query OK, 0 rows affected (0.30 sec)

Verification

After revoking privileges, you can check the user”s grants again −

SHOW GRANTS FOR ''sample_user''@''localhost

The output below confirms that all privileges have been revoked −

Revoking Proxy Privilege

You can make one user as a proxy of another by granting the PROXY privilege to it. If you do so, both users have the same privileges.

Example

Assume we have created users named sample_user, proxy_user in MySQL using the CREATE statement −

CREATE USER sample_user, proxy_user IDENTIFIED BY ''testpassword

Following is the output obtained −

Query OK, 0 rows affected (0.52 sec)

Now, we are creating a table ”Employee” −

CREATE TABLE Employee (
ID INT, Name VARCHAR(15), Phone INT, SAL INT);

We get the output as shown below −

Query OK, 0 rows affected (6.47 sec)

Following query grants SELECT and INSERT privileges on the table created above, to the user sample_user −

GRANT SELECT, INSERT ON Emp TO sample_user;

The output obtained is as follows −

Query OK, 0 rows affected (0.28 sec)

Now, we can assign proxy privileges to the user proxy_user using the GRANT statement as shown below −

GRANT PROXY ON sample_user TO proxy_user;

The result produced is −

Query OK, 0 rows affected (1.61 sec)

You can revoke a proxy privilege using the REVOKE PROXY statement as shown below −

REVOKE PROXY ON sample_user FROM proxy_user;

We get the following result −

Query OK, 0 rows affected (0.33 sec)

Revoking a Role

A role in MySQL is a set of privileges with name. You can create one or more roles in MySQL using the CREATE ROLE statement. If you use the GRANT statement without the ON clause, you can grant a role instead of privileges.

Example

Following query creates a role named TestRole_ReadOnly −

CREATE ROLE ''TestRole_ReadOnly

Following is the output of the above code −

Query OK, 0 rows affected (0.13 sec)

Now, let us grant read only privilege to the created role using the GRANT statement −

GRANT SELECT ON * . * TO ''TestRole_ReadOnly

The result obtained is −

Query OK, 0 rows affected (0.14 sec)

Then, you can GRANT the created role to a user as follows −

CREATE USER ''newuser''@''localhost'' IDENTIFIED BY ''password

Output of the above code is as follows −

Query OK, 0 rows affected (0.14 sec)

Next, you can grant the ”TestRole_ReadOnly” role to the ”newuser”@”localhost”−

GRANT ''TestRole_ReadOnly'' TO ''newuser''@''localhost

We get the following result −

Query OK, 0 rows affected (0.13 sec)

Following query revokes the role from the user −

REVOKE ''TestRole_ReadOnly'' FROM ''newuser''@''localhost

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

Query OK, 0 rows affected (1.23 sec)

Revoking Privileges Using a Client Program

We can also revoke privileges from a MySQL user using a client program.

Syntax

Following are the syntaxes to revoke MySQL Privileges in various programming languages −

$sql = "REVOKE ALL, GRANT OPTION FROM user_name";
$mysqli->query($sql);

sql= "REVOKE privilege_name(s) ON object FROM user_account_name";
con.query(sql, function (err, result) {
   if (err) throw err;
      console.log(result);
});

String sql = "REVOKE ALL PRIVILEGES, GRANT OPTION FROM USER_NAME";
statement.execute(sql);

sql = f"REVOKE {privileges} ON your_database.* FROM ''{username_to_revoke}''@''localhost''";
cursorObj.execute(sql);

Example

Following are the implementations of this operation in various programming languages −

$dbhost = ''localhost
$dbuser = ''root
$dbpass = ''password
$mysqli = new mysqli($dbhost, $dbuser, $dbpass);
if($mysqli->connect_errno ) {
   printf("Connect failed: %s
", $mysqli->connect_error);
   exit();
}
//printf(''Connected successfully.
'');
$sql = "REVOKE ALL, GRANT OPTION FROM Sarika";
if($result = $mysqli->query($sql)){
   printf("Revoke privileges executed successfully...!");
}
if($mysqli->error){
   printf("Failed..!" , $mysqli->error);
}
$mysqli->close();

Revoke privileges executed successfully...!

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

  sql = "CREATE USER ''test_user''@''localhost'' IDENTIFIED BY ''testpassword"
  con.query(sql);

  sql = "CREATE DATABASE test_database;"
  con.query(sql);

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

  sql = "CREATE TABLE MyTable(data VARCHAR(255));"
  con.query(sql);

  sql = "GRANT SELECT ON test_database.MyTable TO ''test_user''@''localhost"
  con.query(sql);

  sql = "SHOW GRANTS FOR ''test_user''@''localhost";
  con.query(sql, function(err, result){
    if (err) throw err;
    console.log("**Granted privileges:**");
    console.log(result);
    console.log("--------------------------");
  });
  sql = "REVOKE SELECT ON test_database.MyTable FROM ''test_user''@''localhost"
  con.query(sql);

  sql = "SHOW GRANTS FOR ''test_user''@''localhost";
  con.query(sql, function(err, result){
    if (err) throw err;
    console.log("**Grants after revoking:**");
    console.log(result);
  });
});

Connected!
--------------------------
**Granted privileges:**
[
  {
    ''Grants for test_user@localhost'': ''GRANT USAGE ON *.* TO `test_user`@`localhost`''
  },
  {
    ''Grants for test_user@localhost'': ''GRANT SELECT ON `test_database`.`mytable` TO `test_user`@`localhost`''
  }
]
--------------------------
**Grants after revoking:**
[
  {
    ''Grants for test_user@localhost'': ''GRANT USAGE ON *.* TO `test_user`@`localhost`''
  }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class RevokePriv {
	public static void main(String[] args) {
		String url = "jdbc:mysql://localhost:3306/TUTORIALS";
		String user = "root";
		String password = "password";
		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 = "REVOKE ALL PRIVILEGES, GRANT OPTION FROM Vivek";
            st.execute(sql);
            System.out.println("You revoked all the privileges form user ''Vivek''");
		}catch(Exception e) {
			e.printStackTrace();
		}
	}
}

You revoked all the privileges form user ''Vivek''

import mysql.connector
# creating the connection object
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password''
)
username_to_revoke = ''newUser''
# privileges we want to revoke
privileges = ''SELECT, INSERT, UPDATE''
# Create a cursor object for the connection
cursorObj = connection.cursor()
cursorObj.execute(f"REVOKE {privileges} ON your_database.* FROM ''{username_to_revoke}''@''localhost''")
print(f"Privileges revoked from the user ''{username_to_revoke}'' successfully.")
cursorObj.close()
connection.close()

Privileges revoked from the user ''newUser'' successfully.