MySQL is sorted by the pinyin of Chinese characters, according to the first letter category

Sometimes the project needs to follow the pinyin of Chinese characters, such as contact list, mineral classification, etc., some of which need to be phonetic Alphabet from A to Z.

　　if the stored Chinese character field encoding uses the GBK character set, because the GBK code encoding itself uses the phonetic sorting method (commonly used first-class Chinese characters 3,755 Pinyin sorting, two-level Chinese characters are not, but considering the names of people are commonly used Chinese characters, Therefore, only for the first level of Chinese characters can be correctly sorted also enough, directly after the query statement to add the order by name ASC, the query results will be sorted by the last name in ascending order; If the field that stores the name is in the UTF8 character set, you need to transcode the field at sort time, and the corresponding code is the order by convert (name using GBK) ASC, and the result of the query is also sorted in ascending order of the last name.

According to the first letter of pinyin display, you need to use an intermediate table, stored in the letter corresponding to the encoding range of Chinese characters, so that each time to take out the Chinese characters and then with the intermediate table comparison, the intermediate table to create and insert data SQL as follows:

/*Navicat MySQL Data transfertarget Server type:mysqltarget server Version:50617file encoding:65001date : 2015-07-02 11:12:15*/SETForeign_key_checks=0;-- ------------------------------Table structure for t_coslers-- ----------------------------DROP TABLE IF EXISTS' t_coslers ';CREATE TABLE' t_coslers ' (' ID ')int( One) not NULLauto_increment, ' f_py 'Char(1)CHARACTER SETUtf8DEFAULT NULL, ' Cbegin 'smallint(5) unsigned not NULL, ' cend 'smallint(5) unsigned not NULL,  PRIMARY KEY(' id ')) ENGINE=InnoDB auto_increment= - DEFAULTCHARSET=latin1;-- ------------------------------Records of T_coslers-- ----------------------------INSERT  into' T_coslers 'VALUES('1','A','45217','45252');INSERT  into' T_coslers 'VALUES('2','B','45253','45760');INSERT  into' T_coslers 'VALUES('3','C','45761','46317');INSERT  into' T_coslers 'VALUES('4','D','46318','46825');INSERT  into' T_coslers 'VALUES('5','E','46826','47009');INSERT  into' T_coslers 'VALUES('6','F','47010','47296');INSERT  into' T_coslers 'VALUES('7','G','47297','47613');INSERT  into' T_coslers 'VALUES('8','H','47614','48118');INSERT  into' T_coslers 'VALUES('9','J','48119','49061');INSERT  into' T_coslers 'VALUES('Ten','K','49062','49323');INSERT  into' T_coslers 'VALUES(' One','L','49324','49895');INSERT  into' T_coslers 'VALUES(' A','M','49896','50370');INSERT  into' T_coslers 'VALUES(' -','N','50371','50613');INSERT  into' T_coslers 'VALUES(' -','O','50614','50621');INSERT  into' T_coslers 'VALUES(' the','P','50622','50905');INSERT  into' T_coslers 'VALUES(' -','Q','50906','51386');INSERT  into' T_coslers 'VALUES(' -','R','51387','51445');INSERT  into' T_coslers 'VALUES(' -','S','51446','52217');INSERT  into' T_coslers 'VALUES(' +','T','52218','52697');INSERT  into' T_coslers 'VALUES(' -','W','52698','52979');INSERT  into' T_coslers 'VALUES(' +','X','52980','53640');INSERT  into' T_coslers 'VALUES(' A','Y','53689','54480');INSERT  into' T_coslers 'VALUES(' at','Z','54481','55289');

An example of querying the first letter of Chinese characters with an intermediate table is the following (UTF8 character set):

SELECT t1.id, T1.name, T2.f_py  from t_user T1, t_coslers T2 WHERE  CONV (HEX (left (CONVERT1between and T2.cendORDERbyconvertASC

One thing to note is that the above sorting, classification method of Polyphone support is not very good, require high-precision students to use with caution.

MySQL is sorted by the pinyin of Chinese characters, according to the first letter category