Changing database encoding from latin1 to UTF8

Hi,

We would like to re-encode our MySQL database fro Latin1 to UTF8 and I read that its not just changing the Database encoding to UTF8 but also changing existing characters entered in the database to UTF8.

I did some search and found out that the correct way to do this is using ICONV however I am new to this so I do not know how to do it. I have already installed the LibIconv software that I found in this link: http://gnuwin32.sourceforge.net/packages/libiconv.htm.

But I do not know where to start and what to do.

Here is my System:

OS: Windows XP SP2
SOftware for Testing: XAMMP

Can you tell me steo by step on how to change the encoding . I know this is the one to use:

iconv -f latin1 -t utf8 db_name_data.sql > db_name_data_utf8.sql

but I do not know where to do this. Here is the source that I found.
http://www.khelll.com/blog/mysql/changing-database-encoding-from-latin-to-utf8/

Thanks.
openaccount1Asked:
Who is Participating?
 
karoldvlConnect With a Mentor Commented:
Fire up the console ("cmd"). Go to your XAMPP directory - for instance:
cd c:\xampp\mysql\bin

Then follow the tutorial you've linked (http://www.khelll.com/blog/mysql/changing-database-encoding-from-latin-to-utf8/).

If it's not clear, update us with the steps you're having problems with. By the way, what's the size of your database? If it's not that big, you can also use a text editor (f.e. Notepad++) instead of iconv to change the encoding of the dump.
0
 
openaccount1Author Commented:
Having problem with the iconv step. It says that it is not recognized as an internal or external command. operatable program or a batch file.

Our database is very large. Also, if you have an easier way to do this can you send the information. e.g. it can be a program where we only need to specify the sql file and it will d the conversion.

Thanks.
0
 
karoldvlCommented:
You will need to provide full path to the iconv program in order to execute it.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
openaccount1Author Commented:
I am not really a programmer and just ding the steps in MS Dos. Can you tell me step by step how to specify the iconv path. Thanks
0
 
karoldvlConnect With a Mentor Commented:
I mean here:
iconv -f latin1 -t utf8 db_name_data.sql > db_name_data_utf8.sql

you have to give full path to iconv (where you installed it):
C:\path\to\iconv_directory\iconv -f latin1 -t utf8 db_name_data.sql > db_name_data_utf8.sql

This should do the trick. If not, update us further.
0
 
openaccount1Author Commented:
I checked my Database after following all instruction in the page mentioed above even after using iconv. My pages are still in Latin1 format (e.g. characters are in entity form) even though the encoding is UTF-8 already.
0
 
openaccount1Author Commented:
Any other way to convert our database to UTF8 including th current texts in the database? This means that the entity forms will be converted to their special character equivalent because UTF8 does not use entity forms on these characters. Å, Ë,Ñ, Ü, ß etc... but after doing the steps these characters are still in entity form but with UTF8 they should not be.
0
 
karoldvlCommented:
Could you give an example of how your current data looks like (after conversion)?
0
 
openaccount1Author Commented:
Hi,


I have attached a sample Dbase after using icnov to convert to UTF8.

Aries
sample-data-utf8.sql
sample-schema-utf8.sql
0
 
karoldvlCommented:
OK, I see. So this is a different problem you're having right now.

We want to decode the entities into their normal form. I doubt iconv can be used for it.

There are probably three ways to deal with it I can think of right now.

1) Make a search & replace in the dump using a text editor (not feasible due to dump size, right?) or some batch script (not so straightforward on Windows).
2) Make some batch PHP script to parse your database data or dump, use <a href="http://www.php.net/manual/en/function.html-entity-decode.php">html-entity-decode</a> to convert those entites (not very efficient).
3) Make your database do it for you (if you have the data in place). Should be efficient, but mundane. I think something like this should work:
UPDATE comments SET name = REPLACE(name, "&pound;", "£");
You would have to repeat it for all entities you have, and text fields. See for a start whether it works at all at some small dataset, as I'm not 100% sure about this.


0
 
openaccount1Author Commented:
Thnaks for showing how to use iconv. for the changing of entity to special character, I will try to find a quick solution. Thanks
0
All Courses

From novice to tech pro — start learning today.