Oracle 5.0, Reference Manual

Page: 799 / 3232

Configuring the Character Set and Collation for Applications
779
• Specify character settings per database. For example, applications that use one database might
require
utf8
, whereas applications that use another database might require
sjis
.
• Specify character settings at server startup. This causes the server to use the given settings for all
applications that do not make other arrangements.
• Specify character settings at configuration time, if you build MySQL from source. This causes the
server to use the given settings for all applications, without having to specify them at server startup.
When different applications require different character settings, the per-database technique provides
a good deal of flexibility. If most or all applications use the same character set, specifying character
settings at server startup or configuration time may be most convenient.
For the per-database or server-startup techniques, the settings control the character set for
data storage. Applications must also tell the server which character set to use for client/server
communications, as described in the following instructions.
The examples shown here assume use of the
utf8
character set and
utf8_general_ci
collation.
Specify character settings per database. To create a database such that its tables will use a given
default character set and collation for data storage, use a
CREATE DATABASE
statement like this:
CREATE DATABASE mydb
DEFAULT CHARACTER SET utf8
DEFAULT COLLATE utf8_general_ci;
Tables created in the database will use
utf8
and
utf8_general_ci
by default for any character
columns.
Applications that use the database should also configure their connection to the server each time
they connect. This can be done by executing a
SET NAMES 'utf8'
statement after connecting. The
statement can be used regardless of connection method: The
mysql
client, PHP scripts, and so forth.
In some cases, it may be possible to configure the connection to use the desired character set some
other way. For example, for connections made using
mysql
, you can specify the
--default-
character-set=utf8
[266] command-line option to achieve the same effect as
SET NAMES
'utf8'
.
For more information about configuring client connections, see Section 10.1.4, “Connection Character
Sets and Collations” .
If you change the default character set or collation for a database, stored routines that use the
database defaults must be dropped and recreated so that they use the new defaults. (In a stored
routine, variables with character data types use the database defaults if the character set or collation
are not specified explicitly. See
Section 13.1.9, “
CREATE PROCEDURE
and
CREATE FUNCTION
Syntax” .)
Specify character settings at server startup. To select a character set and collation at server
startup, use the
--character-set-server
[403] and
--collation-server
[403] options. For
example, to specify the options in an option file, include these lines:
[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci
These settings apply server-wide and apply as the defaults for databases created by any application,
and for tables created in those databases.
It is still necessary for applications to configure their connection using
SET NAMES
or equivalent
after they connect, as described previously. You might be tempted to start the server with the
--init_connect="SET NAMES 'utf8'"
[456] option to cause
SET NAMES
to be executed
automatically for each client that connects. However, this will yield inconsistent results because the
init_connect
[456] value is not executed for users who have the
SUPER
[578] privilege.