Fix SQL garbled characters in normal database files on the MySQL page

Source: Internet
Author: User
Tags wordpress database
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!

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.