Detailed explanation of MySQL character set issues (picture)

Recently, an old project of the company needed to support emoji expressions. At first, I thought it would be just a matter of modifying the table fields of the database, but unexpectedly it caused a series of problems. Summarize it here as a reminder.

01 MySQL Character setSettings

• ##System variables:

character_set_server: Default internal operating character set

character_set_client: Character set used by client source data
character_set_connection: Connection layer character set
character_set_results: Query result character set
character_set_database: Default character set of the currently selected database
character_set_system: System metadata (field name, etc.) character set

02 Character set conversion process in MySQL

• When MySQL Server receives the request, it converts the request data from

  character_set_client to character_set_connection;

• before performing internal operations Convert the request data from

  character_set_connection to the internal operating character set, which is determined as follows:

• Use the CHARACTER SET setting value of the field in the table;

• If the above value does not exist, use the DEFAULT CHARACTER SET setting value of the corresponding data table (MySQL extension, non-SQL standard);

• If the above value does not exist, use the DEFAULT CHARACTER SET setting value of the corresponding database;

• If the above value does not exist, use the

  character_set_server setting value.

• Convert the operation results from the internal operation character set to

  character_set_results, in response to the request.

03 MySQL startup

Configuration file my.cnf

default-character-set: Set mysql command The default character set for line connection

character-set-server: Set the system's internal operating character set (i.e. the character_set_server mentioned above)
collation_server: Set the system's default character set sorting

04 PHP connection MySQL (taking PDO connection mode as an example)

Two common ways to set character sets

##mysql:host=localhost;dbname=db_demo;charset=utf8mb4
PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8mb4"
05 Summary

Data The character set of the table field needs to be
• utf8mb4

  , this is just required

generally
• character_set_client

  ,character_set_connection, The character sets of character_set_results are the same, and they are all utf8mb4 (these three parameters are all utf8mb4 by default. If not, emoji expressions cannot be realized. For reasons, please refer to Character Set Conversion process).

• The character set used to connect to the database

  also needs to be utf8mb4

##character_set_server# The types of default character sets such as ##,
• character_set_database

  are not that important, but it is best to keep them consistentThere are indeed many pitfalls in TP, stay away from TP

The above is the detailed content of Detailed explanation of MySQL character set issues (picture). For more information, please follow other related articles on the PHP Chinese website!