MySQL一些好用的函数

一、增强查询语句的函数：
1、on duplicate key update 新增或者更新数据（这个语法慎用，当设置了唯一索引时，如果更新的数据唯一索引已在数据库存在，但主键不一致，会导致更新失效；另外如果更新进去的数据跟数据库完全一致，虽然是更新，但返回的是1）
2、if（a?,b,c），有点像三元运算符，a为条件，b为条件成立时的值，c为条件不成立时的值
3、设置主键为uuid：
UPDATE t_mdm_cus_customer_copy SET mdm_id = UUID()
UPDATE t_mdm_cus_customer_copy SET mdm_id = REPLACE(UUID(),'-','')
4、union用于连接两个以上的select语句，将结果合并到一起，重复数据会被删除
5、使用group by查询数据时获取的是查询结果的第一条，想要获取最新一条可以跟order by结合使用，但是注意要加上limit（这就很鸡肋）参考博客：https://blog.csdn.net/HXNLYW/article/details/102681680
6、mysql中将查询结果的list转为一个字段，使用函数group_concat()；
7、查询是否存在满足条件的记录时，不要使用select count(*)查询全表，可使用select 1 from table where 条件 limit 1 在查询到一条满足条件的记录时就返回，提高效率；
8、Row_Number() OVER()对重复数据进行分组编号排序：https://www.cnblogs.com/weifeng123/p/8931598.html；
9、没有主键时count(1)更快，有主键时查询行数可使用count(*)，msysql5.6之后的版本做了优化，会使用最优方式来进行查询，count(字段名)会过滤掉为null的记录；

二、时间相关：
1、字符串转时间：UPDATE TABLE SET add_date = STR_TO_DATE(add_date,'%Y-%m-%d %H:%i:%s')
2、时间转字符串：select date_format(now(), '%Y-%m-%d');
3、时间转时间戳：select unix_timestamp(now());
4、字符串转时间戳：select unix_timestamp('2016-01-02');
5、时间戳转时间：select from_unixtime(1451997924);
6、时间戳转字符串：select from_unixtime(1451997924,'%Y-%d');
7、获取时间差函数：
TIMESTAMPDIFF：按指定方式获取时间差
–相差1天
　　select TIMESTAMPDIFF(DAY, '2018-03-20 23:59:00', '2015-03-22 00:00:00');
　　--相差49小时
　　select TIMESTAMPDIFF(HOUR, '2018-03-20 09:00:00', '2018-03-22 10:00:00');
　　--相差2940分钟
　　select TIMESTAMPDIFF(MINUTE, '2018-03-20 09:00:00', '2018-03-22 10:00:00');
　　--相差176400秒
　　select TIMESTAMPDIFF(SECOND, '2018-03-20 09:00:00', '2018-03-22 10:00:00');
DATEDIFF：获取相差的天数
– 相差2天
　　select DATEDIFF('2018-03-22 09:00:00', '2018-03-20 07:00:00');

三、json相关
关于mysql的json类型可参考：https://blog.csdn.net/ghostyusheng/article/details/84260831，https://blog.csdn.net/qq_21187515/article/details/90760337，https://www.cnblogs.com/waterystone/p/5626098.html
1、查找json格式列的指定字段值：SELECT json_extract(字段名,'$.json结构') FROM 表名
2、查找json格式列中的指定属性（不带双引号）：select json->>'$.field' from table 或者 select json_unquote(json_extract(json,'$.field')) from table
3、比较json格式字段时，除了比较内容外，还需要比较类型，因此需要使用cast（字段名 as json）转为json格式进行比较

四、字符串相关
1、替换字段某个字符：update 表名 set 字段名=REPLACE (字段名,'原来的值','要修改的值')
2、查询字段是否包含某个字符：like，find_in_set，locate，instr，REGEXP正则表达式函数（select field REGEXP 'abc'查找field列包含‘abc’的记录）
3、字符串截取：left（str,n）左边n位，right（str,n）右边n位，substring（str,n）从第n位开始截取
4、获取字段长度：length获取字段字节的长度（一个汉字三个字节，数字或字母一个字节）；char_length获取字段字符的个数（不区分中英文，一个中文或一个字母或一个符号都算一个字符）
5、大小写转换：小写转大写upper()；大写转小写lower()；
6、去除指定字符函数：trim()，参考：https://blog.csdn.net/moakun/article/details/82110700

五、数值计算相关
int类型的字段存储小数时会被四舍五入取整数位；
max(xx)函数求的是查询结果中某一列的最大值，greatest(a,b,c,d)函数求的是某一行数据中某几列的最大值；
查找某一列中纯数字的最大记录：SELECT MAX(cus_property_id) FROM t_mdm_cus_attributes WHERE cus_property_id REGEXP '^[0-9]+$'；

六、MySql查询内置信息的函数：
查询当前所在数据库：select database()
查询所有表信息：SELECT * FROM information_schema.tables
查询某个数据库中某个表的字段信息：SELECT * FROM information_schema.columns WHERE table_schema = 'user-center' AND table_name = 'sys_user'；

七、操作表属性相关：
设置表自增主键从哪个值开始：向表中插入数据前执行alter table user AUTO_INCREMENT=10000；

站在巨人肩上，均为个人使用总结，如有不当之处，望指出！