针对于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

　　

 

oracle部分函数转换mysql：

to_char() :dateformat();

to_number:CAST('数值' AS SIGNED   INTEGER)；

mysql 24小时格式化格式：'%Y%m%d%H%i%S'