巧解如何编写可重用的MySQL查询

数据库 MySQL
MySQL查询在MySQL的应用中是很重要的,MySQL查询得到广泛应用,编写可重用的MySQL查询对一些初学者来说可能是比较困难的,下面就为大家介绍编写可重用的MySQL查询的方法。

导读:当人们提及可重用的MySQL查询的时候,立即映入脑海的往往就是存储过程了。虽然这些存储过程是编写可重用代码不可分割的一部分,但要记住的是,它们只是很少的一部分而已,而非全部。此外,其它可重用代码包括视图、内置函数以及用户定义的函数。在本文中,我们将向读者详细介绍如何编写可重用的MySQL查询,以令我们的选择语句可以更好的适用于各种查询,也可以将MySQL查询的工作做的更好。

一、关于视图
  视图的用途很多,例如简化复杂的模式及查询,或者提供安全性等等。视图提供安全性的一种途径是对开发者隐藏审计字段。视图还可通过减少列的数目来提高性能。这个想法是只引用索引字段,而索引字段的搜索速度是非常之快的。实际上,这种想法实现起来很费劲,因为你必须确保不会访问隐藏列。然而,我们这里主要是利用视图模拟两个或更多个表之间的连接,以降低查询的复杂性。很多时候,要想将数据库中用户的概要信息整理成符合第三范式的形式,可能需要多达六次连接操作,例如:select *
  from Users u

  inner join UserPhoneNumbers upn on u.user_id = upn.user_id

  inner join UserScreenNames usn on u.user_id = usn.user_id

  inner join UserAffiliations ua on u.user_id = ua.user_id

  inner join Affiliations a on a.affiliation_id = ua.affiliation_id

  inner join UserWorkHistory uwh on u.user_id = uwh.user_id

  inner join Affiliations wa on uwh.affiliation_id = wa.affiliation_id

下面,我们用一个视图来替换上面的查找过程:

CREATE VIEW `vusers` AS
  select *

  from Users u

  inner join UserPhoneNumbers upn on u.user_id = upn.user_id

  inner join UserScreenNames usn on u.user_id = usn.user_id

  inner join UserAffiliations ua on u.user_id = ua.user_id

  inner join Affiliations a on a.affiliation_id = ua.affiliation_id

  inner join UserWorkHistory uwh on u.user_id = uwh.user_id

  inner join Affiliations wa on uwh.affiliation_id = wa.affiliation_id;

现在,我们可以通过以下简单的选择语句来检索用户概要信息了:

select *
  from vusers u

  where u.user_id = 100

#p#

二、关于MySQL内置函数
  GROUP_CONCAT()函数可以用来聚集表中的所有行,并返回组成交叉表水平轴的SELECT列表。实际上,这使得将交叉表的选择语句移植到存储过程中成为可能。其他的函数,如Count()、Month()和MonthName(),以及过滤语句,如CASE WHEN ELSE,都可以让我们的代码更具通用性。

三、建立自己的函数
  如果在MySQL内建的函数中没有我们所想要的,那么我们不妨自己动手,丰衣足食。 在编写自己的函数的时候,一定要考虑到该函数的通用性。

  下面是一个示例函数,用来检查是否指定了强制性的存储过程参数。这里不允许使用空行或者空白符,所以该函数将进行相应的检查:

BEGIN
  DECLARE isEmpty TINYINT;

  SET isEmpty = (param_name IS NULL or char_length(trim(param_name)) = 0);

  RETURN isEmpty;

  END


注意,在我们的函数中调用了内建的两个函数,即char_length()和trim()。现在,我们总能够将其作为一个通用函数使用了。

  需要提示的是,在我们编写自己的函数之前,***先在网上搜索一下,看看别人是否已经做过这项工作了,免得重复相同的工作。这时,我们要特别留意那些MySQL函数仓库站点,如www.mysqludf.org,我们很可能在这里找到所需的函数。

#p#

四、存储过程
  我们知道,存储过程能够起到代码模块化和集中化的作用。然而,将SQL代码放入存储过程本身并不意味着就能提高通用性或者可重用性。举例来说,下面的语句将生成一份反映各员工去年奖金总数的报告:

SELECT e.name,
  e.salary,

  COUNT(b.bonus_id) AS "Total Bonuses"

  FROM employees e

  LEFT OUTER JOIN

  (SELECT emp_id, bonus_id FROM bonuses WHERE YEAR(award_date) = 2010) AS b

  ON e.id = b.emp_id

  GROUP BY e.id;

下面我们将其转变成一个存储过程:

CREATE PROCEDURE `p_2010_bonuses_lst`()
  LANGUAGE SQL

  NOT DETERMINISTIC

  CONTAINS SQL

  SQL SECURITY DEFINER

  BEGIN

  SELECT e.name,

  e.salary,

  COUNT(b.bonus_id) AS "Total Bonuses"

  FROM employees e

  LEFT OUTER JOIN

  (SELECT emp_id, bonus_id FROM bonuses WHERE YEAR(award_date) = 2010) AS b

  ON e.id = b.emp_id

  GROUP BY e.id;

  END;

  现在,其他人或程序就可以方便的使用这个过程了,不过这里有个时间限制,就是只能在明年之前使用。但是,我们为什么要创建这种有限制的东西呢?因为,我们每年都可能需要生成相似的报告,所以下面我们要去掉这个时间限制。

为此,我们将该过程中的硬编码的日期删除掉,如下所示:

CREATE PROCEDURE `p_yearly_bonuses_lst`(IN `@year` INT)
  LANGUAGE SQL

  NOT DETERMINISTIC

  CONTAINS SQL

  SQL SECURITY DEFINER

  BEGIN

  SELECT e.name,

  e.salary,

  COUNT(b.bonus_id) AS "Total Bonuses"

  FROM employees e

  LEFT OUTER JOIN

  (SELECT emp_id, bonus_id FROM bonuses WHERE YEAR(award_date) = @year) AS b

  ON e.id = b.emp_id

  GROUP BY e.id;

  END;


  作为一名有上进心的开发人员,我们会自问是否可以做得更好呢?客户程序也许对起始日期和结束日期方面有更高的灵活性要求,比如他们可能要求日期范围与财政年度一致。从这方面考虑,不管客户程序是否要求,我们的都必须提供一个起始日期和终止日期参数。 MySQL有一个非常不错的BETWEEN运算符,可以用来处理某个范围内的值。

下面我们就将其用于起始日期和终止日期:

CREATE PROCEDURE `p_bonuses_lst`( IN `@StartDate` DATETIME,
  IN `@EndDate` DATETIME )

  LANGUAGE SQL

  NOT DETERMINISTIC

  CONTAINS SQL

  SQL SECURITY DEFINER

  BEGIN

  SELECT e.name,

  e.salary,

  COUNT(b.bonus_id) AS "Total Bonuses"

  FROM employees e

  LEFT OUTER JOIN

  (SELECT emp_id,

  bonus_id

  FROM bonuses

  WHERE award_date Between @StartDate AND @EndDate) AS b

  ON e.id = b.emp_id

  GROUP BY e.id;

  END;

五、小结
  在本文中,我们讨论了如何利用视图、内建函数和用户定义函数以及存储过程来提高SELECT查询的通用性和可重用性。为了便于理解,我们还给出了一些实例代码,以便帮助读者理解本文讲到的内容。根据局部性原理,现在执行的操作,近期内很可能会再次执行该操作,所以提高可重用性是非常有帮助的。希望大家通过这篇文章的阅读,能够掌握如何编写可重用的MySQL查询的方法,为以后的工作能有所帮助。

【编辑推荐】

  1. MYSQL查询重复记录的方法
  2. MySQL查询分页的优化
  3. 教您如何设计高效合理的MySQL查询语句
责任编辑:迎迎 来源: IT168
相关推荐

2024-01-03 08:00:00

Java软件开发代码

2018-11-08 15:50:18

前端Javascript重用性

2009-08-16 16:11:05

2013-04-15 09:02:43

JavaScriptJS

2009-11-26 14:40:51

无线路由器

2010-10-08 15:53:42

2010-09-17 13:45:40

JVM termina

2020-06-02 09:06:31

VueTransition前端

2011-07-21 13:24:14

java

2023-11-08 13:55:27

2010-09-27 10:20:09

JVMLinux

2010-09-26 15:53:25

JVM内存溢出

2009-08-11 09:16:00

2017-09-07 16:20:39

SQL查询语句查询优化

2010-06-09 09:15:58

JSF 2Ajax组件

2023-11-30 09:36:39

2013-04-23 09:14:22

可重用云工具Java安全框架云中间件

2016-11-30 18:35:03

JavaScript

2013-04-23 09:24:51

2010-12-24 15:38:53

C#单例模式
点赞
收藏

51CTO技术栈公众号