Connections, Character Sets and Strings

(Page 1 of 4 )

In this second part of a series of articles that explain how to work with strings and string data, you'll learn how to write string literals and more. It is excerpted from chapter five of the MySQL Cookbook, Second Edition, written by Paul DuBois (O'Reilly; ISBN: 059652708X). Copyright © 2006 O'Reilly Media, Inc. All rights reserved. Used with permission from the publisher. Available from booksellers or direct from O'Reilly Media.

5.3  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';
  
  SET NAMES 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 Connector/J for Java clients is one such interface. It detects the character set used on the server side automatically when you connect, but you can specify a different set explicitly by using the characterEncoding property in the connection URL. The property value should be the Java-style character-set name. For example, to select utf8, you might use a connection URL like this:
  
     jdbc:mysql://localhost/cookbook?characterEncoding=UTF-8
  
  This is preferable to SET NAMES because MySQL Connector/J performs character set conversion on behalf of the application but is unaware of which character set applies if you use SET NAMES.

By the way, you should make sure that the character set used by your display device matches what you’re using for MySQL. Otherwise, even with MySQL handling the data properly, it might display as garbage. Suppose that you’re using the mysql program in a terminal window and that you configure MySQL to use utf8 and store utf8-encoded Japanese data. If you set your terminal window to use euc-jp encoding, that is also Japanese, but its encoding for Japanese characters is different from utf8, so the data will not display as you expect.

ucs2 cannot be used as the connection character set.

Next: 5.4 Writing String Literals >>
 

More Database Articles Articles
More By O'Reilly Media

Comments On: Connections, Character Sets and Strings

>>> Be the FIRST to comment on this article!

Buy this book...

This article is excerpted from chapter five of the MySQL Cookbook, Second Edition, written by Paul DuBois (O'Reilly; ISBN: 059652708X). Check it out today at your favorite bookstore. Buy this book now.