Using Character Set Introducer, Conversion Functions, and Collation in MySQL

The article continues a previous discussion on MySQL character sets, focusing on SQL statements that control character encoding and collation. It introduces the character introducer , which allows a string to be explicitly associated with a charset and optional collation, independent of session variables.

Syntax: [_charset_name] 'string' [COLLATE collation_name] . Example queries demonstrate using _utf8mb4 '北京加油❤!' COLLATE utf8mb4_bin AS result and show the effect of omitting the COLLATE clause, which defaults to the session's collation.

The article then covers character set conversion functions:

CONVERT() – similar to the introducer but only specifies a charset. Example: SELECT CONVERT('北京加油❤!' USING utf8mb4); (valid) versus SELECT CONVERT('北京加油❤!' USING latin1); (invalid, produces garbled output).

CHARSET() – returns the charset of a string. Example: SET @a='北京加油❤!'; SELECT CHARSET(@a); returns utf8 .

Next, the SET NAMES statement is described. Syntax: SET NAMES 'charset_name' [COLLATE 'collation_name'] | DEFAULT . Executing SET NAMES latin1 COLLATE latin1_bin; changes session variables character_set_client , character_set_results , character_set_connection , and collation_connection . The article shows how to revert to defaults with SET NAMES DEFAULT; and notes that some charsets (e.g., utf32) are not allowed.

The SET CHARACTER SET (or SET CHARSET ) statement is similar but inherits the connection charset from character_set_database . Example: SET CHARACTER SET latin1; updates session variables accordingly, but the connection charset follows the database charset.

The COLLATE clause forces a specific collation, overriding existing rules. Examples illustrate changing the order of results in a query by appending COLLATE utf8mb4_0900_bin to the ORDER BY clause, and using collations in column aliases, aggregate functions, and WHERE conditions.

MySQL’s coercibility metric (0‑6) determines which collation takes precedence. The article provides examples using the COERCIBILITY() function for literals, collated strings, dates, and system constants, showing how lower values have higher priority.

In summary, the article reviews the main SQL commands for controlling character sets and collations in MySQL—SET NAMES/SET CHARSET, COLLATE, and the character introducer—providing practical examples to help developers manage encoding correctly.