MySQL Cookbook, 2nd Edition

Setting the Client Connection Character Set Properly

Problem

You’re executing SQL statements or producing query results that don’t use the default character set.

Solution

Use SET NAMES or an equivalent method to set your connection to the proper character set.

Discussion

When you send information back and forth between your application and the server, you should tell MySQL what the appropriate character set is. For example, the default character set is latin1, but that may not always be the proper character set to use for connections to the server. If you’re working with Greek data, trying to display it using latin1 will result in gibberish on your screen. If you’re using Unicode strings in the utf8 character set, latin1 might not be sufficient to represent all the characters that you might need.

To deal with this problem, configure your connection with the server to use the appropriate character set. There are various ways to do this:

• If your client program supports the --default-character-set option, you can use it to set the character set at program invocation time. mysql is one such program. Put the option in an option file so that it takes effect each time you connect to the server:

  [mysql]
  default-character-set=utf8

• Issue a SET NAMES statement after you connect:

  mysql>SET NAMES 'utf8';

  SETNAMES also allows the connection collation to be specified:

  mysql>SET NAMES 'utf8' COLLATE 'utf8_general_ci';

• Some programming interfaces provide their own method of setting the character set. MySQL ...