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.

Rechercher dans le manuel MySQL

Traduction non disponible

Le manuel MySQL n'est pas encore traduit en français sur l'infobrol. Seule la version anglaise est disponible pour l'instant.

Document créé le 26/06/2006, dernière modification le 26/10/2018
Source du document imprimé : https://www.gaudry.be/mysql-rf-charset-collation-implementations.html

L'infobrol est un site personnel dont le contenu n'engage que moi. Le texte est mis à disposition sous licence CreativeCommons(BY-NC-SA). Plus d'info sur les conditions d'utilisation et sur l'auteur.

Références

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

Ces références et liens indiquent des documents consultés lors de la rédaction de cette page, ou qui peuvent apporter un complément d'information, mais les auteurs de ces sources ne peuvent être tenus responsables du contenu de cette page.
L'auteur de ce site est seul responsable de la manière dont sont présentés ici les différents concepts, et des libertés qui sont prises avec les ouvrages de référence. N'oubliez pas que vous devez croiser les informations de sources multiples afin de diminuer les risques d'erreurs.

Table des matières Haut