如何编写可重用的MySQL查询

本篇内容主要讲解“如何编写可重用的MySQL查询”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“如何编写可重用的MySQL查询”吧!

成都创新互联公司长期为数千家客户提供的网站建设服务，团队从业经验10年，关注不同地域、不同群体，并针对不同对象提供差异化的产品和服务；打造开放共赢平台，与合作伙伴共同营造健康的互联网生态环境。为东营企业提供专业的成都做网站、网站制作、成都外贸网站建设，东营网站改版等技术服务。拥有十年丰富建站经验和众多成功案例,为您定制开发。

广宗ssl适用于网站、小程序/APP、API接口等需要进行数据传输应用场景，ssl证书未来市场广阔！成为创新互联公司的ssl证书销售渠道，可以享受市场价格4-6折优惠！如果有意向欢迎电话联系或者加微信：18980820575（备注：SSL证书合作）期待与您的合作！

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

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

双击代码全选

1 2 3 4 5 6 7 8

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

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

双击代码全选

1 2 3 4 5 6 7 8 9

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;

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

双击代码全选

1 2 3

select * from   vusers uwhere  u.user_id = 100

二、关于MySQL内置函数
　GROUP_CONCAT()函数可以用来聚集表中的所有行，并返回组成交叉表水平轴的SELECT列表。实际上，这使得将交叉表的选择语句移植到存储 过程中成为可能。其他的函数，如Count()、Month()和MonthName()，以及过滤语句，如CASE WHEN ELSE，都可以让我们的代码更具通用性。
三、建立自己的函数
如果在MySQL内建的函数中没有我们所想要的，那么我们不妨自己动手，丰衣足食。 在编写自己的函数的时候，一定要考虑到该函数的通用性。
下面是一个示例函数，用来检查是否指定了强制性的存储过程参数。这里不允许使用空行或者空白符，所以该函数将进行相应的检查：

双击代码全选

1 2 3 4 5

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，我们很可能在这里找到所需的函数。

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

双击代码全选

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25

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_idGROUP BY e.id;  　　下面我们将其转变成一个存储过程：CREATE PROCEDURE `p_2010_bonuses_lst`() LANGUAGE SQL NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER BEGINSELECT 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_idGROUP BY e.id;     END;

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

双击代码全选

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

CREATE PROCEDURE `p_yearly_bonuses_lst`(IN `@year` INT) LANGUAGE SQL NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER BEGINSELECT 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_idGROUP BY e.id;     END;

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

双击代码全选

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

CREATE PROCEDURE `p_bonuses_lst`( IN `@StartDate` DATETIME,                                   IN `@EndDate`   DATETIME ) LANGUAGE SQL NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER BEGINSELECT 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_idGROUP BY e.id;     END;

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

到此，相信大家对“如何编写可重用的MySQL查询”有了更深的了解，不妨来实际操作一番吧！这里是创新互联网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！

文章名称：如何编写可重用的MySQL查询
浏览路径：http://mswzjz.cn/article/jjieig.html