今天一同事從網(wǎng)上找到一個函數(shù)問，為什么函數(shù)執(zhí)行不成功，具體函數(shù)如下：

-- 建立漢字轉(zhuǎn)換拼音函數(shù)
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 ;

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

官網(wǎng)5.6 語法如下：（CREATE PROCEDURE and CREATE FUNCTION Syntax）

CREATE
    [DEFINER = { user | CURRENT_USER }]
    PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_bodyCREATE
    [DEFINER = { user | CURRENT_USER }]
    FUNCTION sp_name ([func_parameter[,...]])
    RETURNS type
    [characteristic ...] routine_bodyproc_parameter:
    [ IN | OUT | INOUT ] param_name typefunc_parameter:    param_name typetype:    Any valid MySQL data typecharacteristic:
    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

官網(wǎng)參考地址：http://dev.mysql.com/doc/refman/5.6/en/create-procedure.html

在mysql中，函數(shù)的執(zhí)行結(jié)果如果不確定，創(chuàng)建函數(shù)則不成功。各關(guān)鍵字含義如下：

[NOT] DETERMINISTIC 表示結(jié)果是不確定的，相同的輸入可能得到不同的輸出。如果沒有指定任意一個值，默認為[NOT] DETERMINISTIC 

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

CONTAINS SQL表明子程序包含SQL語句，但是不包含讀寫數(shù)據(jù)的語句；

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

READS SQL DATA：說明子程序包含讀數(shù)據(jù)的語句；

MODIFIES SQL DATA表明子程序包含寫數(shù)據(jù)的語句。

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

將上述SQL增加關(guān)鍵字，做如下改寫即可執(zhí)行：

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;