mysql自定义函数

create function testdate(pbirthday date)
returns integer
begin
return year(now()) - year(pbirthday);
end;


select testdate(birthday)<40 from user;


set global log_bin_trust_function_creators = 1;
DROP FUNCTION IF EXISTS rand_string;
CREATE FUNCTION rand_string(n INT)
RETURNS VARCHAR(255)
BEGIN
DECLARE chars_str varchar(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
DECLARE return_str varchar(255) DEFAULT '';
DECLARE i INT DEFAULT 0;
WHILE i < n DO
SET return_str = concat(return_str,substring(chars_str , FLOOR(1 + RAND()*62 ),1));
SET i = i +1;
END WHILE;
RETURN return_str;
END ;


select rand_string(32);


set global log_bin_trust_function_creators = 1;
DROP FUNCTION IF EXISTS imeiToUerId;
create function imeiToUerId(myiImei varchar(30))
returns integer
begin
DECLARE result integer DEFAULT 0;
select user_id into result from user_users where imei=myiImei LIMIT 1;
return result;
end;


功能：截取指定字符串 从 某子字符串 开始（包括子字符串） 到 指定字符串。如：CALL sp_str('小鸡跳棋_1.0.0_128:100;杨家将战斗_1.0.2_129:3267','小鸡跳棋',';',@result);（返回结果为：小鸡跳棋_1.0.0_128:100）

set global log_bin_trust_function_creators = 1;
DROP FUNCTION IF EXISTS subValue;
create function subValue(p_str VARCHAR(10000), p_begin_str VARCHAR(50), p_end_str VARCHAR(50))
returns VARCHAR(255)
begin
DECLARE p_result varchar(255) DEFAULT null;

DECLARE m_len INT DEFAULT 0;
DECLARE m_index INT DEFAULT 0;
select locate(p_begin_str,p_str)+char_length(p_begin_str) into m_index;
select locate(p_end_str,p_str,m_index) into m_len;
if(m_len=0) then
select SUBSTRING(p_str,m_index,char_length(p_str)) INTO p_result;
end if;

if(m_len>0) then
select SUBSTRING(p_str,m_index,m_len-m_index) INTO p_result;
end if;

select CONCAT(p_begin_str,p_result) into p_result;

return p_result;
end;