13. Collations

A collation determines how two texts are compared: equality, ordering, GROUP BY, DISTINCT, PRIMARY / UNIQUE keys, joins, LIKE, partitions and full-text search. Miraj offers five, chosen per column, per table or per expression, following the rules of the reference. All text is stored as UTF-8 (utf8mb4).

13.1 The collations#

SELECT 'ß' = 'ss' COLLATE utf8mb4_general_ci     AS general,
       'ß' = 'ss' COLLATE utf8mb4_unicode_ci     AS unicode,
       '😀' = '😁' COLLATE utf8mb4_unicode_520_ci AS unicode_520,
       'æ' = 'ae' COLLATE utf8mb4_uca1400_ai_ci  AS uca1400,
       'é' = 'e'  COLLATE utf8mb4_bin            AS bin;

+---------+---------+-------------+---------+-----+
| general | unicode | unicode_520 | uca1400 | bin |
+---------+---------+-------------+---------+-----+
|       0 |       1 |           0 |       1 |   0 |
+---------+---------+-------------+---------+-----+

All are PAD SPACE: trailing spaces (U+0020 only) are ignored by =, ordering, GROUP BY, DISTINCT, keys and joins ('a' = 'a ' is 1, even in binary; a trailing tab counts). LIKE never pads with spaces ('a ' LIKE 'a' is false).

The utf8_… aliases are read as utf8mb3_…. CHARACTER SET latin1 is accepted for a column but the text is still stored as utf8mb4. The binary type (BINARY, VARBINARY, BLOB, CAST(… AS BINARY)) is always compared byte by byte, without PAD.

A JSON column is a LONGTEXT in utf8mb4_bin with the implicit constraint CHECK (json_valid(col)): two documents that differ only in case are different, and JSON_UNQUOTE(JSON_EXTRACT(doc, '$.a')) = 'x' does not find "X". SHOW CREATE TABLE writes it in this form:

  `j` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`j`))

13.2 Choosing the collation#

Column and table#

CREATE TABLE liasse (
    code   VARCHAR(10) COLLATE utf8mb4_bin PRIMARY KEY,   -- 'A1' and 'a1' are two codes
    nom    VARCHAR(80) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
    notes  TEXT                                           -- table default
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

• [CHARACTER SET set] COLLATE name after the type of a text column (CHAR, VARCHAR, *TEXT, ENUM, SET); CHARACTER SET set alone gives the default collation of the set, the general collation.
• The BINARY attribute of a text column (VARCHAR(10) BINARY) is equivalent to COLLATE utf8mb4_bin; BINARY COLLATE with any other name: error 1302.
• [DEFAULT] CHARSET = set, [DEFAULT] COLLATE = name as a table option: default for text columns written without a collation.
• Example, with the rows ('A1', 'Straße') and ('a1', 'Dupont'): WHERE code = 'a1' returns only a1, WHERE nom = 'strasse' returns A1, and SHOW CREATE TABLE liasse writes `code` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL and `nom` varchar(80) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL; SHOW FULL COLUMNS FROM liasse shows utf8mb4_bin, utf8mb4_unicode_ci and utf8mb4_general_ci in the Collation column. Changing code to utf8mb4_general_ci afterwards returns Duplicate entry 'a1' for key 'PRIMARY' (1062).
• CREATE TABLE … LIKE copies columns and default; CREATE TABLE … AS SELECT and view columns take the collation of the source column or expression (CONCAT(col_bin, 'x') stays binary).

Database#

CREATE DATABASE compta CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SHOW CREATE DATABASE compta;

+----------+-----------------------------------------------------------------------------------------------+
| Database | Create Database                                                                               |
+----------+-----------------------------------------------------------------------------------------------+
| compta   | CREATE DATABASE `compta` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */ |
+----------+-----------------------------------------------------------------------------------------------+

USE compta;
CREATE TABLE t (n VARCHAR(10));
-- SHOW CREATE TABLE t: … ) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
ALTER DATABASE compta COLLATE utf8mb4_bin;      -- only for tables created afterwards
CREATE TABLE t2 (n VARCHAR(10));
-- SHOW CREATE TABLE t2: … ) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

• CREATE DATABASE … [DEFAULT] [CHARACTER SET set] [DEFAULT] COLLATE name sets the default collation of the database: a table created afterwards without COLLATE= takes it, as do its text columns written without a collation (a temporary table too). CHARACTER SET utf8mb4 alone gives the general collation.
• ALTER {DATABASE | SCHEMA} [name] [DEFAULT] [CHARACTER SET set] [DEFAULT] COLLATE name changes it for tables created afterwards; existing tables keep theirs. Without a name: the current database (1046 with no current database; an unknown database returns error 1, Can't create/write to file './inconnue/db.opt'). COMMENT is accepted and ignored; UPGRADE DATA DIRECTORY NAME: 1235.
• The default is stored with the database (options.mro file in its directory, absent for the general collation), carried over by backups and, in the Cluster edition, by the secondaries.

ALTER TABLE#

• MODIFY / CHANGE / ADD of a column: the COLLATE written, otherwise the table default; RENAME COLUMN keeps the collation.
• [DEFAULT] COLLATE = name changes the table default without touching existing columns; [DEFAULT] CHARSET = set alone resets it to the default collation of the set (general for utf8mb4, utf8mb3 and latin1; CHARSET binary has no effect). Alone (with COMMENT or RENAME), these options change the schema without copying the rows.
• CONVERT TO CHARACTER SET utf8mb4 [COLLATE name] converts all text columns, JSON included : after CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, SHOW CREATE TABLE writes `j` longtext DEFAULT NULL CHECK (json_valid(`j`)) (table collation).
• Changing the collation of a key column rechecks the key: values that have become equal ('A1' and 'a1' going from binary to general, 'ß' and 'ss' going to Unicode) give 1062 and the table is left unchanged.
• Changing the collation of a partitioning column is refused (1235).

Expression#

expr COLLATE name imposes a collation on a text expression:

SELECT 'a' COLLATE utf8mb4_bin = 'A';                    -- 0
SELECT * FROM clients WHERE nom = 'strasse' COLLATE utf8mb4_unicode_ci;   -- finds « Straße »
SELECT code FROM liasse ORDER BY code COLLATE utf8mb4_general_ci;

The name must be a utf8mb4 collation ('a' COLLATE utf8_bin or COLLATE latin1_bin: 1253); COLLATE binary: 1064. Two exceptions, for applications written for older databases: applied directly to a column, the name can be that of another set (nom COLLATE utf8mb3_general_ci is accepted and compares as the matching collation); after CONVERT(x USING set), it is checked against the written set (CONVERT(x USING utf8mb3) COLLATE utf8mb3_general_ci is accepted).

Connection#

A text literal ('abc') takes the connection collation, @@collation_connection (COERCIBLE coercibility: a column always wins over it):

SET NAMES utf8mb4 COLLATE utf8mb4_bin;
SELECT 'a' = 'A';                                      -- 0
SELECT * FROM clients WHERE nom = 'DUPONT';            -- collation of the nom column
SET NAMES utf8mb4;                                     -- back to utf8mb4_general_ci

• SET NAMES set COLLATE name, SET collation_connection = name (or DEFAULT) change the collation of the literals of the session's following statements; SET NAMES set alone and SET character_set_connection = set reset the general collation, SET CHARACTER SET set that of the current database (@@collation_database). SET GLOBAL collation_connection has no effect.
• Collation of another character set in SET NAMES: 1253. Name unknown to the engine but prefixed with a recognized set (for example utf8mb4_0900_ai_ci, sent by default by some connectors): accepted, general collation (SET collation_connection adds warning 1273). Name that matches no recognized set (SET NAMES utf8mb4 COLLATE klingon_ci, SET collation_connection | collation_server | collation_database = 'klingon_ci'): error 1273.
• Unknown character set in SET NAMES, SET CHARACTER SET (SET CHARSET) or SET character_set_client | _results | _connection | _server | _database: error 1115 (SET NAMES klingon). NULL is accepted only for character_set_results (elsewhere: 1231).
• The collation is resolved when the statement is compiled, with no per-row cost. It does not apply to expressions stored with a table (default value, generated column, CHECK, partitioning) nor to event bodies: their literals stay in the general collation, whichever session runs them.
• Stored objects: like the reference, a procedure, function, trigger or view keeps the connection collation of the session that created it. The literals of its body (or of its query) stay in it, whichever session calls or reads it, and even after a restart. A view created under SET NAMES utf8mb4 COLLATE utf8mb4_bin compares 'a' = 'A' in binary for every reader; its stored result (ordinary or CACHED view, REFRESH VIEW) applies to all sessions. The frozen collation can be read in the collation_connection column of SHOW CREATE PROCEDURE | FUNCTION | TRIGGER | VIEW, of SHOW PROCEDURE | FUNCTION STATUS, SHOW TRIGGERS and of information_schema.ROUTINES, TRIGGERS and VIEWS (COLLATION_CONNECTION). An object created before this version is utf8mb4_general_ci.
• A user variable assigned a literal keeps the connection collation at the time of the assignment (§ 13.3).
• The ? parameters of a prepared statement (binary protocol, EXECUTE … USING) take, like a literal, the connection collation at execution time (coercible): after SET NAMES utf8mb4 COLLATE utf8mb4_bin, ? = 'A' is 0 for 'a', even for a statement prepared before the SET NAMES.
• A function that returns a text without taking it from a text argument (HEX, MD5, DATE_FORMAT, FORMAT, CONCAT(1, 2), LPAD(1, 3, 0)…) returns it under the connection collation, coercible; in an expression stored with a table or a routine, under the general collation.
• The session starts under the collation announced by the client at connection time (handshake number): 45 utf8mb4_general_ci, 46 utf8mb4_bin, 224 utf8mb4_unicode_ci, 246 utf8mb4_unicode_520_ci. Any other number — 33 (utf8mb3), 255 (utf8mb4_0900_ai_ci, announced by default by recent connectors), unknown number — leaves utf8mb4_general_ci.

13.3 Coercibility: which collation wins#

When two texts meet (=, <, IN, BETWEEN, CASE, LIKE, STRCMP, LOCATE, join, UNION, CONCAT…), each carries a coercibility; the lowest wins:

• Column against literal: the column wins (WHERE code = 'a1' on a binary column does not return 'A1'); an explicit COLLATE wins over the column.
• With equal coercibility and different collations, the widest wins: Unicode, then general, then binary. Binary column against general column: general comparison; general column against Unicode column: Unicode comparison; 'a' COLLATE utf8mb4_bin = 'A' COLLATE utf8mb4_general_ci is 1.
• The rule holds for any number of operands (u IN (g, 'x'), CASE, BETWEEN, UNION), for functions (CONCAT(u, g) is implicit Unicode) and for joins.

Intended differences from the reference: Miraj never raises 1267, 1270 or 1271, and a binary column no longer wins over a general or Unicode column (the reference compares in binary there). A database whose tables were created under different defaults (utf8mb3_uca1400_ai_ci on one side, utf8mb3_general_ci on the other) compares its columns without error, in Unicode. An index serves this comparison only on the side of the column already in Unicode (§ 13.5).

Variables, triggers and routines#

• User variable: @v keeps the collation of the value assigned to it (SET @v = x COLLATE utf8mb4_bin, SELECT col INTO @v, SELECT @v := col, SET @v = … in a routine), USERVAR coercibility: it wins over a literal (@v = 'a1' compares in binary), not over a column. A new assignment without COLLATE takes the collation of the value: that of the connection for a literal (SET NAMES … COLLATE), general by default.
• Trigger: NEW.col and OLD.col keep the collation of the column. On a binary column, IF NEW.code = 'a1' is false for 'A1'; on a Unicode column, NEW.nom = 'ss' is true for 'ß'. COLLATION(NEW.code) returns utf8mb4_bin, COERCIBILITY(NEW.code) 2.
• Routine: a parameter, a variable (DECLARE v VARCHAR(20) COLLATE utf8mb4_unicode_ci) or the result of a function (RETURNS VARCHAR(20) COLLATE utf8mb4_bin) keep the declared collation; the BINARY attribute is utf8mb4_bin. A collation name that Miraj does not know stays accepted there with no effect (a stored routine can always be read back).
• Without COLLATE: a parameter, a variable (of a routine or trigger) or the result of a function takes the default collation of the database at CREATE time, stored with the object: in a utf8mb4_unicode_ci database, DECLARE v VARCHAR(10) DEFAULT 'ß' then v = 'ss' is true, even after an ALTER DATABASE … COLLATE utf8mb4_general_ci (only routines created afterwards take the new default). The Database Collation column of SHOW CREATE and information_schema.ROUTINES.DATABASE_COLLATION show this frozen default; SHOW CREATE PROCEDURE returns the text as written, with no COLLATE added. information_schema.PARAMETERS.COLLATION_NAME gives the collation of each parameter and of the result.
• Intended difference: NEW.col of a general column and a routine variable without COLLATE (or general) stay coercible (COERCIBILITY returns 6, not 2). Against a binary column, the column then wins, as on the reference where binary wins at equal coercibility; two implicit ones would take the widest here (general). The current path, all general, is unchanged.

13.4 Functions and operators#

UPPER and LOWER remain Unicode case mappings whatever the collation.

13.5 Indexes, keys and partitions#

Primary and UNIQUE keys, secondary indexes, foreign keys, trigram and FULLTEXT indexes, and partition bounds and lists follow the collation of their column: in binary 'A1' and 'a1' are two keys, in Unicode 'ß' and 'ss' make only one (1062). An index serves a condition only if the resolved collation of the condition is that of the column: WHERE code COLLATE utf8mb4_general_ci = 'a1' reads the whole table. A FULLTEXT index on columns with different collations: 1283.

13.6 Metadata#

• SHOW CREATE TABLE writes CHARACTER SET utf8mb4 COLLATE name after the type of a column whose collation differs from the table default, and DEFAULT CHARSET=utf8mb4 COLLATE=name when the table default is not the general collation or is not that of the database.
• SHOW CREATE DATABASE adds /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE name */ when the default of the database is not the general collation; information_schema.SCHEMATA (DEFAULT_COLLATION_NAME); @@collation_database (set by USE), @@collation_connection.
• SHOW FULL COLUMNS (Collation column), information_schema.COLUMNS.COLLATION_NAME, TABLES.TABLE_COLLATION.
• SHOW COLLATION and information_schema.COLLATIONS: every name accepted by COLLATE, with the number, set and Sortlen of the reference, all PAD SPACE: latin1_swedish_ci (8, default), latin1_bin (47), latin1_general_ci (48), utf8mb3_general_ci (33, default), utf8mb3_bin (83), utf8mb3_unicode_ci (192), utf8mb3_unicode_520_ci (214), utf8mb4_general_ci (45, default), utf8mb4_bin (46), utf8mb4_unicode_ci (224), utf8mb4_unicode_520_ci (246); Sortlen 8 for the unicode ones, 1 for the others. utf8mb4_uca1400_ai_ci (2304) and utf8mb3_uca1400_ai_ci (2048) appear only in COLLATION_CHARACTER_SET_APPLICABILITY, as on the reference (which writes uca1400_ai_ci in COLLATION_NAME there; Miraj the full name). The utf8_… aliases are listed under their utf8mb3_… name. Each name is filed under one of the engine's five collations (§ 13.1).
• SHOW CHARACTER SET and information_schema.CHARACTER_SETS: latin1 (cp1252 West European, 1), utf8mb3 (3) and utf8mb4 (4), general default; latin1 and utf8mb3 are accepted but filed as utf8mb4 (§ 13.8).
• Reference 12.2 no longer marks utf8mb4_general_ci or utf8mb3_general_ci as the default of their set (its default is uca1400_ai_ci); Miraj, for which it is the default, marks them.
• Protocol: a Unicode or binary result column is announced as 224 or 46; a general column, like a JSON column, is still announced as 33.

13.7 Errors#

13.8 Differences and out of scope#

• Comparing unicode_ci to unicode_520_ci (or to uca1400_ai_ci) gives 1267 on the reference; Miraj compares under the most recent table, with no error.
• A table created before October 17, 2026 under unicode_520_ci or uca1400_ai_ci had been filed under unicode_ci: it stays that way (its file keeps only unicode), and SHOW CREATE TABLE writes it so; its indexes remain valid. ALTER TABLE … CONVERT TO CHARACTER SET utf8mb4 COLLATE … moves it to the wanted table.
• LIKE 'prefix%' under uca1400_ai_ci through an index: the reference computes the range without taking contractions into account and loses rows ('И' + breve LIKE 'И%'); Miraj always returns the result of row-by-row evaluation.
• Mixes with no winner: the reference raises 1267 / 1270 / 1271, Miraj compares under the widest collation (§ 13.3).
• CHARACTER SET utf8mb4 alone, SET NAMES utf8mb4 without COLLATE, CONVERT(… USING utf8mb4), the _utf8mb4 introducer and the handshake of a client announcing 45 give the general collation; the 12.x reference uses uca1400_ai_ci there (character_set_collations variable), so that on it even a server started with --collation-server=utf8mb4_general_ci compares the literals of a connection under uca1400_ai_ci. Since the UCA 14 tables are now exact, this default is a deliberate choice (compatibility of databases and dumps written without COLLATE under 10.x), not a limitation.
• CHARACTER SET latin1 (or utf8mb3) column: accepted, filed as utf8mb4 under the general collation (or binary for latin1_bin), and displayed as such (CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci in a non-general table; the reference: CHARACTER SET latin1 COLLATE latin1_swedish_ci). Difference kept: displaying latin1 would make tools that read back SHOW CREATE TABLE believe the column only takes latin1 characters.
• Number 255 (utf8mb4_0900_ai_ci) at connection: session in utf8mb4_general_ci (depending on its version, the reference makes it an alias of a Unicode collation; not observed); a user variable passed to EXECUTE … USING takes the connection collation there, not its own.
• NEW.col of a general column and general routine variables: coercible and not implicit (§ 13.3, "Variables, triggers and routines").
• Literals of events and of table expressions: always in the general collation (the reference keeps, for an event, the connection collation of its creation). Routines and triggers written in the PostgreSQL language: nothing is frozen, general collation.
• Writing through a view (UPDATE / DELETE / INSERT on an updatable view, WITH CHECK OPTION): the view's conditions, copied into the rewritten statement, are compiled under the collation of the writing session, not the one frozen with the view.
• Frozen database default of a routine or trigger: only the collation is kept, not the character set (always utf8mb4); the character_set_client column is always utf8mb4.
• SHOW CREATE DATABASE of a database in the general collation keeps the short form (CREATE DATABASE `name`); the reference always writes the /*!40100 … */ comment.
• CHARSET(), COLLATION() and COERCIBILITY() are available (chapter 10, §10.9): they return the character set, collation and coercibility (0 to 8) of the expression as the engine resolves it, binary for a number, a date or NULL, latin1 / latin1_swedish_ci or utf8mb3 for CONVERT(… USING set).
• Out of scope: NO PAD collations, case- or accent-sensitive collations other than binary (*_as_cs, *_ai_cs), language-specific ones (german2, czech, turkish…), utf8mb4_0900_*: 1273.
• Existing databases: any table created before collations were introduced is read with the general collation, indexes and partitions unchanged; only a deliberate ALTER TABLE … COLLATE changes it.