MySQL-Function 函数与操作符

MySQL Function 函数与操作符笔记

函数和操作符总索引
12.1 Built-In Function and Operator Reference
https://dev.mysql.com/doc/refman/5.7/en/built-in-function-reference.html

---

日期和时间函数

12.7 Date and Time Functions
https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html

now()

NOW([fsp])
返回当前日期和时间，格式为 ‘YYYY-MM-DD HH:MM:SS’ 或 YYYYMMDDHHMMSS

fsp 取值 0 到 6，指定小数精度

mysql> SELECT NOW();
        -> '2007-12-15 23:50:26'
mysql> SELECT NOW() + 0;
        -> 20071215235026.000000
mysql> SELECT NOW();
        -> '2021-05-19 19:58:56.572'

curtime()

CURTIME([fsp]) 只能查出时间，没有日期

mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 12:58:13  |
+-----------+
1 row in set (0.00 sec)

DATE_SUB 日期减

查询7天前的时间点
select DATE_SUB(NOW(), INTERVAL 7 DAY)

now()和sysdate()区别

mysql的官方解释：
NOW() returns a constant time that indicates the time at which the statement began to execute.
This differs from the behavior for SYSDATE(), which returns the exact time at which it executes.

now()函数返回语句开始执行的时间；而sysdate()返回函数执行时的时间。

mysql now() sysdate() 区别
https://my.oschina.net/friendship/blog/510532

---

FROM_UNIXTIME 时间戳转日期

FROM_UNIXTIME(unix_timestamp[,format])
其中 unix_timestamp 表示距离 1970-01-01 00:00:00 的秒数

mysql> SELECT FROM_UNIXTIME(1447430881);
        -> '2015-11-13 10:08:01'
mysql> SELECT FROM_UNIXTIME(1447430881) + 0;
        -> 20151113100801
mysql> SELECT FROM_UNIXTIME(1447430881, '%Y %D %M %h:%i:%s %x');
        -> '2015 13th November 10:08:01 2015'

https://dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html#function_from-unixtime

UNIX_TIMESTAMP 日期转时间戳

UNIX_TIMESTAMP([date])

不带参数使用时返回当前时间的时间戳（秒）

mysql> SELECT UNIX_TIMESTAMP();
        -> 1447431666
mysql> SELECT UNIX_TIMESTAMP('2015-11-13 10:20:19');
        -> 1447431619
mysql> SELECT UNIX_TIMESTAMP('2015-11-13 10:20:19.012');
        -> 1447431619.012

https://dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html#function_unix-timestamp

---

YEARWEEK 日期转年周

周数是 0 到 53 之间的数字

SELECT YEARWEEK(“2017-06-15”);
201724

datetime create_time 格式 2023-09-06 12:58:19，按周统计：

select yearweek(left(create_time, 10)) as week, sum(count)
from xx_history
group by week
order by week;

---

根据生日计算年龄

TIMESTAMPDIFF(YEAR, DATE(u.birthday), CURDATE()) as 'age'
其中 birthday 是 date 类型

---

信息函数

12.15 Information Functions
https://dev.mysql.com/doc/refman/5.6/en/information-functions.html

LAST_INSERT_ID()

https://dev.mysql.com/doc/refman/5.6/en/information-functions.html#function_last-insert-id

简单说来，就是这个函数将返回插入的那条记录在表中自增的那个字段的值，一般我们都给那个自增字段命名为ID。这样就可以返回刚插入的记录的ID值了。

这个函数是基于connection的，也就是不会被其他客户端的connection影响到，所以结果是准确的。如果使用select max(id) from table,在高密度的插入请求下，是有可能出问题的，返回错误值

SELECT @@IDENTITY 和 last_insert_id() 获取的是当前数据库连接的前一次执行的值。其他连接执行的值不会影响当前线程。时下流行的框架（如Spring-jdbc、mybatis、hibernate）的数据库连接都是存在 ThreadLocal 中的、是线程隔离的，所以不会获取到其他线程中的自增id。当多线程编程时、强制把数据库连接传给各个线程同时执行时才会取到其他线程的自增id。

select @@identity 也能查到自增id，和 last_insert_id() 函数一样。

Mysql中LAST_INSERT_ID()的函数使用详解
https://blog.csdn.net/hsd2012/article/details/51464409

---

比较操作符

12.3.2 Comparison Functions and Operators
https://dev.mysql.com/doc/refman/5.6/en/comparison-operators.html

coalesce()返回第一个非null值

COALESCE(value,...)
返回列表中的第一个非null表达式的值
COALESCE是一个函数， coalesce(exp_1, exp_2, …,exp_n) 依次参考各参数表达式，遇到非null值即停止并返回该值。如果所有的表达式都是空值，最终将返回一个空值

select coalesce(null, 2, 3, 1);
结果是第一个非空值2

select coalesce(null, ‘’, 2, 3, 1);
结果是第一个非空值’’

COALESCE(expression1, expressionN)
与此 CASE 函数等效：

CASE
WHEN (expression1 IS NOT NULL) THEN expression1
when expression2 is not null then expression2
...
WHEN (expressionN IS NOT NULL) THEN expressionN
ELSE NULL
END

---

字符串函数

12.8 String Functions and Operators
https://dev.mysql.com/doc/refman/5.7/en/string-functions.html

left/right

LEFT(str,len) 返回字符串 str 最左侧的 len 个字符
RIGHT(str,len) 返回字符串 str 最右侧的 len 个字符

例如：
select left('abcdef',3)，查询结果为abc
select right('foobarbar', 4) 结果为 rbar
SELECT LEFT(Name, 5)，查询Name左5个字符
where left(op_time,8)=20160601，匹配op_time左8位是20160601的

https://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_left
https://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_right

substring_index() 字符串截取

SUBSTRING_INDEX(str, delim, count)

返回字符串 str 中分隔符 delim 第 count 次出现位置之前（后）的字符串。
count 为正数时，返回从左数第 count 个 delim 之前的子串。
count 为负数时，返回从右数第 count 个 delim 之后的子串。
delim 区分大小写。

mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);
        -> 'www.mysql'
mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);
        -> 'mysql.com'

-- 查 email 地址及 email 前缀
select email, SUBSTRING_INDEX(email,"@",1) as 'email_prefix' from user;

function_substring-index
https://dev.mysql.com/doc/refman/5.6/en/string-functions.html#function_substring-index

---

length() 字符串字节长度

LENGTH(str)
length是计算字段的长度一个汉字是算三个字符,一个数字或字母算一个字符

mysql> SELECT LENGTH('text');  -> 4
       SELECT LENGTH('吗');  -> 3

function_length
https://dev.mysql.com/doc/refman/5.6/en/string-functions.html#function_length

rand() mysql随机字符串/随机姓名

-- 随机 0-1
select floor(rand() * 2);

-- 随机布尔值
select if(floor(rand() * 2) = 1 , '是' , '否');

-- 随机2位小数
select convert(floor(rand() * 70 + 30) / 100, decimal(10,2));

-- 100内的随机两位小数浮点数
select convert(rand() * 100, decimal(10,2));

-- 随机N位字符串
select substring(MD5(RAND()),1,20);
select substr(md5(rand()),1,20);

-- 随机姓名 可根据需要增加/减少样本
set @SURNAME = '王李张刘陈杨黄赵吴周徐孙马朱胡郭何高林罗郑梁谢宋唐位许韩冯邓曹彭曾萧田董潘袁于蒋蔡余杜叶程苏魏吕丁任沈姚卢姜崔钟谭陆汪范金石廖贾夏韦傅方白邹孟熊秦邱江尹薛阎段雷侯龙史陶黎贺顾毛郝龚邵万钱严覃武戴莫孔向汤';
set @NAME = '丹举义之乐书乾云亦从代以伟佑俊修健傲儿元光兰冬冰冷凌凝凡凯初力勤千卉半华南博又友同向君听和哲嘉国坚城夏夜天奇奥如妙子存季孤宇安宛宸寒寻尔尧山岚峻巧平幼康建开弘强彤彦彬彭心忆志念怀怜恨惜慕成擎敏文新旋旭昊明易昕映春昱晋晓晗晟景晴智曼朋朗杰松枫柏柔柳格桃梦楷槐正水沛波泽洁洋济浦浩海涛润涵渊源溥濮瀚灵灿炎烟烨然煊煜熙熠玉珊珍理琪琴瑜瑞瑶瑾璞痴皓盼真睿碧磊祥祺秉程立竹笑紫绍经绿群翠翰致航良芙芷苍苑若茂荣莲菡菱萱蓉蓝蕊蕾薇蝶觅访诚语谷豪赋超越轩辉达远邃醉金鑫锦问雁雅雨雪霖霜露青靖静风飞香驰骞高鸿鹏鹤黎';
-- length(@surname)/3 是因为中文字符占用3个长度
set @FULL_NAME = concat(substr(@surname,floor(rand()*length(@surname)/3+1),1), substr(@NAME,floor(rand()*length(@NAME)/3+1),1), substr(@NAME,floor(rand()*length(@NAME)/3+1),1));
select @FULL_NAME;

concat() 字符串串接/int转字符串

CONCAT(str1, str2, ...)
字符串串接，可用于 int 等类型转换为字符串，比如 concat(8,’0’) 得到字符串 ‘80’
其中任意参数为null，则结果为null

SELECT CONCAT('device', id%2800); 字符串拼接数字

mysql> SELECT CONCAT('My', 'S', 'QL');
        -> 'MySQL'
mysql> SELECT CONCAT('My', NULL, 'QL');
        -> NULL
mysql> SELECT CONCAT(14.3);
        -> '14.3'

For quoted strings, concatenation can be performed by placing the strings next to each other:
mysql> SELECT 'My' 'S' 'QL';
        -> 'MySQL'

function_concat
https://dev.mysql.com/doc/refman/5.6/en/string-functions.html#function_concat

lpad(str,len,padstr) 字符串左填充

LPAD(str,len,padstr)
LPAD(str,len,padstr) 返回字符串 str, 其左边由字符串padstr 填补到len 字符长度。假如str 的长度大于len, 则返回值被缩短至 len 字符。

例如：

mysql> SELECT LPAD('hi',4,'??');
        -> '??hi'
mysql> SELECT LPAD('hi',1,'??');
        -> 'h'
mysql> select LPAD('1', 8, 0);
-> '00000001'

function_lpad
https://dev.mysql.com/doc/refman/5.6/en/string-functions.html#function_lpad

find_in_set()

https://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_find-in-set

FIND_IN_SET(str,strlist)
strlist 是逗号分割的字符串，如果 str 在 strlist 中存在，返回序号 1…n

---

聚集函数

12.19.1 Aggregate (GROUP BY) Function Descriptions
https://dev.mysql.com/doc/refman/5.6/en/group-by-functions.html

group_concat() 查询转为逗号分隔列

GROUP_CONCAT(expr)

例如查询逗号分割的用户id

select group_concat(id) from user;

超过 group_concat_max_len 长度的会被截断，默认 1024

GROUP_CONCAT(expr)
https://dev.mysql.com/doc/refman/5.6/en/group-by-functions.html#function_group-concat

---

类型转换函数

12.10 Cast Functions and Operators
https://dev.mysql.com/doc/refman/5.6/en/cast-functions.html

cast() 字符串转int

CAST(expr AS type)
将 varchar 转为 int 可以用 cast(a as signed) 其中 a 为 varchar 类型的字符串

例如：

select cast('125e342.83' as signed) as clm1, cast('foo seo 589' as signed) as clm2,cast('3.35' as signed) as clm3;
+------+------+------+
| clm1 | clm2 | clm3 |
+------+------+------+
|  125 |    0 |    3 |
+------+------+------+

convert() 类型转换

CONVERT(expr,type), CONVERT(expr USING transcoding_name)
varchar 转 int 也可以用 convert(a, signed)，其中a是varchar类型

type 可以是以下值中的一个：
BINARY[(N)]
CHAR[(N)]
DATE
DATETIME
DECIMAL
SIGNED [INTEGER]
TIME
UNSIGNED [INTEGER]

hex(N_or_S) 转为十六进制

如果 N_or_S 是一个数字, 返回 N_or_S 的十六进制值的字符串表示，其中 N_or_S 是一个长整型(BIGINT)数。这同等于 CONV(N,10,16).
如果 N_or_S 是一个字符串，返回 N_or_S 在 N_or_S 的每个字符转化为两个十六进制数字的十六进制字符串表示。

比如 blob 存储的特征可以 hex(feature) 转为十六进制查看。

---

Miscellaneous Functions

https://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html

uuid()

https://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html#function_uuid

mysql> SELECT UUID();
        -> '6ccd780c-baba-1026-9564-5b8c656024db'

---

用户自定义变量

9.4 User-Defined Variables
https://dev.mysql.com/doc/refman/5.6/en/user-variables.html

设置自定义变量

使用 set 来定义变量：

SET @var_name = expr [, @var_name = expr] ...

或者使用 select：

SELECT @var_name := expr [, @var_name = expr] ...

用户变量：以@开始，形式为@var_name,以区分用户变量及列名。它可以是任何随机的，复合的标量表达式，只要其中没有列指定。
一个变量名可以由当前字符集的数字字母字符和_、$和.组成。缺省字符集是ISO-8859-1 Latin1；这可以用mysqld 的–default-character-set 选项更改字符集。
对于SET，可以使用=或:=来赋值，对于SELECT只能使用:=来赋值。

使用自定义变量

比如先定义一个 user_id 变量，后面的sql都引用这个变量，不用写好几次了：

set @var_user_id=147884;
delete from user where user_id=@var_user_id;
delete from user_address where user_id=@var_user_id;

---