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

This article introduces how to deal with character sets in MySQL. The content is very detailed. Interested friends can use it for reference. I hope it will be helpful to you.

How to deal with the character set of MySQL

Send a request

Client (character_set_client) = "database connection (character_set_connection) =" storage (table,column)

Return request

Storage (table,column) = "database connection (character_set_connection) =" client (character_set_results)

At each non-initial node, a character set conversion operation is performed from the previous node to the current node. For example, there are the following environments:

Character_set_connectionutf-8character_set_resultsgbkcharacter_set_clientgb2312 has table A, and the field character set is all BIG5.

When sending a request, the data is first converted from gbk to utf-8, then to BIG5, and then stored.

When the request is returned, the data is first converted from BIG5 to utf-8, then to gb2312, and then sent to the client.

What are the functions of MySQL character set architecture

1. Allow different clients to have different character sets. A typical example is that I have a utf-8 site, which is a client whose charsetclient is utf-8. At the same time, I may need to read and write the database on a gbk terminal, which is another client, but its character set is gbk.

two。 When operating a file system through a database, you need to change the file path to the character set of the file system. For example, my client is gbk and the server file system is utf-8. Operate "/ porn / Rina.rmvb". In the data sent, the data of "film" is different from that of the server. At this point, there needs to be a way to convert the "film" of GBK to utf-8. Here MySQL introduces something called character_filesystem to do this.

Besides, I can't think of any other function for the time being. But think about it, do we really need this kind of processing? a lot of websites just want their data to get in and out. There are two more situations here.

1. I want to be able to sort or do like operations according to the data. First of all, sorting, for fields containing Chinese, the concept of sorting according to the character set is like chicken ribs. Sorting in simplified Chinese is generally expected to be sorted by pinyin. I haven't really learned about the check in MySQL, but from the programs I've come into contact with, I need to do this kind of sorting, which is to build a special field to store pinyin. On the other hand, there are polyphonic words in Pinyin. If it is UTF-8, there is also a situation in which a certain range of Chinese is shared by China, Japan and South Korea at the same time. It is not so easy to implement, so neither GBK nor UTF-8 checksets of MySQL should implement pinyin. I dare say that most of the websites that use MySQL in China now use a check set that is just a byte sort. For byte sorting, you don't need to use any character set at all. So for Chinese sites, MySQL character parity doesn't make any sense in sorting.

But when it comes to like operations, it makes a little bit of sense. For example, my like'%a%', may match to a part of Chinese that contains a. Of course, this will not happen under utf-8, because the storage format of utf-8 causes a to be only a, not part of a multi-byte character. But there may be this problem in other character sets. In the end, like, like order, makes parity meaningless.

two。 If you don't need to sort data, like, or full-text search at all, stop using things like char,varchar,text. Binary,varbinary,BLOB is the right choice. Things like binary do not convert character sets when they are stored or removed, but when sorting, they are sorted only according to binary content, so they are much more efficient than char,varchar,text.

In this case, there is no need for character sets. However, according to the current MySQL architecture, the character set operation between client and connection ignores the field type, and the character set conversion still occurs between the two nodes.

Also mention the set character set in PHP. Please stop using statements like mysql_query ("setnamesutf8"). Mysql_set_charset () is the most complete way to set the character set. The latter has one more setting than the former, that is, the charset member of structMySQL is also set. This member variable plays an important role in escape, especially for GBK, an encoding format that runs with "" as part of a character. If you only use mysql_query ("setnamesXXX"), there will be major security vulnerabilities in some character sets, causing mysql_real_escape_string to become as insecure as addslashes.

If the computer wants to process all kinds of character set text accurately, it needs to carry on the character coding, so that the computer can recognize and store all kinds of characters.

On how to deal with the character set in MySQL to share here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.