MySQL implementation first letter from a-Z sort

1. General sort ASC DESC

ASC Positive Sequence

DESC Flashback

-- no more talking here .

2. Custom Sorting

custom sorting is sorted according to the specific string (number) order that you want. mainly using the function FIELD (STR,STR1,STR2,STR3,...)

MySQL Custom Sort,str with str1,str2,str3 ... to compare and follow STR1,STR2,STR3 ... the sequential output, if you encounter Str to be NULL or does not exist STR1,STR2,STR3 ... The sequence for the case in the 0 ,

EG1:

SELECT * FROM Test order BY field (value, ' test1 ', ' test2 ', ' test3 ', ' test4 ') asc/desc

EG2:

SELECT * from test where value in (' Test1 ', ' test2 ', ' test3 ', ' test4 ') the Order by field (value, ' test1 ', ' test2 ', ' test3 ', ' test4 ') asc/desc-- Ensure that only the conditions are met for sorting

3. Sort by Chinese phonetic Alphabet

If the table field uses a GBK Encoded, we can order by value directly, because GBK itself is sorted by phonetic Alphabet abcdefghigk ... , the second is compared when the first bit is the same, and so on. If the table field uses the UTF-8 encoding, usually we will encode it so that we can use the MySQL convert method to convert GBK to sort.

EG1:

SELECT * from the user ORDER by CONVERT (name USING GBK) ASC;

As we can see, this method of matching does not combine Chinese and English to sort together, but to sort each of them. Therefore, we use the MySQL stored procedure to do whether the user's name is Chinese or English, can be a comprehensive sort, the specific code is as follows:

　　　

1 DELIMITER $$2 CREATE FUNCTION' Fristpinyin ' (p_nameVARCHAR(255))RETURNS varchar(255) CHARSET UTF83 Deterministic4 BEGIN   5     DECLAREV_returnVARCHAR(255); 6     DECLAREV_boolINT DEFAULT 0; 7     DECLAREFirst_varcharVARCHAR(1); 8    9     SETFirst_varchar=  Left(CONVERT(P_name USING GBK),1); Ten     SELECTFirst_varchar REGEXP'[A-za-z]'  intoV_bool;  One     IFV_bool= 1  Then   A       SETV_return=First_varchar;  -     ELSE   -       SETV_return=ELT (INTERVAL (CONV (HEX ( Left(CONVERT(P_name USING GBK),1)), -,Ten),      the           0xb0a1,0xb0c5,0xb2c1,0xb4ee,0xb6ea,0XB7A2,0xb8c1,0xb9fe,0xbbf7,      -           0xbfa6,0xc0ac,0xc2e8,0xc4c3,0xc5b6,0xc5be,0xc6da,0XC8BB,0xc8f6, -                     0xCBFA,0xCDDA,0XCEF4,0xd1b9,0xd4d1),      -       'A','B','C','D','E','F','G','H','J','K','L','M','N','O','P','Q','R','S','T','W','X','Y','Z');  +     END IF;  -     RETURNV_return;  + END$$   ADELIMITER;

View Code

The query statement is: Select Fristpinyin (name) as Pinyin_index,name from App_user ORDER by Fristpinyin (name) ASC;

Attention:

1) The Fristpinyin function only needs to run once, directly after the operation of the query statement, if the following also run together will appear:[ERR] 1304-function Fristpinyin already exists ;

2) Since Hanyu Pinyin does not take ' I ', ' U ', ' V ', ' W ' start, so the conversion is only ' A ', ' B ', ' C ', ' D ', ' E ', ' F ', ' G ', ' H ', ' J ', ' K ', ' L ', ' M ', ' N ', ' O ', ' P ', ' Q ', ' R ', ' S ', ' T ', ' W ', ' X ', ' Y ', ' Z '.

Three sorts of comparisons result:

MySQL implementation first letter from a-Z sort