To solve this problem, there are usually two methods on the net, with a third method at the bottom, as suggested by the official documentation.
The first method is to batch generate ALTER TABLE's database modification statements, and then copy into the Text Processing tool, and then batch the prefix to modify the desired, and then paste into the MySQL tool execution window, so that the ability to modify the data table prefix, as follows:
Select Concat (' ALTER TABLE ', TABLE_NAME, ' Rename to ', table_name) from Information_schema.tables where table_name like ' XX _%‘;
This morning xx is the old table prefix in your database, after like this part is specifying that all the matching table prefixes are stitched into an ALTER TABLE statement that can be executed independently;
The second method is to write an execution script yourself, and then run the script to implement a batch conversion of the database table prefixes, as follows:
<?php//set up the relevant information echo ' <meta charset= ' utf-8 > '; $dbserver = ' localhost '; $dbname = ' AA ';//Replace with your database name $dbuser= ' root ';//replace your database user name $dbpassword= ';//replace your database password $old_prefix= ' bb_ ';//modify the table prefix $new_prefix= ' aa_ ';//modified table prefix 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 table names in the database $result = Mysql_list_tables ($dbname);//This is an official document that does not establish the use of the IF (! $result) {print "DB Error, could not list table \ n "; Print "MySQL Error:". Mysql_error (); Exit;} Add the table name to $datawhile ($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, $v 1); if ($preg) {$tab _name[$k] = $v 1[2]; $table _name[$k] = str_replace ($old _prefix, ", $v); }}if ($preg) {foreach ($tab _name as $k = + $v) {$sql = ' RENAME TABLE '. $old _prefix. $v. $new _Prefix. $v. "; mysql_query ($sql); } print "Data table prefix:". $old _prefix. " <br> "." has been modified to: ". $new _prefix." <br> ";} else{print "Your data table prefix". $old _prefix. " Input error. Please check the relevant data table prefix "; if (Mysql_free_result ($result)) {return true; }}
The third Way, because Mysql_list_tables () This method is officially not recommended, so the entire code, that is, this part of the change, the other part, similar to the above, as follows:
<?php//set up the relevant information echo ' <meta charset= ' utf-8 > '; $dbserver = ' localhost '; $dbname = ' AA ';//Replace with your database name $dbuser= ' root ';//replace your database user name $dbpassword= ';//replace your database password $old_prefix= ' bb_ ';//modify the table prefix $new_prefix= ' aa_ ';//modified table prefix 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 (where there is a difference from the second method) $data = Array (), $sql = "Use AA";//Specify which database mysql_query ($sql) is used; $sql = "SHOW TABLES"; $result = mysql_query ($sql), if (! $result) {echo "DB Error, could not list tables\n"; Echo ' MySQL Error: '. Mysql_error (); Exit;} Add the table name to $datawhile ($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, $v 1); if ($preg) {$tab _name[$k] = $v 1[2]; $table _name[$k] = str_replace ($old _prefix, ", $v); }}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 "Your data table prefix". $old _prefix. " Input error. Please check the relevant data table prefix "; if (Mysql_free_result ($result)) {return true; }}
PHP implementation of bulk Modification table Prefix method Introduction ~