Mysql之SQL函数总结

MySQL Date 函数，统计当天、一周、一月等的数据

Date 函数

函数	描述
NOW()	返回当前的日期和时间
CURDATE()	返回当前的日期
CURTIME()	返回当前的时间
DATE()	提取日期或日期/时间表达式的日期部分
EXTRACT()	返回日期/时间按的单独部分
DATE_ADD()	给日期添加指定的时间间隔
DATE_SUB()	从日期减去指定的时间间隔
DATEDIFF()	返回两个日期之间的天数
DATE_FORMAT()	用不同的格式显示日期/时间

DATE_ADD()

函数向日期添加指定的时间间隔

DATE_ADD(date,INTERVAL expr type)

date 参数是合法的日期表达式。expr 参数是您希望添加的时间间隔。

type 参数可以是下列值：

DATEDIFF()

函数返回两个日期之间的天数

DATEDIFF(date1,date2)

date1 和 date2 参数是合法的日期或日期/时间表达式。

**注释：**只有值的日期部分参与计算。

MySQL 使用下列数据类型在数据库中存储日期或日期/时间值

• DATE - 格式 YYYY-MM-DD
• DATETIME - 格式: YYYY-MM-DD HH:MM:SS
• TIMESTAMP - 格式: YYYY-MM-DD HH:MM:SS
• YEAR - 格式 YYYY 或 YY

当前日期和时间其他的 是MySQL的

SELECT LOCALTIME();
SELECT LOCALTIMESTAMP();
SELECT SYSDATE();

UNIX_TIMESTAMP() 转为时间戳 – 1天86400秒 时间戳以 秒 为单位

– 返回当前时间的UNIX格式数字串，或者说是 UNIX 时间戳（从 UTC 时间’1970-01-01 00:00:00’开始的秒数），通常为十位，如 1344887103。
SELECT UNIX_TIMESTAMP(NOW()); – 当前的时间戳
SELECT UNIX_TIMESTAMP(LEFT(NOW(),10)); – 当天00:00:00 秒的时间戳
SELECT UNIX_TIMESTAMP(CURRENT_DATE); – 当天00:00:00 秒的时间戳
SELECT UNIX_TIMESTAMP(CURRENT_DATE()); – 当天00:00:00 秒的时间戳

SELECT UNIX_TIMESTAMP(CURRENT_DATE) + 86399; – 当天23:59:59 秒的时间戳
SELECT UNIX_TIMESTAMP(CURRENT_DATE()) + 86399; – 当天23:59:59 秒的时间戳
SELECT UNIX_TIMESTAMP(CONCAT(CURDATE(),’ ',‘23:59:59’)); – 当天23:59:59 秒的时间戳 拼接
SELECT UNIX_TIMESTAMP(DATE_ADD(CURRENT_DATE, INTERVAL 86399 SECOND)); – 当天23:59:59 秒的时间戳 – 当天的00:00 向后+86399秒
SELECT UNIX_TIMESTAMP(DATE_ADD(CURRENT_DATE, INTERVAL 1 DAY)) - 1; – 当天23:59:59 秒的时间戳 – 当天的00:00 向后的一天 减1秒

今天

SELECT * FROM 表名 WHERE TO_DAYS(时间字段名)=TO_DAYS(NOW());
SELECT * FROM 表名 WHERE TO_DAYS(时间字段名)=TO_DAYS(CURRENT_DATE());

昨天

SELECT * FROM 表名 WHERE TO_DAYS( NOW( ) ) - TO_DAYS( 时间字段名) = 1;
SELECT * FROM 表名 WHERE TO_DAYS( NOW( ) ) = TO_DAYS( 时间字段名) + 1;

近7天

SELECT * FROM 表名 WHERE TO_DAYS(CURDATE()) - TO_DAYS(时间字段名)<=7;
SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(时间字段名);

近30天

SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(时间字段名)

本月

SELECT * FROM 表名 WHERE DATE_FORMAT( 时间字段名, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )

上一月

SELECT * FROM 表名 WHERE PERIOD_DIFF( date_format( now( ) , '%Y%m' ) , date_format( 时间字段名, '%Y%m' ) ) =1

查询本季度数据

SELECT * from `ht_invoice_information` where QUARTER(create_date)=QUARTER(now());

查询上季度数据

select * from `ht_invoice_information` where QUARTER(create_date)=QUARTER(DATE_SUB(now(),interval 1 QUARTER));

查询本年数据

select * from `ht_invoice_information` where YEAR(create_date)=YEAR(NOW());

查询上年数据

select * from `ht_invoice_information` where year(create_date)=year(date_sub(now(),interval 1 year));

查询当前这周的数据

SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,'%Y-%m-%d')) = YEARWEEK(now());

查询上周的数据

SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,'%Y-%m-%d')) = YEARWEEK(now())-1;

查询上个月的数据

select name,submittime from enterprise where date_format(submittime,'%Y-%m')=date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),'%Y-%m')

select * from user where DATE_FORMAT(pudate,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m') ; 

select * from user where WEEKOFYEAR(FROM_UNIXTIME(pudate,'%y-%m-%d')) = WEEKOFYEAR(now()) 

select * from user where MONTH(FROM_UNIXTIME(pudate,'%y-%m-%d')) = MONTH(now()) 

select * from user where YEAR(FROM_UNIXTIME(pudate,'%y-%m-%d')) = YEAR(now()) and MONTH(FROM_UNIXTIME(pudate,'%y-%m-%d')) = MONTH(now()) 
select * from user where pudate between  上月最后一天  and 下月第一天 

查询当前月份的数据

select name,submittime from enterprise   where date_format(submittime,'%Y-%m')=date_format(now(),'%Y-%m')

查询距离当前现在6个月的数据

select name,submittime from enterprise where submittime between date_sub(now(),interval 6 month) and now();

参考：https://www.w3school.com.cn/sql/sql_dates.asp

Mysql中 (@i:=@i+1)的作用

Oracle中有一个伪列rownum，可以在生成查询结果表的时候生成一组递增的序列号。MySQL中没有这个伪列，但是有时候要用，可以用如下方法模拟生成一列自增序号。

语法

需要在from末尾设置变量的初始化格式为：, (select @i:=99) as init

sql示例

select (@i:=@i+1) as rownum, log.id
from start_log log
, (select @i:=99) as init

如果是多表联查，跟上述sql类似，连查完后定义一个初始化序列号即可

应用：Date函数共同使用

select log.create_time, date_add('2021-12-1 00:00:00', interval @i := @i + 1 DAY_MINUTE) dat 
from  start_log log , (select @i := 0) as init

参考：

https://www.cnblogs.com/zelzzz/p/7881650.html

JSON 函数

官方地址：https://dev.mysql.com/doc/refman/8.0/en/json-function-reference.html

MySQL 5.7.8开始支持 json类型，但是性能一般

以下只是介绍函数的主要功能，几个常用的操作函数，具体详解官网都有

表 12.22 JSON 函数

姓名	描述
->	评估路径后从 JSON 列返回值；相当于 JSON_EXTRACT()。
->>	在评估路径并取消引用结果后从 JSON 列返回值；相当于 JSON_UNQUOTE(JSON_EXTRACT())。
JSON_ARRAY()	创建 JSON 数组
JSON_ARRAY_APPEND()	将数据附加到 JSON 文档
JSON_ARRAY_INSERT()	插入 JSON 数组
JSON_CONTAINS()	JSON 文档是否包含路径中的特定对象
JSON_CONTAINS_PATH()	JSON 文档是否包含路径中的任何数据
JSON_DEPTH()	JSON 文档的最大深度
JSON_EXTRACT()	从 JSON 文档返回数据
JSON_INSERT()	将数据插入 JSON 文档
JSON_KEYS()	来自 JSON 文档的键数组
JSON_LENGTH()	JSON 文档中的元素数
JSON_MERGE()	合并 JSON 文档，保留重复的键。不推荐使用的 JSON_MERGE_PRESERVE() 同义词
JSON_MERGE_PATCH()	合并 JSON 文档，替换重复键的值
JSON_MERGE_PRESERVE()	合并 JSON 文档，保留重复键
JSON_OBJECT()	JSON 对象操作
JSON_PRETTY()	以人类可读的格式打印 JSON 文档
JSON_QUOTE()	引用 JSON 文档
JSON_REMOVE()	从 JSON 文档中删除数据
JSON_REPLACE()	替换 JSON 文档中的值
JSON_SCHEMA_VALID()	根据 JSON 模式验证 JSON 文档；如果文档根据模式进行验证，则返回 TRUE/1，否则返回 FALSE/0
JSON_SCHEMA_VALIDATION_REPORT()	根据 JSON 模式验证 JSON 文档；以 JSON 格式返回关于验证结果的报告，包括成功或失败以及失败的原因
JSON_SEARCH()	JSON 文档中的值路径
JSON_SET()	将数据插入 JSON 文档
JSON_STORAGE_FREE()	部分更新后 JSON 列值的二进制表示中释放的空间
JSON_STORAGE_SIZE()	用于存储 JSON 文档的二进制表示的空间
JSON_TABLE()	将 JSON 表达式中的数据作为关系表返回
JSON_TYPE()	JSON 值的类型
JSON_UNQUOTE()	取消引用 JSON 值
JSON_VALID()	JSON 值是否有效
JSON_VALUE()	在提供的路径指向的位置从 JSON 文档中提取值；将此值作为 VARCHAR(512) 或指定类型返回
MEMBER OF()	如果第一个操作数与作为第二个操作数传递的 JSON 数组的任何元素匹配，则返回 true (1)，否则返回 false (0)

对记录的操作

创建有json字段的表

-- 创建表
CREATE TABLE t_json(id INT PRIMARY KEY, sname VARCHAR(20) , info  JSON);

插入记录

-- 插入含有json数组的记录
INSERT INTO t_json(id,sname,info) VALUES( 1, 'name1', JSON_ARRAY(1, "abc", NULL, TRUE, CURTIME()));

-- 插入含有json对象的记录
INSERT INTO t_json(id,sname,info) VALUES( 2, 'name2', JSON_OBJECT("age", 20, "time", now()));
INSERT INTO t_json(id,sname,info) VALUES( 3, 'name3', '{"age":20, "time":"2018-07-14 10:52:00"}');

查询记录

-- 查询记录
SELECT sname,JSON_EXTRACT(info,'$.age') FROM t_json;
SELECT sname,info->'$.age' FROM t_json;
-- 查询key
SELECT id,json_keys(info) FROM t_json;

修改记录

-- 增加键
UPDATE t_json SET info = json_set(info,'$.ip','192.168.1.1') WHERE id = 2;

-- 变更值
UPDATE t_json SET info = json_set(info,'$.ip','192.168.1.2') WHERE id = 2;

-- 删除键
UPDATE t_json SET info = json_remove(info,'$.ip') WHERE id = 2;

创建json值函数

JSON_ARRAY 生成json数组

-- JSON_ARRAY(val1,val2,val3...)
-- 生成一个包含指定元素的json数组。
SELECT JSON_ARRAY(1, "abc", NULL, TRUE, CURTIME()); -- [1, "abc", null, true, "10:37:08.000000"]

JSON_OBJECT 生成json对象

-- JSON_OBJECT(key1,val1,key2,val2...)
-- 生成一个包含指定K-V对的json object。如果有key为NULL或参数个数为奇数，则抛错。
SELECT JSON_OBJECT('age', 20, 'time', now()); -- {"age": 20, "time": "2021-06-18 14:49:57.000000"}

JSON_QUOTE 加"号

-- JSON_QUOTE(json_val)
-- 将json_val用"号括起来。
SELECT JSON_QUOTE('[1,2,3]'); -- "[1,2,3]" 

搜索json值函数

JSON_CONTAINS 指定数据是否存在

set @j = '{"a": 1, "b": 2, "c": {"d": 4}}';
-- JSON_CONTAINS(json_doc, val[, path])
-- 查询json文档是否在指定path包含指定的数据，包含则返回1，否则返回0。如果有参数为NULL或path不存在，则返回NULL。
SELECT JSON_CONTAINS(@j, '4', '$.c.d'); -- 1

JSON_CONTAINS_PATH 指定路径是否存在

-- JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] ...)
-- 查询是否存在指定路径，存在则返回1，否则返回0。如果有参数为NULL，则返回NULL。
-- one_or_all只能取值"one"或"all"，one表示只要有一个存在即可；all表示所有的都存在才行。
SELECT JSON_CONTAINS_PATH(@j, 'one', '$.a', '$.e'); -- 1
SELECT JSON_CONTAINS_PATH(@j, 'all', '$.a', '$.c.d'); -- 1

JSON_EXTRACT 查找所有指定数据

-- JSON_EXTRACT(json_doc, path[, path] ...)
-- 从json文档里抽取数据。如果有参数有NULL或path不存在，则返回NULL。如果抽取出多个path，则返回的数据封闭在一个json array里。
set @j2 = '[10, 20, [30, 40]]';
SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]'); -- 20
SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]', '$[0]'); -- [20, 10]
SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[2][*]'); -- [30, 40]


JSON_EXTRACT(`remark`, '$.payment_time') #会取出有双引号
JSON_UNQUOTE(JSON_EXTRACT(`remark`,'$.payment_time') )# 不会取出双引号
`remark`->>'$.payment_time' # 不会取出双引号
unix_timestamp(`remark`->>'$.payment_time') ## 不会取出双引号且格式化时间

JSON_KEYS 查找所有指定键值

-- JSON_KEYS(json_doc[, path])
-- 获取json文档在指定路径下的所有键值，返回一个json array。如果有参数为NULL或path不存在，则返回NULL。
SELECT JSON_KEYS('{"a": 1, "b": {"c": 30}}'); -- ["a", "b"]
SELECT JSON_KEYS('{"a": 1, "b": {"c": 30}}', '$.b'); -- ["c"]
SELECT id,json_keys(info) FROM t_json;

JSON_SEARCH 查找所有指定值的位置

-- JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] ...])
-- 查询包含指定字符串的paths，并作为一个json array返回。如果有参数为NUL或path不存在，则返回NULL。
-- one_or_all："one"表示查询到一个即返回；"all"表示查询所有。
-- search_str：要查询的字符串。 可以用LIKE里的'%'或‘_’匹配。
-- path：在指定path下查。
SET @j3 = '["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]';
SELECT JSON_SEARCH(@j3, 'one', 'abc'); -- "$[0]"
SELECT JSON_SEARCH(@j3, 'all', 'abc'); -- ["$[0]", "$[2].x"]
SELECT JSON_SEARCH(@j3, 'all', 'abc', NULL, '$[2]'); -- "$[2].x"
SELECT JSON_SEARCH(@j3, 'all', '10'); -- "$[1][0].k"
SELECT JSON_SEARCH(@j3, 'all', '%b%'); -- ["$[0]", "$[2].x", "$[3].y"]
SELECT JSON_SEARCH(@j3, 'all', '%b%', NULL, '$[2]'); -- "$[2].x"

修改json值函数

JSON_ARRAY_APPEND 指定位置追加数组元素

-- JSON_ARRAY_APPEND(json_doc, path, val[, path, val] ...)
-- 在指定path的json array尾部追加val。如果指定path是一个json object，则将其封装成一个json array再追加。如果有参数为NULL，则返回NULL。
SET @j4 = '["a", ["b", "c"], "d"]';
-- SELECT JSON_ARRAY_APPEND(@j4, '$[1][0]', 3); -- ["a", [["b", 3], "c"], "d"]
SET @j5 = '{"a": 1, "b": [2, 3], "c": 4}';
SELECT JSON_ARRAY_APPEND(@j5, '$.b', 'x'); -- {"a": 1, "b": [2, 3, "x"], "c": 4} 
SELECT JSON_ARRAY_APPEND(@j5, '$.c', 'y'); -- {"a": 1, "b": [2, 3], "c": [4, "y"]}
SELECT JSON_ARRAY_APPEND(@j5, '$', 'z'); -- [{"a": 1, "b": [2, 3], "c": 4}, "z"]

JSON_ARRAY_INSERT 指定位置插入数组元素

-- JSON_ARRAY_INSERT(json_doc, path, val[, path, val] ...)
-- 在path指定的json array元素插入val，原位置及以右的元素顺次右移。如果path指定的数据非json array元素，则略过此val；如果指定的元素下标超过json array的长度，则插入尾部。
SET @j6 = '["a", {"b": [1, 2]}, [3, 4]]';
SELECT JSON_ARRAY_INSERT(@j6, '$[1]', 'x'); -- ["a", "x", {"b": [1, 2]}, [3, 4]]
SELECT JSON_ARRAY_INSERT(@j6, '$[100]', 'x'); -- ["a", {"b": [1, 2]}, [3, 4], "x"]
SELECT JSON_ARRAY_INSERT(@j6, '$[1].b[0]', 'x'); -- ["a", {"b": ["x", 1, 2]}, [3, 4]]
SELECT JSON_ARRAY_INSERT(@j6, '$[0]', 'x', '$[3][1]', 'y'); -- ["x", "a", {"b": [1, 2]}, [3, "y", 4]]

JSON_INSERT 指定位置插入

-- JSON_INSERT(json_doc, path, val[, path, val] ...)
-- 在指定path下插入数据，如果path已存在，则忽略此val（不存在才插入）。
SET @j7 = '{ "a": 1, "b": [2, 3]}';
SELECT JSON_INSERT(@j7, '$.a', 10, '$.c', '[true, false]'); -- {"a": 1, "b": [2, 3], "c": "[true, false]"}

JSON_REPLACE 指定位置替换

-- JSON_REPLACE(json_doc, path, val[, path, val] ...)
-- 替换指定路径的数据，如果某个路径不存在则略过（存在才替换）。如果有参数为NULL，则返回NULL。
SELECT JSON_REPLACE(@j7, '$.a', 10, '$.c', '[true, false]'); -- {"a": 10, "b": [2, 3]}

JSON_SET 指定位置设置

-- JSON_SET(json_doc, path, val[, path, val] ...)
-- 设置指定路径的数据（不管是否存在）。如果有参数为NULL，则返回NULL。
SELECT JSON_SET(@j7, '$.a', 10, '$.c', '[true, false]'); -- {"a": 10, "b": [2, 3], "c": "[true, false]"}

JSON_MERGE 合并

-- JSON_MERGE(json_doc, json_doc[, json_doc] ...)
-- merge多个json文档。规则如下：
-- 如果都是json array，则结果自动merge为一个json array；
-- 如果都是json object，则结果自动merge为一个json object；
-- 如果有多种类型，则将非json array的元素封装成json array再按照规则一进行mege。
SELECT JSON_MERGE('[1, 2]', '[true, false]'); -- [1, 2, true, false]
SELECT JSON_MERGE('{"name": "x"}', '{"id": 47}'); -- {"id": 47, "name": "x"}
SELECT JSON_MERGE('1', 'true'); -- [1, true]
SELECT JSON_MERGE('[1, 2]', '{"id": 47}'); -- [1, 2, {"id": 47}]

JSON_REMOVE 指定位置移除

-- JSON_REMOVE(json_doc, path[, path] ...)
-- 移除指定路径的数据，如果某个路径不存在则略过此路径。如果有参数为NULL，则返回NULL。
SET @j8 = '["a", ["b", "c"], "d"]';
SELECT JSON_REMOVE(@j8, '$[1]'); -- ["a", "d"]

JSON_UNQUOTE 去"号

-- JSON_UNQUOTE(val)
-- 去掉val的引号。如果val为NULL，则返回NULL。
SELECT JSON_UNQUOTE("\"123\""); -- 123

返回json值属性的函数

JSON_DEPTH 深度

-- JSON_DEPTH(json_doc)
-- 获取json文档的深度。如果参数为NULL，则返回NULL。
-- 空的json array、json object或标量的深度为1。
SELECT JSON_DEPTH('{}'), JSON_DEPTH('[]'), JSON_DEPTH('true'); -- 1 1 1
SELECT JSON_DEPTH('[10, 20]'), JSON_DEPTH('[[], {}]'); -- 2 2
SELECT JSON_DEPTH('[10, {"a": 20}]'); -- 3

JSON_LENGTH 长度

-- JSON_LENGTH(json_doc[, path])
-- 获取指定路径下的长度。如果参数为NULL，则返回NULL。　
-- 长度的计算规则：
-- 标量的长度为1；
-- json array的长度为元素的个数；
-- json object的长度为key的个数。
SELECT JSON_LENGTH('[1, 2, {"a": 3}]'); -- 3
SELECT JSON_LENGTH('{"a": 1, "b": {"c": 30}}'); -- 2
SELECT JSON_LENGTH('{"a": 1, "b": {"c": 30}}', '$.b'); -- 1

JSON_TYPE 类型

-- JSON_TYPE(json_val)
-- 获取json文档的具体类型。如果参数为NULL，则返回NULL。
select JSON_TYPE('[1,2]'); -- ARRAY

4.JSON_VALID 是否有效json格式

-- JSON_VALID(val)
-- 判断val是否为有效的json格式，是为1，不是为0。如果参数为NUL，则返回NULL。
SELECT JSON_VALID('{"a": 1}'); -- 1
SELECT JSON_VALID('hello'), JSON_VALID('"hello"'); -- 1

参考：

https://www.cnblogs.com/ooo0/p/9309277.html

MySQL巧妙利用临时表把以逗号分隔的字符串转换成行

分隔效果

-- 分隔前
1,2,3,4
-- 分隔后
1
2
3
4

分隔命令行

SELECT 
	substring_index(substring_index('1,2,3,4',',', b.help_topic_id + 1), ',', -1) result
FROM 
	mysql.help_topic b
where 
	b.help_topic_id <  (LENGTH('1,2,3,4') - LENGTH(REPLACE('1,2,3,4', ',', '')) + 1);

命令行解释

help_topic本身是Mysql一个帮助解释注释表，用于解释Mysql各种专有名词，由于这张表数据ID是从0顺序增加的，方便我们用于计数，但是8.0.17版本的只有686条数据，超过这个数字，我们就需要己自定义一张表

可以用做计数的临时表，查询的语句只会用help_topic计数，超出的部分其实都是脏数据

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-0qhFBa4j-1640230172308)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20211211195634257.png)]

b.help_topic_id < 获取分隔后的总行数

b.help_topic_id <  (LENGTH('1,2,3,4') - LENGTH(REPLACE('1,2,3,4', ',', '')) + 1)

这个语句其实是一个遍历，help_topic_id 从0开始递增到

按照顺序截取，先截取第 n 个分隔符之前的所有字符，再截取字符串最后一位

1  		—— 1
1,2		—— 2
1,2,3	—— 3
1,2,3,4	—— 4

涉及函数

substring_index(str，delim，count)

参数名	解释
str	需要拆分的字符串
delim	分隔符，通过某字符进行拆分
count	当 count 为正数，取第 n 个分隔符之前的所有字符； 当 count 为负数，取倒数第 n 个分隔符之后的所有字符。

replace( str, from_str, to_str)

参数名	解释
str	需要进行替换的字符串
from_str	需要被替换的字符串
to_str	需要替换的字符串

length(str) 获取字符串长度