功能 - 函数 - 《MySQL认知和调优》

函数删除与创建
函数示例
参考

函数删除与创建

确保函数名唯一，在创建时需要先删除同名函数，实现的方式可以通过一下两种方式：
创建或替换

 CREATE OR REPLACE FUNCTION

删除后创建

DROP FUNCTION IF EXISTS `FUNCTION_NAME`;
CREATE FUNCTION

函数示例

IP转化成整数

DROP FUNCTION IF EXISTS `GAOX_A2UI`;
CREATE FUNCTION `GAOX_A2UI` ( ip CHAR ( 31 ) ) 
-- 返回值类型
RETURNS  INT UNSIGNED
BEGIN
-- 定义变量及类型
DECLARE    value1 CHAR ( 10 );
DECLARE    value2 CHAR ( 10 );
DECLARE    value3 CHAR ( 10 );
DECLARE    value4 CHAR ( 10 );

SET value1 = SUBSTRING_INDEX( ip, '.', 1 );

SET value2 = SUBSTRING_INDEX( ip, '.', 2 );
SET value2 = SUBSTRING_INDEX( value2, '.', - 1 );

SET value3 = SUBSTRING_INDEX( ip, '.', - 2 );
SET value3 = SUBSTRING_INDEX( value3, '.', 1 );

SET value4 = SUBSTRING_INDEX( ip, '.', - 1 );

SET value1 = value1 << 24;
SET value2 = value2 << 16;
SET value3 = value3 << 8;
-- 这里进行了一步转换，将char数组转换成无符号int
-- RETURN CONVERT((value1 + value2 + value3 + value4),UNSIGNED INT);
-- 内部自动类型转换
RETURN (value1 + value2 + value3 + value4);
END;

调用外部函数和MySQL自带的函数是一样的，

SELECT GAOX_A2UI("192.168.1.38") as ipi;

整数转IP，可以选择填充0

DROP FUNCTION IF EXISTS `GAOX_UI2A`;
CREATE FUNCTION `GAOX_UI2A` ( ip INT UNSIGNED, fullAddr INT ) 
RETURNS CHAR ( 31 ) 

BEGIN
DECLARE    str4 CHAR ( 3 );
DECLARE    str3 CHAR ( 3 );
DECLARE    str2 CHAR ( 3 );

-- 取后八位二进制
SET str4 = ip & 0xFF;
-- 右移8位
SET ip = ip >> 8;
SET str3 = ip & 0xFF;
SET ip = ip >> 8;
SET str2 = ip & 0xFF;
SET ip = ip >> 8;

IF fullAddr =1 THEN
SET ip = LPAD( ip, 3, '0' );
SET str2 = LPAD( str2, 3, '0' );
SET str3 = LPAD( str3, 3, '0' );
SET str4 = LPAD( str4, 3, '0' );    
END IF;

RETURN CONCAT( ip, '.', str2, '.', str3, '.', str4 );
END;

SELECT INET_ATON('192.168.1.38') as ipa
UNION ALL
SELECT    GAOX_UI2A ( 3232235814 ,0) as ipa
UNION ALL
SELECT    GAOX_UI2A ( 3232235814 ,1) as ipa;

参考

http://dcx.sybase.com/1101/zh/dbreference_zh11/create-function-user-defined-statement.html
https://dev.mysql.com/doc/refman/8.0/en/create-procedure.html