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’)

数学函数

日期函数

函数	功能	示例	注意事项
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)

聚合函数

其它函数

函数	功能	示例	注意事项
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),最大区别在于函数有返回值，存储过程没有返回值。