MySQL query results sorted, name field has Chinese name, English name, how to sort by a-Z order name

1. The name field is used to store the customer's name, and the query result needs to be sorted by surname Pinyin, there is now a problem,
Names include both Chinese and English names, such as Zhang San, John Doe, Jaewon Park, Lao Wang
So query out the results of the English name of the person forever ranked first
Sort I'm using the following sentence
CONVERT ( name USING gbk) COLLATE gbk_chinese_ci

How can the first letter of English be sorted by pinyin?

Reply content:

1. The name field is used to store the customer's name, and the query result needs to be sorted by surname Pinyin, there is now a problem,
Names include both Chinese and English names, such as Zhang San, John Doe, Jaewon Park, Lao Wang
So query out the results of the English name of the person forever ranked first
Sort I'm using the following sentence
CONVERT ( name USING gbk) COLLATE gbk_chinese_ci

How can the first letter of English be sorted by pinyin?

Thank you for your invitation.
Just use MySQL to solve, Chinese and English mix in a A-Z sort, I have no way

One of my little ideas is that you can add a column, turn Chinese into pinyin, and then sort by alphabetical

Create a new column to save the first letter if it is in English, and if it is in Chinese, save the pinyin initials and use it to sort
At least how to get the Chinese pinyin, online solutions a lot, search for it Yourself

Add a column, save the corresponding name of the pinyin, so it is a good sort of

'--Create a function to get the first letter of Chinese characters
CREATE FUNCTION FirstPinyin (p_name varchar (255)) RETURNS varchar (255) CHARSET UTF8
BEGIN

DECLARE V_RETURN VARCHAR(255);SET V_RETURN = ELT(INTERVAL(CONV(HEX(left(CONVERT(P_NAME USING gbk),1)),16,10),     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');    RETURN V_RETURN;

END
--Writing query statements
SELECT * from article ORDER by Ifnull (Firstpinyin (title), substring (title, 1, 1)) ASC '

Convert Chinese to pinyin and then sort by first letter

Recommend Chinese pinyin to handle Chinese pinyin quality solution based on Thesaurus

