mysql根据拼音首字母查询某字段以及mavicat创建函数遇到的坑

    业务需求上，需要做一个根据英文首字母查询名称的功能，在网上查阅相关资料后，决定采用mysql函数的方式完成。函数来源于https://blog.csdn.net/omayyouhappy/article/details/78352789，感谢这位博主，函数代码如下，支持三个字母以内的查询

CREATE DEFINER=`root`@`%` FUNCTION `GET_FIRST_PINYIN_CHAR`(PARAM VARCHAR(255)) RETURNS varchar(6) CHARSET utf8
BEGIN
	DECLARE V_RETURN VARCHAR(255);
	DECLARE V_RETURN_TWO VARCHAR(255);
	DECLARE V_RETURN_THREE VARCHAR(255);
	DECLARE V_FIRST_CHAR VARCHAR(2);
	DECLARE V_TWO_CHAR VARCHAR(2);
	DECLARE V_THREE_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;
	SET V_TWO_CHAR = UPPER(SUBSTR(PARAM,2,1));
	SET V_RETURN_TWO = V_TWO_CHAR;
	IF LENGTH( V_TWO_CHAR) <> CHARACTER_LENGTH( V_TWO_CHAR ) THEN
	SET V_RETURN_TWO = ELT(INTERVAL(CONV(HEX(LEFT(CONVERT(UPPER(SUBSTR(PARAM,2,1)) 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_THREE_CHAR = UPPER(SUBSTR(PARAM,3,1));
	SET V_RETURN_THREE= V_THREE_CHAR;
	IF LENGTH( V_THREE_CHAR) <> CHARACTER_LENGTH( V_THREE_CHAR ) THEN
	SET V_THREE_CHAR = ELT(INTERVAL(CONV(HEX(LEFT(CONVERT(UPPER(SUBSTR(PARAM,3,1)) 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 CONCAT(V_RETURN,V_RETURN_TWO,V_RETURN_THREE);
END

上述函数创建成功后，我们在xml文件的查询语句中写上动态sql，该功能就实现了，sql如下：

<if test="name != null and name != '' ">
    AND (name LIKE CONCAT('%',#{name},'%') or get_first_pinyin_char(name) LIKE CONCAT('%',#{name},'%'))                
</if>

 

当使用navicat创建函数时，产生了一些问题：

问题一：

ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

这是我们开启了bin-log, 我们就必须指定我们的函数是否是

• DETERMINISTIC 不确定的
• NO SQL 没有SQl语句，当然也不会修改数据
• READS SQL DATA 只是读取数据，当然也不会修改数据
• MODIFIES SQL DATA 要修改数据
• CONTAINS SQL 包含了SQL语句

其中在function里面，只有 DETERMINISTIC, NO SQL 和 READS SQL DATA 被支持。如果我们开启了 bin-log, 我们就必须为我们的function指定一个参数。
在MySQL中创建函数时出现这种错误的解决方法：
set global log_bin_trust_function_creators=TRUE;

问题二：

创建函数时提示1064语法错误，提示错误位置在函数方法体的第一个分号处，找破头也没找到错误在哪，查阅资料后发现，执行sql语句时，以'分号'判定语句是否结束。所以创建函数时，到方法体的第一个分号，就认为sql结束，导致错误

[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''

解决方法：在函数前加上'delimiter $$'，在函数后加上'$$'和'delimiter ;'

delimiter $$
CREATE DEFINER=`12212`@`%` FUNCTION test(...) RETURNS ... CHARSET utf8
BEGIN
  ...
END$$
delimiter ;

'delimiter'的作用是将原本的分号换成文中的'$$',创建函数完成后，再通过'delimiter ;'将结束符再换回分号，防止上述错误发生

但是，我在使用该方法后依旧没有解决该问题，仍然报这个错误，实在找不出问题，最后找到一种解决方法

    在navicat中先创建一个简单的函数，保存成功后，点击编辑，一行一行的将代码拷贝进来，以分号为一行，拷贝一行点击一次保存，直到将所有的代码拷贝完毕，最后终于可以正常使用。