Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "the concept and setting method of MySQL character set". In the operation of practical cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

I. basic concepts of character set

1. Character (Character) refers to the smallest semantic symbol in human language. For example,'A','B', etc.

two。 Given a series of characters, each character is assigned a numeric value that represents the corresponding character, which is the Encoding of the character. For example, if we assign a value of 0 to the character'A' and a value of 1 to the character'B', then 0 is the encoding of the character'A'.

3. Given a series of characters and given the corresponding encoding, the set of all these characters and coding pairs is the Character Set. For example, if the list of characters given is {'Aggregano'}, {'A'= > 0, 'Bai = > 1} is a character set.

4. Character order (Collation) refers to the comparison rules between characters in the same character set.

5. After the character order is determined, what is the equivalent character and the size relationship between the characters can be defined on a character set

6. Each character order uniquely corresponds to a character set, but a character set can correspond to multiple character orders, one of which is the default character order (Default Collation)

Character order names in 7.MySQL follow naming conventions: they start with the character set name corresponding to the character order, and end with _ ci (for case insensitivity), _ cs (for case sensitivity), or _ bin (for comparison by coded value). For example, under the character order "utf8_general_ci", the characters "a" and "A" are equivalent.

2. MySQL default character set

MySQL can specify the character set into a database, a table, and a column. Traditional programs do not use such complex configurations when creating databases and data tables, they use default configurations.

(1) when compiling MySQL, you specify a default character set, which is latin1

(2) when installing MySQL, you can specify a default character set in the configuration file (my.ini). If not, this value inherits from the

(3) when you start mysqld, you can specify a default character set in the command line parameters. If not, this value inherits from the configuration in the configuration file. In this case, character_set_server is set to the default character set.

(4) install MySQL select multilingual support, the installer will automatically set default_character_set to UTF-8 in the configuration file to ensure that all columns of all tables in all databases are stored in UTF-8 by default.

III. MySQL character set setting

1. System variable:

-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 of the currently selected database

-character_set_system: system metadata (field name, etc.) character set

-there are also variables starting with collation_ that correspond to the above to describe the character order.

two。 Use introducer to specify the character set of the text string:

-format: [_ charset] 'string' [COLLATE collation]

-for example:

SELECT _ latin1 'string'

SELECT _ utf8 'Hello' COLLATE utf8_general_ci

-the text string modified by introducer is directly converted to internal character set processing without redundant transcoding during the request process.

4. Modify my.cnf settings for client and server character sets

1. Stop MySQL;service mysqld stop

two。 Back up the my.cnf file: cp / etc/my.cnf / etc/my.cnf.bak

Modify my.cnf

Vi / etc/my.cnf

Add under [client], and client is the control client.

Default-character-set=utf8

Add under [mysqld], and mysqld is the control server

Default-character-set=utf8

3. Restart MySQL

Spark220:mysqladmin:/usr/local/mysql: > service mysqld restart

4. Check to see if the character set has been modified

Mysql > show variables like'% char%'

Note: modify the configuration file and restart the server to take effect permanently

Fifth, detect character set

Mysql > SHOW CHARACTER SET

Mysql > SHOW COLLATION

Mysql > SHOW VARIABLES LIKE 'character%'

Mysql > SHOW VARIABLES LIKE 'collation%'

Mysql > SQL functions HEX, LENGTH, CHAR_LENGTH

Mysql > SQL functions CHARSET, COLLATION

The character set of MySQL mainly consists of two concepts, one is Character Sets, the other is Collations, the former is character content and coding, and the latter is some rules for comparing the former. These two parameter sets can be specified at four levels: database instance, single database, table, column, and so on. For users, it is generally recommended to use utf8 encoding to store data. In order to solve the problem of garbled code, it is not only the storage of MySQL data, but also the coding mode of user's program file and the connection mode between user program and MySQL database.

The client connection is useless. At this time, the client generally needs to specify a utf8 connection to avoid garbled code. Also known as the legendary general set names command.

The set names utf8 command corresponds to the following server-side commands:

SET character_set_client = utf8

SET character_set_results = utf8

SET character_set_connection = utf8

Note: the three parameters cannot be written in the configuration file my.cnf and can only be dynamically modified through the set command, but set in init_connect, this command is used in every

Execution is triggered when all ordinary users connect. You can add the following line to the [mysqld] section to set the connection character set:

Add: under [mysqld]:

Init_connect = 'SET NAMES utf8'

Summary:

1. When compiling and installing MySQL, specify two parameters to use utf8 encoding.

2. The configuration file my.cnf sets two parameters and sets init_connect parameters at the same time.

3. The configuration file my.cnf sets two parameters, while the client connection specifies the set names command.

4. Add default-character-set parameters to client and server in the configuration file my.cnf for easy management.

This is the end of the introduction of "the concept and setting method of MySQL character set". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.