mysql: 常用函数总结以及高级函数用法

数值型函数

字符串函数

日期和时间函数

MySQL 字符串函数

MySQL 数字函数

MySQL 日期函数

MySQL自带的日期函数TIMESTAMPDIFF计算两个日期相差的秒数、分钟数、小时数、天数、周数、季度数、月数、年数，当前日期增加或者减少一天、一周等等。

SELECT TIMESTAMPDIFF(类型,开始时间,结束时间)
相差的秒数：

SELECT TIMESTAMPDIFF(SECOND,'1993-03-23 00:00:00',DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%S'))
相差的分钟数：

SELECT TIMESTAMPDIFF(MINUTE,'1993-03-23 00:00:00',DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%S'))
相差的小时数：

SELECT TIMESTAMPDIFF(HOUR,'1993-03-23 00:00:00 00:00:00',DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%S'))
相差的天数：

SELECT TIMESTAMPDIFF(DAY,'1993-03-23 00:00:00',DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%S'))
相差的周数：

SELECT TIMESTAMPDIFF(WEEK,'1993-03-23 00:00:00',DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%S'))
相差的季度数：

SELECT TIMESTAMPDIFF(QUARTER,'1993-03-23 00:00:00',DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%S'))
相差的月数：

SELECT TIMESTAMPDIFF(MONTH,'1993-03-23 00:00:00',DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%S'))
相差的年数：

SELECT TIMESTAMPDIFF(YEAR,'1993-03-23 00:00:00',DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%S'))
获取当前日期：

SELECT NOW()
SELECT CURDATE()
当前日期增加一天：

SELECT DATE_SUB(CURDATE(),INTERVAL -1 DAY)
当前日期减少一天：

SELECT DATE_SUB(CURDATE(),INTERVAL 1 DAY)
当前日期增加一周：

SELECT DATE_SUB(CURDATE(),INTERVAL -1 WEEK)
当前日期增加一月：

SELECT DATE_SUB(CURDATE(),INTERVAL -1 MONTH)

SELECT DATE_SUB(NOW(),INTERVAL -1 MONTH)
FRAC_SECOND  毫秒
SECOND  秒
MINUTE  分钟
HOUR  小时
DAY  天
WEEK  星期
MONTH  月
QUARTER  季度
YEAR  年

MySQL 高级函数

项目中学习到的新函数用法

group_concat()

1、功能：将group by产生的同一个分组中的值连接起来，返回一个字符串结果。
2、语法：group_concat( [distinct] 要连接的字段 [order by 排序字段 asc/desc ] [separator ‘分隔符’] )
说明：通过使用distinct可以排除重复值；如果希望对结果中的值进行排序，可以使用order by子句；separator是一个字符串值，缺省为一个逗号。

select GROUP_CONCAT(DISTINCT data_code order by data_code desc separator '_') from sip_dic_info c where c.data_type = 'BC_02';

类型转换cast 或者convert

SELECT CAST("2017-08-29" AS DATE);
SELECT CAST("2" AS signed); 
select CONVERT('12332',signed); // 有多个重载函数

批量插入之replace语句/insert into… on duplicate key update语句

• replace into 表名l (id,字段1) values (1,‘2’),(2,‘3’),…(x,‘y’);

• insert into 表名 values(列值1,列值2,列值3…) on duplicate key update 列名1=值/values(列名1),列名2=列值/valules(列名2)…

二者结论:

1:唯一性索引冲突时，两种方式都会增加数据表AUTO_INCREMENT的值。

2:replace遇到主键/唯一索引冲突是先删除再插入，无冲突直接进行insert。

总结从上面的测试结果看出，相同之处：
（1），没有key的时候，replace与insert … on deplicate key udpate相同。
（2），有key的时候，都保留主键值，并且auto_increment自动+1
不同之处：有key的时候，replace是delete老记录，而录入新的记录，所以原有的所有记录会被清除，这个时候，如果replace语句的字段不全的话，有些原有的比如例子中c字段的值会被自动填充为默认值。

注意事项：

ON DUPLICATE KEY 如果a=1 OR b=2与多个行向匹配，则只有一个行被更新。通常，您应该尽量避免对带有多个唯一关键字的表使用ON DUPLICATE KEY子句。

REPLACE可能影响3条以上的记录，这是因为在表中有超过一个的唯一索引。在这种情况下，REPLACE将考虑每一个唯一索引，并对每一个索引对应的重复记录都删除，然后插入这条新记录。假设有一个table1表，有3个字段a, b, c。它们都有一个唯一索引。

您可以在UPDATE子句中使用VALUES(col_name)函数从INSERT…UPDATE语句的INSERT部分引用列值。换句话说，如果没有发生重复关键字冲突，则UPDATE子句中的VALUES(col_name)可以引用被插入的col_name的值。本函数特别适用于多行插入。VALUES()函数只在INSERT…UPDATE语句中有意义，其它时候会返回NULL。

INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6) ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);

参考博客 https://blog.csdn.net/taoy86/article/details/89322202

ELT - 返回对应位置的字符串。

ELT(N,str1,str2,str3,…)

select convert(elt(floor(10*rand())+1,'11','13','17','19','21','23','29','31','37','41'),decimal(8,2));

select elt(floor(2*rand())+1,'02','03')

concat_ws

concat_ws('-',IF(ISNULL(p.begin_road_segment_name),'',p.begin_road_segment_name),IF(ISNULL(p.end_road_segment_name),'',p.end_road_segment_name)) roadSegmentName