Administering MySQL: International Usage and Log Files

4.7 MySQL Localization and International Usage

4.7.1 The Character Set Used for Data and Sorting

By default, MySQL uses the ISO-8859-1 (Latin1) character set with sorting according to Swedish/Finnish rules. These defaults are suitable for the United States and most of western Europe.

All MySQL binary distributions are compiled with --with-extra-charsets=complex. This adds code to all standard programs that enables them to handle latin1 and all multi-byte character sets within the binary. Other character sets will be loaded from a character-set definition file when needed.

The character set determines what characters are allowed in names. It also determines how strings are sorted by the ORDER BY and GROUP BY clauses of the SELECT statement.

You can change the character set with the --default-character-set option when you start the server. The character sets available depend on the --with-charset=charset and --with-extra-charsets= list-of-charsets | complex | all | none options to configure, and the character set configuration files listed in SHAREDIR/charsets/Index. See Section 2.3.2, "Typical configure Options."

As of MySQL 4.1.1, you can also change the character set collation with the --default-collation option when you start the server. The collation must be a legal collation for the default character set. (Use the SHOW COLLATION statement to determine which collations are available for each character set.) See Section 2.3.2, "Typical configure Options."

If you change the character set when running MySQL, that may also change the sort order. Consequently, you must run myisamchk -r -q --set-character-set=charset on all tables, or your indexes may not be ordered correctly.

When a client connects to a MySQL server, the server indicates to the client what the server's default character set is. The client will switch to use this character set for this connection.

You should use mysql_real_escape_string() when escaping strings for an SQL query. mysql_real_escape_string() is identical to the old mysql_escape_string() function, except that it takes the MYSQL connection handle as the first parameter so that the appropriate character set can be taken into account when escaping characters.

If the client is compiled with different paths than where the server is installed and the user who configured MySQL didn't include all character sets in the MySQL binary, you must tell the client where it can find the additional character sets it will need if the server runs with a different character set than the client.

You can do this by specifying a --character-sets-dir option to indicate the path to the directory in which the dynamic MySQL character sets are stored. For example, you can put the following in an option file:

[client]
character-sets-dir=/usr/local/mysql/share/mysql/

charsets

You can force the client to use a specific character set as follows:

[client]
default-character-set=charset

This is normally unnecessary, however.

4.7.1.1 Using the German Character Set

To get German sorting order, you should start mysqld with a --default-character-set=latin1_de option. This affects server behavior in several ways:

• When sorting and comparing strings, the following mapping is performed on the strings before doing the comparison:

  ä -> ae
  ö -> oe
  ü -> ue
  ß -> ss

• All accented characters are converted to their unaccented uppercase counterpart. All letters are converted to uppercase.

• When comparing strings with LIKE, the one-character to two-character mapping is not done. All letters are converted to uppercase. Accents are removed from all letters except Ü, ü, Ö, ö, Ä, and ä.

4.7.2 Setting the Error Message Language

By default, mysqld produces error messages in English, but they can also be displayed in any of these other languages: Czech, Danish, Dutch, Estonian, French, German, Greek, Hungarian, Italian, Japanese, Korean, Norwegian, Norwegian-ny, Polish, Portuguese, Romanian, Russian, Slovak, Spanish, or Swedish.

To start mysqld with a particular language for error messages, use the --language or -L option. The option value can be a language name or the full path to the error message file. For example:

shell> mysqld --language=swedish

Or:

shell> mysqld --language=/usr/local/share/swedish

The language name should be specified in lowercase.

The language files are located (by default) in the share/LANGUAGE directory under the MySQL base directory.

To change the error message file, you should edit the errmsg.txt file, and then execute the following command to generate the errmsg.sys file:

shell> comp_err errmsg.txt errmsg.sys

If you upgrade to a newer version of MySQL, remember to repeat your changes with the new errmsg.txt file.

4.7.3 Adding a New Character Set

This section discusses the procedure for adding another character set to MySQL. You must have a MySQL source distribution to use these instructions.

To choose the proper procedure, decide whether the character set is simple or complex:

• If the character set does not need to use special string collating routines for sorting and does not need multi-byte character support, it is simple.

• If it needs either of those features, it is complex.

For example, latin1 and danish are simple character sets, whereas big5 and czech are complex character sets.

In the following procedures, the name of your character set is represented by MYSET.

For a simple character set, do the following:

1. Add MYSET to the end of the sql/share/charsets/Index file. Assign a unique number to it.

2. Create the file sql/share/charsets/MYSET.conf. (You can use a copy of sql/share/charsets/latin1.conf as the basis for this file.)

   The syntax for the file is very simple:

   • Comments start with a '#' character and proceed to the end of the line.

   • Words are separated by arbitrary amounts of whitespace.

   • When defining the character set, every word must be a number in hexadecimal format.

   • The ctype array takes up the first 257 words. The to_lower[], to_upper[] and sort_order[] arrays take up 256 words each after that.

3. See Section 4.7.4, "The Character Definition Arrays."

4. Add the character set name to the CHARSETS_AVAILABLE and COMPILED_CHARSETS lists in configure.in.

5. Reconfigure, recompile, and test.

For a complex character set, do the following:

1. Create the file strings/ctype-MYSET.c in the MySQL source distribution.

2. Add MYSET to the end of the sql/share/charsets/Index file. Assign a unique number to it.

3. Look at one of the existing ctype-*.c files (such as strings/ctype-big5.c) to see what needs to be defined. Note that the arrays in your file must have names like ctype_MYSET, to_lower_MYSET, and so on. These correspond to the arrays for a simple character set. See Section 4.7.4, "The Character Definition Arrays."

4. Near the top of the file, place a special comment like this:

   /*
   * This comment is parsed by configure to
   
   create ctype.c,
   * so don't change it unless you know what
   
   you are doing.
   *
   * .configure. number_MYSET=MYNUMBER
   
   * .configure. strxfrm_multiply_MYSET=N
   
   * .configure. mbmaxlen_MYSET=N
   
   */

5. The configure program uses this comment to include the character set into the MySQL library automatically.

   The strxfrm_multiply and mbmaxlen lines are explained in the following sections. You need include them only if you need the string collating functions or the multi-byte character set functions, respectively.

6. You should then create some of the following functions:

   • my_strncoll_MYSET()

   • my_strcoll_MYSET()

   • my_strxfrm_MYSET()

   • my_like_range_MYSET()

7. See Section 4.7.5, "String Collating Support."

8. Add the character set name to the CHARSETS_AVAILABLE and COMPILED_CHARSETS lists in configure.in.

9. Reconfigure, recompile, and test.

The sql/share/charsets/README file includes additional instructions.

If you want to have the character set included in the MySQL distribution, mail a patch to the MySQL internals mailing list. See Section 1.7.1.1, "The MySQL Mailing Lists."