MySQL database sorting selection function and how to choose the encoding format

Preface: In the creation of the database, there will be an option, such as collation, usually in the creation of the database and did not pay attention to, but chose the default, and did not feel any problems, today to see this sudden curiosity, so read some information to do the following some summary, if there are errors, please treatise.

What is the role of this sort rule? 　　 You can avoid some errors that occur when the database is imported. Many times there will be characters garbled when importing the database, but if you customize the code, it will be easier to find the problem. In MySQL we often use utf8_unicode_ci and utf8_general_ci, there are some differences, the current, utf8_unicode_ci proofing rules only partially support the Unicode proofing rules algorithm. Some characters are still not supported. Also, combinations of tokens cannot be fully supported. This mainly affects some minority languages of Vietnam and Russia, such as Udmurt, Tatar, Bashkir and Mari.

　　Utf8_general_ci is a legacy proofing rule and does not support extensions. It can only be compared between characters. This means that the UTF8_GENERAL_CI proofing rules are relatively fast, but less accurate than the proofing rules that use UTF8_UNICODE_CI.

For example, using the Utf8_general_ci and utf8_unicode_ci two proofing rules below compare equality: ä= aö= oü= U The difference between the two proofing rules is that for utf8_general_ci the following equation is established: ß= s however, for Utf8_unicode_ci the following equation is true: ß= SS performs the UTF8 character-set collation rules associated with a specific language only if it is not a good use of the utf8_unicode_ci sort. For example, for German and French, Utf8_unicode_ci works very well, so you no longer need to create special UTF8 proofing rules for both languages.　　Utf8_general_ci also works with German and French, except ' ß ' equals ' s ', not ' SS '. If your app can accept these, then you should use UTF8_GENERAL_CI because it's fast. Otherwise, use utf8_unicode_ci because it is more accurate.

　　

Above we talked about UTF8_XXXX_CI, but for the above encoding format CI still somewhat puzzled, of course, not only CI, and ki,wi What, they represent what?

　　The collation name consists of two parts, and the first half refers to the character set supported by this collation
Such as:
Chinese_prc_cs_ai_ws
First half: Refers to the Unicode character set, the chinese_prc_ pointer to the continental simplified Unicode collation
The second half of the collation is the suffix meaning:
_bin binary Ordering
_ci (CS) is case sensitive, CI is not differentiated, CS is distinguished
_ai (AS) are accent-sensitive, AI not differentiated, as differentiated
_ki (KS) Whether the kana type is differentiated, KI does not differentiate, KS differentiates
_WI (WS) Whether the width of the WI is not differentiated, WS

　　

　　Now the collation has a certain understanding of the need for the encoding format, usually I use UTF8_GENERAL_CI, it is best to do code unification, will reduce the database garbled this situation occurs ...

Part of the article refers to http://www.jb51.net/article/48775.htm

MySQL database sorting selection function and how to choose the encoding format