Working with Strings - MySQL - page 146

• If you omit both CHARACTER SET and COLLATE , the column is assigned the table
default character set and collation. A table definition can include those attributes
following the closing parenthesis at the end of the CREATE TABLE statement. If
present, they apply to columns that have no explicit character set or collation of
their own. If omitted, the table defaults are taken from the database defaults. You
can specify the database defaults when you create the database with the CREATE
DATABASE statement. The server defaults apply to the database if they are omitted.
The server default character set and collation are latin1 and latin1_swedish_ci , so
strings by default use the latin1 character set and are not case sensitive. To change this,
set the character_set_server and collation_server system variables at server start‐
up (see Recipe 22.1 ).
MySQL also supports ENUM and SET string types, which are used for columns that have
a fixed set of permitted values. The CHARACTER SET and COLLATE attributes apply to these
data types as well.
5.3. Setting the Client Connection Character Set
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 may need to tell MySQL the appropriate character set. 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 have Greek data, displaying it using latin1 will result
in gibberish on your screen. If you use Unicode strings in the utf8 character set, lat
in1 might not be sufficient to represent all the characters that you might need.
To deal with this problem, configure your connection to use the appropriate character
set. You have several ways to do this:
• Issue a SET NAMES statement after you connect:
mysql> SET NAMES 'utf8';
SET NAMES permits the connection collation to be specified as well: