Troubleshoot PHP access MySQL 4.1 garbled problem

mysql| Solve | The problem

Multi-language support introduced from MySQL 4.1 is really great, and some of the features have gone beyond other database systems. However, during the test, I found that using the PHP statements prior to MySQL 4.1 would result in garbled data, even if the table character set was used. After reading the tenth chapter "Character Set Support" in the new MySQL online manual, I finally found the solution and tested it.

MySQL 4.1 's character set support (Character set Support) has two aspects: Character set (Character set) and Sort method (collation). The support for character sets is refined to four tiers: Servers (server), databases (database), Data tables (table), and connections (connection).

The settings for viewing the system's character set and sorting can be set by using the following two commands:

Mysql> show VARIABLES like ' character_set_% ';
+--------------------------+----------------------------+
| variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | Latin1 |
| character_set_connection | Latin1 |
| Character_set_database | Latin1 |
| Character_set_results | Latin1 |
| Character_set_server | Latin1 |
| Character_set_system | UTF8 |
| Character_sets_dir | /usr/share/mysql/charsets/|
+--------------------------+----------------------------+
7 Rows in Set (0.00 sec)

Mysql> show VARIABLES like ' collation_% ';
+----------------------+-------------------+
| variable_name | Value |
+----------------------+-------------------+
| collation_connection | Latin1_swedish_ci |
| Collation_database | Latin1_swedish_ci |
| Collation_server | Latin1_swedish_ci |
+----------------------+-------------------+
3 Rows in Set (0.00 sec)

The values listed above are the default values for the system. (It's strange how the system defaults to the Swedish sort of latin1) ...

When we access the MySQL database through PHP in the way we used to, even if we set the default character set of the table to UTF8 and send the query via UTF-8 encoding, you will find that the database is still garbled. The problem is on the connection connection layer. The workaround is to execute the following sentence before sending the query:

SET NAMES ' UTF8 ';

It corresponds to the following three-sentence instruction:
SET character_set_client = UTF8;
SET character_set_results = UTF8;
SET character_set_connection = UTF8;

Try again, is it normal? ^_^ enjoy!