Skip navigation links
9.5 Character Set Configuration »
Section Navigation      [Toggle]

9.4.4. Adding a UCA Collation to a Unicode Character Set

UCA collations for Unicode character sets can be added to MySQL without recompiling by using a subset of the Locale Data Markup Language (LDML), which is available at http://www.unicode.org/reports/tr35/. With this method, you begin with an existing “base” collation. Then you describe the new collation in terms of how it differs from the base collation, rather than defining the entire collation. The following table lists the base collations for the Unicode character sets.

Character Set Base Collation
utf8 utf8_unicode_ci
ucs2 ucs2_unicode_ci
utf16 utf16_unicode_ci
utf32 utf32_unicode_ci

The following brief summary describes the LDML characteristics required for understanding the procedure for adding a collation given later in this section:

  • LDML has reset, shift, and identity rules.

  • Characters named in these rules can be written in \unnnn format, where nnnn is the hexadecimal Unicode code point value. Basic Latin letters A-Z and a-z can also be written literally (this is a MySQL limitation; the LDML specification allows literal non-Latin1 characters in the rules). Only characters in the Basic Multilingual Plane can be specified. This notation does not apply to characters outside the BMP range of 0000 to FFFF.

  • A reset rule does not specify any ordering in and of itself. Instead, it “resets” the ordering for subsequent shift rules to cause them to be taken in relation to a given character. Either of the following rules resets subsequent shift rules to be taken in relation to the letter 'A': 

    <reset>A</reset>

<reset>\u0041</reset>

  • Shift rules define primary, secondary, and tertiary differences of a character from another character. They are specified using <p>, <s>, and <t> elements. Either of the following rules specifies a primary shift rule for the 'G' character: 

    <p>G</p>

<p>\u0047</p>

    • Use primary differences to distinguish separate letters.

    • Use secondary differences to distinguish accent variations.

    • Use tertiary differences to distinguish lettercase variations.

  • Identity rules indicate that one character sorts identically to another. The following rules cause 'b' sort the same as 'a': 

    <reset>a</reset>
<i>b</i>

    Identity rules are supported as of MySQL 5.5.3. Prior to 5.5.3, use <s> ... </s> instead.

To add a UCA collation for a Unicode character set without recompiling MySQL, use the following procedure. The example adds a collation named utf8_phone_ci to the utf8 character set. The collation is designed for a scenario involving a Web application for which users post their names and phone numbers. Phone numbers can be given in very different formats: 

+7-12345-67
+7-12-345-67
+7 12 345 67
+7 (12) 345 67
+71234567

The problem raised by dealing with these kinds of values is that the varying allowable formats make searching for a specific phone number very difficult. The solution is to define a new collation that reorders punctuation characters, making them ignorable.

  1. Choose a collation ID, as shown in Section 9.4.2, “Choosing a Collation ID”. The following steps use an ID of 1029.

  2. You will need to modify the Index.xml configuration file. This file will be located in the directory named by the character_sets_dir system variable. You can check the variable value as follows, although the path name might be different on your system: 

    mysql> SHOW VARIABLES LIKE 'character_sets_dir';
+--------------------+-----------------------------------------+
| Variable_name      | Value                                   |
+--------------------+-----------------------------------------+
| character_sets_dir | /user/local/mysql/share/mysql/charsets/ |
+--------------------+-----------------------------------------+

  3. Choose a name for the collation and list it in the Index.xml file. In addition, you'll need to provide the collation ordering rules. Find the <charset> element for the character set to which the collation is being added, and add a <collation> element that indicates the collation name and ID. Within the <collation> element, provide a <rules> element containing the ordering rules: 

    <charset name="utf8">
  ...
  <!-- associate collation name with its ID -->
  <collation name="utf8_phone_ci" id="1029">
    <rules>
      <reset>\u0000</reset>
        <i>\u0020</i> <!-- space -->
        <i>\u0028</i> <!-- left parenthesis -->
        <i>\u0029</i> <!-- right parenthesis -->
        <i>\u002B</i> <!-- plus -->
        <i>\u002D</i> <!-- hyphen -->
    </rules>
  </collation>
  ...
</charset>

  4. If you want a similar collation for other Unicode character sets, add other <collation> elements. For example, to define ucs2_phone_ci, add a <collation> element to the <charset name="ucs2"> element. Remember that each collation must have its own unique ID.

  5. Restart the server and use this statement to verify that the collation is present: 

    mysql> SHOW COLLATION LIKE 'utf8_phone_ci';
+---------------+---------+------+---------+----------+---------+
| Collation     | Charset | Id   | Default | Compiled | Sortlen |
+---------------+---------+------+---------+----------+---------+
| utf8_phone_ci | utf8    | 1029 |         |          |       8 |
+---------------+---------+------+---------+----------+---------+

Now we can test the collation to make sure that it has the desired properties.

Create a table containing some sample phone numbers using the new collation: 

mysql> CREATE TABLE phonebook (
    ->   name VARCHAR(64),
    ->   phone VARCHAR(64) CHARACTER SET utf8 COLLATE utf8_phone_ci
    -> );
Query OK, 0 rows affected (0.09 sec)

mysql> INSERT INTO phonebook VALUES ('Svoj','+7 912 800 80 02');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO phonebook VALUES ('Hf','+7 (912) 800 80 04');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO phonebook VALUES ('Bar','+7-912-800-80-01');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO phonebook VALUES ('Ramil','(7912) 800 80 03');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO phonebook VALUES ('Sanja','+380 (912) 8008005');
Query OK, 1 row affected (0.00 sec)

Run some queries to see whether the ignored punctuation characters are in fact ignored for sorting and comparisons: 

mysql> SELECT * FROM phonebook ORDER BY phone;
+-------+--------------------+
| name  | phone              |
+-------+--------------------+
| Sanja | +380 (912) 8008005 |
| Bar   | +7-912-800-80-01   |
| Svoj  | +7 912 800 80 02   |
| Ramil | (7912) 800 80 03   |
| Hf    | +7 (912) 800 80 04 |
+-------+--------------------+
5 rows in set (0.00 sec)

mysql> SELECT * FROM phonebook WHERE phone='+7(912)800-80-01';
+------+------------------+
| name | phone            |
+------+------------------+
| Bar  | +7-912-800-80-01 |
+------+------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM phonebook WHERE phone='79128008001';
+------+------------------+
| name | phone            |
+------+------------------+
| Bar  | +7-912-800-80-01 |
+------+------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM phonebook WHERE phone='7 9 1 2 8 0 0 8 0 0 1';
+------+------------------+
| name | phone            |
+------+------------------+
| Bar  | +7-912-800-80-01 |
+------+------------------+
1 row in set (0.00 sec)