MySQL bulk modify table prefix and table name SQL statement

Method One: Use SQL statement to modify MySQL database table prefix name

The first thing we think of is to use SQL query statements to modify, this method is also very convenient, just enter the phpMyAdmin, in the Run SQL query box to enter the following name on it.

ALTER table The original table name RENAME to new table name;

such as: ALTER TABLE old_post RENAME to New_post;

SQL query statement has a disadvantage, that is, a SQL statement can only modify the table name of a database, if you want to accurately modify a table, very useful. It is not recommended if you have a lot of database tables. Have wood there, a method class that modifies multiple database table prefix names in batches? Yes, please see the introduction below

Cases

The code is as follows	Copy Code
Select CONCAT (' ALTER TABLE ', table_name, ' RENAME to ', table_name, '; ') From Information_schema.tables Where table_name like ' uc_% ';

Note: Like ' uc_% ' where uc_ is the table prefix you need to replace.

The following code is encountered today, the head of the table is DB, but no line appears very messy, so the batch will "Dbtable_name" into "Db_table_name"
The main function is the substring function of MySQL

SUBSTRING (str,pos) syntax

SUBSTRING (filed,m): Intercepts the filed field from the beginning of the first m characters to the end of the string;

SUBSTRING (filed,m,n): intercepts a string of n that is the length of the filed field starting with the first m character;

STR, character

POS, starting from the first few to fetch

The code is as follows	Copy Code
Select CONCAT (' ALTER TABLE ', table_name, ' RENAME to Db_ ', substring (table_name,3), '; ') From Information_schema.tables Where table_name like ' db% '; Heavy to result ALTER TABLE uc_aaa RENAME to UC_AAA; ALTER TABLE uc_bbb RENAME to UC_BBB;

Bulk copy to Notepad or et such as editing tools, and then bulk replace RENAME to UC into RENAME to you want the table prefix
Completed before execution.

This completes the table name bulk modification Pull ...

Method Two: Php script batch modify MySQL database table prefix

After testing, successfully modified. If you need to, please refer to the following reference:

1, the following code to copy to Notepad, according to their own situation to modify the database information, and save the editdata.php.

The code is as follows

Copy Code

<?php Set up relevant information. $dbserver = ' localhost ';//connected server is typically localhost $dbname = ' newdata ';//Database name $dbuser = ' root ';//database user name $dbpassword = ' 123456 ';//Database Password $old _prefix= ' old_ ';//prefix of database $new _prefix= ' new_ ';//database prefix revision 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 the table name in $data while ($row = Mysql_fetch_row ($result)) { $data [] = $row [0]; } Filter the name of the table 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]; $tab _name[$k] = str_replace ($old _prefix, ", $v); } } if ($preg) { Echo ' <pre> '; Print_r ($tab _name); Exit (); Bulk renaming 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 related database table; if (Mysql_free_result ($result)) { return true; } } ?>

Bulk Delete Table is also very simple

The code is as follows	Copy Code
Select CONCAT (' drop table ', table_name, '; ') From Information_schema.tables Where table_name like ' uc_% ';

Note: Like ' uc_% ' where uc_ is the table prefix you need to replace.
Executing the query automatically generates SQL statements such as DROP table table_name.
You can then copy the drop statement to perform the delete operation.