Rename MySQL table via Command Line

Posted on

This is actually a simple explanation in order to rename a single table in MySQL database. There are a simple command which can be used in order to rename a single table in MySQL database. Below are several steps which is needed to be executed in order to achieve that goal. These are the steps which is needed to be followed :

1. Log in to MySQL command console.

By logging in to MySQL command console, we can further execute the command which is going to rename a single table in MySQL database. Accessing MySQL Database Server can be read in the following article. Below is the display’s execution :

user@hostname:~$ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.7.12-0ubuntu1.1 (Ubuntu)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

2. After successfully connecting to MySQL Database Server, select the correct database where the intended table which is going to be renamed by executing the following SQL Query command in MySQL command console exists :

use database_name

Description : 
use : It is an SQL query command used to select or to activate the database which is going to be operated
database_name : It is the name of the database which is chosen to be operated further.

For an example if the database name is list, therefore the command executed must be typed as shown below :

mysql> use list; 
Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed 
mysql>
  1. Soon as the selection of the database where the intended table to be renamed exist has already success, execute the following SQL Query command to display tables exists in the database selected as follows :
show tables
mysql> show tables; 
+----------------+ 
| Tables_in_list | 
+----------------+ 
| file           | 
+----------------+ 
1 row in set (0,00 sec) 
mysql> 

Based on the above SQL Query command’s execution, the output shown that the table is only one which named file. Now, the main purpose is trying to rename the table named file into files. Below is the command execution to rename the table :

mysql> alter table rename file files; 
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'rename file files' at line 1 
mysql> rename table file files; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'files' at line 1 mysql> rename table file to files; Query OK, 0 rows affected (0,14 sec) mysql>

There are several experiment above to check which one is the correct SQL query to rename the table. But apparently the correct one is shown in the following pattern :

rename table old_table_name to new_table_name

Description : 
rename : It is an SQL command which is used in MySQL Database Server to rename, in this context table name
table : It is additional parameter for the SQL command 'rename' to specify that the renaming target is a table
old_table_name : It is the old table name
to : It is a part of reserved keyword for renaming table pattern for SQL in MySQL Database Server.  
new_table_name : It is the new table name

So,the old table name is file and the new one is files. Try to make sure that the table name has changed by re-execute the fommand :

mysql> show tables;
+----------------+
| Tables_in_list |
+----------------+
| files          |
+----------------+
1 row in set (0,00 sec)

mysql>

so, the command has already executed successfully.

Leave a Reply