sql–查询json、时间、字符串的高级用法

本文章总结sql的json、时间格式、字符串判断转换的使用。核心点还是在于json字段的提取(1.5)、时间的比较(2.2，2.3)以及字符串的查询(3.1)，其他都算是优化和补充点。

@

目录

1.sql–json使用

1.1.json_array–转成数组

1.2.json_object–转成json对象

1.2.json_quote–转成json

1.3.json_merge–合并json

1.4.json_valid–判断是否为json

1.5.json_extract–提取json摄影作品赏析(重点*)

1.6.json完整内容

2.sql–时间计算比较

2.1.时间–添加、减少

2.2时间格式转换

2.3统计本年/本月/本周/本日(重点*)

2.4 时间格式总结

3.sql–字符串

3.1.con初二数学知识点cat–模糊查询

3.2.locate–查找字符串

3.3.if、ca、round–优化显示与字段计算

1.sql–json使用

解释一下为什么sql要用到json存储和查询，因为很多时候前端展示内容或者后端存储无用字段过多，所以没必要后台新建太多字段。并且为了方便项目上线后快速排查问题反馈解决办法给用户，故而引入了json来存储和查询，实际开发中库表字段设计成json的情况也比较多。

​ 先给出基本建库查询操作

# sql--建库create table t_ba_info (    id bigint not null primary key auto_increment,    content json);# sql--入库inrt into t_ba_info (content)values(    '{    "author": "huyuqiao-a",    "blog": "https://www.cnblogs.com/meditation5201314/",    "content": [        {"name": "empirefree-01",        "age": 18        },        {"name": "empirefree-02",        "age": 19        },        {"name": "empirefree-03",        "age": 19        }    ]}');

1.1.json_array–转成数组

# sql--查询# json--对象转数组lect json_array(content) from t_ba_info

1.2.json_object–转成json对象

#json--对象转json格式--自定义lect json_object('name', 'huyuqiao', 'age', '18',  'profession','java开发工程师')

1.2.json_quote–转成json

#json--引用字符串作为json值lect json_quote('"huyuqiao"')

1.3.json_merge–合并json

#json--合并字符串lect json_merge('{"blog": "www.baidu鹤蚌相争.com"}', '{"name":"huyuqiao"}')

1.4.json_valid–判断是否为json

#json--判断json格式是否有效--0 表示无效，1 表示有效，null 表示参数为nulllect json_valid("huyuqiao"), json_valid('"huyuqiao"'), json_valid(null)

1.5.json_extract–提取json(重点*)

#json--查询json内容(重点)lectjson_extract(content, '$.blog' ) as '个人博客',json_extract(content, '$.content' ) as '个人信息' fromt_ba_info

1.6.json完整内容

​ 官方文档：
https://dev.mysql.com/doc/refman/5.7/en/json-function-reference.html

​ 官网上给的语句很多，我挑的是比较常用的几个，主要还是json_extract用的很常见

分类函数描述创建jsonjson_array创建json数组json_object创建json对象json_quote将json转成json字符串类型查询jsonjson_contains判断是否包含某个json值json_contains_path判断某个路径下是否包json值json_extract提取json值column->pathjson_extract的简洁写法，mysql 5.7.9开始支持column->>pathjson_unquote(column -> path)的简洁写法json_keys提取json中的键值为json数组json_arch按给定字符串关键字搜索json，返回匹配的路径修改jsonjson_append废弃，mysql 5.7.9开始改名为json_array_appendjson_array_append末尾添加数组元素，如果原有值是数值或json对象，则转成数组后，再添加元素json_array_inrt插入数组元素json_inrt插入值（插入新值，但不替换已经存在的旧值）json_merge合并json数组或对象json_remove删除json数据json_replace替换值（只替换已经存在的旧值）json_t设置值（替换旧值，并插入不存在的新值）json_unquote去除json字符串的引号，将值转成string类型返回json属性json_depth返回json文档的最大深度json_length返回json文档的长度json_type返回json值得类型json_valid判断是否为合法json文档

2.sql–时间计算比较

​ sql里面最常见的就是时间的查询比较了，下面列出自己平时用的比较多的sql时间比较

2.1.时间–添加、减少

#现在时间增加1分钟lect date_add(now(), interval 1 hour_minute)#现在时间减少1分钟lect date_sub(now(), interval 1 hour_minute)

2.2时间格式转换

#时间格式转换--年月日  时分秒lect date_format(now(), '%y-%m-%d')lect date_format(now(), '%y-%m-%d %h:%i:%s')

2.3统计本年/本月/本周/本日(重点*)

​ sql时间比较中最多的应该就是统计本年/本月/本周/本日的时间了

#统计本年/本月/本周/本日数据 #注：这里统计本周的时候多个1是因为美国时间和中国时间不是一样的，需要进行校正，网上很多都没有校正lectnow( ),to_days( now( ) ),yearweek( date_format( now( ), '%y-%m-%d' ), 1 ),year ( now( ) ) whereto_days( now( ) ) = to_days( now( ) ) or yearweek( date_format( now( ), '%y-%m-%d' ), 1 ) = yearweek( now( ), 1 ) or date_format( now( 竖琴好学吗), '%y%m' ) = date_format( curdate( ), '%y%m' ) or year ( now( ) ) = year ( now( ) )

2.4 时间格式总结

​ 注意：sql时间如下这种

year，month，day，hour，minute，cond，microcond

​ 而且类似这种interval ‘1 2 3 4’day_cond都是从右往左根据时间类型看，这种就是1天2小时3分钟4秒，比如如下这种

#比当前时间减少了1天2小时3分钟4秒lect now(), now() - interval '1 2 3 4' day_cond#比当前时间减少了1分钟lect now(), now() - interval '1' hour_minute

​ 下面是sql中时间的所有格式

microcondcondminutehourdayweekmonthquarteryearcond_microcondminute_microcondminute_condhour_microcondhour_condhour_minuteday_microcondday_condday_minuteday_houryear_month

3.sql–字符串

3.1.concat–模糊查询

lect now() where now() like concat('%', 'huyuqiao ','%')

3.2.locate–查找字符串

#子串在字符串中第一次出现索引(从1开始数，没找到则返回0)lect locate('qiao', 'huyuqiaoqiao')

3.3.if、ca、round–优化显示与字段计算

​ 下面这些是用于优化查询字段的，1可以改成table.column。

# 判断字段： round(number,保留几位有效数字，默认四舍五入)lectif( 1 = 1, '是', '否' ) as '状态',ca1 when 1 then'扣款' when 2 then'奖励' when 3 then'投诉' end 申请类型,c干瞪眼游戏oncat( ca 1 when 1 then '-' when 2 then '+' when 3 then '-' end, round( 200 / 100, 2 ) ) as 金额

书山有路勤为径，学海无涯苦作舟。程序员不仅要懂代码，更要懂生活，关注我，一起进步。