实用数据库函数总结

脱敏处理
方法一：insert(mobile,4,4,'****')表示mobile从第四个字符开始往后的4个字符用4个星号代替
方法二：函数组合 concat(left(mobile,3),'****',right(mobile,4)) 其中concat表示把多个字符串拼接，left表示截取mobile的左边3个字符，right表示截取mobile的右边4个字符
效果：13700001234 = 137****1234

判断字段不为空 (一般写法where field is not null and field != '')
char_length(str):返回str所包含的字符数，一个多字节字符算一个字符
length(str): 返回字符串的字节长度，如utf8中，一个汉字3字节，数字和字母算一个字节where length(str) > 0

============================

//格式自行指定，也可做时间类型的格式转换
字符串转日期 DATE_FORMAT(NOW(),'%Y-%m-%d %H:%m:%s')
字符串转时间 TIME_FORMAT(NOW(),'%H:%m:%s')
日期效果：20190814152312 = 2019-08-14 15:23:12
时间效果：152312 = 15:23:12
另外str_to_date(‘14.08.2019 15:23:12’, ‘%m.%d.%Y %h:%i:%s’); //这种不推荐，有时候是null而不是2019-08-14 15:23:12

计算两日期之间天数
方法一：datediff(now(),'20190812') 输出： 3
方法二：to_days(now()) - to_days('20190812') 输出： 3
方法三：timestampdiff(day,now(),'20190812') 输出： 3 ， day是单位，其他值有frac_second 表示间隔是毫秒、second、minute、hour、day、week、month、
quarter 季度、year

日期加一天 date_add(now(), interval 1 day) 或者 adddate(now(), interval 1 day) day也可换成其他单位：年月日时分秒以及类似hour_second的复合单位date_add(now(), interval '01:15:30' hour_second)
日期减一天 date_sub(now(), interval 1 day) 或者 subdate(now(), interval 1 day)
时间加一小时 date_add(now(), interval 1 hour)
时间减一小时 date_sub(now(), interval 1 hour)

查询一天、一周
where to_days(yourtime) = to_days(now())、
where yearweek(date_format(yourtime,'%Y-%m-%d')) = yearweek(now())

查询时间段是否有重复时间段（有交集）

SELECT * FROM t_user WHERE !(beg_time > #{qry_end_time} OR end_time < #{qry_beg_time})

思路如下
开始时间大于查询结束时间，此单一where条件下无交集
结束时间小于查询结束时间，此单一where条件下无交集
合并后取反得交集

避免插入重复数据

 INSERT IGNORE INTO my_table SELECT * FROM my_table2; #IGNORE 若主键冲突或唯一约束，则忽略错误，返回0（成功时返回1）. 这条重复的数据就不会被插入
 REPLACE INTO my_table SELECT * FROM my_table2; # REPLACE 若主键冲突或唯一约束，则先删除旧的在插入新的。返回2 或者 1（字段都一样返回1，不一样返2）

============================

四舍五入：round(x, D)：四舍五入保留D位小数，D默认为0， 可以为负数， 如round(19, -1)返回20
数据精度控制 truncate(x, D):截断至保留D位小数，D可以为负数， 如trancate(19,-1)返回10
正负数判断：sign(x): 返回x的符号，正负零分别返回1， -1， 0

字符串转数字型(排序不正准确)
根据varchar类型的字段进行排序，有时候是不准确的。需要用到函数进行数据类型转换
CAST(value as type)或者CONVERT(value, type);其中type如下

SIGNED 整型 适合整数
DECIMAL 浮点型 适合金额之类
UNSIGNED 无符号整数
DATE/TIME/TIMEDATETIME 日期类型
CHAR() 字符串
BINARY 二进制

SELECT * FROM t_user  ORDER BY CAST(yournumber AS SIGNED) ASC 
-- ORDER BY CONVERT(yournumber, UNSIGNED )  ASC 
-- yournumber 是varchar类型存储了1,2,3...