MySql varchar change from Latin1 to UTF8

Question

In a mySql table I'm using Latin1 character set to store text in a varchar field. As our website now is supported in more countries we need support for UTF8 instead. What will happen if I change these fields to UTF8 instead? Is it secure to do this or will it mess up the data inside these fields? Is it something I need to think about when changing the field to UTF8?

Thanks!

Porcelain

Answer 1

MySQL handles this nicely:

CREATE TEMPORARY TABLE t1 (
  c VARCHAR(10)
) CHARACTER SET ="latin1";

INSERT INTO t1 VALUES ("æøå");
SELECT * FROM t1; # 'æøå'

ALTER TABLE t1 CHARACTER SET = "utf8";
SELECT * FROM t1; # 'æøå'

DROP TEMPORARY TABLE t1;

EDIT: And there are no latin-1 characters that cannot be stored as utf-8, so you shouldn't get any dataloss

Borman

Thanks! I'm using Navicat, can I (instead of your solution) in Design table mode just change from Latin1 to UTF8? (Which will alter the table) – Porcelain

I don't know Navicat, but I would guess it just does an alter table. Be sure to take a backup of your table first :) – Borman

Answer 2

There shouldn't be any problems on the database front. MySQL will handle the conversion of data between encodings at the time you make the change.

There are other things you need to be aware of when you add support for UTF-8 to your website.

• For best results, you should make sure that connections to the database are using UTF-8 as the connection character set. You can do this by issuing SET NAMES utf8 when you make the connection.
• You need to make sure that your web pages themselves are declared to the browser as encoded in UTF-8, by changing the meta tag.
• Security considerations: You should check user input to make sure it is valid UTF-8, to avoid the possibility of cross-site scripting (XSS) attacks

Burra