Comparison between mysqli::get_charset and SHOW VARIABLES LIKE 'character_set_%' in MySQL CLI

When using PHP to manipulate MySQL databases, we often need to pay attention to character set settings, especially when it comes to Chinese or other multibyte characters. Improper character set settings may lead to garbled code or data storage errors. For character sets, PHP's mysqli extension provides the mysqli::get_charset function, while in the MySQL command line tool (CLI), we usually use SHOW VARIABLES LIKE 'character_set_%' to view related variables.

This article will analyze the differences between the two in depth to help developers understand their uses and differences.

1?? What is mysqli::get_charset?

mysqli::get_charset() is a method of mysqli object in PHP, which is used to obtain the character set information of the current connection.
It returns an object containing the following properties:

• charset : The character set name of the currently connected, such as utf8mb4

• collation : collation, e.g. utf8mb4_general_ci

• dir : the directory of the character set definition file (usually empty)

• min_length , max_length , number , state : internally used character set information

For example, use code to get the current connection character set:

 <?php
$mysqli = new mysqli('m66.net', 'user', 'password', 'database');

if ($mysqli->connect_error) {
    die('Connection failed: ' . $mysqli->connect_error);
}

$charsetInfo = $mysqli->get_charset();
echo 'Current character set: ' . $charsetInfo->charset . PHP_EOL;
echo 'Sorting rules: ' . $charsetInfo->collation . PHP_EOL;

$mysqli->close();
?>

This method is aimed at the character set settings of the current PHP mysqli connection object , which is equivalent to the mysqli_set_charset() setting or default value.

2?? What is SHOW VARIABLES LIKE 'character_set_%'?

In the MySQL CLI or other SQL tools,
You can view the character set related variables of the current MySQL server through the following command:

 SHOW VARIABLES LIKE 'character_set_%';

This lists a range of configurations, for example:

• character_set_client : The character set sent by the client to the server

• character_set_connection : The character set of the server parsing statement

• character_set_database : The character set of the current default database

• character_set_results : The character set of the query result is returned by the server

• character_set_server : server default character set

• character_set_system : System metadata character set

These variables involve both server-level and session-level character sets, not just PHP connections.

For example, run in the CLI:

 mysql> SHOW VARIABLES LIKE 'character_set_%';

You may see:

This information more fully reflects the character set configuration of the current MySQL instance and session.

3?? Core difference

Simply put, mysqli::get_charset() is one end of PHP;
SHOW VARIABLES LIKE 'character_set_%' is the whole of the database server.

4?? Why are both important?

When we develop multilingual or multi-region-supported applications,
Ensure that the character sets of the client (PHP), connection, database and server are consistent.
It is the key to avoiding garbled code.

For example:

• You may set mysqli_set_charset('utf8mb4') in PHP,
  But the character_set_database of the database is still latin1 ,
  The data stored in this case may have problems.

• Through mysqli::get_charset() , you can confirm whether the PHP connection layer is set successfully;
  And through SHOW VARIABLES LIKE 'character_set_%' , you can confirm the default settings of the server and database.

Only by combining the two can we fully grasp the character set link.

in conclusion

mysqli::get_charset() and SHOW VARIABLES LIKE 'character_set_%'
It is two powerful tools for developers to troubleshoot character set problems.
One is on the client (PHP) side and one is on the server (MySQL) side.

Understand the differences and relationships between the two,
Not only helps write high-quality, garbled-free applications,
It can also provide more clues when troubleshooting cross-system data flows.

Remember: character set consistency is a systematic project.
Not just configuration issues on one side of PHP or MySQL.