MySQL uses SQL statements to modify table names

In MySQL, you can use the SQL statement rename table to modify the table name.

The basic syntax of the SQL statement rename table to modify the table name is:

RENAME TABLE <old table name> TO <new table name>;

Let's modify the test table to test1 table.

1. First, check which tables are in the current database.

mysql> show tables;
+-------------------+
| Tables_in_cainiao |
+-------------------+
| test |
| test2 |
+-------------------+
2 rows in set (0.00 sec)

2. Perform the rename operation and change test to test1.

mysql> rename table test to test1;
Query OK, 0 rows affected (0.08 sec)

3. Check the results again.

mysql> show tables;
+-------------------+
| Tables_in_cainiao |
+-------------------+
| test1 |
| test2 |
+-------------------+
2 rows in set (0.00 sec)

Supplement: How to modify table names, table attribute names, etc. in MySQL

alter table table name change original column name new column name type; --modify the column attribute name of the table

alter table table name modify column name type; --Modify the class type of the table

alter table table name drop column name; --Delete a column of the table

alter table table name add column name type; -- add a column

alter table table name rename new table name; --modify the table name

Supplement: MYSQL batch modify table prefix and table name sql statement

Modify table name

ALTER TABLE original table name RENAME TO new table name;

One SQL statement can only modify one table

show tables;

SELECT
 CONCAT(
 'ALTER TABLE ',
 table_name,
 'RENAME TO db_',
 substring(table_name, 4),
 ';'
 )
FROM
 information_schema. TABLES
WHERE
 table_name LIKE 'ct%';

Copy the batch to Notepad++, keep only the SQL statements, and then copy them to MySQL for execution

2.php script batch modify mysql database table prefix

<?php
//Set relevant information $dbserver='localhost'; //The connected server is usually localhost
$dbname='corethink';//database name$dbuser='root';//database user name$dbpassword='root';//database password$old_prefix='ct_';//database prefix$new_prefix='new_';//database prefix is changed toif ( !is_string($dbname) || !is_string($old_prefix)|| !is_string($new_prefix) ){
 return false;
}
 
if (!mysql_connect($dbserver, $dbuser, $dbpassword)) {
 print 'Could not connect to mysql';
 exit;
}
//Get all table names in the database $result = mysql_list_tables($dbname);
if (!$result){
 print "DB Error, could not list tablesn";
 print 'MySQL Error: ' . mysql_error();
 exit;
}
//Store the table name in $data
 while ($row = mysql_fetch_row($result)) {
 $data[] = $row[0];
}
//Filter the table name to modify the prefix foreach($data as $k => $v){
 $preg = preg_match("/^($old_prefix{1})([a-zA-Z0-9_-]+)/i", $v, $v1);
 if($preg){
 $tab_name[$k] = $v1[2];
 }
}
if($preg){ 
 foreach($tab_name as $k => $v){
 $sql = 'RENAME TABLE `'.$old_prefix.$v.'` TO `'.$new_prefix.$v.'`';
 mysql_query($sql);
 }
 print data table prefix: .$old_prefix."<br>".has been changed to: .$new_prefix."<br>"; 
}else{ print The prefix of your database table.$old_prefix.Incorrect input. Please check the prefix of the relevant database table; 
 if (mysql_free_result($result)){
 return true;
 }
}
?>

Because the mysql_list_tables method is outdated, a message indicating that the method is outdated will be displayed when running the above program

Deprecated: Function mysql_list_tables() is deprecated in … on line xxx

Set error_reporting in php.ini to not display method deprecation prompts

error_reporting = E_ALL & ~E_NOTICE & ~E_DEPRECATED

3. Batch delete table

SELECT
 CONCAT(
 'drop table ',
 table_name,
 ';'
 )
FROM
 information_schema. TABLES
WHERE
 table_name LIKE 'uc_%';

Execute the query, and the SQL statement drop table table_name will be automatically generated.

The above is my personal experience. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM. If there are any mistakes or incomplete considerations, please feel free to correct me.

You may also be interested in: