MariaDB default collation provides incredibly precise control over all the character sets and collations in our data. At Bobcares, with our Server Management Service, we can handle your MariaDB issues.

MariaDB Default Collation

The rules for comparing and sorting a specific character set are known as collation. In MariaDB, the default collation is latin1_swedish_ci. For instance, a subset of a character set could consist of the letters A, B, and C. A default collation could define these as appearing in descending order of C, B, and A.

Considering different case characters will add more complexity. The characters A and a would be evaluated differently in a binary collation, resulting in a specific ordering. However, a case-insensitive collation would evaluate ‘A’ and ‘a’ as the same. While each collation is only associated with one character set, a character set can be associated with many collations.

MariaDB supports many collations, including big5_chinese_ci, big5_bin, big5_chinese_nopad_ci, big5_nopad_bin, dec8_swedish_ci and so on. Collations that have a ‘ci’ at the end of their names are case-insensitive. Collations that have a ‘cs’ at the end of their names are case sensitive. If the name contains the word “nopad,” it means that the collation is of type NO PAD rather than PADSPACE. NO PAD collations regard trailing spaces as normal characters.

Viewing MariaDB Collation

We can definitely see the supported collation on our server with the SHOW COLLATION commands. The entire collation list is also provided on the MariaDB official site.

SHOW COLLATION command: The output from SHOW COLLATION includes all available collations. Syntax is:

SHOW COLLATION
    [LIKE 'pattern' | WHERE expr]

EXAMPLE

SHOW COLLATION LIKE 'latin1%';
+-------------------+---------+----+---------+----------+---------+
| Collation         | Charset | Id | Default | Compiled | Sortlen |
+-------------------+---------+----+---------+----------+---------+
| latin1_german1_ci | latin1  |  5 |         | Yes      |       1 |
| latin1_swedish_ci | latin1  |  8 | Yes     | Yes      |       1 |
| latin1_danish_ci  | latin1  | 15 |         | Yes      |       1 |
| latin1_german2_ci | latin1  | 31 |         | Yes      |       2 |
| latin1_bin        | latin1  | 47 |         | Yes      |       1 |
| latin1_general_ci | latin1  | 48 |         | Yes      |       1 |
| latin1_general_cs | latin1  | 49 |         | Yes      |       1 |
| latin1_spanish_ci | latin1  | 94 |         | Yes      |       1 |
+-------------------+---------+----+---------+----------+---------+

Changing MariaDB Collation

We can configure collations for client-server communication as well as from the server level down to the column level. Consider the code:

SET collation_connection = latin1_german2_ci;

SELECT 'Mueller' = 'Müller';
+-----------------------+
| 'Mueller' = 'Müller'  |
+-----------------------+
|                     1 |
+-----------------------+

The same two characters will evaluate as equivalent if the connection character set changes to latin1_german2_ci, or German phone book, using the collation_connection system variable.

Illegal Collation Mix

In MariaDB 10.1.28, we may encounter Error 1267 when performing comparison operations in views on tables that use binary constants. For instance,

CREATE TABLE test.t1 (
   a TEXT CHARACTER SET gbk 
) ENGINE=InnoDB 
CHARSET=latin1
COLLATE=latin1_general_cs;

INSERT INTO t1 VALUES ('user_a');

CREATE VIEW v1 AS
SELECT a <> 0xEE5D FROM t1;

SELECT * FROM v1;
Error 1267 (HY000): Illegal mix of collations (gbk_chinese_ci, IMPLICIT)
and (latin_swedish_ci, COERCIBLE) for operation

The binary character changes to a string literal by MariaDB when the view query writes to the file. Thus leading to confusion when the SELECT statement is used. If we run into this problem, we should force the character set in the view to be what we want. This error by MariaDB is a result of a bug that was in MariaDB 10.1.29. In this circumstance, later releases do not throw errors.

[Looking for a solution to another query? We are just a click away.]

Conclusion

A column without a specified collation will search for the table default, the table for the database, and the database for the server because character sets and collations always cascade down. As a result, we can have incredibly precise control over all the character sets and collations in our data. To summarize, our skilled Support team briefly explains the MariaDB collation details.