mysql 函数 & 自定义函数

字符函数

函数	功能	示例	注意事项
concat()	连接多个字符串	concat('a','b')	连接‘a’和‘b’，返回新的字符串‘ab’
substr()	截取字符串	substr('abcde',1,3)	在字符串‘abcde’中从第1个字符开始截取3个字符，并返回
lower()/upper()	字符串大小写转换	lower('abD')	把字符串‘abD’都转换成小写，并返回
replace()	替换指定字符	replace('abcda','a','x')	把字符串‘abcda’中的‘a’都替换成‘x’，并返回新的字符串
length()	计算字符串长度	length('abcde')	返回字符串‘abcde’的长度
trim()	去掉前后空格	trim(' abc ')	返回去掉了前后空格的新字符串
lpad()/rpad()	左右填充指定字符	lpad('abc',10,'-')	用‘-’填充字符串‘abc’至长度为10
instr()	子串第一次出现的索引	instr('MySQL','S')
left()	返回字符串 s 最左边的 n 个字符	left('mysql',2)
right()	返回字符串 s 最右边的 n 个字符	right('Mysql',2)
reverse()	字符串反转（逆序）函数，返回与原始字符串顺序相反的字符串	reverse('mysql')

数学函数

函数	功能	示例	注意事项
ceil()	向上取整	ceil(1.1)	2
floor()	向下取整	ceil(1.9)	1
round()	四舍五入	round(2.5)	3
mod()	取模	mod(21,4)	1
truncate()	截断小数	truncate(1.258,2)	1.25（多余的小数位直接截断）
rand()	返回随机数	rand()	随机数[0,1)
format()	截断位数	format(1.258,2)	1.26（最后一位四舍五入得来)

日期函数

函数	功能	示例	注意事项
now()	返回当前日期时间	now()	2020-05-15 16:27:59
year()/month()/day()	返回年/月/日	year(now())	2020
hour()/minute()/secend()	返回时/分/秒	hour(now())	16
curtime()	返回时分秒部分	curtime(now())	16:27:59
curdate()	返回日期部分	curdate(now())	2020-05-15
date_format()	格式化日期	date_format(now(),'%Y/%m/%d')	2020/05/15
str_to_date()	把字符串表示的日期转换成日期格式	str_to_date('2020-05-15 00:00:00','%Y%m%d')	2020/05/15
datediff()	返回两个日期之间的天数差	datediff(now(),'2020-01-01')	135
unix_timestamp()	时间戳	unix_timestamp()
from_unixtime()	将时间戳转为date格式	from_unixtime(unix_timestamp())
time_to_sec()	date时间转为秒	time_to_sec(now())	参数必须为date格式时间，时间戳不可以
sec_to_time()	秒-->date
cast()	类型转换	cast("2017-08-29" AS datetime)

聚合函数

函数	功能	示例	注意事项
max()	返回最大值	max(列名)	返回一列的最大值
min()	返回最小值	min(列名)	返回一列的最小值
avg()	返回平均值	avg(列名)	返回一列的平均值
sum()	返回和	sum(列名)	返回一列的和
count()	返回数量	count(列名)	返回一列的数量（不计算NULL）

其它函数

函数	功能	示例	注意事项
group_concat()	将查询多行结果合并为一行	group_concat(字段 separator str) 使用str分隔
concat_ws()	拼接多个数据	concat_ws(str,字段,字段,字段)
md5()	MD5加密字符串	md5(str)
if()	类似于三目运算	if(test,t,f)	如果test是真，返回t；否则返回f
ifnull()		ifnull(a,b)	a等于null,返回b，否则返回a本身
nullif()	比较两个参数是否相同	nullif(a,b)	a=b 则返回null，否则返回a本身

自定义mysql函数

函数的格式

CREATE FUNCTION 函数名(参数名 参数类型) RETURNS FLOAT

# 固定格式
delimiter $
CREATE FUNCTION 函数名(参数列表) RETURNS 返回值类型
BEGIN

	函数体;

END

注意点：
1、参数列表：参数名 参数类型
2、函数肯定要有返回值，返回值只有一个
3、函数的参数可以不写

实例 ，批量插入数据

# ---------------自定义函数--------------------------
# 创建数据库
create database bigDataBase;
# 切换使用数据库
use bigDataBase;
# 创建表
create table testDigData
(
    id             int unsigned primary key auto_increment,
    name           varchar(255) not null default '',
    age            int          not null default 0,
    phone          varchar(255) not null default '',
    createDate     datetime     not null default NOW(),
    lastUpdateTime datetime     not null default NOW()
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8;
# 查看表
show table status;
#查看log_bin_trust_function_creators是否开启：默认关闭 批量插入数据必须为开启状态
# 参数log_bin_trust_function_creators，它功效用于开启二进制模块
show variables like 'log_bin_trust_function_creators';
# 开启
set global log_bin_trust_function_creators = 1;

# 自定义函数
delimiter $
create function bigInsto(number int)
    returns varchar(200)
begin
    declare rand_str varchar(100) default 'qwertyuiopasdfghjklzxcvbnmQWERTYUIOPASDFGHJKLZXCVBNM';
    declare countNumber int default 0;
    WHILE countNumber <= number
        DO
            insert into testDigData(name, age, phone, createdate, lastupdatetime)
            VALUES (substr(rand_str, ceil(rand() * 5),
                (ceil(rand() * 5))),
                (ceil(rand() * 100)),
                concat('1',substring(cast(3 + (rand() * 10) % 7 AS char(50)), 1, 1),right(left(trim(cast(rand() AS char(50))), 11), 9)),
                now(), now());
            set countNumber =countNumber+1;
        END WHILE;
    return countNumber;
end;

# -----解释------
delimiter $
create function bigInsto(number int) --创建一个函数名为bigInsto  入参为(参数名,数据类型)
    returns varchar(200)--返回数据，数据类型
begin	--函数体开始
    declare rand_str varchar(100) default 'qwertyuiopasdfghjklzxcvbnmQWERTYUIOPASDFGHJKLZXCVBNM'; --定义一个rand_str的变量 数据类型，默认值
    declare countNumber int default 0; --定义【declare关键字】一个rand_str的变量 数据类型，默认值
    WHILE countNumber <= number --while 循环
        DO
        ---insert 语句
            insert into testDigData(name, age, phone, createdate, lastupdatetime) 
            VALUES (substr(rand_str, ceil(rand() * 5),
                (ceil(rand() * 5))),
                (ceil(rand() * 100)),
                concat('1',substring(cast(3 + (rand() * 10) % 7 AS char(50)), 1, 1),right(left(trim(cast(rand() AS char(50))), 11), 9)),
                now(), now());
            set countNumber =countNumber+1;
        END WHILE;
    return countNumber; --返回值
end; --函数体结束

# 查当前创建的所有自定义函数
show  function status;
# 使用自定义函数批量参数 数据
select  bigInsto(1000000);
# 查询当前表数据
select * from testDigData;
# 删除自定义的函数
drop function bigInsto;
# 清除表数据 并且清除组件id
truncate  table  testDigData;

注意：

• 由于进行大批量数据插入，mysql会报一个错误。需要设置参数log_bin_trust_function_creators，它功效用于开启二进制模块，否则会报错：This function has none of DETERMINISTIC..开启log_bin_trust_function_creators
• 函数(FUNCTION)和存储过程(PROCEDURE),最大区别在于函数有返回值，存储过程没有返回值。

---

相关Mysql文章系列

mysql索引&索引数据结构

mysql 函数 & 自定义函数

mysql常用命令&架构&引擎

mysql事务

mysql日志