Solved

MySQL Converting from latin1 to utf8

Posted on 2010-11-13
3
1,413 Views
Last Modified: 2012-05-10
I'm a little confused as to how to proceed. I have a number of tables that now need to be able to store their Text and VarChar fields as UTF8 so as to support the Japanese AND English character set.

I'm new to using MySQL Workbench but from what I can tell, my tables are stored with the latin1 collation and all of the Text and VarChar fields use their table's default collation.

I have been able to place Japanese characters into a field from a command line session (SSH in to the box and then ran mysql from the command prompt) using an update command:

Update Products Set ProductName = "This is ¿¿¿¿¿ test" Where ProductNo = 1011

When I issue a "Select ProductName From Products Where ProductNo = 1011", MySQL shows me the correct characters. My Putty session is using utf8 and seems to display Japanese characters with ease.

The problem is that if I look at the data using Workbench, the Japanese characters are replaced with accented European characters. I suspect that this is because the default collation is latin1 and Putty or MySQL is translating things so they seem to work.

Q: Can I use the Alter Table command (or the Workbench Alter table dialog) to change the collation on each table to "utf8 - default collation" resulting in the current English text remaining intact (since it is compatible with the first 256 characters of utf8) and then I'll be able to populate and retrieve data from now on in utf8 without having any weird conversion issues?





I'm using MySQL 5.0.77 and MySql Workbench with an SSH connection for DB admin.
0
Comment
Question by:ou81aswell
  • 2
3 Comments
 
LVL 77

Accepted Solution

by:
arnold earned 500 total points
ID: 34129524
http://dev.mysql.com/doc/refman/5.1/en/alter-table.html

alter table tablename convert to character set UTF8 collation=utf8

You may need to run the same thing on the database level
alter database
http://dev.mysql.com/doc/refman/5.0/en/alter-database.html
0
 

Author Comment

by:ou81aswell
ID: 34129567
Thanks.

Can you confirm that going from latin1 to utf8 will not result in any weird character conversions. All of the data in there right now is 7-bit ascii with no control characters (I guess the Text fields have have line separators)?

I want to do this with MySql Workbench but am a bit confused because it only gives me the option to set the collation. There is no option to set the character set although in the description is makes it sound like collation is the same thing as character set. I have attached a screen shot.

I'm also confused as to which utf8 to choose. As you can see in the screen shot, there is utf8 default, general, bin, unicode followed by a bunch of language/country options. I was hoping to not have to be specific since I want to be able to use other languages as well in the future.

 MySQL Workbench Alter Table Dialog
0
 
LVL 77

Expert Comment

by:arnold
ID: 34129933
I have to look at workbench to see which option you should use:
http://dev.mysql.com/doc/refman/5.0/en/charset-charsets.html describes the character sets/collation options.

You may want to first copy the current database into a new one and then try your changes on the test database to make sure the change does not have an adverse impact.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Update Query 23 93
mySql Syntax 7 44
Best database setup for image uploads 6 52
MySQL ERROR 1045 (28000) 2 67
More Fun with XML and MySQL – Parsing Delimited String with a Single SQL Statement Are you ready for another of my SQL tidbits?  Hopefully so, as in this adventure, I will be covering a topic that comes up a lot which is parsing a comma (or other…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now