Shulou(Shulou.com)06/01 Report--

First of all, there are two main concepts in MySQL's character set problem.

One is Character Sets.

One is Collations.

The former is character content and coding, while 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.

First of all, MySQL has a default character set, which is determined during installation and can be compiled through DEFAULT_CHARSET= when compiling MySQL

The parameters utf8 and DEFAULT_COLLATION=utf8_general_ci (MySQL5.5 version, version 5.1 with-- with-charset=)

Utf8-- with-collation=utf8_general_ci) to specify the default character set as utf8, which is also the best way once and for all.

The client connection to the database is also encoded by default utf8, and the application does not require any processing.

Unfortunately, many people do not specify these two parameters when compiling and installing MySQL, and most people install it through binary programs, so the default character set of MySQL is latin1. At this time, we can still specify the default character set of MySQL and add it through the my.cnf file.

Two parameters:

1. Add under [mysqld]

Default-character-set=utf8 (mysql version 5. 5 adds character-set-server=utf8) 2. Add default-character-set=utf8 under [client]

In this way, we do not have to specify the character set of utf8 when building databases and tables. This method of writing in the configuration file solves the problem of data storage and comparison, but it has no effect on the client connection. At this time, the client generally needs to specify a utf8 connection to avoid garbled code. Also known as the legendary general setnames command. In fact, 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 = xutf8

But these three parameters cannot be written in the configuration file my.cnf. It can only be modified dynamically through the set command. What we need is to write it down in the configuration file.

The way to live forever. So at this time, whether there is a solution to the problem on the server side? the feasible idea is to set it in init_connect. This command is triggered and executed when each ordinary user connects. You can add the following line to the [mysqld] section to set the connection character set:

Add: under [mysqld]:

Init_connect = 'SET NAMES utf8'

This solves the problem simply, but it should be noted that this command does not take effect on users with super permissions. You can simply test it as follows:

[root@localhost init.d] # mysql-u root-p

Enter password:

Welcome to the MySQL monitor. Commands end with; or\ g.

Your MySQL connection id is 6

Server version: 5.1.49-log MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

This software comes with ABSOLUTELY NO WARRANTY. This is free software

And you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.

Mysql > show variables like'% collation%'

+-+ +

| | Variable_name | Value |

+-+ +

| | collation_connection | latin1_swedish_ci |

| | collation_database | utf8_general_ci |

| | collation_server | utf8_general_ci |

+-+ +

3 rows in set (0.00 sec)

Mysql > show variables like'% character%'

+-+

| | Variable_name | Value |

+-+

| | character_set_client | latin1 |

| | character_set_connection | latin1 |

| | character_set_database | utf8 |

| | character_set_filesystem | binary |

| | character_set_results | latin1 |

| | character_set_server | utf8 |

| | character_set_system | utf8 |

| | character_sets_dir | / usr/share/mysql/charsets/ |

+-+

8 rows in set (0.00 sec)

We see that the connection-related parameter that root users see is still latin1, while the average user sees utf8.

In fact, for client command programs such as mysql,mysqladmin, mysqlcheck, mysqlimport, and mysqlshow, you can set the following parameters in the configuration file

In the [client] section, specify that these programs are encoded and connected with utf8 by default:

Default-character-set = utf8

Many materials on the Internet (including the so-called awesome people of some large companies) say that adding this line can completely solve the problem of garbled code, which is incorrect. This parameter is only useful for the above official client programs, but is invalid for ordinary third-party programs, such as php code.

Of course, this parameter is useful to facilitate the management of DBA, and it also explains why what DBA sees is sometimes different from what the average user sees.

In addition to the storage of the database, the way to connect to the database, and the coding of the program itself should also be saved in utf8 mode, of course, this has nothing to do with the MySQL database, but the problem of client display.

To sum up, it is:

1. It is preferred to specify two parameters to use utf8 encoding when compiling and installing MySQL.

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

Third, set two parameters in the configuration file my.cnf, while the client connection specifies the set names command.

4. Add the default-character-set parameter to the configuration file my.cnf for easy management.

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.