My database now and all tables inside collact set to latin1. I need to convert all to utf8 succesfuly without any problem or content losing. Mean, you should also change the whole gibberish over there and set the new collact as utf8. My database size is 103MB. Looking for best offers and someone experienced only! The database belong to vbulletin system. PM me your offers. Marco.
A website im supporting needs to have multilingual characters. The default character set for MySQL is latin1. This unfortunately will not support Chinese nor other wierd multibyte characters. It will quietly support them, but returns gibberish and will cause frustration all round. After digging around, the best character set to use is UTF8. To set the default charset for the server, the my.cfg/my.ini file has to be modified: default-character-set=utf8 Unfortunately, once a database and their tables have been defined as latin1, they remain as latin1 unless you run this for each database: alter database mydatabase charset=utf8; and for each table: alter table mytable charset=utf8; and for each varchar/char type column: alter table mytable alter column mycol charset=utf8; and repeat ad infinitum.... This is rather tedious and boring, so there should be a better way. And that is to dump out the sql, change the charset and dump it back in. Here is the script. ===== latin1ToUTF8.sh echo Script to convert MySQL latin1 charsets to utf8. echo Usage: $0 dbname echo 060329 yky Created. echo Dumping out $1 database mysqldump --add-drop-table $1 > db.sql mydate=`date +%y%m%d` echo Making a backup mkdir bak &> /dev/null cp db.sql bak/$1.$mydate.sql echo String replacing latin1 with utf8 cat db.sql | replace CHARSET=latin1 CHARSET=utf8 > db2.sql echo Pumping back $1 into database mysql $1 < db2.sql echo Changing db charset to utf8 mysql $1 -e "alter database $1 charset=utf8;" echo $1 Done! ====== There must be a better way ?! yk. http://yoonkit.blogspot.com/2006/03/mysql-charset-from-latin1-to-utf8.html