Collations in Predicate Operations

MySQL Tutorials - Herong's Tutorial Examples

∟Collations in Predicate Operations

This section provides tutorial examples on how collations are used in predicate operations by MySQL.

What Is Collation? A collation is a set of rules used to compare characters in a particular character set. Here some examples of collations supported in MySQL:

latin1_general_ci - Case insensitive collation for "Latine1" character set and encoding.
latin1_general_cs - Case sensitive collation for "Latine1" character set and encoding.
utf8mb4_unicode_ci - Case sensitive collation for "Unicode" character set with UTF8 encoding.
utf8mb4_bin - Binary collation for "Unicode" character set with UTF8 encoding.

General rules on how collations are used in MySQL:

MySQL maintains 4 default collation settings: collation_server, collation_database, collation_connection, and default_collation_for_utf8mb4.
A character string (CHAR, VARCHAR, and TEXT) column is defined with the "collation_database" collation, if not specified in the "CREATE TABLE" statement.
A character string (CHAR, VARCHAR, and TEXT) literal in a SQL statement is defined with the "collation_connection" collation implicitly.
Character string collations in an operation are implicitly casted to a single collation to complete the operation.
Character string collations in an operation can be explicitly casted to override implicit castings.
Explicitly casted character string collations in an operation must match each other to avoid the "Illegal mix of collations" error.

Here are some examples of using collations in predicate operations in MySQL:

1. List all collations for all Unicode characters with UTF8 encodings:

mysql> show collation like 'utf8%';
| utf8_bin          | utf8  |  83 |      | Yes  |  1 | PAD SPACE  |
| utf8_croatian_ci  | utf8  | 213 |      | Yes  |  8 | PAD SPACE  |
| utf8_czech_ci     | utf8  | 202 |      | Yes  |  8 | PAD SPACE  |
| utf8_danish_ci    | utf8  | 203 |      | Yes  |  8 | PAD SPACE  |
| utf8_esperanto_ci | utf8  | 209 |      | Yes  |  8 | PAD SPACE  |
| utf8_estonian_ci  | utf8  | 198 |      | Yes  |  8 | PAD SPACE  |
| utf8_general_ci   | utf8  |  33 | Yes  | Yes  |  1 | PAD SPACE  |
...

2. See the default collation settings:

mysql> show variables like '%collation%';
+-------------------------------+--------------------+
| Variable_name                 | Value              |
+-------------------------------+--------------------+
| collation_connection          | utf8mb4_0900_ai_ci |
| collation_database            | utf8mb4_0900_ai_ci |
| collation_server              | utf8mb4_0900_ai_ci |
| default_collation_for_utf8mb4 | utf8mb4_0900_ai_ci |
+-------------------------------+--------------------+

3. Define table columns with given collations. Character sets are derived from collations.

mysql> CREATE TABLE Test (
    ->   Title CHAR(16) COLLATE latin1_general_ci,
    ->   Alias CHAR(16) COLLATE utf8mb4_unicode_ci);

mysql> show create table Test;
  Test  CREATE TABLE `Test` (

    `Title` char(16) CHARACTER SET latin1
      COLLATE latin1_general_ci DEFAULT NULL,

    `Alias` char(16) CHARACTER SET utf8mb4
      COLLATE utf8mb4_unicode_ci DEFAULT NULL

  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |

mysql> INSERT INTO Test VALUES ('HERONG','Herong');

4. Use implicit collation casting on columns:

mysql> SELECT * FROM Test WHERE Title = Alias;
+--------+--------+
| Title  | Alias  |
+--------+--------+
| HERONG | Herong |
+--------+--------+

5. Use implicit collation casting on column and literal:

mysql> SELECT * FROM Test WHERE Title = 'herong';
+--------+--------+
| Title  | Alias  |
+--------+--------+
| HERONG | Herong |
+--------+--------+

6. Use explicit collation casting:

mysql> SELECT * FROM Test WHERE Title = Alias COLLATE utf8mb4_bin;
  Empty set (0.00 sec)

mysql> SELECT * FROM Test WHERE Title COLLATE latin1_general_cs = Alias;
  ERROR 1267 (HY000): Illegal mix of collations (latin1_general_cs,EXPLICIT)
  and (utf8mb4_unicode_ci,IMPLICIT) for operation '='

mysql> SELECT * FROM Test WHERE Title = Alias COLLATE latin1_general_cs;
ERROR 1253 (42000): COLLATION 'latin1_general_cs' is
  not valid for CHARACTER SET 'utf8mb4'

7. Specify character set on literal.

mysql> SELECT * FROM Test WHERE Title = 'Herong' COLLATE latin1_general_cs;
ERROR 1253 (42000): COLLATION 'latin1_general_cs' is
  not valid for CHARACTER SET 'utf8mb4'

mysql> SELECT * FROM Test
    ->   WHERE Title = _latin1'Herong' COLLATE latin1_general_cs;
Empty set (0.00 sec)