MySQL character set and character sorting

MySQL's character set and character Fu She:
Word Fu She: Word Fu She (Collation) refers to the comparison rule between characters within the same character set
A character Fu She uniquely corresponds to a set of characters, but a character set can correspond to multiple Fu She, one of which is the default word Fu She

MySQL's character set and character Fu She have four levels of default settings: Server-level, database-level, data-table-level, field-level

The name of the Fu She in MySQL is named according to the specification, starting with the character Fu She corresponding to the CharSet. _ci (case insensitive), _cs (case sensitive), or _bin (compared by coded value)
For example: Under the word Fu She "Utf8_general_ci", the characters "a" and "a" are equivalent

MySQL Character Set settings:
System variables:
–character_set_server: Default internal operation character Set
–character_set_client: The character set used by the client source data
–character_set_connection: Connection Layer Character Set
–character_set_results: Query result Character Set
–character_set_database: The default character set for the currently selected database
–character_set_system: System metadata (field name, etc.) character set
– There are also variables that begin with Collation_ to describe the character Fu She

The character set conversion process in MySQL:
1.MySQL server converts request data from character_set_client to Character_set_connection when request is received
2. Convert the request data from character_set_connection to the internal operation character set before doing an internal operation, which is determined as follows
-Use the character set value for each data field
-If the above value does not exist, the default CHARACTER set value of the corresponding data table is used (MySQL extension, non-SQL standard)
-If the above value does not exist, the default CHARACTER set value of the corresponding database is used
-If the above value does not exist, use the Character_set_server setting value
3. Convert the operation result from the internal operation character set to Character_set_results


command to detect character set problems;
SHOW CHARACTER SET;
SHOW COLLATION;
SHOW VARIABLES like ' character% ';
SHOW VARIABLES like ' collation% ';
SQL function Hex, LENGTH, Char_length
SQL Functions CharSet, COLLATION

Precautions:
The Default_character_set setting in 1.MY.CNF only affects the connection character set when the MySQL command is connected to the server and does not use the Libmysqlclient library
Applications that have no effect
2. SQL operations on fields are usually performed in the internal operating character set, not affected by the connection character set settings

Summarize:
The MySQL character set can be refined to a single library, a table, and a column. However, the default settings are generally used
1. When compiling MySQL, a default character set is specified, and this character set is Latin1
2. When installing MySQL, you can specify a default character set in the configuration file, and if not specified, this value inherits the compile-time character set
3. When you start mysqld, you can use Character_set_server to specify the default character set, and if you do not specify the configuration in the inheritance configuration file
4. Select multi-language support when installing MySQL and automatically set the configuration to UTF-8 when the program is installed

The default MySQL default character set is Latin1


To modify the default character set:
1. The simplest method of modification:
Add default-character-set = UTF8 in the MySQL configuration file
Character_set_server = UTF8
Restart the server when you are finished modifying
2. modifying character sets online
mysql> SET character_set_client = UTF8;
mysql> SET character_set_connection = UTF8;
mysql> SET character_set_database = UTF8;
mysql> SET character_set_results = UTF8;
mysql> SET character_set_server = UTF8;
mysql> SET collation_connection = UTF8;
mysql> SET collation_database = UTF8;
mysql> SET collation_server = UTF8;

The default character set for the table is UTF8 and the query is sent by UTF-8 encoding, and the database is still garbled. There's something wrong with 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;

My weekend vacation is not good learning experience, if not, please point out. Thank you!

MySQL character set and character sorting