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

This article mainly introduces "the command of MYSQL to view the default character set". In daily operation, I believe that many people have doubts about the command of MYSQL to view the default character set. The editor has consulted all kinds of data and sorted out a simple and easy-to-use method of operation. I hope it will be helpful to answer the doubts of "command of MYSQL to view the default character set". Next, please follow the editor to study!

Some time ago, I have been perplexed by the character set of mysql. Today, I will summarize the knowledge in this area.

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.MySQL default character set

MySQL's specification of a character set can be refined into a database, a table, a column, and what character set should be used.

However, traditional programs do not use such complex configurations when creating databases and data tables, they use the default configuration, so where does the default configuration come from?

(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 d, you can specify a default character set in the command line arguments. If not, this value inherits from the configuration in the configuration file, and character_set_server is set to this 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.

To sum up, if there is no modification anywhere, then all tables in all databases and all fields are stored in latin1, but if we install MySQL, we will generally choose multi-language support, that is to say, 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, the character set of mysql is latin1 (ISO_8859_1)

In general, you can view the character set and sorting method of the system through 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

Using mysql > SHOW VARIABLES LIKE character%; to view, it is found 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

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

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

Summary:

Therefore, it doesn't matter to us what database version we use, whether it's 3.x, 4.0.x or 4.1.x, there are two important things:

1) set the database code correctly. The character set of MySQL version 4.0 and below always defaults to ISO8859-1 MySQL 4.1, which you can choose when installing. If you are going to use UTF-8, you should specify UTF-8 when you create the database (you can also change it after you have created it. Version 4.1 or above can also specify the character set of the table separately)

2) set the database connection code correctly. After setting the database code, you should specify the connection code when connecting to the database, for example, when using jdbc connection, specify the connection as utf8 mode.

At this point, the study on the "MYSQL command to view the default character set" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.