10.13.1 Collation Implementation Types

MySQL implements several types of collations:

Simple collations for 8-bit character sets

This kind of collation is implemented using an array of 256 weights that defines a one-to-one mapping from character codes to weights. latin1_swedish_ci is an example. It is a case-insensitive collation, so the uppercase and lowercase versions of a character have the same weights and they compare as equal.

mysql> SET NAMES 'latin1' COLLATE 'latin1_swedish_ci';
Query OK, 0 rows affected (0.01 sec)
mysql> SELECT HEX(WEIGHT_STRING('a')), HEX(WEIGHT_STRING('A'));
+-------------------------+-------------------------+
| HEX(WEIGHT_STRING('a')) | HEX(WEIGHT_STRING('A')) |
+-------------------------+-------------------------+
| 41 | 41 |
+-------------------------+-------------------------+
1 row in set (0.01 sec)
mysql> SELECT 'a' = 'A';
+-----------+
| 'a' = 'A' |
+-----------+
| 1 |
+-----------+
1 row in set (0.12 sec)

For implementation instructions, see Section 10.13.3, “Adding a Simple Collation to an 8-Bit Character Set”.

Complex collations for 8-bit character sets

This kind of collation is implemented using functions in a C source file that define how to order characters, as described in Section 10.12, “Adding a Character Set”.

Collations for non-Unicode multibyte character sets

For this type of collation, 8-bit (single-byte) and multibyte characters are handled differently. For 8-bit characters, character codes map to weights in case-insensitive fashion. (For example, the single-byte characters 'a' and 'A' both have a weight of 0x41.) For multibyte characters, there are two types of relationship between character codes and weights:

Weights equal character codes. sjis_japanese_ci is an example of this kind of collation. The multibyte character 'ぢ' has a character code of 0x82C0, and the weight is also 0x82C0.
1. mysql> CREATE TABLE t1
2. (c1 VARCHAR(2) CHARACTER SET sjis COLLATE sjis_japanese_ci);
3. Query OK, 0 rows affected (0.01 sec)
5. mysql> INSERT INTO t1 VALUES ('a'),('A'),(0x82C0);
6. Query OK, 3 rows affected (0.00 sec)
7. Records: 3 Duplicates: 0 Warnings: 0
9. mysql> SELECT c1, HEX(c1), HEX(WEIGHT_STRING(c1)) FROM t1;
10. +------+---------+------------------------+
11. | c1 | HEX(c1) | HEX(WEIGHT_STRING(c1)) |
12. +------+---------+------------------------+
13. | a | 61 | 41 |
14. | A | 41 | 41 |
15. | ぢ | 82C0 | 82C0 |
16. +------+---------+------------------------+
17. 3 rows in set (0.00 sec)
Character codes map one-to-one to weights, but a code is not necessarily equal to the weight. gbk_chinese_ci is an example of this kind of collation. The multibyte character '膰' has a character code of 0x81B0 but a weight of 0xC286.
1. mysql> CREATE TABLE t1
2. (c1 VARCHAR(2) CHARACTER SET gbk COLLATE gbk_chinese_ci);
3. Query OK, 0 rows affected (0.33 sec)
5. mysql> INSERT INTO t1 VALUES ('a'),('A'),(0x81B0);
6. Query OK, 3 rows affected (0.00 sec)
7. Records: 3 Duplicates: 0 Warnings: 0
9. mysql> SELECT c1, HEX(c1), HEX(WEIGHT_STRING(c1)) FROM t1;
10. +------+---------+------------------------+
11. | c1 | HEX(c1) | HEX(WEIGHT_STRING(c1)) |
12. +------+---------+------------------------+
13. | a | 61 | 41 |
14. | A | 41 | 41 |
15. | 膰 | 81B0 | C286 |
16. +------+---------+------------------------+
17. 3 rows in set (0.00 sec)

For implementation instructions, see Section 10.12, “Adding a Character Set”.

Collations for Unicode multibyte character sets

Some of these collations are based on the Unicode Collation Algorithm (UCA), others are not.

Non-UCA collations have a one-to-one mapping from character code to weight. In MySQL, such collations are case insensitive and accent insensitive. utf8_general_ci is an example: 'a', 'A', 'À', and 'á' each have different character codes but all have a weight of 0x0041 and compare as equal.

mysql> SET NAMES 'utf8' COLLATE 'utf8_general_ci';
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE t1
(c1 CHAR(1) CHARACTER SET UTF8 COLLATE utf8_general_ci);
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO t1 VALUES ('a'),('A'),('À'),('á');
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> SELECT c1, HEX(c1), HEX(WEIGHT_STRING(c1)) FROM t1;
+------+---------+------------------------+
| c1 | HEX(c1) | HEX(WEIGHT_STRING(c1)) |
+------+---------+------------------------+
| a | 61 | 0041 |
| A | 41 | 0041 |
| À | C380 | 0041 |
| á | C3A1 | 0041 |
+------+---------+------------------------+
4 rows in set (0.00 sec)

UCA-based collations in MySQL have these properties:

If a character has weights, each weight uses 2 bytes (16 bits).
A character may have zero weights (or an empty weight). In this case, the character is ignorable. Example: "U+0000 NULL" does not have a weight and is ignorable.
A character may have one weight. Example: 'a' has a weight of 0x0E33.
1. mysql> SET NAMES 'utf8' COLLATE 'utf8_unicode_ci';
2. Query OK, 0 rows affected (0.05 sec)
4. mysql> SELECT HEX('a'), HEX(WEIGHT_STRING('a'));
5. +----------+-------------------------+
6. | HEX('a') | HEX(WEIGHT_STRING('a')) |
7. +----------+-------------------------+
8. | 61 | 0E33 |
9. +----------+-------------------------+
10. 1 row in set (0.02 sec)
A character may have many weights. This is an expansion. Example: The German letter 'ß' (SZ ligature, or SHARP S) has a weight of 0x0FEA0FEA.
1. mysql> SET NAMES 'utf8' COLLATE 'utf8_unicode_ci';
2. Query OK, 0 rows affected (0.11 sec)
4. mysql> SELECT HEX('ß'), HEX(WEIGHT_STRING('ß'));
5. +-----------+--------------------------+
6. | HEX('ß') | HEX(WEIGHT_STRING('ß')) |
7. +-----------+--------------------------+
8. | C39F | 0FEA0FEA |
9. +-----------+--------------------------+
10. 1 row in set (0.00 sec)
Many characters may have one weight. This is a contraction. Example: 'ch' is a single letter in Czech and has a weight of 0x0EE2.
1. mysql> SET NAMES 'utf8' COLLATE 'utf8_czech_ci';
2. Query OK, 0 rows affected (0.09 sec)
4. mysql> SELECT HEX('ch'), HEX(WEIGHT_STRING('ch'));
5. +-----------+--------------------------+
6. | HEX('ch') | HEX(WEIGHT_STRING('ch')) |
7. +-----------+--------------------------+
8. | 6368 | 0EE2 |
9. +-----------+--------------------------+
10. 1 row in set (0.00 sec)

A many-characters-to-many-weights mapping is also possible (this is contraction with expansion), but is not supported by MySQL.

For implementation instructions, for a non-UCA collation, see Section 10.12, “Adding a Character Set”. For a UCA collation, see Section 10.13.4, “Adding a UCA Collation to a Unicode Character Set”.

Miscellaneous collations

There are also a few collations that do not fall into any of the previous categories.

Find a PHP function

Document created the 26/06/2006, last modified the 26/10/2018
Source of the printed document:https://www.gaudry.be/en/mysql-rf-charset-collation-implementations.html

The infobrol is a personal site whose content is my sole responsibility. The text is available under CreativeCommons license (BY-NC-SA). More info on the terms of use and the author.

References

Manuel MySQL : https://dev.mysql.com/

These references and links indicate documents consulted during the writing of this page, or which may provide additional information, but the authors of these sources can not be held responsible for the content of this page.
The author This site is solely responsible for the way in which the various concepts, and the freedoms that are taken with the reference works, are presented here. Remember that you must cross multiple source information to reduce the risk of errors.

Contents Haut