MySQL Connection Character Set Conversion

When a client communicates with a MySQL server, data must often be converted between different character encodings to ensure text is stored and retrieved accurately. This article explains how MySQL manages character set conversion during a connection session, focusing on the three critical system variables—character_set_client, character_set_connection, and character_set_results—that govern the flow of data from the client to the database and back.

The Connection Character Set Variables

MySQL uses three session-level variables to handle the transition of character encodings during a connection:

  1. character_set_client: The character set in which the client sends SQL statements to the server.
  2. character_set_connection: The character set the server uses to translate incoming statements before executing them.
  3. character_set_results: The character set in which the server returns query results (including text data and metadata) back to the client.

Step-by-Step Character Set Flow

The conversion process occurs in three distinct phases during a single query lifecycle.

Phase 1: Client to Server (Incoming Query)

When you write a query and send it to MySQL: * The server assumes the query is encoded in the character set specified by character_set_client. * Upon receiving the query, the server converts the SQL statement from character_set_client into the character set specified by character_set_connection. * String literals within the query are converted to character_set_connection unless they are preceded by a character set introducer (e.g., _utf8mb4 'text').

Phase 2: Query Execution and Storage Comparison

During execution, the server may need to compare or store the incoming data: * If the query compares a string literal to a database column, the server compares the character sets of both operands. * If the column’s character set differs from character_set_connection, the server converts the connection string to the column’s character set to perform the comparison or to insert the data.

Phase 3: Server to Client (Outgoing Results)

When returning the results of a query: * The data is retrieved from the database storage engine in the column’s specific character set. * Before sending the data over the network, the server automatically converts the results from the column’s character set into the character set specified by character_set_results. * The client receives the data in the encoding specified by character_set_results.

How to Configure Connection Character Sets

You can configure these connection variables manually or use shorthand commands to align them.

Using SET NAMES

The most common way to configure these settings is the SET NAMES statement. This command sets all three connection variables to a single character set.

SET NAMES 'utf8mb4';

Executing this statement is equivalent to running:

SET character_set_client = utf8mb4;
SET character_set_connection = utf8mb4;
SET character_set_results = utf8mb4;

Using SET CHARACTER SET

Alternatively, the SET CHARACTER SET statement can be used. This sets character_set_client and character_set_results to the specified character set, but sets character_set_connection to the default character set of the currently selected database.

SET CHARACTER SET 'utf8mb4';