Mysql实现汉字首字母大写搜索

浅沫记忆

一、需求
在KTV项目中需要针对歌手和歌名的拼音首字母大写进行搜索即   周杰伦x=》ZJLx 类似这种的模糊搜索。
二、方案
1.在实现过程中其实有很多种，比如msyql中对name字段在加一个拼音的字段，用来专门存每个字符的首字母大写，在搜索的时候直接搜索该字段。这个方法可以只是在添加歌曲或者更新歌曲名的时候需要维护这个拼音字段不是很方便。
2.mysql在查询的时候将歌曲名字汉字首字母转拼音然后子查询。这个方法的好处是只需要在查询的时候做处理，不需要单独维护其他的字段。在性能上会有些影响。
三、实现
主要实现方案二、首先需要创建自定义函数get_chinese_initial 函数，该函数主要获取传入字符的拼音首字母大写。

DELIMITER $$
CREATE FUNCTION `get_chinese_initials`(in_string VARCHAR(255)) RETURNS VARCHAR(255) CHARSET utf8
NO SQL
BEGIN
DECLARE tmp_str VARCHAR(255) CHARSET gbk DEFAULT '' ; 
DECLARE tmp_len SMALLINT DEFAULT 0;
DECLARE tmp_loc SMALLINT DEFAULT 0;
DECLARE tmp_char VARCHAR(2) CHARSET gbk DEFAULT '';
DECLARE tmp_rs VARCHAR(255)CHARSET gbk DEFAULT '';
DECLARE tmp_cc VARCHAR(2) CHARSET gbk DEFAULT '';
SET tmp_str = in_string;
SET tmp_len = LENGTH(tmp_str);
WHILE tmp_len > 0 DO 
  SET tmp_char = LEFT(tmp_str,1);
  SET tmp_cc = tmp_char;
  #获取字符的编码范围的位置，为了确认汉字拼音首字母是那一个
  SET tmp_loc=INTERVAL(CONV(HEX(tmp_char),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);
  #判断左端首个字符是多字节还是单字节字符，要是多字节则认为是汉字且作以下拼音获取，要是单字节则不处理。如果是多字节字符但是不在对应的编码范围之内，即对应的不是大写字母则也不做处理，这样数字或者特殊字符就保持原样了
  IF (LENGTH(tmp_char)>1 AND tmp_loc>0 AND tmp_loc<24) THEN
  #获得汉字拼音首字符
  SELECT ELT(tmp_loc,'A','B','C','D','E','F','G','H','J','K','L','M','N','O','P','Q','R','S','T','W','X','Y','Z') INTO tmp_cc; 
  END IF;
  #将当前tmp_str左端首个字符拼音首字符与返回字符串拼接
  SET tmp_rs = CONCAT(tmp_rs,tmp_cc);
  #将tmp_str左端首字符去除
  SET tmp_str = SUBSTRING(tmp_str,2);
  #计算当前字符串长度
  SET tmp_len = LENGTH(tmp_str);
END WHILE;
RETURN tmp_rs;
END$$
DELIMITER ;

这个方法的原理是从左到右遍历字符串的每一位，然后查看每一位的长度 (length(name)) 如果长度大于1则表示是汉字，然后根据编码范围获取大写拼音，等于1表示为单词，或则特殊字符不处理。
执行成功后 查看创建结果

SHOW FUNCTION STATUS

有的话就执行成功了。
查询示例

通过调用get_chinese_initials 方法可以直接获取到name字段中，中文的首字母大写，对于特殊 小写字符b 、大写字符A、数字1都 不处理，如果需要处理需要手动修改while语句中 的条件即可。
下面是实际调用测试:

四、结束语
mysql自定义函数请参考​
另外在创建的过程中会出现 This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declarat... 的问题，这个是因为数据库开启了binlog 在创建的过程中 需要满足一下几个参数

1.DETERMINISTIC 不确定的
2.NO SQL没有sql语句，当然也不会修改数据
3.READS SQL DATA只是读取数据，当然也不会修改数据
4.MODIFIES SQL DATA要修改数据
5.CONTAINS SQL包含了SQL语句
其中在function里，只有DETERMINISTIC，NO SQL和READS SQL DATA被支持。如果我们开启了bin-log，我们就必须为我们的function指定一个参数。

即

​