Khóa học miễn phí MySQL – Rename Tables nhận dự án làm có lương

MySQL – Rename Tables

Table of content


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.

Tables_in_tutorials
cust1
cust2
cust3

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.

Tables_in_tutorials
buyer1
buyer2
buyer3

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 −

To rename a table into MySQL database through PHP program, we need to execute RENAME TABLE statement using the mysqli function query() as −

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

To rename a table into MySQL database through Node.js program, we need to execute RENAME TABLE statement using the query() function of the mysql2 library as −

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

To rename a table into MySQL database through Java program, we need to execute RENAME TABLE statement using the JDBC function executeUpdate() as −

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

To rename a table into MySQL database through Python program, we need to execute RENAME TABLE statement using the execute() function of the MySQL Connector/Python as −

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

Output

The output obtained is as follows −

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

Output

The output produced is as follows −

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

Output

The output obtained is as shown below −

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

Output

Following is the output of the above code −

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

Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc