MySQL Character Set and sorting method

Welcome to the Linux community forum and interact with 2 million technical staff. MySQL strings are classified into two categories: 1) binary strings: a string of bytes. The interpretation of bytes does not involve character sets, therefore, it does not have the concept of character sets and sorting methods 2) Non-binary string: a sequence composed of characters, the character set is used to explain the content of the string

Welcome to the Linux community forum and interact with 2 million technical staff> MySQL strings are classified into two categories: 1) binary strings: a string of bytes, the interpretation of bytes does not involve character sets. Therefore, it does not have the concept of character sets and sorting methods. 2) Non-binary string: a sequence composed of characters. The character set is used to explain the content of a character string.

Welcome to the Linux community forum and interact with 2 million technicians>

MySQL strings are classified into two categories:

1) binary string: a string of bytes. The interpretation of bytes does not involve character sets. Therefore, it does not have the concept of character sets and sorting methods.

2) Non-binary string: a sequence composed of characters. The character set is used to explain the content of the string. The sorting method determines the character size.

Character Set and sorting method

The relationship between character sets and sorting methods is as follows: a character set can have one or more sorting methods, and has a default sorting method. We can use the following example:

From the preceding example, we can see that the gbk character set has two sorting methods (gbk_chinese_ci and gbk_bin respectively). The default sorting method is gbk_chinese_ci.

The naming rules for sorting methods are: Character Set name_ _ suffix. The meanings of typical suffixes are as follows:

1) _ ci: case-insensitive sorting method

2) _ cs: case-sensitive sorting method

3) _ bin: Binary sorting method. The size comparison is based on character encoding and does not involve human languages. Therefore, the _ bin sorting method does not contain human languages.

Therefore, the gbk_chinese_ci sorting method indicates that the character set is gbk, and the human language uses Chinese to compare the size and compare the time zone with Case sensitivity.

Common functions

Character Set Guide

Character Set guidance allows MySQL to specify a character set to interpret character constants. Its syntax is:

_ Charset str

For example:

_ Utf8 'abc' indicates that the String constant 'abc' is described in the utf8 character set'

Character Set Conversion

The convert () function can convert a string to a specified character set. Its syntax is:

Convert (str using charset)

For example, convert ('abc' using utf8) indicates converting the 'abc' character set to uft8.

Length () function -- returns the length of the byte

Char_length () -- returns the length of characters.

System variables related to character sets

Use the following statement to view system variables related to character sets:

Their meanings are as follows:

Character_set_system: character set used by the MySQL database identifier, which is always utf8

Character_set_server and collation_server: Default Character Set and sorting method of the server

Character_set_database and collation_database: Default Character Set and sorting method of the current database

The following three variables affect communication between the client and the server:

Character_set_client: character set used by the client to send SQL statements to the server

Character_set_results: character set used by the server to return results to the client

Character_set_connection: if it is different from character_set_client, the SQL statement sent from the client is converted to the character set specified by character_set_client.

By default, the preceding three variables are set to the same value. If a client wants to use another character set to communicate with the server, modify them, for example:

[SQL]

Set character_set_client = utf8;

Set character_set_results = utf8;

Set character_set_connection = utf8;

Alternatively, you can use the following statement to achieve the same effect:

[SQL]

Set names 'utf8 ';