文章目录
- 案例1:MySQL树形结构查询
- 案例2:MySQL查询一段时间内的所有日期
摘要
案例1:MySQL树形结构查询
在页面开发过程中,如图一所示的树形控件很常见,而大多数情况下,树形控件中需要显示的数据事先在数据库中通过parent_id
的形式配置好,需要的时候再查询对应的数据,但此时所有的查询条件一般又只有一个最顶层的id
。
这时需要通过这个最顶层的id
获取到它的所有子级数据,这种查询在Oracle数据库中可以通过start with t.id=#{id} connect by prior t.id=t.parent_id
这种形式实现,MySQL8.1以上的版本也提供了这种形式,但在较低版本中,这种形式还不支持,需要通过其他的方法实现。
这种方法需要实现创建一个函数,用于获取到此顶层id所对应的所有子级数据的id,之后通过find_in_set()
函数查询所有数据
主要步骤如下:
- 新建函数,用户查询所有子级数据
drop function if exists get_children;
create function get_children(id varchar(64)) returns varchar(1000)
-- 获取所有子级数据的ID,逗号分隔
begin
declare res varchar(1000);
declare temp varchar(100);
set res=id;
select group_concat(test_id) into temp from test where find_in_set(test_id,id)>0;
while temp is not null do
set res=concat(res,',',temp);
select group_concat(test_id) into temp from test where find_in_set(test_id,temp)>0;
end while;
return res;
end;
- sql
select * from test where find_in_set(test_id,get_children('testId'));
案例2:MySQL查询一段时间内的所有日期
另一种比较常见的控件形式是统计图表,在统计图表中经常会遇到按时间统计数据的逻辑,如图二所示,而在数据库层面通常不是每天都有数据,这种情况下查询出的结果中间可能会中断。需要先获取到统计时间内的所有日期,再根据关联查询的形式获取到所有日期的的数据。
同样的,这种获取一段时间内所有日期的查询,在Oracle数据库中可以通过一行查询轻松实现,但在MySQL中没有内置这种功能。这里我们可以通过使用中间表的形式实现。
Oracle中的查询方式为:
select to_char(to_date('2019-01-01','yyyy-mm-dd')+level-1,'yyyy-mm-dd') temp_date from dual connect by to_date('2019-01-01','yyyy-mm-dd')+level-1 <= to_date('2019-02-01','yyyy-mm-dd');
主要步骤如下:
- 创建中间表,并按需求生成数据,如10000条
create table temp_calender(
id int auto_increment primary key
) comment '日期区间中间表';
drop procedure is exists add_calenders;
create procedure add_calenders() begin
declare temp int default 1;
while temp <= 10000 do
insert into temp_calender(id) values (temp);
set temp = temp + 1;
end while;
commit;
end;
call add_calenders;
- sql
select date_format(date_add('2018-01-01',interval id-1 day),'%Y-%m-%d') as temp_day from temp_calender where date_add('2019-01-01',interval id-1 day) <= '2019-02-01' order by temp_day;
在这种实现方式下,中间日期能生成最大的数量取决于中间表temp_calender中数据的数量(此例中为10000),虽然存在不足,但在大多数业务中已经够用了。