MySQL Tutorials - Herong's Tutorial Examples - v4.46, by Herong Yang
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:
General rules on how collations are used in MySQL:
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)
Table of Contents
MySQL Introduction and Installation
Introduction of MySQL Programs
Perl Programs and MySQL Servers
Java Programs and MySQL Servers
►Collations in Predicate Operations
Examples of Different Types of Operation
Character Strings and Bit Strings
Table Column Types for Different Types of Values
Using DDL to Create Tables and Indexes
Using DML to Insert, Update and Delete Records
Using SELECT to Query Database
Window Functions for Statistical Analysis
Use Index for Better Performance
Transaction Management and Isolation Levels
Defining and Calling Stored Procedures
Variables, Loops and Cursors Used in Stored Procedures
System, User-Defined and Stored Procedure Variables
Storage Engines in MySQL Server
InnoDB Storage Engine - Primary and Secondary Indexes
Performance Tuning and Optimization
Installing MySQL Server on Linux