解决 ThinkPHP5 中多表多字段查询问题(MySQL 视图应用以及介绍)

  • A+
所属分类:MySQL PHP ThinkPHP5

在日常开发中,我们经常会遇到如下问题:

在前端展示的列表中,往往包含多个表的数据且需要查询多个表的字段,进行筛选分页排序等问题,常见的方法是采用 join 方法或者 ThinkPHP 的关联模型、视图查询等。

以上解决问题中,存在如下问题:

前端传到后端的参数以 bootstrap-table 为例,常见的为: keywords、offset、limit、sort、order 等等,我们需要对这些参数进行组装,然后采取如上的查询方法进行数据查询。

然而对于数据查询,我们希望将其封装成固定的方法,传入参数即可。 这样做的好处是, 对于列表,可以用于前端展示、excel导出、其余方法调用等,且不受制于如上的参数。

对于以上的 join 方法等,对于多表中存在相同字段时,往往需要加入表前缀或表的别名,这对于封装的获取数据列表的方法很不友好,对于调用也不友善。

因此,我们采用 视图来解决以上问题。

MySQL 视图

视图本身是一个虚拟表,不存放任何数据。在使用 SQL 语句访问视图的时候,它返回的数据是 MySQL 从其他表中生成的。视图和表是在同一个命名空间,MySQL 在很多地方对于视图和表是同样对待的。不过视图和表也有不同,例如,不能对视图创建触发器,也不能使用 DROP TABLE 命令删除视图。

MySQL 视图创建

我们以 商品表 goods 和商品库存表 goods_stock 为例:

 CREATE VIEW goods_stock_view AS (           
      select g.goods_name, g.goods_sn, gs.* from goods as g , goods_stock as gs where g.goods_id = gs.goods_id
);

实现视图最简单的方法就是讲 SELECT 语句的结果存放到临时表。当需要访问视图的时候,直接访问这个临时表就可以了。

MySQL 视图查询

我们以上述视图为例:

select * from goods_stock_view where repertory_id = 1;

这样做会有明显的性能问题,优化器也很难在这个临时表上进行优化。视图视图的更好的方法是:重写含有视图的查询,将视图的定义 SQL 直接包含进查询的 SQL 中。下面展示将视图定义的 SQL 合并进查询 SQL 后的样子:

 CREATE VIEW goods_stock_view AS (            
        select g.goods_name, g.goods_sn, gs.* from goods as g , goods_stock as gs where g.goods_id = gs.goods_id and repertory_id = 2
);

MySQL 视图对性能的影响

多数人认为视图不能提升性能,实际上,在 MySQL 中 某些情况下视图也可以帮助提升性能。而且视图还可以和其他提升性能的方式叠加使用。例如: 在重构 schema 的时候,可以使用视图,使得在修改底层表结构的时候,应用代码还可以继续运行。

可以使用视图实现基于权限列的权限控制,却不需要真正在系统中创建权限,因此没有额外的开销。

也避免在业务代码中频繁的进行表连接、查询。

由以上介绍, 我们可以了解到,对于多表的多字段查询,我们恰当的使用视图和字段冗余,可以很好的解决代码的复用问题。

avatar

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: