关于myql函数不能执行的问题

最新推荐文章于 2023-03-09 11:42:08 发布

shunshengli

最新推荐文章于 2023-03-09 11:42:08 发布

阅读量573

点赞数

CC 4.0 BY-SA版权

文章标签： MySQL 函数

本文链接：https://blog.youkuaiyun.com/shunshengli/article/details/52641267

本文讨论了在MySQL中遇到函数执行不成功的情况，具体是一个汉字转换为拼音的函数。问题在于函数的确定性标识未设置，导致执行失败。解决方案是在函数定义中添加`DETERMINISTIC`关键字，确保相同输入总能得到相同输出。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

今天一同事从网上找到一个函数问，为什么函数执行不成功，具体函数如下：

-- 建立汉字转换拼音函数
DROP FUNCTION IF EXISTS to_pinyin;
DELIMITER $
CREATE FUNCTION to_pinyin(NAME VARCHAR(255) CHARSET gbk)
RETURNS VARCHAR(255) CHARSET gbk
BEGIN
    DECLARE mycode INT;
    DECLARE tmp_lcode VARCHAR(2) CHARSET gbk;
    DECLARE lcode INT;

DECLARE tmp_rcode VARCHAR(2) CHARSET gbk;
DECLARE rcode INT;

DECLARE mypy VARCHAR(255) CHARSET gbk DEFAULT '';
DECLARE lp INT;

SET mycode = 0;
SET lp = 1;

SET NAME = HEX(NAME);

WHILE lp < LENGTH(NAME) DO

        SET tmp_lcode = SUBSTRING(NAME, lp, 2);
        SET lcode = CAST(ASCII(UNHEX(tmp_lcode)) AS UNSIGNED);
        SET tmp_rcode = SUBSTRING(NAME, lp + 2, 2);
        SET rcode = CAST(ASCII(UNHEX(tmp_rcode)) AS UNSIGNED);
        IF lcode > 128 THEN
            SET mycode =65536 - lcode * 256 - rcode ;
            SELECT CONCAT(mypy,pin_yin_) INTO mypy FROM t_base_pinyin WHERE CODE_ >= ABS(mycode) ORDER BY CODE_ ASC LIMIT 1;
            SET lp = lp + 4;
        ELSE
            SET mypy = CONCAT(mypy,CHAR(CAST(ASCII(UNHEX(SUBSTRING(NAME, lp, 2))) AS UNSIGNED)));
            SET lp = lp + 2;
        END IF;
    END WHILE;
    RETURN LOWER(mypy);
END;
$
DELIMITER ;

--------------------------------------------------------------------------------------------------------

官网5.6 语法如下：（CREATE PROCEDURE and CREATE FUNCTION Syntax）

CREATE
    [DEFINER = { user | CURRENT_USER }]
    PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body

CREATE
    [DEFINER = { user | CURRENT_USER }]
    FUNCTION sp_name ([func_parameter[,...]])
    RETURNS type
    [characteristic ...] routine_body

proc_parameter:
    [ IN | OUT | INOUT ] param_name type

func_parameter:
    param_name type

type:
    Any valid MySQL data type

characteristic:
    COMMENT 'string'
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }

routine_body:
    Valid SQL routine statement

官网参考地址：http://dev.mysql.com/doc/refman/5.6/en/create-procedure.html

在mysql中，函数的执行结果如果不确定，创建函数则不成功。各关键字含义如下：

[NOT] DETERMINISTIC 表示结果是不确定的，相同的输入可能得到不同的输出。如果没有指定任意一个值，默认为[NOT] DETERMINISTIC

CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA：指明子程序使用SQL语句的限制。

CONTAINS SQL表明子程序包含SQL语句，但是不包含读写数据的语句；

NO SQL表明子程序不包含SQL语句；

READS SQL DATA：说明子程序包含读数据的语句；

MODIFIES SQL DATA表明子程序包含写数据的语句。

--------------------------------------------------------------------------------------------------------

将上述SQL增加关键字，做如下改写即可执行：

CREATE FUNCTION to_pinyin(NAME VARCHAR(255) CHARSET utf8)
RETURNS VARCHAR(255) CHARSET utf8
DETERMINISTIC
BEGIN
DECLARE mycode INT;
DECLARE tmp_lcode VARCHAR(2) CHARSET utf8;
DECLARE lcode INT;
DECLARE tmp_rcode VARCHAR(2) CHARSET utf8;
DECLARE rcode INT;
DECLARE mypy VARCHAR(255) CHARSET utf8 DEFAULT '';
DECLARE lp INT;
SET mycode = 0;
SET lp = 1;
SET NAME = HEX(NAME);
WHILE lp < LENGTH(NAME) DO
SET tmp_lcode = SUBSTRING(NAME, lp, 2);
SET lcode = CAST(ASCII(UNHEX(tmp_lcode)) AS UNSIGNED);
SET tmp_rcode = SUBSTRING(NAME, lp + 2, 2);
SET rcode = CAST(ASCII(UNHEX(tmp_rcode)) AS UNSIGNED);
IF lcode > 128 THEN
SET mycode =65536 - lcode * 256 - rcode ;
SELECT CONCAT(mypy,pin_yin_) INTO mypy FROM t_base_pinyin WHERE CODE_ >= ABS(mycode) ORDER BY CODE_ ASC LIMIT 1;
SET lp = lp + 4;
ELSE
SET mypy = CONCAT(mypy,CHAR(CAST(ASCII(UNHEX(SUBSTRING(NAME, lp, 2))) AS UNSIGNED)));
SET lp = lp + 2;
END IF;
END WHILE;
RETURN LOWER(mypy);
END;