Querying the pinyin initials of Chinese characters in MySQL database

The method I provide has the following characteristics:

1. Code simplification, simple to use, as long as the basic SQL statement will be the line
2. No need to build MySQL functions and other complex things
3. The most complete Chinese character library, can query 20,902 characters

Here's how:

1. Establishment of pinyin first letter data sheet
SQL code: (preferably plus primary key and index)

2. DROP TABLE IF EXISTS ' Pinyin ';
4. CREATE TABLE ' Pinyin ' (
6. ' PY ' varchar (1),
8. ' HZ1 ' varchar (1),
10. ' HZ2 ' varchar (1)
12. ) ;
16. INSERT into ' pinyin ' (' PY ', ' HZ1 ', ' HZ2 ') VALUES
18. (' A ',' acridine ',' 驁 '),
20. (' B ',' eight ',' book '),
22. (' C ',' cha ',' wrong '),
24. (' D ',' Otah ',' 鵽 '),
26. (' E ',' Ehegan ',' 樲 '),
28. (' F ',' fa ','鰒 '),
30. (' G ',' 猤 ',' hiker '),
32. (' H ',' 妎 ',' inceѕt '),
34. (' J ',' no ',' 攈 '),
36. (' K ',' ka ',' 穒 '),
38. (' L ','garbage ',' 鱳 '),
40. (' M ',' 嘸 ',' temperature '),
42. (' N ',' cabal ',' 桛 '),
44. (' O ',' Oh ',' 漚 '),
46. (' P ',' 妑 ',' exposure '),
48. (' Q ',' seven ',' 囕 '),
50. (' R ',' 呥 ',' 鶸 '),
52. (' S ',' sa',' 蜶 '),
54. (' T ',' he ',' 籜 '),
56. (' W ',' 屲 ',' clamoring '),
58. (' X ',' XI ',' 鑂 '),
60. (' Y ',' ya ',' Wan Leng '),
62. (' Z ',' as','n ');

after execution, check to see if the data records in the added table have "? The question mark, if any, indicates a problem with the database encoding.
2, query the database table in the first character phonetic Alphabet
SELECT ' Pinyin '. py, ' corresponding table '. *
From ' corresponding table ', ' Pinyin '
WHERE ' Kanji title ' >= ' HZ1 '
and ' Kanji title ' <= ' HZ2 ';
You can also directly query the first character of Chinese characters Phonetic Alphabet
SELECT ' pinyin '. Py
From ' Pinyin '
WHERE ' kanji ' >= ' hz1 '
and ' kanji ' <= ' hz2 ';
The query result is "H"

* * * * * Note
This method is only applicable to GBK encoding, the other encoding needs to be converted to GBK first, can be used with convert (words using GBK)

you can also use functions to handle
CREATEFUNCTIONfirstpy (wordsvarchar (255))RETURNSMediumtext
 BEGIN
           Declarefpychar (1);
           DeclarePCchar (1);
           Declareccchar (4);
           Set@fpy =  UPPER (Left (words,1));
           Set@pc =  (CONVERT (@fpyUSINGGBK));
           Set@cc =  Hex (@pc);
           if@cc>="8140" and@cc<= "FEA0" Then
                     begin
                               Selectpy into@fpy fromPinyinwhereHz2>[email protected]and Hz1<[email protected];
                     end;
           Endif;
           Return@fpy;
 END