Character Set and Collation Overview - Source

Revision: 145602
User: Ian Gilfillan
Date: 2025-01-07 18:15

<<toc>>
== What Are Character Sets and Collations
A character set is a set of characters while a collation is the rules for comparing and sorting a particular character set.

For example, a subset of a character set could consist of the letters ##A##, ##B## and ##C##. A default collation could define these as appearing in an ascending order of ##A, B, C##.

If we consider different case characters, more complexity is added. A binary collation would evaluate the characters ##A## and ##a## differently, ordering them in a particular way. A case-insensitive collation would evaluate ##A## and ##a## equivalently, while the German phone book collation evaluates the characters ##ue## and ##ü## equivalently.

A character set can have many collations associated with it, while each collation is only associated with one character set. In MariaDB, the character set name is always part of the collation name. For example, the ##latin1_german1_ci## collation applies only to the ##latin1## character set. Each character set also has one default collation. The ##latin1## default collation is ##latin1_swedish_ci##.

As an example, by default, the character ##y## comes between ##x## and ##z##, while in Lithuanian, it's sorted between ##i## and ##k##. Similarly, the German phone book order is different to the German dictionary order, so while they share the same character set, the collation is different.

== Viewing Character Sets and Collations
Prior to MariaDB 11.6.0, the default [[data-types-character-sets-and-collations|character set]] is ##latin1## and the default collation is ##latin1_swedish_ci##.  In MariaDB 11.6 the default character set is ##utf8mb4## and the default collation is ##utf8mb4_uca1400_ai_ci##.
This may differ in some distros, see for example [[differences-in-mariadb-in-debian|Differences in MariaDB in Debian]].

You can view a full list of character sets and collations supported by MariaDB at [[supported-character-sets-and-collations|Supported Character Sets and Collations]], or see what's supported on your server with the [[show-character-set|SHOW CHARACTER SET]] and [[show-collation|SHOW COLLATION]] commands.

By default, ##A## comes before ##Z##, so the following evaluates to true:
<<code>>
 SELECT "A" < "Z";
+-----------+
| "A" < "Z" |
+-----------+
|         1 |
+-----------+
<</code>>

By default, comparisons are case-insensitive:
<<code>>
SELECT "A" < "a", "A" = "a";
+-----------+-----------+
| "A" < "a" | "A" = "a" |
+-----------+-----------+
|         0 |         1 |
+-----------+-----------+
<</code>>

== Changing Character Sets and Collations
Character sets and collations can be set from the server level right down to the column level, as well as for client-server communication.

For example, ##ue## and ##ü## are by default evaluated differently.

<<code>>
SELECT 'Mueller' = 'Müller';
+----------------------+
| 'Müller' = 'Mueller' |
+----------------------+
|                    0 |
+----------------------+
<</code>>

By using the [[server-system-variables#collation_connection|collation_connection]] system variable to change the connection character set to ##latin1_german2_ci##, or German phone book, the same two characters will evaluate as equivalent.

<<code>>
SET collation_connection = latin1_german2_ci;

SELECT 'Mueller' = 'Müller';
+-----------------------+
| 'Mueller' = 'Müller'  |
+-----------------------+
|                     1 |
+-----------------------+
<</code>>

See [[setting-character-sets-and-collations|Setting Character Sets and Collations]] for more.

Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.

Character Set and Collation Overview - Source

Products

Services

Pricing

Resources

About Us

Download MariaDB