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

Editor to share with you how to solve the mysql garbled problem, I hope you will gain something after reading this article, let's discuss it together!

Mysql character set support (character set support) has two aspects: character set (character set) and sorting method (collation). Support for character sets is refined to four levels:

Server (server), (database), datasheet (table) and connection (connection).

1. Default character set: mysql can specify the character set into a database, a table, 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) when creating a new database, unless explicitly specified, the character set of the database is set to character_set_server by default

(5) when a database is selected, character_set_database is set as the default character set for that database.

(6) when creating a table in this database, the default character set of the table is set to character_set_database, which is the default character set of the database.

(7) when setting a column in a table, unless explicitly specified, the default character set of this column is the default character set of the table. If nothing is changed, then all tables in all databases and all fields are stored in latin1, but if we install mysql, we usually choose multi-language support, that is, the installer will automatically set default_character_set to utf-8 in the configuration file, which ensures that by default, all tables in all databases are stored in utf-8.

two。 View the default character set (by default, mysql's character set is latin1 (iso_8859_1). In general, you can view the system's character set and sort by using the following two commands:

Mysql > show variables like 'character%'

+-+

| | variable_name | value |

+-+

| | character_set_client | latin1 |

| | character_set_connection | latin1 |

| | character_set_database | latin1 |

| | character_set_filesystem | binary |

| | character_set_results | latin1 |

| | character_set_server | latin1 |

| | character_set_system | utf8 |

| | character_sets_dir | d: "mysql-5.0.37" share "charsets" |

+-+

Mysql > show variables like 'collation_%'

+-+ +

| | variable_name | value |

+-+ +

| | collation_connection | utf8_general_ci |

| | collation_database | utf8_general_ci |

| | collation_server | utf8_general_ci |

+-+ +

3. Modify the default character set

(1) the easiest way to modify is to modify the key values of the character set in the my.ini file of mysql.

For example, default-character-set = utf8

Character_set_server = utf8

After modification, restart the mysql service, service mysql restart

Use mysql > show variables like 'character%'; to view, and find that the database codes have been changed to utf8

+-+

| | variable_name | value |

+-+

| | character_set_client | utf8 |

| | character_set_connection | utf8 |

| | character_set_database | utf8 |

| | character_set_filesystem | binary |

| | character_set_results | utf8 |

| | character_set_server | utf8 |

| | character_set_system | utf8 |

| | character_sets_dir | d: "mysql-5.0.37" share "charsets" |

+-+

(2) there is another way to modify the character set, which is to use the command of mysql

Set character_set_client = utf8

Set character_set_connection = utf8

Set character_set_database = utf8

Set character_set_results = utf8

Set character_set_server = utf8

Mysql > set collation_connection = utf8

Mysql > set collation_database = utf8

Mysql > set collation_server = utf8

In general, even if you set the default character set of the table to utf8 and send the query through utf-8 encoding, you will find that the code stored in the database is still garbled. The problem lies in this connection connection layer. The solution is to execute the following sentence before sending the query:

Set names' utf8'

It is equivalent to the following three instructions:

Set character_set_client = utf8

Set character_set_results = utf8

Set character_set_connection = utf8

For some reason, after I set the above three character sets to utf8 on my machine, the Chinese query results are still garbled. Only after setting character_set_results to gbk can I display Chinese normally from the command line.

Set character_set_results=gbk

After reading this article, I believe you have a certain understanding of "how to solve the problem of mysql garbled". If you want to know more about it, you are welcome to follow the industry information channel. Thank you for reading!

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.