Subscribe to RSS Subscribe to Comments Blog of Roy Chan

Blog of Roy Chan

Fix the MySQL character set problem after MySQL 4.1 forever!

I suppose many people had mention it before. It is talk about the output of your database become rubbish after you upgrade from older MySQL to 4.1. Experience people should senses that it is the problem because MySQL 4.1 get more support on multi-language and charset. You or me may already got the same experience in Oracle/Java and know that we should correct the charset of the MySQL server and client. However, the solution in people’s blog is in chaos.

Some ask you to “SET NAME utf8″ after each connection. Oh, that you need to search “mysql_connect” or “mysql_pconnect” in all your PHP application and put “mysql_query('SET NAME utf8')” after them. You may look through your php.ini or httpd.conf and see if you change the default client charset in PHP/apache but the fact is that the default client charset is fixed libmysql before compilation. You don’t want to compile libmysql again by yourself, right? Some ask you to put init-connect='SET NAME utf8‘ in my.cnf but it never works. Some said it should be init_connect instead of init-connect but still no luck. Some ask you to set default-character-set in section [mysql] of my.cnf but you want PHP will connect with UTF-8 by default too. Ok, here is the solution. What you need is add “default-character-set=utf8 in both [client] and [mysqld] sections in your my.cnf:

[client]
default-character-set=utf8

[mysqld]
default-character-set=utf8

The MySQL will use UTF8 after restart it. You can check it by typing the following in mysql:

SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';

Reference


Share It: [del.icio.us] [Technorati] [Google Bookmark] [Yahoo MyWeb] [Furl]


Comments

  1. February 22nd, 2007 | 18:48

    Thanks Roy! This worked for me even if my linobox had some other encoding issue I had to solve before seeing all stuff working correctly!
    If you have some trouble converting MySQL dbs from one charset to another, I’ve found this page: http://climbtothestars.org/archives/2004/07/18/converting-mysql-database-contents-to-utf-8/ very useful!

    Thanks again! )

Leave a reply

  • :-)
  • :-D
  • :lol:
  • ;-)
  • :-P
  • :-(
  • :'(
  • :'-(
  • >:-(
  • :-O
  • :annoy:
  • :appeal:
  • :asleep:
  • Zzz...
  • :-Q
  • 8-)
  • B-)
  • :clap:
  • :enjoy:
  • :blush:
  • :shy:
  • :*
  • :inlove:
  • :inlove2:
  • :inlove3:
  • :love:
  • :allure:
  • 8D~~~
  • :amative:
  • :chatter:
  • :bored:
  • bored2:
  • X-O~~~
  • :-'|
  • 8-S
  • #_#
  • :dontknow:
  • :embarrased:
  • :excite:
  • :faint:
  • :fuzzy:
  • :plan:
  • :conceal:
  • :regret:
  • :punched:
  • :silent:
  • :-X
  • :tease:
  • :vent:
  • :win:
  • :work:
  • :good:
  • :shit:
  • :bomb:

Based on Fluidity© 1998-2007 Roy Hiu-yeung Chan