（原创）phpAdmin ，mysql 汉字拼音首字查询 解析 2021-02-27

 

（原创）phpAdmin ，mysql 汉字拼音首字查询  解析

最近在用phpAdmin,想实现输入汉字拼音首字母查询出汉字，例如输入ZS,查询出“知识”，“证书”，“在实验室”等等这个功能，网上查询很多文章都是如下这些代码。

 BEGIN
 DECLARE V_RETURN VARCHAR(255);
    DECLARE V_FIRST_CHAR VARCHAR(2);
    SET V_FIRST_CHAR = UPPER(LEFT(PARAM,1));
    SET V_RETURN = V_FIRST_CHAR;
    IF LENGTH( V_FIRST_CHAR) <> CHARACTER_LENGTH( V_FIRST_CHAR ) THEN
    SET V_RETURN = ELT(INTERVAL(CONV(HEX(LEFT(CONVERT(PARAM 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');
    END IF;
    RETURN V_RETURN;
    END$$

初看不懂，消化了很久才明白。现将我的调试记录如下：

调试1

如上图，开始添加程序；

 

 

调试2

 如上图，添加函数，直接将网上查询到的代码放进去。

调试3

如上图，函数 getPY2 添加完成，点击“执行”，测试函数。参数“PARAM”设为“d”，点击“执行”。

如上图，结果为“D”,在测试其它值。

当输入是汉字“啊”时，返回结果是空。

这和预期不符。再次修改代码如下：

如上图，在声明的两个变量后面添加“character set utf8”,并再次测试函数。

如上图，结果只能返回第一个汉字的拼音首字母。

BEGIN 
 DECLARE V_RETURN VARCHAR(255) character set utf8;
 DECLARE V_TEMP_RETURN  VARCHAR(255) character set utf8; 
 DECLARE V_TEMP_CHAR VARCHAR(1) character set utf8; 
DECLARE i VARCHAR(1) character set utf8; 
#set PARAM=CONVERT(PARAM USING gbk);
# RETURN LENGTH(PARAM);
# RETURN CHARACTER_LENGTH(PARAM);
SET V_RETURN='';
SET V_TEMP_RETURN='' ;
 SET i = 1;  
 WHILE i<CHARACTER_LENGTH(PARAM)+1 DO 
SET V_TEMP_CHAR = UPPER(SUBSTRING(PARAM,i,1)); 
SET V_TEMP_RETURN=V_TEMP_CHAR;
#RETURN  V_TEMP_CHAR;

#RETURN LENGTH( V_TEMP_CHAR);
#RETURN CHARACTER_LENGTH( V_TEMP_CHAR );
 IF LENGTH( V_TEMP_CHAR) <> CHARACTER_LENGTH( V_TEMP_CHAR ) THEN 
 SET V_TEMP_RETURN = ELT(INTERVAL(CONV(HEX(LEFT(CONVERT(V_TEMP_CHAR 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');
 END IF;
set V_RETURN=concat(V_RETURN,V_TEMP_RETURN);
 SET i = i+1;   
 END WHILE; 
 RETURN V_RETURN;
 END

如上图，最后的办法是在代码中添加循环，依次截取字符，并将最后的字母拼接在一起。测试结果如函数“getPY”所示如下：

 

 

以上是打通了函数，下面加入sql中看看。

 

 

到此结束，后文是部分函数的说明。

 1. CONVERT(expr USING transcoding_name)
 CONVERT(expr USING transcoding_name) converts data between different character sets. In MySQL, transcoding names are the same as the corresponding character set names. For example, this statement converts the string 'abc' in the default character set to the corresponding string in the utf8 character set:

SELECT CONVERT('abc' USING utf8);

For a string argument str, HEX() returns a hexadecimal string representation of str where each byte of each character in str is converted to two hexadecimal digits. (Multibyte characters therefore become more than two digits.) The inverse of this operation is performed by the UNHEX() function.

2.CONV(N,from_base,to_base)

Converts numbers between different number bases. Returns a string representation of the number N, converted from base from_base to base to_base. Returns NULL if any argument is NULL. The argument N is interpreted as an integer, but may be specified as an integer or a string. The minimum base is 2 and the maximum base is 36. If from_base is a negative number, N is regarded as a signed number. Otherwise, N is treated as unsigned. CONV() works with 64-bit precision.

mysql> SELECT CONV('a',16,2);
        -> '1010'
mysql> SELECT CONV('6E',18,8);
        -> '172'
mysql> SELECT CONV(-17,10,-18);
        -> '-H'
mysql> SELECT CONV(10+'10'+'10'+X'0a',10,10);
        -> '40'

 

3.ELT(N,str1,str2,str3,...)

ELT() returns the Nth element of the list of strings: str1 if N = 1, str2 if N = 2, and so on. Returns NULL if N is less than 1 or greater than the number of arguments. ELT() is the complement of FIELD().

mysql> SELECT ELT(1, 'Aa', 'Bb', 'Cc', 'Dd');
        -> 'Aa'
mysql> SELECT ELT(4, 'Aa', 'Bb', 'Cc', 'Dd');        
        
        
4.LENGTH(str)

Returns the length of the string str, measured in bytes. A multibyte character counts as multiple bytes. This means that for a string containing five 2-byte characters, LENGTH() returns 10, whereas CHAR_LENGTH() returns 5.

 

5.CHAR_LENGTH(str)

Returns the length of the string str, measured in characters. A multibyte character counts as a single character. This means that for a string containing five 2-byte characters, LENGTH() returns 10, whereas CHAR_LENGTH() returns 5.