Methods of converting a database to UTF-8

Methods of converting a database to UTF-8

UTF-8 in mySQL simply signifies a proprietary character encoding. It is very important especially when a database stores text based information.
UTF-8 permits a wide range of characters to be properly stored, sorted and manipulated by the database.
Here at Fixwebnode, as part of our Website Support Services, we regularly help our Customers to perform SQL Databases Operations and Tasks.
In this context, we shall look into the steps to take in order to Convert a Database to UTF-8.

How to Convert a Database to UTF-8?

To convert an existing database table to UTF-8, there a mini script which will make the process easy.
The Script is shown below for your information;
<?php   

// Fill in your Server, User, Database, Password, and Collation configuration below


    $db_server = 'localhost';    

    $db_user = 'database user';  

 

    $db_password = 'password';   

 

    $db_name = 'database name';  

 

    $char_set = 'new character set';   

 

 // Adds the header information header('Content-type: text/plain');   

 // Connects to the MySQL database                               


    $connection = mysql_connect($db_server, $db_user, $db_password) or die(mysql_error() );       

    $db = mysql_select_db($db_name) or die( mysql_error() );  

 

 // Runs the SQL query on teh database      


    $sql = 'SHOW TABLES'; $result = mysql_query($sql) or die( mysql_error() );


 // Runs a loop that finds all collations within the database and changes it to the new collation   


       while ( $row = mysql_fetch_row($result) )   

        {  

            $table = mysql_real_escape_string($row[0]);    

            $sql = "ALTER TABLE

            $table CONVERT TO CHARACTER SET

            $char_set COLLATE utf8_general_ci";

                mysql_query($sql) or die( mysql_error() );  

                print "$table changed successfully.n";      

         }    


 // Update the Collation of the database itself  


    $sql = "ALTER DATABASE CHARACTER SET $char_set;";  

            mysql_query($sql) or die( mysql_error());     

            print "Database collation has been updated successfully.n";       

 // close the connection to the database   mysql_close($connection);          

 

 ?>  

To use this script, save it as "change.php" and then upload it to your website root directory. In this script file, you need to modify its contents to implement the correct database connection parameters and the character set as per your case.
Then, run the script on a web browser, let say your website domain is "domain.com", then the your will enter the following URL on the browser as shown below;
https://domain.com/change.php

It is important to note that this script will work with any character set you want your database table to work with.
You will need to define the character set in the script to change character sets as shown below:
$char_set = ‘character set’;

In any case, all you need to do is to change the "utf8_general_ci" to match the character set as defined in the step above.
For example, if you wish to change the character set to “Hebrew”, then your will have to modify the line in the script as shown below:
$sql = “ALTER TABLE $table CONVERT TO CHARACTER SET $char_set COLLATE hebrew_general_ci”;

More information about changing MySQL character sets and collation  can be found at Character Sets and Collations in MySQL.

Need support in configuring your Website Databases and MySQL Queries? We are available to help your today.

 


Conclusion

Changing MySQL character sets and collation of Databases. This article will guide you on how to Convert a Database table character set to UTF-8.


icon

Sign up to our Newsletter

...and get one free support ticket.