MySQL page normal database file SQL garbled if your database is originally gbk encoding, save the following code as a PHP file, remember to fill in the following database name and database account password
MySQL page normal database file SQL garbled if your database is originally gbk encoding, save the following code as a PHP file, remember to fill in the following database name and database account password
MySQL page normal database file SQL garbled
If your database is originally gbk encoded, save the following code as a PHP file. Remember to fill in the following database name and password
Define ('db _ name', 'putyourdbnamehere '); // Database NAME
Define ('db _ user', 'usernamehere '); // MySQL USER name
Define ('db _ password', 'yourpasswordhere '); // PASSWORD
Define ('db _ host', 'localhost'); // you do not need to modify this item.
Function gbk_DB_Converter_DoIt (){
$ Tables = array ();
$ Tables_with_fields = array ();
// Since we cannot use the WordPress Database into action Class (wp-db.php ),
// We have to make an stand-alone/direct connection to the database.
$ Link_id = mysql_connect (DB_HOST, DB_USER, DB_PASSWORD) or die ('error establishing a database connection ');
Mysql_select_db (DB_NAME, $ link_id );
// Gathering information about tables and all the text/string fields that can be affected
// During the conversion to gbk.
$ Resource = mysql_query ("show tables", $ link_id );
While ($ result = mysql_fetch_row ($ resource ))
$ Tables [] = $ result [0];
If (! Empty ($ tables )){
Foreach (array) $ tables as $ table ){
$ Resource = mysql_query ("EXPLAIN $ table", $ link_id );
While ($ result = mysql_fetch_assoc ($ resource )){
If (preg_match ('/(char) | (text) | (enum) | (set)/', $ result ['type'])
$ Tables_with_fields [$ table] [$ result ['field'] = $ result ['type']. "". ("YES" = $ result ['null']? "": "NOT"). "NULL ".(! Is_null ($ result ['default'])? "DEFAULT '". $ result ['default']. "'": "");
}
}
// Change all text/string fields of the tables to their corresponding binary text/string representations.
Foreach (array) $ tables as $ table)
Mysql_query ("alter table $ table convert to character set binary", $ link_id );
// Change database and tables to gbk Character set.
Mysql_query ("alter database". DB_NAME. "character set gbk", $ link_id );
Foreach (array) $ tables as $ table)
Mysql_query ("alter table $ table convert to character set gbk", $ link_id );
// Return all binary text/string fields previusly changed to their original representations.
Foreach (array) $ tables_with_fields as $ table => $ fields ){
Foreach (array) $ fields as $ field_type => $ field_options ){
Mysql_query ("alter table $ table MODIFY $ field_type $ field_options", $ link_id );
}
}
// Optimize tables and finally close the mysql link.
Foreach (array) $ tables as $ table)
Mysql_query ("optimize table $ table", $ link_id );
Mysql_close ($ link_id );
} Else {
Die ('There are no tables?');
}
Return true;
}
Gbk_DB_Converter_DoIt ();
?>
You can run it on a php site that can access the database.
If your database is UTF-8 encoded, run the following code. Remember to fill in the following database name and password
Define ('db _ name', 'putyourdbnamehere '); // Database NAME
Define ('db _ user', 'usernamehere '); // MySQL USER name
Define ('db _ password', 'yourpasswordhere '); // PASSWORD
Define ('db _ host', 'localhost'); // you do not need to modify this item.
Function UTF8_DB_Converter_DoIt (){
$ Tables = array ();
$ Tables_with_fields = array ();
// Since we cannot use the WordPress Database into action Class (wp-db.php ),
// We have to make an stand-alone/direct connection to the database.
$ Link_id = mysql_connect (DB_HOST, DB_USER, DB_PASSWORD) or die ('error establishing a database connection ');
Mysql_select_db (DB_NAME, $ link_id );
// Gathering information about tables and all the text/string fields that can be affected
// During the conversion to UTF-8.
$ Resource = mysql_query ("show tables", $ link_id );
While ($ result = mysql_fetch_row ($ resource ))
$ Tables [] = $ result [0];
If (! Empty ($ tables )){
Foreach (array) $ tables as $ table ){
$ Resource = mysql_query ("EXPLAIN $ table", $ link_id );
While ($ result = mysql_fetch_assoc ($ resource )){
If (preg_match ('/(char) | (text) | (enum) | (set)/', $ result ['type'])
$ Tables_with_fields [$ table] [$ result ['field'] = $ result ['type']. "". ("YES" = $ result ['null']? "": "NOT"). "NULL ".(! Is_null ($ result ['default'])? "DEFAULT '". $ result ['default']. "'": "");
}
}
// Change all text/string fields of the tables to their corresponding binary text/string representations.
Foreach (array) $ tables as $ table)
Mysql_query ("alter table $ table convert to character set binary", $ link_id );
// Change database and tables to UTF-8 Character set.
Mysql_query ("alter database". DB_NAME. "character set utf8", $ link_id );
Foreach (array) $ tables as $ table)
Mysql_query ("alter table $ table convert to character set utf8", $ link_id );
// Return all binary text/string fields previusly changed to their original representations.
Foreach (array) $ tables_with_fields as $ table => $ fields ){
Foreach (array) $ fields as $ field_type => $ field_options ){
Mysql_query ("alter table $ table MODIFY $ field_type $ field_options", $ link_id );
}
}
// Optimize tables and finally close the mysql link.
Foreach (array) $ tables as $ table)
Mysql_query ("optimize table $ table", $ link_id );
Mysql_close ($ link_id );
} Else {
Die ('There are no tables?');
}
Return true;
}
UTF8_DB_Converter_DoIt ();
?>
After running, log on to phpmyadmin and check whether the garbled data has been restored!