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

MYSQL character set is what, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain for you in detail, people with this need can come to learn, I hope you can gain something.

With regard to the character set of MYSQL, there are many variable settings in the system, which are not clear to many beginners, including myself.

So write something here, hoping to clarify the relationship between these things.

The character set variables for MYSQL can be obtained with the following command:

Show variables like 'char%'

+-+

| | Variable_name | Value |

+-+

| | character_set_client | gbk |

| | character_set_connection | gbk |

| | character_set_database | utf8 |

| | character_set_filesystem | binary |

| | character_set_results |

| | character_set_server | utf8 |

| | character_set_system | utf8 |

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

+-+

The character set variables of MYSQL can actually be divided into two categories

One is about creating OBJECTS.

Character_set_database

Character_set_server

One is to be used when the server handles the connection.

Character_set_client

Character_set_connection

Character_set_results

Our main concern is to deal with the character set of connections.

Pay attention to the character set problem, then you must have encountered the problem of garbled code. In fact, to solve the problem of garbled code on the Internet a lot of search.

If it can be done, the character set of the application / MYSQL.CLENT/MYSQL.CONNETION/ table / MYSQL.RESULT is consistent, and there will be no problem in accessing the data.

I will sort out the relationship between these three connected character sets here.

After many tests, I have summarized the process of dealing with characters in MYSQL as follows:

For example: WINDOWS client CHARSET=GBK, enter "medium" and transcode to $a = D6D0 via WINDOWS.GBK

=

The server receives the value "D6D0" from CLIENT and thinks that "D6D0" is the data of the specified character set of $MYSQL.character_set_client.

# convert from $MYSQL.character_set_client-& gt $MYSQL.character_set_connection (not converted if the character set is the same)

If converted successfully; then

$a = $MYSQL.character_set_client.code

Else

$a = 3f (no error will be reported in this link!)

Fi

# convert from $MYSQL.character_set_connection-& gt $TABLES.character_set (once if the character set is the same)

If converted successfully; then

$a = $MYSQL.character_set_client.code and save it to the table

Else

$a = 3f with error: Incorrect string value

A = 20 (in the case of $MYSQL.character_set_connection = $TABLES.character_set)

Fi

# fetching data from the database.

# from $TABLES.character_set-& gt $MYSQL.character_set_result

If converted successfully; then

$a = $MYSQL.character_set_result.code, normal display

Else

$a = 3f/ garbled, showing:? Or garbled code.

Fi=

The following are the various situations and error messages recorded in the course of my test for your analysis:

Here I specially use two character sets of SSHTERM for testing. We can think of it as an application.

SSHTERMCHAR_clientCHAR_connectiontutf_dumptgbk_dumptlatin1_dumptutf_warningtgbk_warningtlatin1_warninggbkutf8utf8203f3fIncorrect 'xD6xD0'

Save the table UTF8 to UTF8, and the character set of this link will also be converted once. But it was not found in the source character set. Return "empty" Incorrect 'xD6xD0'

Save the table, use UTF8 to GBK Times wrong Incorrect 'xD6xD0'

Enter the table, use UTF8 to LATIN1 Times wrong gbkgbke4b8add6d03f normal normal Incorrect 'xD6xD0'

When saving in the table, use GBK to LATIN1 Times wrong D6D0latin1latin1c396c3903f3fd6d0 normal: (save UTF8 D6D0)

If LATIN1 is used or "D6D0" Incorrect 'xD6xD0'

LATIN1 to GBK report error normal gbkutf8e4b8add6d03f normal normal Incorrect xE4xB8xAD

Storage table, using UTF8 to LATIN1 Times error utf8gbk3f3f3fCLIENT to CONN conversion data has been lost to 3F, the intermediate conversion will not report an error utf8gbkgbke6b693e4b83fData truncated

"E4B8AD" only took _ GBK "E4B8" Incorrect 'xAD

"E4B8AD" is divided into two segments, but AD fails to convert successfully. Why are the processing results of the above two different when xE4xB8xAD' converts from CONN to table? utf8utf8e4b8add6d03f normal Incorrect xE4xB8xADE4B8ADgbkutf8e6b693e4b83fCLENT to CONN data is intercepted

But such processing will not report an error. The data from current to CONN is intercepted.

But this kind of processing will not report an error. Incorrect 'xE6xB6x93' utf8gbke4b8add6d03f normal normal Incorrect' xD6xD0' latin1latin1c3a4c2b8c2ad3f3f3fe4b8ad in theory this is the CODE of "E4B8AD" UTF8, but it's a bit unlike xE4xB8xAD.

The transfer from LATIN1 to GBK is not successful.

In the above table, as long as the DUMP result is e4b8ad/d6d0, the data storage is normal. And it can be read normally.

The case where the DUMP result is c396c390 is special. The reader should be able to understand it with a little analysis. In fact, it is the original storage and original retrieval.

Related commands:

Create a test table:

Create table tutf (name char (10)) engine=myisam default character set=utf8

Create table tgbk (name char (10)) engine=myisam default character set=gbk

Create table tlat (name char (10)) engine=myisam default character set=latin1

Set the relevant character set:

Set character_set_client=gbk

Set character_set_connection=utf8

Set character_set_results=latin1

Insert and DUMP data:

Truncate table tutf;truncate table tgbk;truncate table tlat

Insert into tutf values ('medium'); show warnings

Insert into tgbk values ('medium'); show warnings

Insert into tlat values ('medium'); show warnings

System hexdump / home/mysql/data/test/tutf.MYD

System hexdump / home/mysql/data/test/tgbk.MYD

System hexdump / home/mysql/data/test/tlat.MYD

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

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.