MySQL garbled processing and character set Introduction _ MySQL

Introduction to MySQL garbled processing and character set bitsCN.com

• Problem introduction:

We often encounter some cases of inserting Chinese characters into the MySQL database, but when we select them, we find garbled characters. For example, we enter and exit such a record from Table a: I

Insert into a values ('Hello helloworld ', 'helloworld ');

When you access it, you may find that the result is changed to as shown in:

So how can we solve this problem? Through the following operations on the MySQL character set, you will get the answer!

• Command for viewing the character set of libraries and tables:
  

To solve the character set problem, you must first know what character sets are used in the current system, database, table, client, and so on, and what character sets are supported by the system. The following describes some statements for obtaining relevant information:

1. View all character sets supported by the database

Show character set; or show char set;

2. check the current status, including the character set settings:

Status or/s

The Db characterset corresponds to the db. opt file in the Database Directory:

3. View System character set settings, including all character set settings:

show variables like '%char%';

How to get the result:

The meaning is as follows:

Official documentation on character sets related to connection:

• What character set is the statement in when it leaves the client?

The server takes the character_set_client system variable to be the character set in which statements are sent by the client.

• What character set shocould the server translate a statement to after loading it?

For this, the server uses the character_set_connection and collation_connection system variables. it converts statements sent by the client from character_set_client to character_set_connection (response t for string literals that have an introducer such as _ latin1 or _ utf8 ). collation_connection is important for comparisons of literal strings. for comparisons of strings with column values, collation_connection does not matter because columns have their own collation, which has a higher collation precedence.

• What character set shocould the server translate to before shipping result sets or error messages back to the client?

The character_set_results system variable indicates the character set in which the server returns query results to the client. This includes des result data such as column values, and result metadata such as column names and error messages.

From the above, we can see when the character_set_connection, character_set_client, and character_set_results character sets are used. As you can see, when the client connects to the server, it sends the desired character set name to the mysql server, the server then uses this character set to set the values character_set_connection, character_set_client, and character_set_results. For example, cmd uses gbk while mysql workbench uses utf8.

CMD:

MySql WorkBench:

4. View character set settings in the data table:

show full columns from tablename;

show create table tablename/G;

5. view the database code:

show create database dbname;

• Specify character set when creating:

After knowing how to find the relevant information about the character set, we need to know how to match the corresponding character set for the object when creating the specified object.

1. server level:

When installing MySQL, you can set the default encoding format of the server, or modify character_set_server = utf8 in [mysqld] to set the character_set_server value.

2. database level:

CREATE DATABASE db_name DEFAULT CHARACTER SET utf8;

Note: If the default character set is not specified, the system will set it based on the value of character_set_database, for example:

3. table-level:

CREATE TABLE  `db_name`.`tb_name` (id VARCHAR(20) NOT NULL,name VARCHAR(20) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

It can be seen that the default character set of the defined table is utf8. even if character_set_database is gbk, the columns in the table are not utf8

Note that if the default character set of the table is not defined, it will be set according to the value of character_set_database ,:

4. column level:

CREATE TABLE  `db_name`.`tb_name` (   id varchar(20) NOT NULL,   name varchar(20) CHARACTER SET utf8 );

As you can see, the default character set of the entire table is gbk, so columns without the specified character set use the default character set. if the name of the character set column is specified, the specified character set utf8 is used.

• Modify character set commands

If you have already created an object, what should you do. We should modify the character set for the specified object.

1. modify character_set_connection, character_set_client, and character_set_results values:

For a connection, you can use:

SET NAMES 'charset_name' [COLLATE 'collation_name']

Command

SET NAMES 'charset_name' [COLLATE 'collation_name']

Equivalent

SET character_set_client = charset_name; SET character_set_results = charset_name; SET character_set_connection = charset_name;

You can also modify the configuration file to add default-character-set = utf8 under [mysql] and configure it to the desired character set. (I tried to configure it in my. ini, but it was ineffective. I don't know if it was overwritten by the character set that the client wanted ?)

2. modify the character_set_database field:

ALTER DATABASE db_name     [[DEFAULT] CHARACTER SET charset_name]     [[DEFAULT] COLLATE collation_name]

3. modify the character_set_server field:

The simplest way is to directly change the [mysqld] field in the my. ini configuration file, add character-set-server = gbk, and restart mysqld to change the character set you want.

4. modify the character set of the table:

ALTER TABLE tbl_name     [[DEFAULT] CHARACTER SET charset_name]     [COLLATE collation_name]

5. modify the character set of a column:

col_name {CHAR | VARCHAR | TEXT} (col_length)     [CHARACTER SET charset_name]     [COLLATE collation_name]

For example:

ALTER TABLE t1 MODIFY     col1 VARCHAR(5)       CHARACTER SET latin1       COLLATE latin1_swedish_ci;

• References:

MySQL Character Set Support: http://dev.mysql.com/doc/refman/5.6/en/charset.html

Mysql common View Library, Table character set command: http://bjlfp.blog.163.com/blog/static/773684612012298455765/

The problem of Chinese garbled characters when MySQL inserts data: http://www.cnblogs.com/sunzn/archive/2013/03/14/2960248.html

BitsCN.com