4

I have a table in my ticketing system with 15,000 records, each containing an email which has been converted into a message to be added to a ticket.

Our current problem is with collation - when I got my hands on the system it was using latin1_swedish_ci. However we use several European languages in the system and for that reason we need to be able to correctly store non-ASCII characters.

I was unable to get this to work with the latin1_swedish_ci collation but I have found on my test version of the system that switching the collation to utf8_bin solves the problem.

So I need to know if it will be safe to make this change to my table/column on the live system. Will this take a long amount of time (PHPMyAdmin is pretty horrible when you try to make it work really hard) or will it corrupt any existing data?

Alasdair Stark
  • 1,227
  • 11
  • 32
  • The problem won't be the database itself; the question is whether the *ticketing system* can deal with UTF-8 data. – Pekka Sep 08 '11 at 10:14

1 Answers1

7

It's safe to convert from one charset to an other when all characters from the first charset are representable in the second charset.

This is the case for latin1 to utf8: it's safe.

However you have to ensure that the application itself can handle utf8 data.

On utf8_bin: The utf8 part is the charset (how characters are encoded) and the bin part is the collation. Don't use bin, it would make everything case-sensitive, which is probably not what you expect. Try utf8_unicode_ci instead. (See http://dev.mysql.com/doc/refman/5.5/en/charset-unicode-sets.html )

Arnaud Le Blanc
  • 98,321
  • 23
  • 206
  • 194
  • Great, and when I perform the alter column operation will it perform some conversion on each of the 15,000 rows? I am worried about this as I can't even dump this amount of data from PHPMyAdmin without it going unresponsive – Alasdair Stark Sep 08 '11 at 10:17
  • Yes, it will convert the data. (As usual, do a backup before that, just in case.) – Arnaud Le Blanc Sep 08 '11 at 10:18
  • Also try `ALTER TABLE foo CONVERT TO CHARACTER SET ...` to convert all columns at once. – Arnaud Le Blanc Sep 08 '11 at 10:19
  • Thanks - I will try to do a backup then, but as I said, PHPMyAdmin doesn't really like to do that! – Alasdair Stark Sep 08 '11 at 10:21
  • 1
    Just to let everyone know - I did this tonight and it *didn't* convert any data. I did have a full backup just in case but it was not required as the change was executed immediately with no issues. – Alasdair Stark Sep 09 '11 at 19:01