针对于oracle里的to_char函数和to_number函数mysql不支持，在mysql‘里去自定义这两种函数,或者去做相应的函数转换

在创建过程中可能会遇见：

[HY000][1418] 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)
 

这个时候执行：

set global log_bin_trust_function_creators=TRUE;

mysql自定义to_char：

 

CREATE FUNCTION `TO_CHAR`(`fdate` datetime,`format` varchar(255)) RETURNS varchar(255) CHARSET utf8mb3

BEGIN

DECLARE v_format VARCHAR(100);

    if fdate is null THEN

        RETURN '';

    end if;

    if format is null THEN

        RETURN '';

    end if;

SET v_format=format;

SET v_format = REPLACE(v_format,'hh24','%H');

SET v_format = REPLACE(v_format,'HH24','%H');

SET v_format = REPLACE(v_format,'hh','%H');

SET v_format = REPLACE(v_format,'HH','%H');

SET v_format = REPLACE(v_format,'mi','%i');

SET v_format = REPLACE(v_format,'MI','%i');

SET v_format = REPLACE(v_format,'mI','%i');

SET v_format = REPLACE(v_format,'Mi','%i');

SET v_format = REPLACE(v_format,'ss','%s');

SET v_format = REPLACE(v_format,'SS','%s');

SET v_format = REPLACE(v_format,'YYYY','%Y');

SET v_format = REPLACE(v_format,'yyyy','%Y');

SET v_format = REPLACE(v_format,'MM','%m');

SET v_format = REPLACE(v_format,'mm','%m');

SET v_format = REPLACE(v_format,'DD','%d');

SET v_format = REPLACE(v_format,'dd','%d');

    RETURN DATE_FORMAT(fdate, v_format);

END;

　mysql自定义to_number:

 

CREATE FUNCTION `to_number`(`st` varchar(20)) RETURNS int

BEGIN

RETURN cast( st   as   SIGNED   INTEGER) ;

END

 创建函数模板示例：

CREATE    [DEFINER = user]    FUNCTION sp_name ([func_parameter[,...]])    RETURNS type    [characteristic ...] routine_body   func_parameter:    param_name typetype:    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 BEGIN　　[statement_list]　　...    returnEND

查看函数定义： 

 show create function to_char

删除函数定义：

 DROP FUNCTION IF EXISTS sp_name

查看函数状态：

show function status