针对于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_funct
在创建过程中可能会遇见：
[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);
iffdate isnullTHEN
RETURN'';
endif;
ifformat isnullTHEN
RETURN'';
endif;
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)) RETURNSint
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
魔乐社区（Modelers.cn) 是一个中立、公益的人工智能社区，提供人工智能工具、模型、数据的托管、展示与应用协同服务，为人工智能开发及爱好者搭建开放的学习交流平台。社区通过理事会方式运作，由全产业链共同建设、共同运营、共同享有，推动国产AI生态繁荣发展。
更多推荐
2
0- 0
已为社区贡献2条内容
所有评论(0)