Implementation of modifying table name using SQL statement in MySQL

Time:2021-10-8

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 change 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. Look at the results again.


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

Supplement: modify table name, table attribute name, etc. in MySQL

Alter table name change original column name new column name type– Modify the column property name of the table

Alter table name modify column name type– Modify the class type of the table

Alter table name DROP column name– Delete a column of a table

Alter table name add column name type– Add a column

Alter table name rename new table name– Modify 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 modify only one table


show tables;

1.


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

Batch copy to Notepad + +, only retain SQL statements, and then copy 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_';// Prefix of database
$new_ prefix='new_';// Modify the prefix of the database to
if ( !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 the 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;
}
//Save table name in $data
 while ($row = mysql_fetch_row($result)) {
 $data[] = $row[0];
}
//Filter the table names whose prefixes you want to modify
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 modified to:. $new_ prefix."<br>"; 
}Else {print the prefix of your database table. $old_prefix. Input error. Please check the prefix of the relevant database table; 
 if ( mysql_free_result($result) ){
 return true;
 }
}
?>

Due to MySQL_ list_ The tables method is out of date. When you run the above program, you will be prompted that the method is out of date

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

Set error in php.ini_ Reporting, no method obsolete prompt is displayed

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_%';

When the query is executed, drop table will be generated automatically_ SQL statements like name

The above is my personal experience. I hope I can give you a reference, and I hope you can support developpaer. If you have any mistakes or don’t consider completely, please don’t hesitate to comment.

Recommended Today

New childhood fairy tale [139] little prince 32 — I planted this tree

Pain makes people sober. Everything in life originally depends on themselves. The mercy of others can’t win a better future. A petite thief said, “this time… I planted the tree and this time… I opened the road. If you want to buy the road money this time… ························································································· Another tall thief who blocked the way […]