Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a vb forum with a huge amount of data

I need a way to convert this forum database tables charset with thier data from

latin1_swedish_ci to utf8-general-ci

How to do this ?

share|improve this question
add comment

2 Answers 2

http://dev.mysql.com/doc/refman/5.1/en/alter-table.html

If you want to change the table default character set and all character columns (CHAR, VARCHAR, TEXT) to a new character set, use a statement like this:

ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name;

Follow the link for more details.

Be sure to do this in a test enviornment first. You probably wont run into many issues going from latin1 => utf8 but in general you might run into collation "weirdness" when converting charsets. This is especially true if you have any kind of unique constraints on char-ish columns.

Also keep in mind if you do have any char or varchar columns indexed moving to utf-8 will triple the amount of memory needed

share|improve this answer
    
When you move to utf8 you'll need to run "set names utf8" if interacting via commandline and all clients doing work with the DB should specify the charset they're using as well. A latin 1 é and a utf 8 é are very different things. –  atxdba Sep 18 '11 at 17:39
    
What is your reference for UTF-8 requiring triple the memory of latin1? One of the main benefits of UTF-8 is the small storage size for characters from the ASCII and latin1 sets. –  bignose Sep 22 '11 at 2:26
    
@bignose for one: dev.mysql.com/doc/refman/5.5/en/alter-table.html go down to the 'convert to' section. Describes a caveat that utf8 may require up to 3 bytes, instead of 1 for latin1. –  Derek Downey Jan 4 '12 at 20:08
add comment

You may want to consider setting the database's default character for new tables going forward using ALTER DATABASE. Here is an example using MySQL 5.5.12 for Windows:

mysql> show create database example;
+----------+--------------------------------------------------------------------+
| Database | Create Database                                                    |
+----------+--------------------------------------------------------------------+
| example  | CREATE DATABASE `example` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+--------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> alter database example default character set utf8;
Query OK, 1 row affected (0.01 sec)

mysql> show create database example;
+----------+------------------------------------------------------------------+
| Database | Create Database                                                  |
+----------+------------------------------------------------------------------+
| example  | CREATE DATABASE `example` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+------------------------------------------------------------------+
1 row in set (0.00 sec)

Give it a Try !!!

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.