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.

Zoek in de MySQL-handleiding

Nederlandse vertaling

U hebt gevraagd om deze site in het Nederlands te bezoeken. Voor nu wordt alleen de interface vertaald, maar nog niet alle inhoud.

Als je me wilt helpen met vertalingen, is je bijdrage welkom. Het enige dat u hoeft te doen, is u op de site registreren en mij een bericht sturen waarin u wordt gevraagd om u toe te voegen aan de groep vertalers, zodat u de gewenste pagina's kunt vertalen. Een link onderaan elke vertaalde pagina geeft aan dat u de vertaler bent en heeft een link naar uw profiel.

Bij voorbaat dank.

Document heeft de 26/06/2006 gemaakt, de laatste keer de 26/10/2018 gewijzigd
Bron van het afgedrukte document:https://www.gaudry.be/nl/mysql-rf-charset-collation-implementations.html

De infobrol is een persoonlijke site waarvan de inhoud uitsluitend mijn verantwoordelijkheid is. De tekst is beschikbaar onder CreativeCommons-licentie (BY-NC-SA). Meer info op de gebruiksvoorwaarden en de auteur.

Referenties

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

Deze verwijzingen en links verwijzen naar documenten die geraadpleegd zijn tijdens het schrijven van deze pagina, of die aanvullende informatie kunnen geven, maar de auteurs van deze bronnen kunnen niet verantwoordelijk worden gehouden voor de inhoud van deze pagina.
De auteur Deze site is als enige verantwoordelijk voor de manier waarop de verschillende concepten, en de vrijheden die met de referentiewerken worden genomen, hier worden gepresenteerd. Vergeet niet dat u meerdere broninformatie moet doorgeven om het risico op fouten te verkleinen.

Inhoudsopgave Haut