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
\uformat, wherennnnnnnnis the hexadecimal Unicode code point value. Basic Latin lettersA-Zanda-zcan also be written literally (this is a MySQL limitation; the LDML specification permits 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 of0000toFFFF.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 permissible 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.
Choose a collation ID, as shown in Section 9.4.2, “Choosing a Collation ID”. The following steps use an ID of 1029.
You will need to modify the
Index.xmlconfiguration file. This file will be located in the directory named by thecharacter_sets_dirsystem 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/ | +--------------------+-----------------------------------------+Choose a name for the collation and list it in the
Index.xmlfile. 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>If you want a similar collation for other Unicode character sets, add other
<collation>elements. For example, to defineucs2_phone_ci, add a<collation>element to the<charset name="ucs2">element. Remember that each collation must have its own unique ID.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)