【Mysql】Mysql常用函数

Mysql常用函数

---

1.日期相关

mysql所用的时间戳单位是秒

1.1 时间戳转日期

-- from_unixtime传入decimal，可获取带有毫秒属性的datetime
select from_unixtime(1666324992524 / 1000);  # 2022-10-21 12:03:12.5240
-- 四舍五入
select from_unixtime(round(1666324992524 / 1000));  # 2022-10-21 12:03:13
-- 忽略毫秒
select from_unixtime(floor(1666324992524 / 1000));  # 2022-10-21 12:03:12

1.2 日期转时间戳

select unix_timestamp(now()); # 2022-10-21 12:03:12
select unix_timestamp(now(3)); # 2022-10-21 12:03:12.524

1.2 字符串日期互转

%Y：4位年 ----------- %y：后2位年
%m：2位月 ----------- %c：去前置0月
%d：2位日
%H：（00-23）小时 ----------- %h：（01-12）小时 ----------- %p：AM或PM
%i：2位分钟
%s：2位秒
%f : 毫秒

# 字符串转日期
select str_to_date('2022-10-21 12:03:12.524', '%Y-%m-%d %H-%i-%s'); # 2022-10-21 12:03:12
select str_to_date('2022-10-21 12:03:12.524', '%Y-%m-%d %H-%i-%s.%f'); # 2022-10-21 12:03:12.52400
# 日期转字符串
select date_format(now(), '%Y%m%d%H%i%s'); #20221021120312

2 字符串处理

2.1 长度

-- 字符串转日期
select length('This is a str'); # 13

2.2 截取

substring(strExp, fromIdx, length)
fromIdx : 起始位置，从1开始
length： 截取长度，（可以超过最大长度，不会报错）

-- 字符串转日期
select substring('This is a str', 1 , 2); # Th
select substring('This is a str', 6 , 10056); # is a str

2.3 字符位置

locate(target, strExp, fromIdx)
substring : 查找目标
fromIdx: 起始位置，从1开始

-- 字符串转日期
select locate('i','This is a str', 1); # 3   Th'i's is a str
select locate('i','This is a str', 4); # 6   This 'i's a str

3 JSON相关

3.1 JSON对象

CREATE TABLE student (
id int(11) NOT NULL,
info json NOT NULL, – 也可以用varchar
PRIMARY KEY (id)
);
insert into student values (1,‘{“id”:1,“name”:“张三”,“course”:[“数学”, “语文”]}’);
insert into student values (2,‘{“id”:2,“name”:“李四”,“course”:[“英语”, “语文”]}’);
insert into student values (3,‘{“id”:3,“name”:“王五”,“course”:[“英语”, “语文”],“other”:[{“case1”:1, “case2”:2},{“case2”:“3”}]}’);

3.1.1 验证JSON字符合法

select JSON_VALID('{"id": 123, "name": "张三"}');  # 1
-- 创建JSON数组
select JSON_VALID('not json');  # 0

3.1.1 JSON创建

-- 创建JSON对象
select JSON_OBJECT('id',123,'name','张三');  # {"id": 123, "name": "张三"}
-- 创建JSON数组
select JSON_ARRAY('1',2,JSON_OBJECT('id',1)); # ["1", 2, {"id": 1}]

3.2 JSON查询

3.2.1 属性提取

-- 提取属性
select JSON_EXTRACT(info, '$.name') from student where id = 1;  # "张三"
select info -> '$.name' from student where id = 1;  # "张三"
select info -> '$.other[*].case2' from student where id = 3;  # [2, "3"]

-- 数组元素提取
select info -> '$.course[*]' from student where id = 1;  # ["数学", "语文"]
select info -> '$.course[1]' from student where id = 1;  # "语文"

-- ->> 没有双引号
select info ->> '$.name' from student where id = 1; # 张三   
-- ->> 在where条件中使用不用强匹配类型
select count(1) from student where info -> '$.id' = 1;  # 1 
select count(1) from student where info -> '$.id' = '1';  # 0 
select count(1) from student where info ->> '$.id' = 1;  # 1 
select count(1) from student where info ->> '$.id' = '1';  # 1 

3.2.2 JSON判断

select info -> '$.other[*].case2' from student where id = 3;  # [2, "3"]

-- json路径是否存在
select id from student where json_contains_path(info, 'one', '$.id', '$.other'); # 1 2 3
select id from student where json_contains_path(info, 'all', '$.id', '$.other'); # 3

-- json_contains
select id from student where json_contains(info, '{"id":1}'); # 1
select id from student where json_contains(info -> '$.other[*].case2', '3'); # empty
select id from student where json_contains(info -> '$.other[*].case2', '2'); # 3
select id from student where json_contains(info -> '$.other[*].case2', '"3"'); #3

-- JSON类型
select json_type(info) from student where id = 1; -- OBJECT
select json_type(info -> '$.course') from student where id = 1; -- ARRAY
-- JSON深度
select json_depth(info) from student;  # 3 3 4
-- JSON长度
select json_length(info) from student; # 3 3 4

3.3 全表JSON数组去重

select distinct t.cour from student, 
JSON_TABLE(info, 
          '$.course[*]' columns (cour varchar(200) PATH '$')
          ) t;
# 数学 语文 英语

3.4 JSON数据修改 json_set

update student set info =  json_set(info, '$.name', '张三1')  where info -> '$.name' = '张三';